MySQLのINSERT ... ON DUPLICATE KEY UPDATEでレコードの挿入/更新を便利に実行

## 概要
MySQLのINSERT文には"INSERT ... ON DUPLICATE KEY UPDATE"という構文があります。レコードを挿入または重複があった場合は更新したい場合に、INSERT文とUPDATE文を別個に書くよりはるかに便利になります。
本記事ではそんな"INSERT ... ON DUPLICATE KEY UPDATE"の使い方、便利なポイント、その他仕様についてまとめます。

## 準備

以下のようなテーブルを使います。

``` sql
select version();
+-----------+
| version() |
+-----------+
| 8.0.17    |
+-----------+

CREATE TABLE users (
    id INT(8) NOT NULL,
    name VARCHAR(128),
    age INT(3),
    registered_timestamp timestamp not null default current_timestamp,
    updated_timestamp timestamp not null default current_timestamp on update current_timestamp,
    primary key(id)
);

INSERT INTO users (id, name, age) VALUES(1, 'taro', 20), (2, 'jiro', 18);

+----+------+------+----------------------+---------------------+
| id | name | age  | registered_timestamp | updated_timestamp   |
+----+------+------+----------------------+---------------------+
|  1 | taro |   20 | 2020-06-05 11:05:52  | 2020-06-05 11:05:52 |
|  2 | jiro |   18 | 2020-06-05 11:05:52  | 2020-06-05 11:05:52 |
+----+------+------+----------------------+---------------------+
```

## 基本的な使い方

例えばこのテーブルにid:2のレコードを追加したいが、もしすでにそのレコードがあればその内容を書き換えたい時があるとします。
id:"2"のレコードが存在するかどうかをまず確かめ、存在しなけばINSERT、存在すればUPDATE文を投入することになるでしょう。
必要なSQLは以下のようになります。

```sql
SELECT * FROM users where id = 2;

--結果が返って来なければ
INSERT INTO users (id, name, age) values (2, 'jiro', 19);

--結果が返ってきたら
UPDATE users SET age = 19 where id = 2;
```

試しにJavaからプレースホルダを使ってこのクエリを組み立て、実行してみましょう。

```java
//MySQLとのConnectionインスタンスconnectionを取得

String sqlSelect = "SELECT * FROM users where id = ?;";
PreparedStatement psSelect = connection.prepareStatement(sqlSelect);
psSelect.setInt(1, 2);
ResultSet rs = psSelect.excuteQuery();

if(!rs.next()){
    String sqlInsert = "INSERT INTO users (id, name, age) values (?, ?, ?);";
    PreparedStatement psInsert = connection.prepareStatement(sqlInsert);
    psInsert.setInt(1, 2);
    psInsert.setString(2, "jiro");
    psInsert.setInt(3, 19);
    psInsert.excute();
} else {
    String sqlUpdate = "UPDATE users SET age = ? where id = ?;";
    PreparedStatement psUpdate = connection.prepareStatement(sqlUpdate);
    psUpdate.setInt(1, 19);
    psUpdate.setInt(2, 2);
    psUpdate.excute();
}
connection.commit();
```

かなり面倒です。

"INSERT ... ON DUPLICATE KEY UPDATE"はこうした条件分岐をクエリ上でやってくれるため、

- 対象レコードがあるかどうか (SELECT文)
- なかったら挿入 (INSERT文)
- あったら更新 (UPDATE文)

の三つのクエリを一つにまとめてくれます。

```sql
INSERT INTO users (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = 19;

+----+------+------+----------------------+---------------------+
| id | name | age  | registered_timestamp | updated_timestamp   |
+----+------+------+----------------------+---------------------+
|  1 | taro |   20 | 2020-06-05 11:05:52  | 2020-06-05 11:05:52 |
|  2 | jiro |   19 | 2020-06-05 11:05:52  | 2020-06-05 11:06:24 |
+----+------+------+----------------------+---------------------+

```

この場合、idのカラムが主キーとなっているので"INSERT INTO"の部分でidの重複があれば自動的にそのレコードを更新してくれます。
これをJavaから組み立てると

```java
String sql = "INSERT INTO users (id, name, age) values (?, ?, ?) "+
                "ON DUPLICATE KEY UPDATE age = ?;";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, 2);
ps.setString(2, "jiro");
ps.setInt(3, 19);
ps.setInt(4, 2);
ps.excute();
connection.commit();
```

となり、断然楽です。

## VALUES()関数を使う

さらに"ON DUPLICATE KEY UPDATE"のみで使えるVALUES()という関数を使うと、クエリの組み立てがより簡単になります。
この関数は以下のように使い、INSERT部分で挿入しようとした値を参照することができます。

