mysql update主键冲突_UPDATE 时主键冲突引发的思考【转】

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

root@localhost : yayun 22:59:43> create tablet1 (-> id int unsigned not nullauto_increment,-> id2 int unsigned not null default '0',-> primary key(id)-> )engine=myisam;

Query OK,0 rows affected (0.00sec)

root@localhost : yayun 23:01:00>

改表中有6条记录，如下：

root@localhost : yayun 23:03:35> select * fromt1;+----+-----+

| id | id2 |

+----+-----+

| 2 | 2 |

| 3 | 3 |

| 5 | 5 |

| 4 | 4 |

| 6 | 6 |

| 7 | 7 |

+----+-----+

6 rows in set (0.00sec)

root@localhost : yayun 23:03:42>

现在想要把id字段分别-1，执行以下语句，得到报错：

root@localhost : yayun 23:03:42> update t1 set id=id - 1;

ERROR1062 (23000): Duplicate entry '4' for key 'PRIMARY'root@localhost : yayun 23:04:59>

看看更新后的结果，可以看到：

root@localhost : yayun 23:05:58> select * fromt1;+----+-----+

| id | id2 |

+----+-----+

| 1 | 2 |

| 2 | 3 |

| 5 | 5 |

| 4 | 4 |

| 6 | 6 |

| 7 | 7 |

+----+-----+

6 rows in set (0.00sec)

root@localhost : yayun 23:06:05>

存储在最前面的2条记录更新成功了，后面的则失败，因为第三条记录如果也要更新，则会引发主键冲突。

这个时候，如果我们在更新时增加 ORDER BY 的话，则可以顺利更新成功。

root@localhost : yayun 23:07:52> update t1 set id=id-1 order byid;

Query OK,6 rows affected (0.00sec)

Rows matched:6 Changed: 6 Warnings: 0root@localhost : yayun 23:08:20>

接下来，我们看看把它转成 innodb 表，结果会是怎样的。

root@localhost : yayun 23:09:21> alter table t1 engine =innodb;

Query OK,6 rows affected (0.08sec)

Records:6 Duplicates: 0 Warnings: 0root@localhost : yayun 23:09:23>

root@localhost : yayun 23:10:15> select * fromt1;+----+-----+

| id | id2 |

+----+-----+

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

+----+-----+

6 rows in set (0.00sec)

root@localhost : yayun 23:10:19>

看到变化了吧，行数据按照 id 的顺序来显示了。清空后，自己重新手工插入记录，再看看。

root@localhost : yayun 23:11:05> delete fromt1;

Query OK,6 rows affected (0.00sec)

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.09sec)

Records:6 Duplicates: 0 Warnings: 0root@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.03sec)

Rows matched:6 Changed: 6 Warnings: 0root@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 的顺序来更新了，因此不会报错。