MySQL Insert サンプルコード集 一文で一括挿入する方法とは?
  • facebookページ
  • twitterページ
  • 2019.11.15

    MySQL Insert サンプルコード集 一文で一括挿入する方法とは?

    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からの生成が容易

    カラムに対して、全ての値を指定する構文です。

    デフォルト値設定や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します。

    実行後のemployeesテーブルは、こうなります。

    insert … set … は、カラム指定の省略が可能

    必要なカラムのみ指定して記述できる構文です。

    デフォルト値や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します。

    実行後のemployeesテーブルは、こうなります。

    insert … select …

    select文の結果をinsertします。

    下記のSQLは、employees(社員)テーブルから、emp_no(社員番号)=1のデータをemp_no=3として、employeesテーブルにinsertするSQLです。

    実行後のemployeesテーブルはこうなります。

    なお、以下のように元のカラムを使った計算結果(emp_no+10など)を利用したり、複数のselect実行結果をinsert可能です。

    下記のSQLは、employees(社員)テーブルから、emp_no(社員番号)が10以下のデータをselectし、emp_noは+10、それ以外のカラムはそのままの値をinsertしています。employeesテーブルにはプライマリキーとしてemp_noのみが設定されているため、emp_no以外は重複が可能となってます。

    実行後のemployeesテーブルはこうなります。

    MySQLのinsert独自機能

    MySQLには、標準SQLにはない独自の拡張機能があります。

    【関連記事】
    SQLとはどういうもの? 独自拡張と標準SQLの大きな違いって、何? 
    【こんな使い方も?】SQL insertの使用方法を徹底的に解説

    on duplicate key updateで重複チェックし、存在すればupdateを実行

    insertするデータが重複していたら、代わりにupdate文を実行する場合の構文は以下の通り。

    「insert テーブル名 set …」構文でも「insert テーブル名 … values …」構文でも使えます。

    実際のサンプルで動きを見てみましょう。emp_no=2のデータがある状態で、以下のSQLを実行するとエラーになります。

    出力されるエラーは、以下の通りです。プライマリキーが重複していることを表しています。

    #1062 – Duplicate entry ‘2’ for key ‘PRIMARY’

    下記SQLを実行すると、重複していない場合はinsertを、重複している場合はupdateを実行します。動きが確認できるよう、update時にはfirst_nameをHanako2に変更するようにしました。

    実行すると、エラーが出力されることなく処理が終了。実行後のemployeesテーブルはこうなります。

    updateが実行され、fist_nameがHanako2に更新されていることが確認できました。

    複数のinsertを一括で実行

    insert テーブル名 … values 構文を使って、複数のinsertを1文で記述することが可能です。

    実行すると1文で4レコードがinsertされます。employeesテーブルはこうなります。

    まとめ

    ポテパンダの一言メモ
    • insert … values … は、全カラム指定が必要だが、csvから生成がしやすく複数レコードのinsertが可能
    • insert … set … は、カラム指定の省略が可能
    • insert … select … は、select結果をinsert可能
    • on duplicate key updateを使うと、重複チェックの結果でinsertとupdateを分岐処理可能


    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE