便利クエリ(INSERT ~ ON DUPLICATE KEY UPDATE)

2022/11/12

「INSERT … ON DUPLICATE KEY UPDATE 構文」を利用すると、該当レコードが存在しないときはINSERTを行い、存在する場合はUPDATEしてくれます。ここでは、「INSERT … ON DUPLICATE KEY UPDATE 構文」の動作確認を行います。

動作確認

下記テーブルに対して動作確認します。

mysql> SHOW COLUMNS FROM `users`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| old   | int(11)      | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

レコードが存在しないとき

mysql> SELECT * FROM users;
Empty set (0.00 sec)

mysql> 
mysql> INSERT INTO `users` 
    ->     (`id`, `name`, `old`) 
    -> VALUES 
    ->     (1, "aaa", 24)
    -> ON DUPLICATE KEY UPDATE
    ->     `name` = VALUES(`name`),
    ->     `old` = VALUES(`old`);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM users;
+----+------+-----+
| id | name | old |
+----+------+-----+
|  1 | aaa  |  24 |
+----+------+-----+
1 row in set (0.00 sec)

レコードが挿入されました。

レコードが存在するとき

mysql> SELECT * FROM users;
+----+------+-----+
| id | name | old |
+----+------+-----+
|  1 | aaa  |  24 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> INSERT INTO `users` 
    ->     (`id`, `name`, `old`) 
    -> VALUES 
    ->     (1, "bbb", 62)
    -> ON DUPLICATE KEY UPDATE
    ->     `name` = VALUES(`name`),
    ->     `old` = VALUES(`old`);
Query OK, 2 rows affected (0.00 sec)

mysql> 
mysql> SELECT * FROM users;
+----+------+-----+
| id | name | old |
+----+------+-----+
|  1 | bbb  |  62 |
+----+------+-----+
1 row in set (0.00 sec)

レコードが更新されました。

CASE文の利用

CASE文も利用可能です。

mysql> SELECT * FROM users;
| id | name | old |
+----+------+-----+
|  1 | bbb  |  62 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> 
mysql> INSERT INTO `users` 
    ->     (`id`, `name`, `old`) 
    -> VALUES 
    ->     (1, "ccc", -10)
    -> ON DUPLICATE KEY UPDATE
    ->     `name` = VALUES(`name`),
    ->     `old` = (CASE WHEN values(`old`) < 0
    ->                   THEN 0 
    ->                   ELSE values(`old`) 
    ->              END);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM users;
+----+------+-----+
| id | name | old |
+----+------+-----+
|  1 | ccc  |   0 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> INSERT INTO `users` 
    ->     (`id`, `name`, `old`) 
    -> VALUES 
    ->     (1, "ccc", 40)
    -> ON DUPLICATE KEY UPDATE
    ->     `name` = VALUES(`name`),
    ->     `old` = (CASE WHEN values(`old`) < 0
    ->                   THEN 0 
    ->                   ELSE values(`old`) 
    ->              END);
Query OK, 2 rows affected (0.00 sec)

mysql> 
mysql> SELECT * FROM users;
+----+------+-----+
| id | name | old |
+----+------+-----+
|  1 | ccc  |  40 |
+----+------+-----+
1 row in set (0.00 sec)

1つめのINSERTでは、values(old) < 0 を満たすので、old=0 になっています。
2つめのINSERTでは、values(old) < 0 を満たさないので、old=40になっています。