🕌

[MySQL8.0]UpsertのVALUES()関数が非推奨になったので修正しました

2023/05/20に公開

Upsertを記述する際に利用していた ON DUPLICATE KEY UPDATE A=VALUES(A); のVALUES()が、MySQL8.0.20で非推奨になり、将来のバージョンで削除されるようなので、あらかじめ修正しようと思います！

テーブル定義

この記事内では下記のテーブル定義で説明します。

データは2レコード登録されています。

mysql> select * from EMPLOYEE;
+-------------+---------------+---------------+--------+---------------------+---------------------+
| EMPLOYEE_ID | DEPARTMENT_ID | EMPLOYEE_NAME | GENDER | CREATE_AT           | UPDATE_AT           |
+-------------+---------------+---------------+--------+---------------------+---------------------+
| dummy       |             1 | ダミー        | FEMALE | 2023-05-14 08:04:26 | 2023-05-14 08:04:26 |
| dummy1      |             2 | ダミー1       | MALE   | 2023-05-14 08:04:26 | 2023-05-14 08:04:26 |
+-------------+---------------+---------------+--------+---------------------+---------------------+
2 rows in set (0.02 sec)

そもそもUpsertとは？

Upsert（アップサート）は、データベース操作において、既存のレコードが存在する場合には更新（update）を行い、存在しない場合には新規レコードの挿入（insert）を行う操作を指します。
※updateとinsertの組み合わせから派生した言葉

INSERT ... ON DUPLICATE KEY UPDATE ステートメント

Upsertをするための構文は、INSERT文に ON DUPLICATE KEY UPDATE 句を指定します。そうすると、UNIQUE インデックスまたは PRIMARY KEY で値が重複する場合、古い行の UPDATE が発生します。

例えば、下記のUpsertを流すと、dummyとdummy1は存在するレコードなのでアップデートが走り、dummy2は存在しないレコードなので新規登録がされます。

insert into EMPLOYEE 
 (EMPLOYEE_ID, DEPARTMENT_ID, EMPLOYEE_NAME, GENDER) 
values
 ('dummy',3,'ダミー（更新）','FEMALE'),
 ('dummy1',2,'ダミー1（更新）','MALE'),
 ('dummy2',1,'ダミー2','MALE')
on duplicate key 
update 
 DEPARTMENT_ID = VALUES(DEPARTMENT_ID),
 EMPLOYEE_NAME = VALUES(EMPLOYEE_NAME),
 GENDER = VALUES(GENDER)

実行したあとのデータ状態

mysql> select * from EMPLOYEE;
+-------------+---------------+------------------------+--------+---------------------+---------------------+
| EMPLOYEE_ID | DEPARTMENT_ID | EMPLOYEE_NAME          | GENDER | CREATE_AT           | UPDATE_AT           |
+-------------+---------------+------------------------+--------+---------------------+---------------------+
| dummy       |             3 | ダミー（更新）         | FEMALE | 2023-05-14 08:04:26 | 2023-05-20 01:35:44 |
| dummy1      |             2 | ダミー1（更新）        | MALE   | 2023-05-14 08:04:26 | 2023-05-20 01:35:44 |
| dummy2      |             1 | ダミー2                | MALE   | 2023-05-20 01:35:44 | 2023-05-20 01:35:44 |
+-------------+---------------+------------------------+--------+---------------------+---------------------+
3 rows in set (0.03 sec)

dummyとdummy1のレコードが更新されて、dummy2が追加になっているのが分かりますね。

MySQL8.0.20からVALUES()関数が非推奨になる

どの部分が非推奨になったのでしょうか？

先のSQLの例でいうとON DUPLICATE KEY UPDATE 句にある VALUES()になります。

on duplicate key 
update 
 DEPARTMENT_ID = VALUES(DEPARTMENT_ID),
 EMPLOYEE_NAME = VALUES(EMPLOYEE_NAME),
 GENDER = VALUES(GENDER)

このVALUES()関数は、INSERT ... ON DUPLICATE KEY UPDATE ステートメントの INSERT 部分からカラム値を参照できます。

どういうことかというと、次のSQLがあった場合、

insert into EMPLOYEE
 (EMPLOYEE_ID, DEPARTMENT_ID, EMPLOYEE_NAME, GENDER) 
values 
 ('dummy',3,'ダミー（更新）','FEMALE') 
on duplicate key 
update 
 DEPARTMENT_ID = VALUES(DEPARTMENT_ID),
 EMPLOYEE_NAME = VALUES(EMPLOYEE_NAME)

