MySQL INSERT/INSERT … ON DUPLICATE KEY UPDATE Affected Rows 问题

问题描述

在INSERT/INSERT ... ON DUPLICATE KEY UPDATE操作时, 会存在MySQL连接client_flag是否设置CLIENT_FOUND_ROWS问题； CLIENT_FOUND_ROWS 表示返回匹配到的行数，而不是更改的行数；

默认连接

默认连接时没有设置的，比如MySQL客户端

1
2
3
4
5
6

CREATE TABLE `test_table` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(10) NOT NULL
 ) COMMENT='test' ENGINE='InnoDB';

INSERT INTO `test_table` (`id`, `name`) VALUES ('1', 'Jane');

1
2
3
4

UPDATE test_table SET name = 'Tom' WHERE id = 1;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

1
2
3
4

UPDATE test_table SET name = 'Tom' WHERE id = 1;

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

当重复更新返回结果中 affected rows 是0

设置连接

1
2
3
4

UPDATE test_table SET name = 'Tom' WHERE id = 1;

Query OK, 1 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

当连接时client_flag参数设置了CLIENT_FOUND_ROWS时重复更新返回结果中 affected rows 是1

matched信息是通过info字段返回,和错误信息一样

affected_rows: 1
last_insert_id: 0
status_flag: 2
warnings: 0
info: '(Rows matched: 1  Changed: 0  Warnings: 0'

官方描述

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.

CLIENT_FOUND_ROWS: Return the number of found (matched) rows, not the number of changed rows.

总结

在使用客户端orm框架或者MySQL中间件时需要注意连接是否传递CLIENT_FOUND_ROWS;

参考