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 的顺序来显示了。