【MySQL】 INSERT INTO ~ ON DUPLICATE KEY UPDATE ~の使用上の注意

■ はじめに

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.php
https://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_atupdated_at
1Orange1202017-09-27 23:15:202017-09-27 23:15:20
""
2回目
`id`name`price`created_atupdated_at
1Orange1212017-09-27 23:15:202017-09-27 23:16:13

3回目 ★id=3になっている★
`id`name`price`created_atupdated_at
1Orange1212017-09-27 23:15:202017-09-27 23:16:13
3Apple992017-09-27 23:17:322017-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;

参考文献

https://stackoverflow.com/questions/9189102/too-many-auto-increments-with-on-duplicate-key-update


関連記事

MySQL】 データあればUPDATE、なければINSERT ~ DUPLICATE KEY ~

https://blogs.yahoo.co.jp/dk521123/36225584.html