読者です 読者をやめる 読者になる 読者になる

MySQL で UPSERT する時に考えること

SQL

MySQL で UPSERT をするとき、REPLACE を使うべきか? INSERT のオプション ON DUPLICATE KEY UPDATE を使うべきか?

REPLACE は、キー重複する行を削除してから INSERT することを考えると、ON DUPLICATE KEY UPDATE の方が
更新する列を指定できるから良いのか悪いのか?結論が出ない。

大量の行をUPSERTしたい場合を考慮したい。。

サンプル。。

DELIMITER //
DROP TABLE IF EXISTS test.projects
//
CREATE TABLE test.projects (
  key1           INT NOT NULL
, target_name    VARCHAR(32)  NOT NULL 
, memo           VARCHAR(100) DEFAULT NULL
, PRIMARY KEY (key1)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
//
DELIMITER ;

INSERT INTO projects (key1, target_name, memo )VALUES( 1, 'A', 'あ' ),( 2, 'B', 'い' );
commit;

REPLACE を書く場合。。。

REPLACE projects (key1, target_name, memo 
)VALUES( 1, 'AA', 'AAA' ), ( 2, 'BB', 'BBB' ), ( 3, 'CC', 'CCC' )

ON DUPLICATE KEY UPDATEを書く場合、、

INSERT projects (key1,  target_name, memo 
)VALUES( 1, 'AA', 'AAA' ), ( 2, 'BB', 'BBB' ), ( 3, 'CC', 'CCC' )
ON DUPLICATE KEY UPDATE
  target_name = VALUES(target_name)
, memo = VALUES(memo)

どちらも、

+------+-------------+------+
| key1 | target_name | memo |
+------+-------------+------+
|    1 | AA          | AAA  |
|    2 | BB          | BBB  |
|    3 | CC          | CCC  |
+------+-------------+------+

という結果を得られるけど、、

この ON DUPLICATE KEY UPDATE で指定する列を、target_name だけにするなら、

   ON DUPLICATE KEY UPDATE target_name = VALUES(target_name)

だけにするなら、、

+------+-------------+------+
| key1 | target_name | memo |
+------+-------------+------+
|    1 | AA          | あ   |
|    2 | BB          | い   |
|    3 | CC          | CCC  |
+------+-------------+------+

という結果になる。