INSERT構文
INSERT構文の紹介
INSERTは、テーブルに新しいレコードを挿入します。INSERTには、新しいレコードを挿入するために、VALUESとSETの2種類の構文が用意されています。VALUESの場合は値をテーブルを構成する全フィールドに対応するように順番に指定し、SETはフィールド名と値のペアで必要な分だけ指定していきます。
INSERT ... VALUES構文
INSERT ... VALUES構文でレコードを挿入するための基本的な構文は次の通りです。
INSERT INTO テーブル名 (フィールド名 , フィールド名 ,...) VALUES(値 , 値 ,...);
テーブル名の後に、値を設定するフィールド名を順番に記述します。全ての列にデータを入力する場合、ここのフィールド名は省略しても構いません。VALUESキーワードの後に、登録したい値を記述します。テーブル名の後で指定したフィール名の順番に対応するように記述する必要があります。
INSERT ... SET構文
INSERT ... SET構文でレコードを挿入するための基本的な構文は次の通りです。
INSERT INTO テーブル名 SET フィールド名='値', フィールド名='値', ...;
SETはフィールド名と値のペアを列挙します。
レコードの挿入
それでは、商品(goods)テーブルに1つのレコードを挿入してみましょう。
# INSERT ... VALUES構文 INSERT INTO goods (name,price) VALUES ('Maggot Brain', 2600); # INSERT ... SET構文 INSERT goods SET name='We Won\'t Stop', price=1800;
フィールド名は省略可能です。その場合は、テーブル定義のフィールドの順番通りに値を宣言します。AUTO_INCREMENT宣言されているフィールドは、単純に空の値を指定すると自動的に値が割り当てられます。
レコードを追加したあとの商品テーブル
商品ID(id_g) | 商品名(name) | 価格(price) |
---|---|---|
1 | Afro-American-Arctic | 2300 |
2 | Ready To Die | 1800 |
3 | Fresh | 2300 |
4 | Things Fall Apart | 1600 |
5 | Maggot Brain | 2600 |
6 | We Won't Stop | 1800 |
全フィールドの値を指定して新しいレコードを追加
テーブルで定義されているすべてのフィールドの値を指定する場合は、フィールド名の指定を省略することができます。
# 通常のINSERT ... VALUES構文 INSERT INTO goods (id_g, name, price) VALUES (7, 'Welcome To The Cruel World', '2500'); # フィールド名を省略 INSERT INTO goods VALUES (7, 'Welcome To The Cruel World', '2500');
フィールドの値の指定に式を利用
INSERT構文で指定する「値」に、式を利用することができます。
VALUES(値, フィールド名1 * 2);
計算式の利用
次のクエリ文は、すでにあるレコードを利用して、税込価格のレコードを追加しています。
INSERT goods (name, price) SELECT name, price * 1.05 FROM goods
INSERT構文 (mysql4.1)
DELAYED] [IGNORE]
[INTO] テーブル名 [(フィールド名, ...)]
VALUES ( (値1 | DEFAULT), ...), ...
[ON DEPLICATE KEY UPDATE フィールド名=値, ...]
INSERT
[LOW_PRIORITY |
DELAYED] [IGNORE]
[INTO] テーブル名 [(フィールド名, ...)]
SET フィールド名1=(値1 | DEFAULT), ...
[ON DEPLICATE KEY UPDATE フィールド名=値, ...]
INSERT
[LOW_PRIORITY |
DELAYED] [IGNORE]
[INTO] テーブル名 [(フィールド名, ...)]
SELECT フィールド名リスト
FROM テーブルリスト
WHERE 検索条件
INSERT構文のキーワード
パラメータ | 説明 |
---|---|
INTO | 省略可能なキーワードです。 |
LOW_PRIORITY | テーブルを利用するクライアントがなくなるまで待ってからINSERTを実行します。 (MYSQL:MyISAM テーブルでの使用は控えましょう。 |
IGNORE | 通常では、レコード挿入の際にPRIMARYやUNIQUEと定義されたフィールドで値の重複がある場合、エラーが発生します。IGNOREを宣言しておくと、レコードの挿入が行われないのは同じですが、エラーが発生しません。 |
DELAYED -MySQL拡張 |
INSERT DELAYEDの利点は、多くのクライアントからのINSERTを束ね、一つのブロックで処理されることです。これは別々のINSERTを実行するよりとても速くなります。 この命令文は、MySQLをロギングする際によく利用されます。 |
SELECTサブクエリを使ってレコードを追加
INSERT構文の中でSELECTサブクエリを使用することにより、他のテーブルやビューから選択したレコードを挿入することができます。
SELECTサブクエリ
まずは準備として、INSERT ... SELECT構文で使う過去の商品(goods_old)テーブルを作成します。 過去の商品テーブルはCREATE TABLE ... SELECT構文で商品テーブルをコピーします。その際、LIMITでコピーするレコード数を指定します。
# 過去の商品テーブルの作成 CREATE TABLE goods_old SELECT * FROM goods LIMIT 1; # インデックスの属性などはコピーされないので、追加で定義します ALTER TABLE goods_old MODIFY id_g TINYINT NOT NULL AUTO_INCREMENT, ADD INDEX id_g(id_g); # CREATE TABLE ... SELECT構文で挿入したレコードは必要ないので削除します DELETE FROM goods_old; # 作成したテーブルを確認 DESC goods_old; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id_g | tinyint(4) | | MUL | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | price | decimal(9,0) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+
これで準備が整いました。
商品テーブルに新しいレコードを追加
商品(goods)テーブルに、過去の商品(goods_old)テーブルの値(id_g, name, price)のみを挿入するには、次のようにします。
# 商品テーブルの全レコードを過去の商品テーブルにコピー INSERT INTO goods_old (name, price) SELECT name, price FROM goods; # 登録されたレコードを表示 SELECT * FROM goods_old; +------+----------------------+-------+ | id_g | name | price | +------+----------------------+-------+ | 1 | Afro-American-Arctic | 2300 | | 2 | Ready To Die | 1800 | | 3 | Fresh | 2300 | | 4 | Things Fall Apart | 1600 | | 5 | Maggot Brain | 2600 | | 6 | We Won't Stop | 1800 | +------+----------------------+-------+
INSERTで指定した1番目のフィールドには、SELECTサブクエリで指定した1番目のフィールドの値、2番目以降も同様に挿入されます。
ここで紹介したINSERT ... SELECT構文は同じ構造のテーブルを利用しましたが、値の対応が取れていればテーブル構造が異なっていても問題ありません。
INSERT ... SELECT構文の条件は以下のとおりです。
- クエリは、ORDER BY句を含むことができません。
- INSERT構文のターゲットとなるテーブルは、SELECTサブクエリのFROM句に指定できません。
- プライマリキーの重複違反を無視するには、IGNOREを指定します。
REPLACE構文
REPLACE構文は、INSERT構文と良く似ています。INSERT構文との違いは、UNIQUEインデックス、もしくはPRIMARY KEYなどのユニークなフィールドの値で重複が発生した場合、古いレコードを削除し、新しいレコードを挿入することです。INSERTの場合は古いレコードはそのままで、新しいレコードは挿入されません。
UNIQUEインデックスやPRIMARY KEYが設定されていないテーブルではREPLACEの効果がなく、INSERTとまったく同じ作用となります。
REPLACE構文
REPLACE
[LOW_PRIORITY |
DELAYED] [IGNORE]
[INTO] テーブル名 [(フィールド名, ...)]
VALUES ( (値1 | DEFAULT), ...), ...
[ON DEPLICATE KEY UPDATE フィールド名=値, ...]
REPLACE [LOW_PRIORITY |
DELAYED] [IGNORE]
[INTO] テーブル名 [(フィールド名, ...)]
SET フィールド名1=(値1 | DEFAULT), ...
[ON DEPLICATE KEY UPDATE フィールド名=値, ...]
REPLACE [LOW_PRIORITY |
DELAYED] [IGNORE]
[INTO] テーブル名 [(フィールド名, ...)]
SELECT フィールド名リスト
FROM テーブルリスト
WHERE 検索条件
次のような商品テーブルがあるとします。[商品ID] フィールドの値が 3 のレコードを削除して、同じ [商品ID] フィールドの値で新しいレコードを削除するケースを考えてみましょう。
商品テーブル
商品ID(id_g) | 商品名(name) | 価格(price) |
---|---|---|
1 | Afro-American-Arctic | 2300 |
2 | Ready To Die | 1800 |
3 | Fresh | 2300 |
4 | Things Fall Apart | 1600 |
5 | Maggot Brain | 2600 |
6 | We Won't Stop | 1800 |
古いレコードを削除して新しいレコードを挿入
商品テーブルの[商品ID(id_g)]はPRIMARY KEYでプライマリキーに設定されているので、その値はユニークであることが保障されています。ですから、INSERTで同じ商品IDを登録しようとすると、エラーが発生します。
INSERT goods SET id_g=3, name='Saxophone Colossus'; ERROR 1062 (23000): Duplicate entry '3' for key 1
INSERT構文ではなく、REPLACE構文を使えば、重複した古いレコードは削除され、新しいレコードが追加されます。
# 新しいレコードで古いレコードの上書き REPLACE goods SET id_g=3, name='Saxophone Colossus'; # 商品テーブルの確認 SELECT * FROM goods; +------+----------------------+-------+ | id_g | name | price | +------+----------------------+-------+ | 1 | Afro-American-Arctic | 2300 | | 2 | Ready To Die | 1800 | | 3 | Saxophone Colossus | NULL | | 4 | Things Fall Apart | 1600 | | 5 | Maggot Brain | 2600 | | 6 | We Won't Stop | 1800 | +------+----------------------+-------+
REPLACEで上書き後の商品テーブル
商品ID(id_g) | 商品名(name) | 価格(price) |
---|---|---|
1 | Afro-American-Arctic | 2300 |
2 | Ready To Die | 1800 |
3 | Saxophone Colossus | NULL |
4 | Things Fall Apart | 1600 |
5 | Maggot Brain | 2600 |
6 | We Won't Stop | 1800 |
[価格(price)] フィールドの値に注目してください。先ほどのREPLACE文では、 [価格(price)] フィールドに値を指定しませんでした。REPLACEの処理手順は古いレコードを削除してから新しいレコードを挿入するので、値の指定がなかったフィールドの値は、NULLかデフォルト指定の値が代入されます。前のレコードの値が使われるわけではないので、注意しましょう。