MySQL

トップ > チップス > MySQL

H2のMERGEとMySQLのON DUPLICATE KEY

ちょっとニッチなネタですが。H2にはMERGEという便利な構文があって、対象テーブルにキーが存在すれば、必要な値を更新、無ければ追加、という動作を一行で記述する事が出来ます。MySQLにもREPLACEという構文がありますが、こちらは、「キーが存在した場合、一旦削除」してから追加するという仕様なんですね。つまり明示されていない列があった場合、既存の値がデフォルト値に戻されてしまう場合が生じます。

そこで利用出来そうなのが、ON DUPLCATE KEY構文です。書き方はかなり違いますが、H2のMERGEとほぼ同等の処理をしてくれそうです。さっそく、実験。以下のようなテーブルとデータを準備します。

CREATE TABLE t1 (k INTEGER PRIMARY KEY,v1 INTEGER,v2 INTEGER);
CREATE TABLE t2 (k INTEGER PRIMARY KEY,v2 INTEGER);
INSERT INTO t1 VALUES (1,10,100),(2,20,200),(3,30,300);
INSERT INTO t2 VALUES (3,301),(4,401);


t1とt2のキーは共通で、t2の内容をt1にマージしたい場合を想定しています。普通にINSERTするとkey=3が重複してエラーになってしまいますので、ON DUP..を使って以下のように記述します。

INSERT INTO t1 (k,v2) SELECT k,v2 FROM t2 ON DUPLICATE KEY UPDATE t1.v2 = t2.v2;


すると、以下のように重複箇所では更新処理、そうでない部分は追加処理がされていることを確認できます。

mysql> SELECT * FROM t1;
+---+------+------+
| k | v1 | v2 |
+---+------+------+
| 1 | 10 | 100 |
| 2 | 20 | 200 |
| 3 | 30 | 301 |
| 4 | NULL | 401 |
+---+------+------+


REPLACEを使うとkey=3のv1もNULLに戻されてしまう点が要注意です。

参考URL

• タグ別一覧: [mysql]
• チップス一覧