```sql
-- VALUES(age)は19を返す
INSERT INTO users (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = VALUES(age);
```

さらに、この関数によって"ON DUPLICATE KEY UPDATE"で複数行の挿入/更新が可能になります。

```sql
-- VALUES(age)はid:"1"の行では20、id:"3"の行では17を返す
INSERT INTO users (id, name, age) values (1, 'taro', 20), (3, 'saburo', 17) ON DUPLICATE KEY UPDATE age = VALUES(age);

+----+--------+------+----------------------+---------------------+
| id | name   | age  | registered_timestamp | updated_timestamp   |
+----+--------+------+----------------------+---------------------+
|  1 | taro   |   20 | 2020-06-05 11:05:52  | 2020-06-05 11:05:52 |
|  2 | jiro   |   19 | 2020-06-05 11:05:52  | 2020-06-05 11:06:24 |
|  3 | saburo |   17 | 2020-06-05 11:07:01  | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
```

上のように、主キーに重複があった場合は各レコードで挿入しようとしたageの値に応じて更新を行うことができるわけです。

また、ここで分かる通りキーの重複によってUPDATEを実行しようとした際に、更新しようとした値と既存の値がすべて一致していた場合はUPDATEは実行されず、"ON UPDATE"をつけているカラムも更新されません。(UPDATE自体の仕様と同じ)

この操作を"ON DUPLICATE KEY UPDATE"使わずにプログラム上からクエリを組み立てようとすると、上述のSELECT文を使って条件分岐を作るパターンそれ自体を複数回ループする必要があります。
しかし"ON DUPLICATE KEY UPDATE"でVALUES()関数を使えば以下のように、プレースホルダに代入する操作のみをループすればよいことになります。

```java
int numRecord = 2;
List<String> placeholderList = new ArrayList<>();
for (int i = 0; i < numRecord; i++){
    placeholdersList.add("(?, ?, ?)");
}

String sql = "INSERT INTO users (id, name, age) values "+
                StringUtils.join(placeholdersList, ", ") +
                "ON DUPLICATE KEY UPDATE age = VALUES(age)";
PreparedStatement ps = connection.prepareStatement(sql);

// この部分だけをレコードの数だけループすればよい
// 一つ目のレコード
ps.setInt(1, 1);
ps.setString(2, "taro");
ps.setInt(3, 20);
// 二つ目のレコード
ps.setInt(5, 3);
ps.setString(6, "saburo");
ps.setInt(7, 17);

ps.excute();

connection.commit();
```

レコードごとにデータベースに既に存在するかを確認してINSERTとUPDATEを切り替える操作が、わずか一つのクエリで実行可能になりました。

## 応用

"ON DUPLICATE KEY UPDATE"構文で[IF/CASEの制御フロー関数](https://dev.mysql.com/doc/refman/5.6/ja/control-flow-functions.html
)を使えるため、条件付きの更新を行うことができます。

```sql
+----+--------+------+----------------------+---------------------+
| id | name   | age  | registered_timestamp | updated_timestamp   |
+----+--------+------+----------------------+---------------------+
|  1 | taro   |   20 | 2020-06-05 11:05:52  | 2020-06-05 11:05:52 |
|  2 | jiro   |   19 | 2020-06-05 11:05:52  | 2020-06-05 11:06:24 |
|  3 | saburo |   17 | 2020-06-05 11:07:01  | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+

-- 挿入しようとしたageの値(VALUES(age))がもともとのageの値より大きい場合のみ更新
INSERT INTO users (id, name, age) values (2, 'jiro', 20), (3, 'sabro', 17) ON DUPLICATE KEY UPDATE age = IF(VALUES(age) > age, VALUES(age), age);

+----+--------+------+----------------------+---------------------+
| id | name   | age  | registered_timestamp | updated_timestamp   |
+----+--------+------+----------------------+---------------------+
|  1 | taro   |   20 | 2020-06-05 11:05:52  | 2020-06-05 11:05:52 |
|  2 | jiro   |   20 | 2020-06-05 11:05:52  | 2020-06-05 11:07:38 |
|  3 | saburo |   17 | 2020-06-05 11:07:01  | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+

-- 挿入しようとしたageの値(VALUES(age))がもともとのageの値より大きい場合は1加算、等しい場合はそのまま、小さい場合は新しい値に更新
INSERT INTO users (id, name, age) values (2, 'jiro', 22), (3, 'saburo', 15) 
    ON DUPLICATE KEY UPDATE 
        age = 
        CASE 
        WHEN VALUES(age) > age THEN age + 1 
        WHEN VALUES(age) = age THEN age 
        WHEN VALUES(age) < age THEN VALUES(age) 
        END;

+----+--------+------+----------------------+---------------------+
| id | name   | age  | registered_timestamp | updated_timestamp   |
+----+--------+------+----------------------+---------------------+
|  1 | taro   |   20 | 2020-06-05 11:05:52  | 2020-06-05 11:05:52 |
|  2 | jiro   |   21 | 2020-06-05 11:05:52  | 2020-06-05 11:08:06 |
|  3 | saburo |   15 | 2020-06-05 11:07:01  | 2020-06-05 11:08:06 |
+----+--------+------+----------------------+---------------------+
```

