「このアイテムをユーザー全員に付与したい」という要望がたまに出るのですが、その時にユーザーIDだけ変更したsqlをユーザーの人数分insertしないといけないというインフラ(運用?)の泥臭いお仕事の一つが発生すると思うのですが、それを効率的に、要領よく、なるべく人の手が入らないように(ミスらないように、手早く)する方法、作業記録をまとめておきます。
vimやLinuxコマンドを使用できる環境が前提です。
挿入するsql
例として挿入するsql文は以下。
INSERT INTO `hoge`.`fuge` (`id`, `user_id`, `foo_id`, `created_at`, `updated_at`) VALUES (NULL , '1', '3', now(), now());
「hoge」データベースの「fuge」カラムに「foo_id = 3」のsqlを登録者数分だけinsertする。というものです。これを19万件insertするという簡単なお仕事。
今回はバルクインサートを使い、500件まとまりのinsert分を作り、ファイルに保存してあとから見返せるようにもしておこうと思います。
手順
19万行分作成
まずはinsertする内容を作成するためにvimを開いて、以下の内容をペースト
(NULL , '1', '3', now(), now()),
ここでvimのコマンド
yy 190000p
で19万の複製が完了、次にユーザーIDに連番を振ります。
自分はvimrcに以下の設定がしてあるので
nnoremap co :ContinuousNumber
vnoremap co :ContinuousNumber
command! -count -nargs=1 ContinuousNumber let c = col('.')|for n in range(1, ?-line('.'):1)|exec 'normal! j' . n . <q>|call cursor('.', c)|endfor
参考: Vimで番号を順番につける方法 | MBA-HACK
縦に並んだ数字に連番を振る事が出来ます。
vimに貼付けた19万行の一番上に移動して連番を振りたい数字に移動、連番を振ってあげます。
gg f1 190000co
これで19万のinsertすべきsqlの中身が出来ました。
「insert.sql」とでもして保存しておきましょう
これを500件まとまりのバルクインサートになるように整形します。
バルクインサート形式に整形
整形するためにはLinuxコマンドの「split」と「sed」を使用します。
保存した「insert.sql」のファイルの階層に移動し、
mkdir split split -l 500 insert.sql split/ ls split/
これで500行区切りのファイル郡が作成されます。
次にバルクインサート形式にファイル内を編集します
cd split sed -i "1s/^/INSERT INTO `hoge`.`fuge` (`id`, `user_id`, `foo_id`, `created_at`, `updated_at`) VALUES\n/" * sed -i "501s/,$/\n;/" *
確認のために以下のコマンドを実行
grep VALUES -L * grep ';' -L *
出力がなければOK
19万1行とか500区切りだと飛び出る分のインサートの場合は最終行の「;」が置換されていないので、ファイルを開いて編集しておいてください。
これでファイル一つ一つの内容がバルクインサートで登録出来る形式になりました。
sqlのインサート
次にファイル内のsql文をコマンドライン上から実行します。
今回はmysqlにインサートします。別途コマンドラインからmysqlに接続出来るように、mysqlのオプションを読み替えてください。
cat * | mysql -t
参考: MySQLのSQLをシェルから実行したい – Qiita [キータ]
これで19万件のsqlが実行されました。
作業記録として作成したsqlファイル群をtarで固めてしかるべき所に保存しておけば作業記録にもなるかと思われます。お疲れさまでした。
