MySQLのINSERT


ITコーディネータのシュウです。

autumn-leaves_00256

11月も残りわずかになってきました。安倍首相の決断により、衆議院が解散されました。解散については賛否両論があり、今、激しく論争が展開されていますね。世界の先がよく読めないご時世ですから、このような時は、政治のリーダーがとても大切だと思います。私も、12月14日の総選挙には、しっかりと日本のことを考えて投票するつもりです。
また、先日、高倉健さんが逝去されましたね。私のとても好きな俳優さんの一人でした。最後の映画作品「あなたへ」をテレビで見ました。とても感動しました。「健さん、ありがとう!」と思わず言いたくなりました。ご冥福を心よりお祈りいたします。

<本日の題材>
MySQLのINSERT

前回、MySQLについて取り上げたので、今回もMySQLのことをもう少し取り上げてみようと思います。MySQLのINSERT文には興味深い機能がありましたので、取り上げてみました。

●一括INSERT
テーブルに複数のレコードを追加する場合に、OracleやSQL Serverなどでは1行1行INSERT文を記述すると思いますが、MySQLの場合には、以下のような書式で複数のデータをまとめて登録することができます。

INSERT INTO tbl_name (col_name1, col_name2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), (value1, value2, ...);

試してみます。dbsuserユーザでsampledbに接続します。
mysql –u dbsuser –p
パスワードを入力
use sampledb

前回作成した「item_mst」に、追加でデータを3行登録します。Item_mst のテーブル定義を再度確認します。
DESC item_mst;

item_mst_定義確認

これはOracleとの互換のためにサポートされているようです。
他にも以下の方法などでテーブルの定義を確認できるようです。
・SHOW FULL COLUMNS FROM item_mst;
・SHOW CREAT TABLE item_mst;

それでは、今回は商品として野菜を3レコード登録します。
INSERT INTO item_mst(item_cd, item_name, price) values('Y0001', 'キャベツ', 160), ('Y0002', '玉ねぎ', 200), ('Y0003', '人参', 150);

insert_一括

「item_mst」テーブルのレコードを確認します。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

select_item結果

新しく野菜の3行がまとめて登録されてることが確認できました。

●IGNOREオプション
主キー制約や一意性制約を設定した列には、重複した値は制約違反のため挿入することができません。エラーが返ってきます。しかし、IGNOREオプションを指定した場合はエラーを返さずに(データは挿入されず)、終了させることができます。

通常のINSERT文では重複データの登録時にはエラーが返ってきます。
INSERT INTO item_mst(item_cd, item_name, price) values('K0005', 'マンゴー', 300);

insert_キー重複

ここで、IGNOREオプションを付けてINSERTを実行します。
INSERT IGNORE INTO item_mst(item_cd, item_name, price) values('K0005', 'マンゴー', 300);

insert_ignore

エラーを出さずに終了しました。ただエラーが発生していないだけでデータは追加されていません。確認してみます。

SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

select_item結果2

item_cdが「K0005」は、もともとの「もも」のままであることが確認できます。

●ON DUPLICATE KEY UPDATE句
ON DUPLICATE KEY UPDATE を使うと、テーブルの PRIMARY KEY 、もしくは UNIQUE インデックスの値と、INSERT文で挿入しようとしているデータの値が異なれば INSERTを行うが、同じであれば、ON DUPLICATE KEY UPDATE句で指定した値でUPDATE を実行します。

INSERT INTO item_mst(item_cd, item_name, price) values('K0005', 'キウイ', 100)
ON DUPLICATE KEY UPDATE item_name = VALUES(item_name), price = VALUES(price);

item_mst_duplicate

結果を確認してみます。
SELECT item_cd, item_name, price
FROM item_mst
ORDER BY item_cd;

select_item結果3

主キーである「item_cd」が「K0005」で一致していたので、item_name, price がON DUPLICATE KEY UPDATE句で設定された(今回はINSERT文で指定した値)で更新されていることが確認されました。
※更新する値を指定する部分でVALUES(col_name)を使用すると、引数のカラムに対して新規でデータが追加する場合に格納するはずの値を参照できます。

なお、ON DUPLICATE KEY UPDATE句を使う際には、いくつか下記のような制限はあるようです。
・テーブルに主キーか、UNIQUEインデックスがないと、ON DUPLICATE KEY UPDATE は利用できない
・キーが複数行に一致する場合、MySQL は1行のみ更新する
・ON DUPLICATE KEY UPDATE を使用する場合は DELAYED オプションは無視される

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>