试了一下,的确如此。估计是mysql在UPDATE自己做了个对比,如果new的值和old 的值相同就不更新了。这个猜测需要看一下源代码来确认了。 这个问题有些严重了,至少导致 ROW_COUNT() 为0mysql> update a set id=id; Query OK, 0 rows affected (0.06 sec) Rows matched: 9 Changed: 0 Warnings: 0mysql> update a set id=id+0; Query OK, 0 rows affected (0.02 sec) Rows matched: 9 Changed: 0 Warnings: 0mysql> update a set id=id+1-1; Query OK, 0 rows affected (0.03 sec) Rows matched: 9 Changed: 0 Warnings: 0mysql> update a set id=concat(0,id); Query OK, 0 rows affected (0.00 sec) Rows matched: 9 Changed: 0 Warnings: 0mysql> update a set id=1 where id=1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0mysql>
分别用UPDATE、REPLACE测试了一下 UPDATE TT SET ID=ID (0 row(s) affected) Execution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000REPLACE TT SET ID=ID (1 row(s) affected) Execution Time : 00:00:00:047 Transfer Time : 00:00:00:000 Total Time : 00:00:00:047
mi_update.c, 主程序中会调用这个mi_update() int mi_update(register MI_INFO *info, const uchar *oldrec, uchar *newrec)my_base.h ,关于HA_ERR_RECORD_IS_THE_SAME返回值的说明。 /* row not actually updated: new values same as the old values */ #define HA_ERR_RECORD_IS_THE_SAME 169
试了一下其它数据库中,都没有这样现象。 看来以后这个 row_affected 用起来要小心了。sql server 5> select * from a; 6> go(1 rows affected) id |col -----------|----------- 1| 1 2| 2 3| 3(3 rows affected) 1> update a set id=id; 2> go(3 rows affected) 1>oracle SQL> select * from a; ID COL ---------- ---------- 1 1 2 2 3 3SQL> update a set id=id;3 rows updated.SQL>
Query OK, 0 rows affected (0.06 sec)
Rows matched: 9 Changed: 0 Warnings: 0mysql> update a set id=id+0;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 9 Changed: 0 Warnings: 0mysql> update a set id=id+1-1;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 9 Changed: 0 Warnings: 0mysql> update a set id=concat(0,id);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 9 Changed: 0 Warnings: 0mysql> update a set id=1 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0mysql>
UPDATE TT SET ID=ID
(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000REPLACE TT SET ID=ID
(1 row(s) affected)
Execution Time : 00:00:00:047
Transfer Time : 00:00:00:000
Total Time : 00:00:00:047
REPLACE显示有一条记录,不管表中有多少条记录
error= table->file->ha_update_row(table->record[1],
table->record[0]); if (!error || error == HA_ERR_RECORD_IS_THE_SAME)
{
if (error != HA_ERR_RECORD_IS_THE_SAME)
updated++;
else
error= 0;
}
mi_update.c, 主程序中会调用这个mi_update()
int mi_update(register MI_INFO *info, const uchar *oldrec, uchar *newrec)my_base.h ,关于HA_ERR_RECORD_IS_THE_SAME返回值的说明。
/* row not actually updated: new values same as the old values */
#define HA_ERR_RECORD_IS_THE_SAME 169
5> select * from a;
6> go(1 rows affected)
id |col
-----------|-----------
1| 1
2| 2
3| 3(3 rows affected)
1> update a set id=id;
2> go(3 rows affected)
1>oracle
SQL> select * from a; ID COL
---------- ----------
1 1
2 2
3 3SQL> update a set id=id;3 rows updated.SQL>