Go to Qiita Advent Calendar 2024 Top
LoginSignup
64
42

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@Yuki_Oshima(Yuki Oshima)in株式会社ヴァリューズ

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

Posted at

概要

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

準備

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

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は以下のようになります。

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からプレースホルダを使ってこのクエリを組み立て、実行してみましょう。

//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文)

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

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から組み立てると

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部分で挿入しようとした値を参照することができます。

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

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

-- 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()関数を使えば以下のように、プレースホルダに代入する操作のみをループすればよいことになります。

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の制御フロー関数を使えるため、条件付きの更新を行うことができます。

+----+--------+------+----------------------+---------------------+
| 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の利点と注意点 など)

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

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

とあります1。

検証してみました。

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 リファレンスマニュアル 制御フロー関数

MySQL 5.6 リファレンスマニュアル INSERT ... ON DUPLICATE KEY UPDATE 構文

MySQL: INSERT...ON DUPLICATE KEY UPDATEまとめ

INSERT ON DUPLICATE KEY UPDATEの利点と注意点

  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.)" とあります。 ↩

64
42
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
64
42

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?