繁體中文当前位置: 首页 > 图文教程 > 数据库 > MYSQL > MySQL UPDATE时主键冲突引发的思考
- MYSQL
- Linux环境MySQL服务器级优化讲解
- MySQL在命名用过程中所遇到的常见问题
- MySQL数据库只监听某个特定地址的方法
- MySQL数据库管理必备工具 phpMyAdmin 3.0
- MySQL中两种快速创建空表的方式的区别
- 讲解MySQL数据库的数据类型和建库策略
- 请正确认识MySQL对服务器端光标的限制
- 如何在MySQL查询结果集中得到记录行号
- 在SUSE10环境下安装和配置MySQL数据库
- 由浅入深讲解MySQL数据库索引的选择性
- 轻松掌握MySQL数据库锁机制的相关原理
- 一种特别简单的MySQL数据库安装方法
- 实例讲解如何配置MySQL数据库主从复制
- MySQL数据库中的重要数据应当如何保护
- 详细讲解MySQL数据库对文件操作的封装
- 深入了解MySQL的数据类型以及建库策略
- 解决MySQL数据库中与优化器有关的问题
- 向MySQL数据库的表中录入数据的实用方法
- MySQL数据库中的各种乱码及其解决方法
- 快速掌握怎样选择准备安装的 MySQL版本
MYSQL 中的 MySQL UPDATE时主键冲突引发的思考
出处:互联网 整理: 软晨网（RuanChen.com） 发布: 2009-10-17 浏览: 143 ::
收藏到网摘: n/a
假设有一个表，结构如下：
该表中只有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); mysql> select * from a; |
还是按照 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 的顺序来更新了，因此不会报错。
评论 (0) All
