假设有一个表,结构如下:
root@localhost : yayun 22:59:43> create table t1 ( -> id int unsigned not null auto_increment, -> id2 int unsigned not null default ‘0‘, -> primary key (id) -> )engine=myisam; Query OK, 0 rows affected (0.00 sec) root@localhost : yayun 23:01:00>
改表中有6条记录,如下:
root@localhost : yayun 23:03:35> select * from t1; +----+-----+ | id | id2 | +----+-----+ | 2 | 2 | | 3 | 3 | | 5 | 5 | | 4 | 4 | | 6 | 6 | | 7 | 7 | +----+-----+ 6 rows in set (0.00 sec) root@localhost : yayun 23:03:42>
现在想要把id字段分别-1,执行以下语句,得到报错:
root@localhost : yayun 23:03:42> update t1 set id=id - 1; ERROR 1062 (23000): Duplicate entry ‘4‘ for key ‘PRIMARY‘ root@localhost : yayun 23:04:59>
看看更新后的结果,可以看到:
root@localhost : yayun 23:05:58> select * from t1; +----+-----+ | id | id2 | +----+-----+ | 1 | 2 | | 2 | 3 | | 5 | 5 | | 4 | 4 | | 6 | 6 | | 7 | 7 | +----+-----+ 6 rows in set (0.00 sec) root@localhost : yayun 23:06:05>
存储在最前面的2条记录更新成功了,后面的则失败,因为第三条记录如果也要更新,则会引发主键冲突。
这个时候,如果我们在更新时增加 ORDER BY
的话,则可以顺利更新成功。
root@localhost : yayun 23:07:52> update t1 set id=id-1 order by id; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0 root@localhost : yayun 23:08:20>
接下来,我们看看把它转成 innodb 表,结果会是怎样的。
root@localhost : yayun 23:09:21> alter table t1 engine = innodb; Query OK, 6 rows affected (0.08 sec) Records: 6 Duplicates: 0 Warnings: 0 root@localhost : yayun 23:09:23>
root@localhost : yayun 23:10:15> select * from t1; +----+-----+ | id | id2 | +----+-----+ | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | +----+-----+ 6 rows in set (0.00 sec) root@localhost : yayun 23:10:19>
看到变化了吧,行数据按照 id 的顺序来显示了。清空后,自己重新手工插入记录,再看看。
root@localhost : yayun 23:11:05> delete from t1; Query OK, 6 rows affected (0.00 sec) root@localhost : yayun 23:11:28> INSERT INTO `t1` VALUES (2,2),(3,3),(5,5),(4,4),(6,6),(7,7); Query OK, 6 rows affected (0.09 sec) Records: 6 Duplicates: 0 Warnings: 0 root@localhost : yayun 23:11:33>
还是按照 id 的顺序来显示,然后我们再次执行之前的 update 语句:
root@localhost : yayun 23:12:03> update t1 set id = id - 1; Query OK, 6 rows affected (0.03 sec) Rows matched: 6 Changed: 6 Warnings: 0 root@localhost : yayun 23:12:29>
可以看到,在 innodb 表的情况下,更新是可以成功的。
总结:
myisam表是堆组织表(Heap
Organize Table, HOT),它的索引是采用 b-tree
方式存储的,数据显示时是随机顺序,而非按照主键的索引顺序来显示。
而innodb表是索引组织表(Index Organized Table,
IOT),它的索引则是采用 clustered index 方式,因此主键会按照顺序存储,每次有记录有更新时,会重新整理更新其主键。因此无论是直接从
myisam 表转换过来的,还是后来插入的记录,显示时都会按照主键的顺序。
更新数据时,如果没有指定排序的字段或索引,则默认以随机顺序更新,所以
myisam 表如果不指定 ORDER BY 的话,则采用默认的存储顺序来更新,所以会发生主键冲突的情况。
而 innodb
表总是有主键(如果没有定义,则也有默认主键),如果更新时没有指定排序字段或索引,则按照主键顺序来更新,在上面的例子中,就是按照主键 id
的顺序来更新了,因此不会报错。
原文地址
http://imysql.com/2008_06_17_sth_about_update_duplicate_key
UPDATE 时主键冲突引发的思考【转】,布布扣,bubuko.com
原文:http://www.cnblogs.com/gomysql/p/3768965.html