MySQL UPDATE时主键冲突引发的思考

欢迎您访问我爱IT技术网，今天小编为你分享的编程技术是：【MySQL UPDATE时主键冲突引发的思考】，下面是详细的分享！

MySQL UPDATE时主键冲突引发的思考

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

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

　　清空后，自己重新手工插入记录，再看看。

mysql> INSERT INTO `a` VALUES (2,2),(3,3),(5,5),(4,4),(6,6),(7,7); Query OK, 6 rows affected (0.00 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 的顺序来显示，然后我们再次执行之前的 update 语句：

mysql> update a set id=id - 1; Query OK, 6 rows affected (0.00 sec) Rows 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 UPDATE时主键冲突引发的思考，下面是编辑为你推荐的有价值的用户互动：

　　相关问题：MYSQL 一个主键冲突的问题

　　答：有符号的范围是-8388608到8388607 数据库id超出范围就乱来了。 >>详细

　　相关问题：JAVA捕获MYSQL主键冲突异常 示例如Exception ex 对...

　　答：在需要捕获异常的语句前后加上try块，比如 try{ //需要捕获异常的语句 }catch(Exception ex){ ex.printStackTrace(); } 希望我的解答对你有所帮助 >>详细

　　相关问题：mysql 存储过程 若主键冲突则更新，不冲突则插入数据

　　答：你可以分两部份导入 先导入在表里有IDDATE的更新部份 mysql> UPDATE 表1 SET 列名=(SELECT 列名 FROM 表2 WHERE 表1.IDDATE=表2.IDDATE) WHERE IDDATE in (SELECT IDDATE FROM 表2); 再导入插入部份 INSERT INTO 表1 (SELECT IDDATE,列名 FROM ... >>详细