第3章 SQL構文

INSERT構文:REPLACE構文でデータを追加する

2009年8月17日

INSERT構文

INSERT構文の紹介

INSERTを使って新しいレコードを挿入してみましょう。SELECTと同じく使用頻度が高く、しかもその構文はとてもシンプルです。初めてデータを操作するときは緊張するものですが、とても簡単ですからまずはやってみましょう。

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

INSERT INTOテーブル名 (フィールド名1, フィールド名2, ...)
VALUES(値, フィールド名1 * 2);
計算式の利用

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

INSERT goods (name, price)
SELECT name, price * 1.05
FROM goods
INSERT構文 (mysql4.1)
INSERT [LOW_PRIORITY | 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サブクエリを使用することにより、他のテーブルやビューから選択したレコードを挿入することができます。

INSERT INTO テーブル名 ( フィールド名, フィールド名, ... )
    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かデフォルト指定の値が代入されます。前のレコードの値が使われるわけではないので、注意しましょう。

関連記事

Comment

コメントを残す

メールアドレスが公開されることはありません。

リズムファクトリーはホームページの制作会社です。
ホームページ制作に関するご要望・ご相談はこちらからどうぞ。