MySQL 5.5.19 にて ON DUPLICATE KEY UPDATE 使用時に LAST_INSERT_ID を呼ぶとプライマリキーの値によって不可思議な現象が起こる話

例によって同僚に、ON DUPLICATEを使ってる時の、LAST_INSERT_IDの挙動がなんかおかしいという話を受け調査を開始した。
複数の同僚に手伝ってもらいながら検証した結果次のような現象が起こることが分かったため、メモしておく。

ON DUPLICATE でプライマリキーに「文字」を利用する場合、LAST_INSERT_IDの値が 0 に変化し予期せぬ更新がかかったり、更新が失敗する問題がある。

これは、LAST_INSERT_ID が数値しか扱えないことが原因と思われる。

次に検証手順を記す。
とりあえず、MySQLのバージョンは 5.5.19

■一つ目のパターン「数値」の場合
このパターンは想定通りに動くので、特に問題は起こらない

1.まずは簡単なテーブルを作成する

mysql> CREATE TABLE IF NOT EXISTS fff (
    ->     id          VARCHAR(64),
    ->     last_update DATETIME,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

2.プライマリキーに「数値」をいれて、普通にINSERTする･･･まあ普通に通る

mysql> INSERT INTO fff (id, last_update) VALUES ('9', NOW());
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM fff\G
*************************** 1. row ***************************
         id: 9
last_update: 2013-01-11 22:11:45
1 row in set (0.00 sec)

3. プライマリキーに「数値」をいれて、普通にON DUPLICATEする･･･これも問題なく通る
正しくインサーとされ、LAST_INSERT_IDも正しい値が取れる。

mysql> INSERT INTO fff (id, last_update) VALUES ('9', NOW()) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), last_update = NOW();
Query OK, 2 rows affected (0.02 sec)

mysql> SELECT * FROM fff\G
*************************** 1. row ***************************
         id: 9
last_update: 2013-01-11 22:11:51
1 row in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                9 |
+------------------+
1 row in set (0.00 sec)

4. 最後にお掃除する

mysql> DROP TABLES fff;
Query OK, 0 rows affected (0.01 sec)

■二つ目のパターン「文字」の場合
このパターンは想定通りに動かないので、特に注意を要する

1.まずは簡単なテーブルを作成する

mysql> CREATE TABLE IF NOT EXISTS fff (
    ->     id          VARCHAR(64),
    ->     last_update DATETIME,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

2.プライマリキーに「文字」をいれて、普通にINSERTする･･･まあ普通に通る

mysql> INSERT INTO fff (id, last_update) VALUES ('X', NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM fff\G
*************************** 1. row ***************************
         id: X
last_update: 2013-01-11 22:12:29
1 row in set (0.00 sec)

3. プライマリキーに「文字」をいれて、普通にON DUPLICATEする･･･これはWARNINGSは出るものの問題なく通るように見える
･･･が、SELECTの結果をみるとid が X から 0 になぜか変換されている。
LAST_INSERT_IDもX ではなく 0 が取れる。

この時点ですでにおかしいが、さらに続ける。

mysql> INSERT INTO fff (id, last_update) VALUES ('X', NOW()) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), last_update = NOW();
Query OK, 2 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'X' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM fff\G
*************************** 1. row ***************************
         id: 0
last_update: 2013-01-11 22:12:38
1 row in set (0.02 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

4. 再度プライマリキーに「文字」をいれて、普通にON DUPLICATEする･･･今度はWARNINGSも出ず問題なく通る
SELECT結果も正しく、Xの行が追加されている
LAST_INSERT_IDはやはり X ではなく 0 が取れる。

mysql> INSERT INTO fff (id, last_update) VALUES ('X', NOW()) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), last_update = NOW();
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM fff\G
*************************** 1. row ***************************
         id: 0
last_update: 2013-01-11 22:38:33
*************************** 2. row ***************************
         id: X
last_update: 2013-01-11 22:39:18
2 rows in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

5. さらに再度プライマリキーに「文字」をいれて、普通にON DUPLICATEする･･･今度はERRORになてインサートできない
わかる人にはもうわかると思うが、これは id = 'X' ではなく、既に存在する別の id = 0 の行とおなじプライマリキーで id = 'X' の行をUPDATE 仕様とした結果、プライマリキーの競合が起こり更新できい状態が発生している。

mysql> INSERT INTO fff (id, last_update) VALUES ('X', NOW()) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), last_update = NOW();
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

6. 最後にお掃除する

mysql> DROP TABLES fff;
Query OK, 0 rows affected (0.01 sec)

■まとめ
このように、LAST_INSERT_ID が数値しか扱えないず、LAST_INSERT_ID('文字') とした場合には 0 が返る仕様？によりON DUPLICATEとLAST_INSERT_IDを併用する際にプライマリキーに「文字」をいれた場合、不可思議な現象が発生することが有るようです。