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宣言されているフィールドは、単純に空の値を指定すると自動的に値が割り当てられます。

レコードを追加したあとの商品テーブル

全フィールドの値を指定して新しいレコードを追加

テーブルで定義されているすべてのフィールドの値を指定する場合は、フィールド名の指定を省略することができます。

# 通常の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構文で指定する「値」に、式を利用することができます。

計算式の利用

次のクエリ文は、すでにあるレコードを利用して、税込価格のレコードを追加しています。

INSERT goods　(name, price)
	SELECT name, price * 1.05
	FROM goods

INSERT構文 (mysql4.1)

INSERT構文のキーワード

SELECTサブクエリを使ってレコードを追加

INSERT構文の中で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)]は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で上書き後の商品テーブル

[価格(price)] フィールドの値に注目してください。先ほどのREPLACE文では、 [価格(price)] フィールドに値を指定しませんでした。REPLACEの処理手順は古いレコードを削除してから新しいレコードを挿入するので、値の指定がなかったフィールドの値は、NULLかデフォルト指定の値が代入されます。前のレコードの値が使われるわけではないので、注意しましょう。