裏MySQL クエリー入門 (5) 応用編2 バルクアップデート

　複数行をまとめてINSERTできるMySQLのバルクインサートはわりかし有名です。というか、世間的にはRDBMSなら、えっできて当然でしょっというような勢いですが、RDBMSの標準機能ではないです。はい。よくmysqldumpでダンプしたデータが、他のRDBMSに流し込んだときにエラーが出たりしますがこれが原因です。『--skip-extended-insert』オプションをあたってみましょう。最近ではPostgresでも実装されています。

以下のような都道府県テーブルをサンプルとして紹介しますと

CREATE TABLE  `pref` (
`pref_id`   INT          NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '都道府県ID',
`pref_name` VARCHAR( 5 ) NOT NULL                            COMMENT  '都道府県名'
) ENGINE = MYISAM COMMENT =  '都道府県テーブル';

バルクインサートのサンプルは以下のようになります。

insert into pref(pref_name) values 
('北海道'),('青森県'),('岩手県'),('宮城県'),('秋田県'),('山形県'),('福島県'),('茨城県'),('栃木県'),('群馬県'),('埼玉県'),('千葉県'),('東京都'),('神奈川県'),('新潟県'),('富山県'),('石川県'),('福井県'),('山梨県'),('長野県'),('岐阜県'),('静岡県'),('愛知県'),('三重県'),('滋賀県'),('京都府'),('大阪府'),('兵庫県'),('奈良県'),('和歌山県'),('鳥取県'),('島根県'),('岡山県'),('広島県'),('山口県'),('徳島県'),('香川県'),('愛媛県'),('高知県'),('福岡県'),('佐賀県'),('長崎県'),('熊本県'),('大分県'),('宮崎県'),('鹿児島県'),('沖縄県');

が、バルクアップデートの仕方は知らない方が結構多いと思うので、やり方を書いておきます。実現方法としては裏MySQL クエリー入門 (1) で出てきたeltとfieldを利用します。まず、上記の例のようにautoincrementを使っていて1から順列になっている場合はeltだけでいけます。

update pref set
pref_name = elt(pref_id,
'北海道','青森県','岩手県','宮城県','秋田県','山形県','福島県','茨城県','栃木県','群馬県','埼玉県','千葉県','東京都','神奈川県','新潟県','富山県','石川県','福井県','山梨県','長野県','岐阜県','静岡県','愛知県','三重県','滋賀県','京都府','大阪府','兵庫県','奈良県','和歌山県','鳥取県','島根県','岡山県','広島県','山口県','徳島県','香川県','愛媛県','高知県','福岡県','佐賀県','長崎県','熊本県','大分県','宮崎県','鹿児島県','沖縄県');

　このサンプルの場合だとあまり意味がありませんが、例えばバルクインサートの内容に誤りがあって修正しなければならない時などに役に立ちます。バルクアップデートを使わない場合、以下のようにテーブルの内容を一度削除したりautoincrementが設定されている場合にはリセットしたりしないといけないので、ちょっと面倒です。

TRUNCATE`pref`;
ALTER TABLE `pref` AUTO_INCREMENT = 1

さらに今回のサンプルでは2カラムでしたが複数カラムある場合に入れなおすのは2度手間になります。

　ちなみに、1から順番にすべて書き換えるのではなく、一部分だけ書き換える場合は、fieldも合わせて使います。全体を書き換える場合でない場合はくれぐれもWHERE句も忘れずに、じゃないと、対象以外のレコードが全部NULLになっちゃいます。

UPDATE `pref` SET 
pref_name = ELT(FIELD(pref_id,2,4,7),'青森県','宮崎県','福島県') 
WHERE pref_id IN (2,4,7)

欄外ちょいテク

都道府県、郵便番号のデータなど公的なデータをWEBサイトのテーブルから取ってきてSQLに加工する場合*1

最近のFirefoxであればctrl+マウスで1列を範囲選択することができます。
それをエディタ等にコピペし、正規表現で置換『^』→『'』、『$』→『','』、エスケープシーケンスで置換『\n』→『』

とすると簡単にSQLに加工できます。

自分メモ

Oracleでは、他のテーブルにもインサートできるマルチテーブル・インサートというのがあるらしい。

INSERT ALL 
INTO table_a(col_a1,col_a2..) VALUES(val_a1,val_a2)
INTO table_b(col_b1,col_b2..) VALUES(val_b1,val_b2)
.
.
SELECT * FROM DUAL;

*1:Excelで該当WEBページを直接開いて、テーブル→csv変換という手もありますがこっちの方が楽チンです。

欄外 ちょいテク

自分メモ

欄外ちょいテク