MySQL UPDATE时主键冲突引发的思考
作者:叶金荣 来源:IT专家网 时间:2008-06-21 17:00:00
假设有一个表,结构如下:
mysql> CREATE TABLE `a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id2` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
该表中只有6条记录,如下:
mysql> select * from a;
+----+---------+
| id | city_id |
+----+---------+
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 4 | 4 |
| 6 | 6 |
| 7 | 7 |
+----+---------+
现在想要把id字段分别-1,执行以下语句,得到报错:
mysql> update a set id=id-1;
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
看看更新后的结果,可以看到:
mysql> select * from a;
+----+---------+
| id | city_id |
+----+---------+
| 1 | 2 |
| 2 | 3 |
| 5 | 5 |
| 4 | 4 |
| 6 | 6 |
| 7 | 7 |
+----+---------+
存储在最前面的2条记录更新成功了,后面的则失败,因为第三条记录如果也要更新,则会引发主键冲突。
这个时候,如果我们在更新时增加 ORDER BY 的话,则可以顺利更新成功。
mysql> update a set id=id-1 order by id;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
接下来,我们看看把它转成 innodb 表,结果会是怎样的。
mysql> alter table a engine = innodb;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from a;
+----+---------+
| id | city_id |
+----+---------+
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+----+---------+
看到变化了吧,行数据按照 id 的顺序来显示了。