■ はじめに
https://blogs.yahoo.co.jp/dk521123/36225584.htmlで扱ったInsert or Update「INSERT INTO ~ ON DUPLICATE KEY UPDATE ~」で 盲点だったことをまとめる。
■ 使用上の注意
* AUTO_INCREMENT を使用しているテーブルで、「INSERT INTO ~ ON DUPLICATE KEY UPDATE ~」を使用した場合 UPDATE処理が走った場合でも、インクリメントされてしまう原因
* InnoDB仕様。高速化のため。 * 以下のいずれかで、「従来」ロックモードになり、現象が発生しなくなるらしいが、 パフォーマンスを犠牲にしてまでやることじゃなさそう -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= a) 「--innodb_autoinc_lock_mode=0」オプションを付けて起動 or b) my.cnfのmysqldに「innodb_autoinc_lock_mode=0」と記述 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=https://dev.mysql.com/doc/refman/5.6/ja/innodb-auto-increment-configurable.html
参考文献
http://nplll.com/archives/2011/04/innodbinsert_ignoreauto_increment.phphttps://monochrome-design.jp/blog/195
■ 例
CREATE TABLE
CREATE TABLE `fruit` ( `id` INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` VARCHAR(100) NOT NULL UNIQUE, `price` INT(11) NULL DEFAULT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時' ) COLLATE='utf8_general_ci' ENGINE=InnoDB ;
SQL
* ここからが本番-- 二回実行する INSERT INTO fruit ( name, price ) VALUES ( 'Orange', 120 ) ON DUPLICATE KEY UPDATE name = 'Orange', price = 121 ; -- 三回目で実行する INSERT INTO fruit ( name, price ) VALUES ( 'Apple', 99 ) ON DUPLICATE KEY UPDATE name = 'Apple', price = 97 ;
出力結果
1回目`id | `name | `price | `created_at | updated_at |
1 | Orange | 120 | 2017-09-27 23:15:20 | 2017-09-27 23:15:20 |
""
2回目 `id | `name | `price | `created_at | updated_at |
1 | Orange | 121 | 2017-09-27 23:15:20 | 2017-09-27 23:16:13 |
3回目 ★id=3になっている★
`id | `name | `price | `created_at | updated_at |
1 | Orange | 121 | 2017-09-27 23:15:20 | 2017-09-27 23:16:13 |
3 | Apple | 99 | 2017-09-27 23:17:32 | 2017-09-27 23:17:32 |
■ 代替案
* データが存在してたら更新。次に、データなければ、追加。-- [1] トランザクションを開始する START TRANSACTION; -- [2] データが存在してたら更新 UPDATE fruit SET price=139 WHERE name='Orange'; -- [3] データなければ、追加 INSERT INTO fruit(name, price) SELECT 'Orange' AS name, 139 AS price FROM dual WHERE NOT EXISTS (SELECT * FROM fruit f WHERE f.name = 'Orange' ); -- [4] コミット COMMIT;