UPDATE 时主键冲突引发的思考

作/译者：叶金荣（Email: ），来源：http://imysql.cn，转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

假设有一个表，结构如下：

1mysql> CREATE TABLE `a` (
2  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3  `id2` int(10) unsigned NOT NULL DEFAULT '0',
4  PRIMARY KEY (`id`)
5) ENGINE=MyISAM;

该表中只有6条记录，如下：

1mysql> select * from a;
2+----+---------+
3| id | city_id |
4+----+---------+
5|  2 |       2 |
6|  3 |       3 |
7|  5 |       5 |
8|  4 |       4 |
9|  6 |       6 |
10|  7 |       7 |
11+----+---------+

现在想要把id字段分别-1，执行以下语句，得到报错：

1mysql> update a set id=id-1;
2ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

看看更新后的结果，可以看到：

1mysql> select * from a;
2+----+---------+
3| id | city_id |
4+----+---------+
5|  1 |       2 |
6|  2 |       3 |
7|  5 |       5 |
8|  4 |       4 |
9|  6 |       6 |
10|  7 |       7 |
11+----+---------+

存储在最前面的2条记录更新成功了，后面的则失败，因为第三条记录如果也要更新，则会引发主键冲突。

这个时候，如果我们在更新时增加 ORDER BY 的话，则可以顺利更新成功。

1mysql> update a set id=id-1 order by id;
2Query OK, 6 rows affected (0.00 sec)
3Rows matched: 6  Changed: 6  Warnings: 0

接下来，我们看看把它转成 innodb 表，结果会是怎样的。

1mysql> alter table a engine = innodb;
2Query OK, 6 rows affected (0.01 sec)
3Records: 6  Duplicates: 0  Warnings: 0
4
5 
6mysql> select * from a;
7+----+---------+
8| id | city_id |
9+----+---------+
10|  2 |       2 |
11|  3 |       3 |
12|  4 |       4 |
13|  5 |       5 |
14|  6 |       6 |
15|  7 |       7 |
16+----+---------+

看到变化了吧，行数据按照 id 的顺序来显示了。
清空后，自己重新手工插入记录，再看看。

1mysql> INSERT INTO `a` VALUES (2,2),(3,3),(5,5),(4,4),(6,6),(7,7);
2Query OK, 6 rows affected (0.00 sec)
3Records: 6  Duplicates: 0  Warnings: 0
4mysql> select * from a;
5+----+---------+
6| id | city_id |
7+----+---------+
8|  2 |       2 |
9|  3 |       3 |
10|  4 |       4 |
11|  5 |       5 |
12|  6 |       6 |
13|  7 |       7 |
14+----+---------+

还是按照 id 的顺序来显示，然后我们再次执行之前的 update 语句：

1mysql> update a set id = id - 1;
2Query OK, 6 rows affected (0.00 sec)
3Rows matched: 6  Changed: 6  Warnings: 0

可以看到，在 innodb 表的情况下，更新是可以成功的。

现在我们来分析下。
myisam表是堆组织表(Heap Organize Table, HOT)，它的索引是采用 b-tree 方式存储的，数据显示时是随机顺序，而非按照主键的索引顺序来显示。
而innodb表是索引组织表(Index Organized Table, IOT)，它的索引则是采用 clustered index 方式，因此主键会按照顺序存储，每次有记录有更新时，会重新整理更新其主键。因此无论是直接从 myisam 表转换过来的，还是后来插入的记录，显示时都会按照主键的顺序。
更新数据时，如果没有指定排序的字段或索引，则默认以随机顺序更新，所以 myisam 表如果不指定 ORDER BY 的话，则采用默认的存储顺序来更新，所以会发生主键冲突的情况。
而 innodb 表总是有主键(如果没有定义，则也有默认主键)，如果更新时没有指定排序字段或索引，则按照主键顺序来更新，在上面的例子中，就是按照主键 id 的顺序来更新了，因此不会报错。

本文出自 “MySQL中文网”博客 http://www.imysql.cn/

本文出自 “老叶茶馆” 博客，转载请与作者联系！

UPDATE 时主键冲突引发的思考

评论区

相关推荐