MySQLを利用して開発を行っていると、csvからサンプルデータをimportしたり、DBの中身をエクスポートする場合があります。
Pentaho(Spoon)等のETLツールを使うと、スクリプト化したり、項目の加工を行ったり、エクセルからimportしたり、エクスポートしたりすることもできますので、ぜひその記事も読んで活かしてほしいですが、今回は、シンプルにMySQLの機能を使って実現することにしましょう。
エクスポートする方法
以下のmysqlコマンド構文でcsvファイルをエクスポートできます。
気をつけたいのは、「escaped by」で指定するエスケープ文字ですね。
デフォルトは「」になっていますので、カラムの囲み文字と同じ文字がレコードに含まれると、「」でエスケープされ、「”AD”,”アン”ドラ”」のように出力されます。
通常、csvでは「”」がエスケープに使われますので、それと同じにするには「escaped by ‘”‘」を指定した方が良いと思います。
mysql>
select * from table_name into outfile "D:\export_file_name.csv"
fields
terminated by ',' /* カラムの区切り文字 */
enclosed by '"' /* カラムの囲み文字 */
escaped by '"' /* エスケープ文字(デフォルトは) */
lines terminated by 'rn'; /* 1レコードの改行コード */上記のコマンドで「select * from table_name」の部分が、出力カラムを指定しているところで、基本的にSQLのSelectの結果をファイル出力しています。
そのため、この部分に「select concat(CODE, ‘-‘, COUNTRY), CODE, COUNTRY from table_name」のようなSQLの関数等を使用してカラムに対して加工を行うことも可能です。
上記を応用すると、ヘッダをつけることもできます。
一行目に文字列を出力し、selectの結果とUNIONします。
mysql>
(select 'CODE', 'COUNTRY')
UNION
(select CODE, COUNTRY from country into outfile "D:\export_file_name.csv"
fields
terminated by ',' /* カラムの区切り文字 */
enclosed by '"' /* カラムの囲み文字 */
escaped by '"' /* エスケープ文字(デフォルトは) */
lines terminated by 'rn' /* 1レコードの改行コード */
);上記のように出力すると、Nullを含むレコードは「”N」のように出力されておかしくなってしまいます。
そこで、IFNULL()関数を使用して、あらかじめNullだったらどのように置換するかを指定しておくと良いです。
mysql>
select CODE, IFNULL(COUNTRY, '') from country into outfile "D:\export_file_name.csv"
fields
terminated by ',' /* カラムの区切り文字 */
enclosed by '"' /* カラムの囲み文字 */
escaped by '"' /* エスケープ文字(デフォルトは) */
lines terminated by 'rn'; よく使われる形式として、タブ区切りの形式(tsv)もあります。
上記の原理が分かれば簡単ですね。以下のように指定します。
mysql>
select * from table_name into outfile "D:\export_file_name.csv"
fields
terminated by 't' /* カラムの区切り文字 */
lines terminated by 'rn' /* 1レコードの改行コード */インポートする方法
以下のmysqlコマンド構文でcsvファイルをインポートできます。
mysql>
load data local infile "D:\import_file_name.csv" into table table_name
fields terminated BY ',' /* カラムの区切り文字 */
enclosed by '"' /* カラムの囲み文字 */
lines terminated by 'rn' /* 1レコードの改行コード */
IGNORE 1 LINES; /* 1行を無視する */一行目にヘッダのあるcsvファイルの場合には、上記のように「GNORE 1 LINES」を指定すると1行目を読み飛ばしてくれます。
通常はそのままcsvをインポートしますが、その際に、なんらかの加工を行いたい場合があります。
たとえば日付型のフォーマット加工や文字列置換などです。
その場合には、「ユーザ変数」を利用すると便利です。
上記では、例として、CODEとCOUNTRYというカラムを持つテーブルのCOUNTRYの項目に対して、インポート時に文字列置換(REPLACE)処理を行う例です。
mysql>
load data local infile "D:\export_file_name.csv" into table country
fields terminated BY ',' /* カラムの区切り文字 */
enclosed by '"' /* カラムの囲み文字 */
lines terminated by 'rn' /* 1レコードの改行コード */
(CODE, @var_country)
SET COUNTRY = REPLACE(@var_country, 'E', 'rr');置換だけでなく、
STR_TO_DATE(@date_column, '%m/%d/%Y');のように文字列を日付型に加工したりすることもできます。