バッチでよくあるやつで、
CSVを使って 一括でテーブルをUPSERT（新規または更新）する流れをまとめてみました。
ちなみに mysql です。
Contents
準備
DB作る
|
1 2 |
create database if not exists `loadtest_db` default character set utf8mb4; |
更新対象テーブルつくる
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `users` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `email` VARCHAR(128) NOT NULL UNIQUE, `name` VARCHAR(32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB ; |
データを投入
|
1 2 3 |
INSERT INTO `users` (`email`, `name`) VALUES ('a@example.com', 'a'), ('b@example.com', 'b'), ('c@example.com', 'c'); |
CSVを用意する
以下コマンドでcsvを生成する。
|
1 2 |
$ mysql -u root -p --database=loadtest_db -h localhost --batch -e "select * from users;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > ~/users.csv |
テーブルをcsvに書き出す方法としては、ほかにも mysqldump コマンドを使う方法があります。
awsのフルマネージドなRDSを使っている場合は、 mysqldump より mysql コマンドのほうが使い勝手は良いです。
仮テーブル作る
|
1 2 |
CREATE TEMPORARY TABLE `users_temp` SELECT * FROM `users` WHERE 1=0; |
WHERE 1=0 はインデックスやユニークキー、主キー制約がすべて除去する意図で指定している。
仮テーブルにはデータを投入したい/全件スキャンしたいだけなので、これらは不要。逆にあると、パフォーマンスが落ちるので。
仮テーブル (users_temp) が意図した形で作成されたか確認
意図した形というのは:
- 必要なフィールド（
id,email,name）が揃っている - インデックスやユニークキー、主キー制約がすべて除去されている
|
1 2 |
show create table `users_temp`; |
|
1 2 3 4 5 6 7 |
CREATE TEMPORARY TABLE `users_temp` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `email` varchar(128) NOT NULL, `name` varchar(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; |
ok
CSVを編集する
今回は検証なので適当に users.csv を編集する:
|
1 2 3 4 5 6 |
"id","email","name" "1","a@example.com","update a" "2","b@example.com","b" "3","c@example.com","c" "4","d@example.com","insert d" |
- id:1 は更新レコード
- id:4 は新規レコード
となるように編集した。
そして以下のようにしてCSVを仮テーブルに投入する。
CSVの1行目はヘッダーなので無視するようにする。
CSVを仮テーブルに投入する
|
1 2 3 4 5 6 7 |
LOAD DATA LOCAL INFILE '~/users.csv' INTO TABLE `users_temp` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" IGNORE 1 LINES (`id`, `email`, `name`) ; |
LOAD DATA LOCAL INFILE の LOCAL というのは、mysqlが動いているサーバではなく、クライアント側のファイルを指定するという意味です。 LOCAL を指定しない場合（つまり LOAD DATA INFILEの場合）はmysqlサーバ上のファイルを指定することになります。しかも /var/lib/mysql など特定の場所からの相対パスであることを想定する必要があります（mysqldの設定による）。
仮テーブルにCSVが入ったか確認
|
1 2 |
select * from users_temp; |
|
1 2 3 4 5 |
1 a@example.com update a 2 b@example.com b 3 c@example.com c 4 d@example.com insert d |
ok
仮テーブルのデータを対象テーブルに投入する
|
1 2 3 4 5 |
INSERT INTO `users` SELECT * FROM `users_temp` ON DUPLICATE KEY UPDATE `name` = VALUES(`name`) ; |
- ON DUPLICATE KEY は
idまたはemailのキーで重複したレコードがあった場合の処理を指定するという意味です。 - UPDATE
name= VALUES(name) 重複レコードは CSVの方の値で更新するという意味です。
また UPDATE対象を複数指定することもできます。
|
1 2 |
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `address` = VALUES(`address`) |
確認
投入前:
投入後:
ok
No comment yet, add your voice below!