MySql语句准备“不粘"； [英] MySql statement prepare "not sticking"

问题描述

我正在尝试使用准备好的语句，尽管MySqlCommand执行得很好，但执行时间却很糟糕.我让它将cmd.IsPresed的结果写入控制台，而且可以肯定的是，它是错误的.这是我设置MySqlCommand的地方:

I'm attempting to use a prepared statement, and while the MySqlCommand executes just fine, the execution time is abysmal. I had it write the result of cmd.IsPrepared to the console, and sure enough, it is false. Here is where I setup the MySqlCommand:

MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = @"INSERT INTO dict (pre, dist, dict.char, score) VALUES(@pre, @dist, @char, @score) ON DUPLICATE KEY UPDATE score = score + @score";
cmd.Parameters.Add("@pre", MySqlDbType.VarChar, 32);
cmd.Parameters.Add("@dist", MySqlDbType.Int32);
cmd.Parameters.Add("@char", MySqlDbType.VarChar, 1);
cmd.Parameters.Add("@score", MySqlDbType.Double);
cmd.Prepare();

我还尝试过执行Prepare()，然后再添加具有相同结果的参数.

I've also tried executing the Prepare() before adding parameters with the same result.

然后我有一个代码循环，进行一些计算并设置变量，如下所示:

I then have a loop of code that does some computation and sets variables like so:

cmd.Parameters[3].Value = score;

...并且在运行该命令之前，对该命令不执行任何其他操作:

...and does nothing else to the command until it comes time to run:

Console.WriteLine(cmd.IsPrepared);
cmd.ExecuteNonQuery();

控制台的结果始终为false.这些都是在基本事务中完成的，但似乎并没有使事情变得混乱.不过，我确实在设置MySqlCommand之前就打开了事务.

The result to the console is always false. This is all done within a basic transaction, but that doesn't seem like it should mess things up. I do open the transaction before I setup the MySqlCommand, though.

关于哪里出了问题的任何想法?

Any ideas as to where this is going wrong?

edit:我在Java中复制了代码，并且准备好的语句在其中可以正常工作.因此，这不是我的数据库服务器本身的问题，特别是.net中的问题.当然，.net/connector并没有为每个人所破坏，那么这里可能会发生什么呢?

edit: I replicated the code in java, and the prepared statements work fine in it. So it's not a problem with my database server itself, it is specifically a problem in .net. Surely the .net/connector isn't broken for everyone, so what could possibly be the deal here?

它肯定没有准备好，并且根本没有设置bool值，.net在某些测试输入中的运行时间是如此之长，我没有耐心等待它，但是在java中，相同的输入会运行在约3分钟内两者都使用基本相同的代码.

And it definitely isn't prepared and simply not setting that bool value, the running time in .net for some test input is so long I don't have the patience to wait it out, but in java the same input runs in ~3 minutes. Both use basically the same code.

这是我在.net中进行的一个简单测试，因此您可以看到我正在尝试的完整代码(我从连接字符串中删除了UID和密码，但是在正常的代码中，它们存在于其中建立，并且该语句将数据输入数据库):

Here's a simple test I did in .net, so you can see the full code of what I'm trying (I removed the UID and password from the connection string, but in the normal code they are there, a connection is established, and the statement enters data into the database):

        using (MySqlConnection con = new MySqlConnection(@"SERVER=localhost;DATABASE=rb;UID=;PASSWORD=;"))
        {
            con.Open();

            using (MySqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = @"INSERT INTO test (test.test) VALUES(?asdf)";
                cmd.Prepare(); //doesn't work

                cmd.Parameters.AddWithValue("?asdf", 1);

                cmd.ExecuteNonQuery();
            }
        }

在c#2010中，我将MySql.Data.dll版本6.4.4.0与运行时版本v4.0.30319一起使用.上面的示例代码中还包括MySql.Data.MySqlClient.

I'm using MySql.Data.dll version 6.4.4.0 with a runtime version of v4.0.30319 in c# 2010. I'm also including MySql.Data.MySqlClient for the above example code.

推荐答案

至少在第二个示例中，您似乎正在对MySqlCommand对象进行使用.为了获得准备好的语句的任何好处，您无需处置连接和命令对象.在设置CommandText之后，再设置任何参数之前，您还需要调用prepare.

It looks like you're doing a using on the MySqlCommand object, at least in your second example. To get any benefit of prepared statements, you would need to not dispose of the connection and command objects. You also will want to call prepare after setting CommandText and before setting any parameters.

https://dev .mysql.com/doc/connector-net/zh/connector-net-programming-prepared-preparing.html

MySQL中也没有指定准备好的语句参数，它们是基于顺序指定的. CommandText应该只在参数的位置包含问号，并且参数对象必须按照该顺序完全添加.

Also prepared statement parameters in MySQL aren't named, they're specified based on order. The CommandText should just contain question marks where the parameters are, and the parameter objects need to be added in exactly that order.

这篇关于MySql语句准备“不粘"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！