[分享]UPDATE 时主键冲突引发的思考_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1755 | 回复: 0   主题: [分享]UPDATE 时主键冲突引发的思考        下一篇 
tk
注册用户
等级:中士
经验:207
发帖:80
精华:0
注册:2011-10-19
状态:离线
发送短消息息给tk 加好友    发送短消息息给tk 发消息
发表于: IP:您无权察看 2014-12-22 15:30:06 | [全部帖] [楼主帖] 楼主

假设有一个表,结构如下:

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 的顺序来更新了,因此不会报错。

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论