MySQLのinsert文について、独自仕様の箇所をサンプルコードを使ってまとめています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
MySQLのinsert構文は3パターン
MySQLのinsert文には以下の3パターンがあります。
- insert … values …
- insert … set …
- insert … select …
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5 INSERT 構文
insert … values … は、csvからの生成が容易
カラムに対して、全ての値を指定する構文です。
insert テーブル名 (カラム名1,カラム名2,…) values (値1,値2,…)
デフォルト値設定やNULLが許可されているカラムに対しても、全ての値を指定しなければならないデメリットがある反面、csvファイルからSQLを生成しやすいというメリットがあります。
以下のSQLは、employees（社員）テーブルに、emp_no（社員番号)を1、birth_date（誕生日）を1982年1月1日、first_name（姓名の名）をTaro、last_name（姓名の姓）をYamada、gender（性別）をM（Male。男性）、hire_date（雇用日）を2002年4月1日に指定して1レコードinsertします。
INSERT `employees`( `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` ) VALUES ( 1, '1982-01-01', 'Taro', 'Yamada', 'M', '2002-04-01' )
実行後のemployeesテーブルは、こうなります。
insert … set … は、カラム指定の省略が可能
必要なカラムのみ指定して記述できる構文です。
insert テーブル名 set カラム名1=値1,カラム名2=値2,…
デフォルト値やNULLが許可されているカラムに対しては、値の指定を省略できます。また、update文とほぼ同じ構文で記述できるため、プログラム内でSQLを組み立て易いというメリットもあります。
以下のSQLは、employees（社員）テーブルに、emp_no（社員番号)を2、birth_date（誕生日）を1983年8月8日、first_name（姓名の名）をHanako、last_name（姓名の姓）をTanaka、gender（性別）をF（Female。女性）、hire_date（雇用日）を2002年4月1日に指定して1レコードinsertします。
INSERT `employees` set `emp_no` = 2, `birth_date` = '1983-08-08', `first_name` = 'Hanako', `last_name` = 'Tanaka', `gender` = 'F', `hire_date` = '2002-04-01'
実行後のemployeesテーブルは、こうなります。
insert … select …
select文の結果をinsertします。
insert テーブル名 select …
下記のSQLは、employees（社員）テーブルから、emp_no（社員番号）=1のデータをemp_no=3として、employeesテーブルにinsertするSQLです。
insert employees SELECT 3, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees` WHERE emp_no = 1
実行後のemployeesテーブルはこうなります。
なお、以下のように元のカラムを使った計算結果（emp_no+10など）を利用したり、複数のselect実行結果をinsert可能です。
下記のSQLは、employees（社員）テーブルから、emp_no（社員番号）が10以下のデータをselectし、emp_noは+10、それ以外のカラムはそのままの値をinsertしています。employeesテーブルにはプライマリキーとしてemp_noのみが設定されているため、emp_no以外は重複が可能となってます。
insert employees SELECT emp_no+10, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees` WHERE emp_no < 10
実行後のemployeesテーブルはこうなります。
MySQLのinsert独自機能
MySQLには、標準SQLにはない独自の拡張機能があります。
【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？
▶【こんな使い方も？】SQL insertの使用方法を徹底的に解説
on duplicate key updateで重複チェックし、存在すればupdateを実行
insertするデータが重複していたら、代わりにupdate文を実行する場合の構文は以下の通り。
insert テーブル名 set カラム名1=値1,カラム名2=値2,… on duplicate key update カラム名1=値1,カラム名2=値2,…
「insert テーブル名 set …」構文でも「insert テーブル名 … values …」構文でも使えます。
実際のサンプルで動きを見てみましょう。emp_no=2のデータがある状態で、以下のSQLを実行するとエラーになります。
INSERT `employees` set `emp_no` = 2, `birth_date` = '1983-08-08', `first_name` = 'Hanako', `last_name` = 'Tanaka', `gender` = 'F', `hire_date` = '2002-04-01'
出力されるエラーは、以下の通りです。プライマリキーが重複していることを表しています。
#1062 – Duplicate entry ‘2’ for key ‘PRIMARY’
下記SQLを実行すると、重複していない場合はinsertを、重複している場合はupdateを実行します。動きが確認できるよう、update時にはfirst_nameをHanako2に変更するようにしました。
INSERT `employees` set `emp_no` = 2, `birth_date` = '1983-08-08', `first_name` = 'Hanako', `last_name` = 'Tanaka', `gender` = 'F', `hire_date` = '2002-04-01' on duplicate key UPDATE `emp_no` = 2, `birth_date` = '1983-08-08', `first_name` = 'Hanako2', `last_name` = 'Tanaka', `gender` = 'F', `hire_date` = '2002-04-01'
実行すると、エラーが出力されることなく処理が終了。実行後のemployeesテーブルはこうなります。
updateが実行され、fist_nameがHanako2に更新されていることが確認できました。
複数のinsertを一括で実行
insert テーブル名 … values 構文を使って、複数のinsertを1文で記述することが可能です。
INSERT `employees`(
`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`
)
VALUES
( 4, '1982-01-01', 'Siro', 'Takahashi', 'M', '2003-07-01' ),
( 5, '1979-08-12', 'Yuji', 'Satoh', 'M', '2003-04-05' ),
( 6, '1981-12-05', 'Kyoko', 'Shimada', 'F', '2005-09-01' ),
( 7, '1975-03-15', 'Yuri', 'Ooishi', 'F', '2004-04-15' )
実行すると1文で4レコードがinsertされます。employeesテーブルはこうなります。
まとめ
- insert … values … は、全カラム指定が必要だが、csvから生成がしやすく複数レコードのinsertが可能
- insert … set … は、カラム指定の省略が可能
- insert … select … は、select結果をinsert可能
- on duplicate key updateを使うと、重複チェックの結果でinsertとupdateを分岐処理可能
