MySQLでバルクでUPSERTしたい

Linuxめも

大量の新規登録と更新が混在しているCSVファイルを効率よくUPSERTしたい。

テストデータ作成†

バージョン確認

SELECT version();
+-----------+
| version() |
+-----------+
| 5.7.36    |
+-----------+

product_code と item_code の組み合わせでユニーク。
id は別テーブルと紐づいているので変更されるのはまずい。

テーブル作成

CREATE TABLE product (
    id int AUTO_INCREMENT PRIMARY KEY,
    product_code varchar(255) NOT NULL,
    item_code varchar(255) NOT NULL,
    product_name varchar(255) NOT NULL,
    UNIQUE product_idx (product_code, item_code)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

テストデータ登録

INSERT INTO product (product_code, item_code, product_name) VALUES
('pcode1', 'icode1', 'product1'),
('pcode2', 'icode2', 'product2'),
('pcode3', 'icode3', 'product3');

確認

SELECT * FROM product;
+----+--------------+-----------+--------------+
| id | product_code | item_code | product_name |
+----+--------------+-----------+--------------+
|  1 | pcode1       | icode1    | product1     |
|  2 | pcode2       | icode2    | product2     |
|  3 | pcode3       | icode3    | product3     |
+----+--------------+-----------+--------------+

REPLACEは使えない†

pcode1 の product_name を変更してみる。

REPLACE product (product_code, item_code, product_name) VALUES ('pcode1', 'icode1', 'productA');
Query OK, 2 rows affected (0.01 sec)

2 rows になるのはDELETEとINSERTが実行されるため。

確認

SELECT * FROM product;
+----+--------------+-----------+--------------+
| id | product_code | item_code | product_name |
+----+--------------+-----------+--------------+
|  2 | pcode2       | icode2    | product2     |
|  3 | pcode3       | icode3    | product3     |
|  4 | pcode1       | icode1    | productA     |
+----+--------------+-----------+--------------+

id が 1 だったものが 4 になっているのでREPLACEは使えない。

ELTとFIELDを使う†

良い感じで実現できるらしいが記述がややこしかったので却下。

ON DUPLICATE KEY UPDATEを使う†

pcode1の商品名を戻して、pcode4を登録してみる。

INSERT INTO product (product_code, item_code, product_name) VALUES
('pcode1', 'icode1', 'product1'),
('pcode4', 'icode4', 'product4')
ON DUPLICATE KEY UPDATE product_name = VALUES(product_name);
Query OK, 3 rows affected (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 0

確認

SELECT * FROM product;
+----+--------------+-----------+--------------+
| id | product_code | item_code | product_name |
+----+--------------+-----------+--------------+
|  2 | pcode2       | icode2    | product2     |
|  3 | pcode3       | icode3    | product3     |
|  4 | pcode1       | icode1    | product1     |
|  5 | pcode4       | icode4    | product4     |
+----+--------------+-----------+--------------+

idは変わらず変更され、期待通りに登録された。