mysqlコマンド(import, export編)

linux

CSVでもらったデータをインポートする、またはCSV形式でエクスポートして渡すなんて事たまにありますよね。そんな時用のメモです。

CSV,TSV出力 export

# CSV出力
SELECT * FROM hoge
INTO OUTFILE '/tmp/hoge.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';

# TSV出力
SELECT * FROM hoge
INTO OUTFILE '/tmp/hoge.csv'
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"';

ただ、mysqlでデフォルトで指定されているディレクトリ以外へ出力しようとした場合は以下のようなエラーが出る場合があります。

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

まず、現在どのディレクトリがデフォルトの出力ディレクトリになっているのかを確認します。

mysql> show variables like '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

/var/lib/mysql-files/ がデフォルトの出力先になっているようです。では、そのディレクトリへ試しに出力してみます。

mysql> SELECT * FROM wp_terms
    -> INTO OUTFILE '/var/lib/mysql-files/wp_terms.csv'
    -> FIELDS TERMINATED BY '\t'
    -> OPTIONALLY ENCLOSED BY '"';
Query OK, 64 rows affected (0.00 sec)

実行できました。一応、ファイルがあるかも確認しておきます。

# ls -l /var/lib/mysql-files/wp_terms.csv
-rw-rw-rw- 1 mysql mysql 1785 Jan 28 19:12 /var/lib/mysql-files/wp_terms.csv

特にディレクトリを指定する必要がない場合、上記デフォルトに出力すればOK。以下はさらに任意のディレクトリに指定したい場合の設定です。

設定値を一時的に書き換えられるかを確認します。

mysql> SET GLOBAL secure_file_priv = '';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
mysql> SET SESSION secure_file_priv = '';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

結果、一時的な書き換えは不可だったので、mysqlの設定ファイルを編集し、「secure-file-priv = “”」を追記します。

# vi /etc/my.cnf
[mysqld]
...
secure-file-priv = ""

追記したらmysqlを再起動します。

# service mysqld restart

書き換えた設定が反映されているかを確認します。

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

任意のディレクトリへ出力できるか試してみます。

mysql> SELECT * FROM wp_terms
    -> INTO OUTFILE '/tmp/wp_terms.csv'
    -> FIELDS TERMINATED BY '\t'
    -> OPTIONALLY ENCLOSED BY '"';
Query OK, 64 rows affected (0.00 sec)

これで任意のディレクトリへ出力できるようになりました。

CSV・TSV入力 import

構文は以下。変わるのはFIELDS TERMINATED BY句の部分「,」と「\t」の違いで、CSV・TSVとなります。不要なオプションは削除可能です。

# csvインポート
LOAD DATA LOCAL INFILE '/tmp/hoge.csv'
INTO TABLE hoge
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

# tsvインポート
LOAD DATA LOCAL INFILE '/tmp/hoge.csv'
INTO TABLE hoge
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
【補足】
FIELDS TERMINATED BY
  各項目の区切り文字はカンマ「,」
ENCLOSED BY '"'
  フィールドが引用文字で囲まれていることを期待 
LINES TERMINATED BY '\r\n'
  改行は \r\n (Windowsのデフォ)
IGNORE 1 LINES
  初めの1行(ヘッダー)を無視

インポート時のエラー内容を見たい場合は以下で確認可能です。

show warnings

初期値は結構少ないので、一時的に表示行数を拡張して確認したい場合は以下。ただし、設定変更後の保持量が増えるので、前回の結果メッセージを確認したい場合はもう一度実行する必要があります。

mysql> SHOW VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SET max_error_count=20000;

mysql> SHOW WARNINGS INTO OUTFILE '/tmp/warnings.txt';

コメント

タイトルとURLをコピーしました