VALUES()関数によってINSERT部分からカラム値を参照したあとのSQLは下記のイメージです。

insert into EMPLOYEE 
 (EMPLOYEE_ID, DEPARTMENT_ID, EMPLOYEE_NAME, GENDER) 
values 
 ('dummy',3,'ダミー（更新）','FEMALE') 
on duplicate key 
update 
 DEPARTMENT_ID = 3,
 EMPLOYEE_NAME = 'ダミー（更新）'

つまり、VALUES（カラム名）とすると、指定したカラムに該当する登録したい値を取得してくれます。

この関数は、複数行を登録したいときに非常に便利でした。

どのように修正するか？

さて、非常に便利なVALUES()関数が非推奨になってしまったため、どのように修正すれば良いでしょうか？

方法としては2つあります。

行エイリアス
列エイリアス

いずれも場合も、INSERT文のVALUESまたはSET句の後にAS newキーワードを付けることは共通しています。

具体的にコードで見た方が理解が早いと思うのでそれぞれ見ていきます。

行エイリアス

insert into EMPLOYEE 
 (EMPLOYEE_ID, DEPARTMENT_ID, EMPLOYEE_NAME, GENDER) 
values 
 ('dummy',3,'ダミー（更新）','FEMALE'),
 ('dummy1',2,'ダミー1（更新）','MALE'),
 ('dummy2',1,'ダミー2','MALE')
 AS new
on duplicate key 
update 
 DEPARTMENT_ID = new.DEPARTMENT_ID,
 EMPLOYEE_NAME = new.EMPLOYEE_NAME,
 GENDER = new.GENDER

INSERT文のVALUESの後にAS newキーワードを付けることで行単位をエイリアスにしています。

ON DUPLICATE KEY UPDATE句で、new.カラム名を指定することで、VALUES（カラム名）を指定したときと同様の動作をしてくれます。

列エイリアス

insert into EMPLOYEE 
 (EMPLOYEE_ID, DEPARTMENT_ID, EMPLOYEE_NAME, GENDER) 
values 
 ('dummy',3,'ダミー（更新）','FEMALE'),
 ('dummy1',2,'ダミー1（更新）','MALE'),
 ('dummy2',1,'ダミー2','MALE')
 AS new(employeeId, departmentId, employeeName, sex)
on duplicate key 
update 
 DEPARTMENT_ID = departmentId, 
 EMPLOYEE_NAME = employeeName, 
 GENDER = sex

INSERT文のVALUESの後にAS new(employeeId, departmentId, employeeName, sex)キーワードを付けました。これは、カラム（列）をエイリアスにしています。

ON DUPLICATE KEY UPDATE句で、列エイリアスを指定することで、VALUES（カラム名）を指定したときと同様の動作をしてくれます。

この例では、ON DUPLICATE KEY UPDATE句で行エイリアス（new）を省略して列エイリアスのみを指定しています。その場合は注意が必要で、行エイリアス(new）を省略するときは、列エイリアスはテーブル名と同名にできないという制約があります。そのため、GENDERをsexにしています。

ですが、どうしてもテーブル名と列エイリアスを同名にしたいということであれば、ON DUPLICATE KEY UPDATE句で行エイリアス（new）をきちんと指定すれば可能です。

insert into EMPLOYEE 
 (EMPLOYEE_ID, DEPARTMENT_ID, EMPLOYEE_NAME, GENDER) 
values 
 ('dummy',3,'ダミー（更新）','FEMALE'),
 ('dummy1',2,'ダミー1（更新）','MALE'),
 ('dummy2',1,'ダミー2','MALE')
 AS new(employeeId, departmentId, employeeName, gender)
on duplicate key 
update 
 DEPARTMENT_ID = new.departmentId, 
 EMPLOYEE_NAME = new.employeeName, 
 GENDER = new.gender

また、ON DUPLICATE KEY UPDATE句で列エイリアスemployeeIdを使っていませんが、どうやら列エイリアスで指定したカラム位置で参照する値を決めているようなので、省略はできず指定する必要があります。

行エイリアスと列エイリアスのどちらを使う？

私個人としては、行エイリアスの方が使い勝手が良いかなと感じました。

行エイリアスの方が、これまでのVALUES()関数との使い方が似ていますし、列エイリアスだと、カラム数が多くなったときにエイリアスをつけるのが面倒くさかったり、保守性の観点でも行エイリアスの方が優れているかなと思いました。

ですので、私は行エイリアスを推奨していこうと思います！

以上です。