C#MYSQL-重复键更新-相同查询不同结果 [英] C# MYSQL - ON DUPLICATE KEY UPDATE - Same Query Different Results

问题描述

在我的测试应用中；当我重复运行此查询时，即使没有任何变化，我也会得到正的行数作为结果.

In my test app; I get positive row count as result when I run this query repeatedly, even when there is no change.

insert into  test (k,v) values ('testkey',123) 
on duplicate key update v=values(v);

该查询在MySQL控制台和MySQL Workbench中按预期工作.对于第一次插入，我得到1，对于后续调用，我得到0.

The query works as expected in MySQL Console and MySQL Workbench. I get 1 for the first insert and 0 for the successive calls.

但是，当我尝试使用C#测试应用程序时，即使行中没有任何变化，我也总是得到2.

However when I try in my C# test app I always get 2 even when nothing changes inside the row.

有什么想法吗?建立连接时我丢失了什么东西吗?

Any ideas? Am I missing something while setting up the connection?

这是我的测试表:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` varchar(45) DEFAULT NULL,
  `v` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `icol_UNIQUE` (`k`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

这是我很小的测试代码:

And this is my tiny test code:

using (var con = new MySqlConnection("Database=mydb;Data Source=localhost;User Id=root;Password=mypass;CharSet=utf8;"))
    {
        con.Open();
        var cmd = new MySqlCommand("insert into  test (k,v) values ('testkey',123) on duplicate key update v=values(v)", con);
        var rows = cmd.ExecuteNonQuery();

        Console.WriteLine(rows); // rows is always 2
    }

注意:

• 我正在使用MySql .Net Connector 6.9.8
• 所有数据库字符集/排序规则 设置是utf8

• I am using MySql .Net Connector 6.9.8
• All database charset/collation settings are utf8

推荐答案

阅读下面的手册后，终于找到了解决方案 https://downloads.mysql.com/docs/connector-net-en. pdf

Finally found the solution after reading the manual below https://downloads.mysql.com/docs/connector-net-en.pdf

有一个名为使用受影响的行"，"UseAffectedRows"的连接选项

There is a connection option called Use Affected Rows, UseAffectedRows

其默认值设置为false.

Its default value is set to false.

描述如下:

为true时，连接报告更改的行而不是找到的行. 此选项是在Connector/Net版本5.2.6中添加的.

When true, the connection reports changed rows instead of found rows. This option was added in Connector/Net version 5.2.6.

使用时；连接到服务器时未设置CLIENT_FOUND_ROWS标志

When used; Doesn't set the CLIENT_FOUND_ROWS flag when connecting to the server

在连接期间，MySQL Workbench和MySQL命令行似乎没有使用CLIENT_FOUND_ROWS标志，而MySql .Net Connector默认使用它.

It seems like MySQL Workbench and MySQL Command Line don't use the CLIENT_FOUND_ROWS flag during connection, where the MySql .Net Connector uses it by default.

从INSERT ... ON DUPLICATE KEY UPDATE文档位于 https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

From INSERT ... ON DUPLICATE KEY UPDATE document at https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

使用ON DUPLICATE KEY UPDATE，如果满足以下条件，则每行的受影响行值为1 该行将作为新行插入；如果已更新现有行，则插入2；以及 如果将现有行设置为其当前值，则为0.如果您指定 连接到时将CLIENT_FOUND_ROWS标志设置为mysql_real_connect() mysqld，如果设置了现有行，则受影响的行值为1(而不是0) 还原为当前值.

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

解决方案: 将此添加到连接字符串

Solution: Add this to the connection string

Use Affected Rows=true

这篇关于C#MYSQL-重复键更新-相同查询不同结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！