はじめに
時々、CSVファイルなどのテキストファイルからデータを取り込みたい場面があります。
たとえば、外部システムからエクスポートされたデータや、Excelなどで整形された情報をデータベースに保存したいときなどです。
今回の私のモチベーションは、「SQL100本ノック」をMySQLで解くことでした。
このリポジトリでは PostgreSQL 向けの環境が用意されていますが、
普段は MySQL を使うことがほとんどなので、今回は MySQL で取り組むことにしました。
その際に、配布されているCSVファイルからデータをインポートする必要があったため、MySQLでCSVファイルを読み込む方法についてまとめます。
LOAD DATA
MySQLでは、LOAD DATAステートメントを用いることで、
テキストファイルからテーブルにデータを読み込むことができます。
client側で管理されているテキストファイルを読み込む
LOCAL修飾子を付与することで、client側で管理されているファイルからデータを読み込むことができます。
LOAD DATA LOCAL INFILE 'file_name' INTO TABLE tbl_name;
しかし、LOAD DATA LOCALにはセキュリティ上の問題があるためデフォルトでは無効化されています。
ERROR 3948 (42000) at line 7: Loading local data is disabled; this must be enabled on both the client and server sides
エラー文にもある通り、client側、server側の両方でLOAD DATA LOCALを実行するための設定をする必要があります。
- server: https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_local_infile
- client: https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_local-infile
設定ファイルの[client]セクション、[mysqld]セクションのそれぞれにlocal-infile=1を追加します。(mysqldの設定を変更した場合は適宜restartしてください)
[client]
local-infile=1
[mysqld]
local-infile=1
もしくはmysql clientの実行時に--local-infile=1オプションを追加し、かつSET命令によってmysqld側の設定を変更します。
$ mysql -u**** -p**** --local-infile=1
SET GLOBAL local_infile=1;
server側で管理されているテキストファイルを読み込む
LOCAL修飾子が付与されていない場合、自動的にserver側で管理されているファイルを読み込みます。
LOAD DATA INFILE 'file_name' INTO TABLE tbl_name;
server側で管理されているファイルを読み込む場合、
アカウントにFILE権限が付与されており、かつsecure_file_privで指定されたディレクトリに読み込み対象のファイルが存在している必要があります。
- FILE権限
-
secure_file_priv
CSVファイルを読み込む
LOAD DATAステートメントはテキストファイルを読むためのものであり、CSVファイルを読み込むためのものではないため、フィールドの区切り文字や囲い文字を指定する必要があります。
- 1行目がヘッダーになっている
- カンマ(
,)区切り - 改行コードは
CRLF
を想定すると、以下のようになります。
LOAD DATA
INFILE 'file_name'
INTO TABLE tbl_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
特定のcolumnの値が存在せずに,が連続している部分を読もうとする場合、
NULLではなく、空文字として読まれてしまいIncorrect integer value: '' for column 'column_name'のエラーが発生することがありますが、NULLIF関数を用いてNULLで埋めることができます。
LOAD DATA
INFILE '/var/lib/mysql-files/product.csv'
INTO TABLE product
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(product_cd, category_major_cd, category_medium_cd, category_small_cd, @unit_price, @unit_cost)
SET unit_price = NULLIF(@unit_price, ''), unit_cost = NULLIF(@unit_cost, '');