假設(shè)有一個表，結(jié)構(gòu)如下：

　　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 |
　　+----+---------+


　　現(xiàn)在想要把id字段分別-1，執(zhí)行以下語句，得到報錯：


　　mysql> update a set id=id-1;
　　ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'


　　看看更新后的結(jié)果，可以看到：


　　mysql> select * from a;
　　+----+---------+
　　| id | city_id |
　　+----+---------+
　　| 1 | 2 |
　　| 2 | 3 |
　　| 5 | 5 |
　　| 4 | 4 |
　　| 6 | 6 |
　　| 7 | 7 |
　　+----+---------+


　　存儲在最前面的2條記錄更新成功了，后面的則失敗，因為第三條記錄如果也要更新，則會引發(fā)主鍵沖突。

這個時候，如果我們在更新時增加 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


　　接下來，我們看看把它轉(zhuǎn)成 innodb 表，結(jié)果會是怎樣的。

　　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 |
　　+----+---------+



　　看到變化了吧，行數(shù)據(jù)按照 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 的順序來顯示，然后我們再次執(zhí)行之前的 update 語句：

　　mysql> update a set id = id - 1;
　　Query OK, 6 rows affected (0.00 sec)
　　Rows matched: 6 Changed: 6 Warnings: 0


　　可以看到，在 innodb 表的情況下，更新是可以成功的。

　　現(xiàn)在我們來分析下。

　　myisam表是堆組織表(Heap Organize Table, HOT)，它的索引是采用 b-tree 方式存儲的，數(shù)據(jù)顯示時是隨機(jī)順序，而非按照主鍵的索引順序來顯示。

　　而innodb表是索引組織表(Index Organized Table, IOT)，它的索引則是采用 clustered index 方式，因此主鍵會按照順序存儲，每次有記錄有更新時，會重新整理更新其主鍵。因此無論是直接從 myisam 表轉(zhuǎn)換過來的，還是后來插入的記錄，顯示時都會按照主鍵的順序。

　　更新數(shù)據(jù)時，如果沒有指定排序的字段或索引，則默認(rèn)以隨機(jī)順序更新，所以 myisam 表如果不指定 ORDER BY 的話，則采用默認(rèn)的存儲順序來更新，所以會發(fā)生主鍵沖突的情況。而 innodb 表總是有主鍵(如果沒有定義，則也有默認(rèn)主鍵)，如果更新時沒有指定排序字段或索引，則按照主鍵順序來更新，在上面的例子中，就是按照主鍵 id 的順序來更新了，因此不會報錯。
標(biāo)簽：