INSERT ... SELECT
構文
INSERT DELAYED
構文
INSERT ... ON DUPLICATE KEY UPDATE
構文
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[(col_name
,...)] VALUES ({expr
| DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
または:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
SETcol_name
={expr
| DEFAULT}, ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
または:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[(col_name
,...)] SELECT ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
INSERT
は既存テーブルに新しい行を挿入します。INSERT ... VALUES
と INSERT ... SET
型のステートメントは、明示的に指定された値に基づいて行を挿入します。INSERT ... SELECT
型は別のテーブルから選択された行を挿入します。INSERT ... SELECT
については 項1. 「INSERT ... SELECT
構文」 でさらに詳しく説明されています。
古い行に上書きする為に、INSERT
の代わりに REPLACE
を利用する事ができます。REPLACE
は、古い行を複製する固有キー値を含む新しい行の取り扱いの中では INSERT IGNORE
と同等になります。新しい行は捨てられるのではなく、古い行を置き換えるのに利用されます。詳しくは 項 「REPLACE
構文」 を参照してください。
tbl_name
は行が挿入されるべきテーブルです。ステートメントが値を提供しなければいけないカラムは次のように指定する事ができます。
テーブル名の後にカンマで区切られたカラム名のリストを提供する事ができます。この場合、名前が付けられた各カラムの値には VALUES
リストか SELECT
ステートメントが与えられなければいけません。
もし INSERT ... VALUES
か INSERT ... SELECT
にカラム名のリストを指定しなければ、VALUES
リストか SELECT
ステートメントがテーブル内全てのカラムの値を提供する必要があります。もしテーブル内のカラムの順番がわからなければ、それを見つける為に DESCRIBE tbl_name
を利用してください。
SET
条項はカラム名を明示的に指示します。
カラム値を与える方法はいくつかあります。
もしストリクト SQL モードを利用していない場合、値を明示的に与えられていないカラムはそのデフォルト値(明示的、または暗黙の)に設定されます。例えば、もしテーブル内の全てのカラムに名前を付けないカラムのリストを指定すると、名づけられていないカラムはそのデフォルト値に設定されます。デフォルト値の割り当てについては 項 「データタイプデフォルト値」 で説明しています。項2. 「無効値の制約」 もご参照ください。
もし、デフォルト値を持たない全てのカラムの値を明示的に指定しない限りエラーを発生させる INSERT
ステートメントが必要であれば、ストリクト モードを利用する必要があります。詳しくは 項 「SQL モード」 を参照してください。
明示的にカラムをそのデフォルト値に設定するには、キーワード DEFAULT
を利用してください。これはテーブル内の各カラムの値を持たない不完全な VALUES
リストの書き込みを防ぐ事ができるので、一部を除く全てのカラムに値を割り当てる INSERT
ステートメントの書き込みを簡単にします。そうでなければ、VALUES
リスト内のそれぞれの値に対応するカラム名のリストを書かなければいけません。
また、与えられたカラムのデフォルト値を生成する式の中で利用されるより一般的な形として、DEFAULT(col_name)
を利用する事もできます。
もしカラム リストと VALUES
リストの両方が空なら、INSERT
は各カラム セットを利用してそのデフォルト値に行を作成します。
INSERT INTO tbl_name
() VALUES();
ストリクト モードでは、もしどのカラムもデフォルト値を持っていなければエラーが発生します。そうでなければ、MySQL は明示的に指定されたデフォルト値を持たない全てのカラムに対して暗黙のデフォルト値を利用します。
カラム値を提供する為に expr
式を指定する事ができます。もし式のタイプがカラムのタイプに合わなければ、タイプの変換が行われる可能性があり、そしてデータのタイプによっては、与えられた値の変換が別の挿入値を生み出す事があります。例えば、文字列
'1999.0e-2'
を、INT
、FLOAT
、DECIMAL(10,6)
、または YEAR
カラムに挿入すると、それぞれ 1999
、19.9921
、19.992100
、そして 1999
が挿入される事になります。INT
と YEAR
カラムに格納された値が 1999
である理由は、文字列から整数への変換時には、文字列の最初の部分の、有効な整数や年として判断される部分だけを見るからです。浮動小数点と固定小数点カラムに対しては、文字列から浮動小数点への変換時には、文字列全体を有効な浮動小数点として判断します。
expr
式は、値のリストに早いうちに設定された全てのカラムを参照する事ができます。例えば、col2
の値は、既に割り当てられた col1
を参照する事ができるので、上記のような事が可能なのです。
INSERT INTO tbl_name
(col1,col2) VALUES(15,col1*2);
しかし、col1
の値は、col1
の後で割り当てられた col2
を参照する為、次のような物は正当ではありません。
INSERT INTO tbl_name
(col1,col2) VALUES(col2*2,15);
1つの例外が AUTO_INCREMENT
値を持つカラムに関係しています。別の値の割り当て後に AUTO_INCREMENT
値が発生するので、その割り当ての中での AUTO_INCREMENT
カラムへの参照は 0
を返すのです。
VALUES
構文を利用する INSERT
ステートメントは複数行を挿入する事ができます。これをする為には、それぞれが括弧で囲まれカンマで区切られている、カラム値の複数リストを含んでください。例:
INSERT INTO tbl_name
(a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
各行の値のリストは括弧で囲まれている必要があります。次のステートメントは、カラム名の数とリストの中の値の数が合わない為、不正となります。
INSERT INTO tbl_name
(a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
INSERT
の行に影響された値は mysql_affected_rows()
C API 関数を利用して取得できます。詳しくは 項1. 「mysql_affected_rows()
」 を参照してください。
もし INSERT ... VALUES
ステートメントを複数値リストか INSERT ... SELECT
と共に利用すると、そのステートメントはこのフォーマットで文字列情報を戻します。
Records: 100 Duplicates: 0 Warnings: 0
Records
はステートメントによって生成された行数を指示します。(Duplicates
がゼロ以外の数値になりえる為、実際に挿入された行数でなければいけないという訳ではありません。)既存の固有インデックス値を複製するので、Duplicates
は挿入できなかった行数を指示します。Warnings
は、何らかの形で問題があった、カラム値挿入の試みの回数を指示します。警告は次のような条件時に発生します。
NOT NULL
を宣言していたカラムに NULL
を挿入する。複合行 INSERT
ステートメントや INSERT INTO ... SELECT
ステートメントに対しては、カラムはカラム データ タイプの暗黙のデフォルト値に設定されます。数値タイプ、文字列タイプの空の文字列(''
)、そして日付、時間タイプの 「ゼロ」 値に対してのこの値は 0
です。サーバは単列を戻すかどうかを確認する為に SELECT
からの結果セットを検査しない為、INSERT INTO ... SELECT
ステートメントは複合行挿入と同じ方法で扱われます。(単列 INSERT
に対しては、NULL
が NOT NULL
カラムに挿入された時、警告は発生しません。代わりに、ステートメントはエラーになり失敗します。)
数値カラムを、範囲外の値に設定する。値はその範囲の終点に一番近いところでクリップされます。
数値カラムに '10.34 a'
のような値を割り当てる。後続の非数値テキストは取り除かれ、残りの数値部分が挿入されます。もし文字列値がその最初の部分に数値を持たない場合は、そのカラムは
0
に設定されます。
文字列を、カラムの最大長さを上回る文字列カラムに (CHAR
、VARCHAR
、TEXT
、または BLOB
)挿入する。値はカラムの最大長さまで切り捨てられます。
日付や時間カラムに不正である値を挿入する。カラムはそのタイプにとって適正であるゼロの値に設定されます。
もしC API を利用していれば、mysql_info()
関数を呼び出す事で、情報文字列を得ることができます。詳しくは 項35. 「mysql_info()
」 を参照してください。
もし AUTO_INCREMENT
カラムを持つテーブルに INSERT
が行を挿入すれば、SQL LAST_INSERT_ID()
関数を利用する事でそのカラムに利用される値を見つける事ができます。C API の内部から、mysql_insert_id()
関数を利用してください。しかし、2つの関数がいつでも全く同じ働きをする訳ではない事に注意してください。AUTO_INCREMENT
カラムに関しての、INSERT
ステートメントの動作の更なる情報は 項 「情報関数」 と 項37. 「mysql_insert_id()
」 で紹介されています。
INSERT
ステートメントは次の修飾因子をサポートします。
もし DELAYED
キーワードを利用すると、サーバはバッファに挿入される行を置くので、INSERT DELAYED
ステートメントを発行するクライアントは即座に再開されます。もしテーブルが使用中であれば、サーバが行を保持します。テーブルがフリーであればサーバは行の挿入を始め、新しいリクエストがないかを調べる為に定期的にテーブルを確認します。もしあれば、遅れた行の列はテーブルが再度フリーになるまでサスペンドされます。項2. 「INSERT DELAYED
構文」 を参照してください。
サーバは、INSERT ... SELECT
か
INSERT ... ON DUPLICATE KEY UPDATE
に対して
DELAYED
を無視します。
もし LOW_PRIORITY
キーワードを利用すると、別のクライアントがテーブルからの読み込みをしなくなるまで、INSERT
の実行が遅れます。これには、既存クライアントが読み込みをしている最中、そして INSERT LOW_PRIORITY
ステートメントが待っている最中に読み込みを始めてしまう別のクライアントが含まれます。しかし、INSERT LOW_PRIORITY
ステートメントを発行するクライアントがリード ヘビー環境の中で長時間(または永遠に)待つ事は可能です。(これは、クライアントにそのまま作業を続けさせる
INSERT DELAYED
とは逆の機能です。並列挿入ができなくなるので、通常 LOW_PRIORITY
は MyISAM
テーブルと一緒には利用されません。詳しくは 項 「同時挿入」 を参照してください。
もし HIGH_PRIORITY
を指定すると、サーバが --low-priority-updates
オプションでスタートされている場合その効果が無効になります。また、並行挿入も利用されなくなります。
もし IGNORE
キーワードを利用したら、INSERT
ステートメントの実行中に起きたエラーは警告として扱われます。例えば、IGNORE
が無いと、テーブル内に既存の UNIQUE
インデックスや PRIMARY KEY
値を複製する行に複製キー エラーが起き、そのステートメントは異常終了されます。IGNORE
を利用すると、行の挿入はされませんが、エラーも発行されません。もし IGNORE
が指定されなければ、データ変換はステートメントに関するエラーを引き起こします。IGNORE
を利用すると、不正な値は一番近い値に調節されて挿入され、警告が発生されますがステートメントは異常終了しません。いくつのテーブルが実際に挿入されたのか、mysql_info()
C API 関数を利用して調べる事ができます。
もし ON DUPLICATE KEY UPDATE
を指定し、UNIQUE
インデックスか PRIMARY KEY
内で複製値を引き起こす行が挿入されると、古い行の UPDATE
が実行されます。詳しくは 項3. 「INSERT ... ON DUPLICATE KEY UPDATE
構文」 を参照してください。
INSERT ... SELECT
構文INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[(col_name
,...)] SELECT ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
INSERT ... SELECT
を利用すると、1つ、または複数のテーブルから多くの行をすばやく挿入する事ができます。例:
INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
次の条件は INSERT ... SELECT
ステートメントを保持します。
複製キー違反を引き起こす行を無視する為に IGNORE
を指定してください。
DELAYED
は INSERT ... SELECT
と共に無視されます。
INSERT
ステートメントの対象テーブルはクエリの SELECT
部のFROM
条項内に現れます。(これは MySQL の古いバージョンでは不可能でした。)この場合、MySQL は行を保持する為に SELECT
からテンポラリ テーブルを作成し、そして対象テーブルにそれらの行を挿入します。
AUTO_INCREMENT
カラムは通常通り機能します。
バイナリ ログが元テーブルを再作成する為に利用できる事を保障する為に、MySQL は INSERT ... SELECT
ステートメントへの並列挿入を許可しません。
現在は、サブクエリの中で1つのテーブルに挿入し、同じテーブルから選択する事はできません。
SELECT
と INSERT
が同じテーブルを参照した時に、曖昧なカラム参照の問題を防ぐ為に、SELECT
部分で利用される各テーブルに固有のエイリアスを与え、その部分の中でのカラム名を適切なエイリアスに限定してください。
ON DUPLICATE KEY UPDATE
の値部分の中で、SELECT
部分の中で GROUP BY
を利用しない限り、別のテーブル内でカラムの参照をする事ができます。値の部分で非固有カラム名を指定しなければいけない、という副作用が1つあります。
INSERT DELAYED
構文INSERT DELAYED ...
INSERT
ステートメントの DELAYED
オプションは、もし INSERT
が完了するのを待つ事ができない、または待つ必要がないクライアントを持っている場合に大変有効となる、スタンダード SQL の MySQL 拡張子です。これは、MySQL
をログに利用し、完了までに長時間かかる SELECT
と UPDATE
ステートメントを定期的に起動させる時によく起こる状態です。
クライアントが INSERT DELAYED
を利用する時、サーバからすぐに OK が出て、テーブルが別のスレッドによって使用中でなければ行が挿入される為にキューを作ります。
INSERT DELAYED
を利用する事のそれ以外の大きな利益は、たくさんのクライアントからの挿入は一緒にまとめられ、ひとつのブロックに書き込まれると言う事です。これは、別々の挿入を何度も行うよりも早く機能します。
INSERT DELAYED
は、もしテーブルが他の形で利用されていないのであれば、通常の INSERT
よりも遅いという事に注意してください。また、サーバには、遅れている行を持つ各テーブルに別々のスレッドを扱う為の、追加オーバーヘッドもあります。これは、本当に
INSERT DELAYED
が必要だという事が確実な時だけ利用するべきであるという事を意味します。
キューを作った行は、テーブルに挿入されるまでメモリ内だけで保持されます。これは、もしmysqld を強制的に終了させたり (例えば、kill -9
を利用して)、mysqld が突然停止してしまったりすると、ディスクに書き込まれる前のキューを作った行は全て失われてしまう という事を意味します。
DELAYED
の利用に関しては、いくつかの制限があります。
INSERT DELAYED
は MyISAM
、MEMORY
、そして ARCHIVE
テーブルとのみ機能します。項13.4. 「MyISAM
ストレージエンジン」、項13.7. 「MEMORY
(HEAP
) ストレージエンジン」、そして 項13.10. 「ARCHIVE
ストレージエンジン」 を参照してください。
もしデータファイル中にフリー ブロックがなければ、MyISAM
テーブルには並列 SELECT
と INSERT
ステートメントがサポートされます。これらの条件下では、INSERT DELAYED
を MyISAM
と一緒に利用しなければいけない事はほとんどありません。
INSERT DELAYED
は、値リストを指定する INSERT
ステートメントにだけ利用されなければいけません。サーバは、INSERT ... SELECT
か INSERT ... ON DUPLICATE KEY UPDATE
に対して DELAYED
を無視します。
INSERT DELAYED
ステートメントがすぐに返されるので、そのステートメントが生成するであろう AUTO_INCREMENT
値を得る為に、行が挿入される前に、LAST_INSERT_ID()
を利用する事はできません。
DELAYED
行は、実際に挿入されるまでは SELECT
ステートメントには見えません。
DELAYED
は、スレーブにマスタとは異なるデータを持たせる事があるので、スレーブ複製サーバ上では無視されます。
テーブルが書き込みロックされ、ALTER TABLE
がテーブル構造を変更するのに利用されると、保留中の INSERT DELAYED
ステートメントは失われてしまいます。
INSERT DELAYED
は画面をサポートしません。
次に、INSERT
や REPLACE
に DELAYED
を利用した時に何が起こるかを詳しく説明しています。この説明の中では、「スレッド」 は INSERT DELAYED
ステートメントを受け取ったスレッドで、「ハンドラ」 は特定のテーブルの為に全ての INSERT DELAYED
ステートメントを扱うスレッドを表しています。
スレッドが DELAYED
ステートメントをテーブルに実行した時、もし同じようなハンドラが既に存在していなければ、全ての DELAYED
ステートメントをテーブルに生成する為にハンドラ スレッドが作成されます。
スレッドは、ハンドラが以前に DELAYED
ロックを習得したかどうかを確認します。もし習得していなければ、ハンドラ スレッドに対して習得するよう命令します。もし他のスレッドが READ
か WRITE
ロックをテーブル上に持っていても、DELAYED
ロックを得る事ができます。しかし、ハンドラはテーブル構造が最新であるかどうかを確認する為に、全ての ALTER TABLE
ロックや FLUSH TABLES
ステートメントが終了するのを待ちます。
スレッドは INSERT
ステートメントを実行しますが、行をテーブルに書き込む代わりに、ハンドラ スレッドに管理されているキューに最終行のコピーを置きます。構文エラーは全てスレッドに見つけられ、クライアント
プログラムにリポートされます。
クライアントは、挿入操作が完了する前に INSERT
が返る為、複製行の数や、結果として生じる行の AUTO_INCREMENT
値をサーバから得る事ができません。(もし C API を利用すると、同じ理由で mysql_info()
関数からは意味のある答えが返りません。)
行がテーブルに挿入された時、バイナリ ログはハンドラ スレッドによって更新されます。複合行挿入の場合、最初の行が挿入された時にバイナリ ログが更新されます。
delayed_insert_limit
行が書かれる度に、ハンドラはまだ保留中の SELECT
ステートメントがないかどうかを確認します。もしあれば、続ける前にそれらを実行させます。
ハンドラのキューに行が無くなると、テーブルのロックは外されます。もし新しい INSERT DELAYED
ステートメントが delayed_insert_timeout
秒以内に受信されたら、ハンドラは終了します。
もし delayed_queue_size
以上の行が、特定のハンドラ キューの中で保留中だったら、 INSERT DELAYED
をリクエストしているスレッドは、キューの中にスペースができるまで待ちます。これは、遅れたメモリのキューの為に mysqld が全てのメモリを使わない事を保障する為に行われます。
ハンドラスレッドは、Command
カラム内の delayed_insert
と共に、MySQL プロセス リスト内に現れます。これは、もし FLUSH TABLES
ステートメントを実行したり、KILL thread_id
を利用したりすると中止されます。しかし、終了する前にまずテーブル内でキューを作っている全ての行を格納します。この最中は、別のスレッドから新しい
INSERT
ステートメントを受け入れません。もしこの後に INSERT DELAYED
ステートメントを実行すると、新しいハンドラ スレッドが作成されます。
もし起動中の INSERT DELAYED
ハンドラがあったら、INSERT DELAYED
ステートメントは通常の INSERT
ステートメントより高い優先権を持つという事を意味します。その他の更新ステートメントは、INSERT DELAYED
キューが空になるか、誰かがハンドラスレッドを終了させるか (KILL thread_id
を利用して)、誰かが FLUSH TABLES
を実行するまで待たなければいけません。
次の状態変数は INSERT DELAYED
ステートメントの情報を提供します。
状態変数 | 意味 |
Delayed_insert_threads |
ハンドラ スレッド数 |
Delayed_writes |
INSERT DELAYED で書かれた行数 |
Not_flushed_delayed_rows |
書き込みを待つ行数 |
SHOW STATUS
ステートメントか、mysqladmin extended-status コマンドを実行する事でこれらの変数を見る事ができます。
INSERT ... ON DUPLICATE KEY UPDATE
構文もし ON DUPLICATE KEY UPDATE
を指定し、UNIQUE
インデックスか PRIMARY KEY
内で複製値を引き起こす行が挿入されると、古い行の UPDATE
が実行されます。例えば、もしカラム a
が UNIQUE
として宣言され、それが値 1
を含んでいたら、次の2つのステートメントは同一効果を持ちます。
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1;
もしその行が新しいレコードとして挿入されると、行に影響される値は1となり、もし既存レコードが更新されると2になります。
もしカラム b
も固有であれば、INSERT
は代わりにこの UPDATE
ステートメントと同等になります。
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
もし a=1 OR b=2
がいくつかの行とマッチすれば、1つの 行だけが更新されます。通常、複数の固有インデックスを持つテーブル上で ON DUPLICATE KEY
条項を利用するのは避けるべきです。
INSERT ... UPDATE
ステートメントの INSERT
部分からカラム値を参照する為に、UPDATE
条項の中で VALUES(col_name)
関数を利用する事ができます。言い換えると、UPDATE
条項の中の VALUES(col_name)
は、挿入される col_name
の値を参照し、複製キーの矛盾は起きないという事です。この関数は、時に複合行挿入をする時に有効です。VALUES()
関数は、INSERT ... UPDATE
ステートメントの中でだけ意味を持ち、そうでなければ NULL
を返します。例:
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
そのステートメントは次の2つと同一です。
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3; INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;
もしテーブルが AUTO_INCREMENT
カラムを含み INSERT ... UPDATE
が行を挿入すると、LAST_INSERT_ID()
関数は AUTO_INCREMENT
値を返します。もしステートメントが代わりに行を更新すると、LAST_INSERT_ID()
は無意味になります。しかし、LAST_INSERT_ID(expr)
を利用する事でこれに対処する事ができます。id
が AUTO_INCREMENT
カラムだと仮定してください。LAST_INSERT_ID()
が更新に意味を持つようにするには、次のように行を挿入してください。
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
ON DUPLICATE KEY UPDATE
を利用する時は DELAYED
オプションは無視されます。