前回(https://shiro-secret-base.com/?p=945)に続き、mysqlで痒いところをまとめるシリーズ
mysqlでSELECTした結果をファイルに出したり、逆にファイルのデータをテーブルにインポートしたりする方法をまとめる。
SELECTした結果をエクスポート
INTO OUTFILEを使う
形式
|
1 2 |
SELECT ${col1},${col2},${col3} FROM ${table} INTO OUTFILE '${file_path}' FIELDS TERMINATED BY '${separator}'; |
${...}は引数
引数
- ${colN}(N=1,2,3...) - カラム名(*にすればもちろん全てのカラムになる)
- ${table} - テーブル名
- ${file_path} - 出力したいファイルパス、絶対パスにすること
- ${separator} - フィールドの区切り文字(CSVで格納したい場合は','カンマにすればよい)
実行例
SELECTした結果をcsvファイルに格納して確認するまで
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
MySQL root@(none):db1> SELECT * FROM tb1; +------+------+------+ | bang | nama | tosi | +------+------+------+ | A101 | 斉藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | +------+------+------+ 5 rows in set Time: 0.013s MySQL root@(none):db1> SELECT * FROM tb1 INTO OUTFILE '/work/files/tb1_20210601.csv' -> FIELDS TERMINATED BY ','; Query OK, 5 rows affected Time: 0.011s MySQL root@(none):db1> exit Goodbye! root@3086fcdb2da7:/work/files# cat tb1_20210601.csv A101,斉藤,40 A102,高橋,28 A103,中川,20 A104,渡辺,23 A105,西沢,35 |
ファイルのデータをテーブルにインポートする
持ち前のCSVデータを一気にテーブルに挿入したい場合は
LOADDATA INFILEを使う
形式
|
1 2 3 |
LOAD DATA INFILE '${file_path}' INTO TABLE ${table} FIELDS TERMINATED BY '${separator}'; |
引数の意味はエクスポートと同じなので割愛
実行例
usersテーブルにusers_sample.csvをインポートし、SELECTで出力するまで
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
MySQL root@(none):db1> DESC users; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | user_id | varchar(255) | NO | PRI | <null> | | | name | varchar(255) | YES | | <null> | | | age | int(10) unsigned | YES | | <null> | | +---------+------------------+------+-----+---------+-------+ 3 rows in set Time: 0.013s MySQL root@(none):db1> LOAD DATA INFILE '/work/files/users_sample.csv' -> INTO TABLE users -> FIELDS TERMINATED BY ','; Query OK, 7 rows affected Time: 0.005s MySQL root@(none):db1> SELECT * FROM users; +---------+----------------+-----+ | user_id | name | age | +---------+----------------+-----+ | A001 | ロックマン | 20 | | A002 | ガッツマン | 22 | | A003 | ファイヤーマン | 23 | | A004 | ボンバーマン | 21 | | A005 | エアーマン | 20 | | A006 | スパークマン | 22 | | A007 | ウッドマン | 25 | +---------+----------------+-----+ 7 rows in set Time: 0.007s |
まとめ
- テーブルのエクスポートは
INTO OUTFILEを使う - テーブルへのインポートは
LOADDATE INFILEを使う
参考記事
- https://phpjavascriptroom.com/?t=mysql&p=mysqlimport
- https://qiita.com/tas6/items/ec7e23278ee2b40aad79
基礎からのMySQL 第3版 (基礎からシリーズ)
2012年の改訂版から5年半ぶりの改訂データベースに触れるのが本当に初めてという方に向けて、「データベースとは何か」という基本中の基本から、MySQLを使ったデータベースの作成と操作、PHPとの連携によるWebアプリケーション作成の入り口までを、豊富な図とサンプルにより解説します。5年間で周辺環境が変わりましたので、そちらを一新しての刊行です。
2012年の改訂版から5年半ぶりの改訂データベースに触れるのが本当に初めてという方に向けて、「データベースとは何か」という基本中の基本から、MySQLを使ったデータベースの作成と操作、PHPとの連携によるWebアプリケーション作成の入り口までを、豊富な図とサンプルにより解説します。5年間で周辺環境が変わりましたので、そちらを一新しての刊行です。