UPDATE时主键冲突引发的思考_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了UPDATE时主键冲突引发的思考_MySQL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2744字,纯文字阅读大概需要4分钟。
内容图文
假设有一个表,结构如下: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 的顺序来更新了,因此不会报错
内容总结
以上是互联网集市为您收集整理的UPDATE时主键冲突引发的思考_MySQL全部内容,希望文章能够帮你解决UPDATE时主键冲突引发的思考_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。