MariaDB(MySQL)でINSERT … ON DUPLICATE KEY UPDATEとREPLACE INTOの違い

MariaDB(MySQL)でINSERT … ON DUPLICATE KEY UPDATEとREPLACE INTOの違い

MariaDB(MySQL)で 主キーまたはユニークキーで衝突した場合には更新、そうでない場合は挿入を行う場合、
INSERT INTO テーブル(カラム1,カラム2,・・・) VALUES(値1,値2,・・・) ON DUPLICATE KEY UPDATE カラム2=VALUES(カラム2),・・・;
REPLACE INTO テーブル(カラム1,カラム2,・・・) VALUES(値1,値2,・・・);
のどちらかを使います。
以下具体的なSQLで解説します。

テーブル例

テーブル構造が以下とします。
CREATE TABLE IF NOT EXISTS t_upd_ins(id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), price INT);

レコードは以下とします。
INSERT INTO t_upd_ins(name, price) VALUES('キャベツ',250), ('レタス',200), ('ほうれん草',160);

SELECT * FROM t_upd_ins;
+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
|  1 | キャベツ        |   250 |
|  2 | レタス          |   200 |
|  3 | ほうれん草      |   160 |
+----+-----------------+-------+

INSERT INTO ON DUPLICATE KEY UPDATEで挿入と更新

ON DUPLICATE KEY UPDATEでレコードを挿入します。

INSERT INTO t_upd_ins(id,name,price) VALUES(4,'はくさい',300) ON DUPLICATE KEY UPDATE name=VALUES(name), price=VALUES(price);

ON DUPLICATE KEY UPDATEでレコードを更新します。

INSERT INTO t_upd_ins(id,name,price) VALUES(1,'キャベツ',260) ON DUPLICATE KEY UPDATE name=VALUES(name), price=VALUES(price);

同じ構文で挿入や更新が出来ているか確認します。

select * from t_upd_ins;
+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
|  1 | キャベツ        |   260 |
|  2 | レタス          |   200 |
|  3 | ほうれん草      |   160 |
|  4 | はくさい        |   300 |
+----+-----------------+-------+

REPLACE INTOで挿入と更新

REPLACE INTOでレコードを挿入します。

REPLACE INTO t_upd_ins(id,name,price) VALUES(5,'小松菜',150);

REPLACE INTOでレコードを更新します。

REPLACE INTO t_upd_ins(id,name,price) VALUES(4,'はくさい',440);

同じ構文で挿入や更新が出来ているか確認します。

select * from t_upd_ins;
+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
|  1 | キャベツ        |   260 |
|  2 | レタス          |   200 |
|  3 | ほうれん草      |   160 |
|  4 | はくさい        |   440 |
|  5 | 小松菜          |   150 |
+----+-----------------+-------+

REPLACE INTOを使う場合の注意点

REPLACE INTOは、更新時に実際にはレコードの削除と追加を行っています。
よって、外部キー参照がある場合は整合性エラーになる可能性があります。
また、トリガーや監査ログでは「削除＋挿入」と記録されるため、履歴が不自然になります。