## AUTO_INCREMENTとON DUPLICATE KEY UPDATE

テーブルに自動採番(AUTO_INCREMENT)のカラムが存在する場合は、"ON DUPLICATE KEY UPDATE"構文を使った場合に更新されずにUPDATEを実行しても連番が一つ進む、と書いてある記事がよく出てきます。
([INSERT ON DUPLICATE KEY UPDATEの利点と注意点](https://qiita.com/QBMK_IQU/items/4515c935b48acd0c953e) など)

しかし、MySQL version 5.6のリファレンスで

>(これらの効果は、a が自動インクリメントカラムである InnoDB テーブルに対して同じではありません。自動インクリメントカラムを使用した場合、INSERT ステートメントは自動インクリメント値を増やしますが、UPDATE は増やしません。)

とあります[^1]。

[^1]: 筆者が使っているversion 8のリファレンスにも "(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)" とあります。

検証してみました。

```sql
CREATE TABLE users_increment (
    id INT(8) NOT NULL AUTO_INCREMENT,
    name VARCHAR(128),
    age INT(3),
    registered_timestamp timestamp not null default current_timestamp,
    updated_timestamp timestamp not null default current_timestamp on update current_timestamp,
    primary key(id)
);

INSERT INTO users_increment (name, age) VALUES('taro', 20), ('jiro', 18);
+----+------+------+----------------------+---------------------+
| id | name | age  | registered_timestamp | updated_timestamp   |
+----+------+------+----------------------+---------------------+
|  1 | taro |   20 | 2020-06-05 11:17:58  | 2020-06-05 11:17:58 |
|  2 | jiro |   18 | 2020-06-05 11:17:58  | 2020-06-05 11:17:58 |
+----+------+------+----------------------+---------------------+

-- キーが重複しているのでINSERT文は実行されない
-- UPDATEされても自動連番が進むなら、次にINSERTされたレコードのidは"4"になるはず
INSERT INTO users_increment (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = VALUES(age);
+----+------+------+----------------------+---------------------+
| id | name | age  | registered_timestamp | updated_timestamp   |
+----+------+------+----------------------+---------------------+
|  1 | taro |   20 | 2020-06-05 11:17:58  | 2020-06-05 11:17:58 |
|  2 | jiro |   19 | 2020-06-05 11:17:58  | 2020-06-05 11:19:01 |
+----+------+------+----------------------+---------------------+

-- 実際に次に挿入されたレコードは"3"
INSERT INTO users_increment (name, age) values ('saburo', 17);
+----+--------+------+----------------------+---------------------+
| id | name   | age  | registered_timestamp | updated_timestamp   |
+----+--------+------+----------------------+---------------------+
|  1 | taro   |   20 | 2020-06-05 11:17:58  | 2020-06-05 11:17:58 |
|  2 | jiro   |   19 | 2020-06-05 11:17:58  | 2020-06-05 11:19:01 |
|  3 | saburo |   17 | 2020-06-05 11:19:46  | 2020-06-05 11:19:46 |
+----+--------+------+----------------------+---------------------+

```

以上のように、最近のバージョンのMySQLでは"ON DUPLICATE KEY UPDATE"してもINSERTされなければ自動連番は進みません。

## 参考文献

[MySQL 5.6 リファレンスマニュアル 制御フロー関数](https://dev.mysql.com/doc/refman/5.6/ja/control-flow-functions.html)

[MySQL 5.6 リファレンスマニュアル INSERT ... ON DUPLICATE KEY UPDATE 構文](https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html)

[MySQL: INSERT...ON DUPLICATE KEY UPDATEまとめ](https://qiita.com/yuzroz/items/f0eccf847b2ea42f885f)

[INSERT ON DUPLICATE KEY UPDATEの利点と注意点](https://qiita.com/QBMK_IQU/items/4515c935b48acd0c953e)