UPDATE 時主鍵衝突引發的思考

日期:2008-07-04  作者:喜騰小二  來源:PHPChina


作/譯者:葉金榮(Email: ),來源:http://imysql.cn,轉載請注明作/譯者和出處,並且不能用於商業用途,違者必究。

假設有一個表,結構如下:

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 的順序來更新了,因此不會報錯。

<<<返回技術中心

技術文章

站內新聞

我要啦免费统计