MySQLの”LOAD DATA INFILE”などでCSVファイル入出力時の文字コード、権限などの注意点

wp-admin

7年前

MySQLの”LOAD DATA INFILE”コマンドなどでCSVファイルを使ってデータ入力、データ出力する際の文字コード、権限などの注意点についてまとめました。
※MySQLの文字コードについては以下の記事も参照。
MySQLのサーバー、データベース、テーブル、カラムの文字コード設定とクライアントからのサーバー接続時の文字コード設定

※目次をクリックすると目次の下部にコンテンツが表示されます。

１．CSVファイルインポートの使用方法、注意点
２．File権限
３．文字コード
４．CSVファイルエクスポート

CSVファイルインポートの使用方法、注意点

１）構文
　
LOAD DATA LOCAL INFILE “ファイル名” INTO TABLE テーブル名
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘;
　
注）”LOAD DATA INFILE～”をCentOS6.5、MySQL5.1.73の環境で実施した際には、下記エラーが発生してうまく実行できませんでした。
ERROR 13 (HY000): Can’t get stat of ‘/home/user/sample.csv’ (Errcode: 13)
　
（Linuxの場合）
LOAD DATA LOCAL INFILE “/home/user/sample.csv” INTO TABLE テーブル名 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘;
　
（Windowsの場合）
LOAD DATA LOCAL INFILE “C:\\work\\sample.csv” INTO TABLE テーブル名 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘;
　
２）ユニークキーによる重複エラー
　
デフォルトでIGNOREオプションが有効になっているようで、ユニークキーによる重複エラーが発生してもそのデータはスキップして処理を継続してくれます。
　
３）idなどをAUTO_INCREMENTにしている場合
　
データを入力せず（カンマは必要）にCSVファイルを作成すると自動で入力されました。

File権限

CentOS6.5、MySQL5.1.73でCSVファイルインポートを実施した際、下記権限エラーが発生しました。
ERROR 1045 (28000): Access denied for user ‘dbuser’@’localhost’ (using password: YES)
　
下記コマンドで権限を付与してあったのですがFILE権限も付与する必要があるようです。
GRANT ALL PRIVILEGES ON ～
　
FILE権限付与のコマンド
GRANT FILE ON *.* TO ユーザー名@localhost;

文字コード

“LOAD DATA INFILE”コマンドでデータをテーブルにインポートする際に適用される文字コードは、データベースの文字コード設定（character_set_database）が適用されるようなので注意が必要です。
　
sjisのデータをインポートしたい場合、テーブル、インポートするCSVファイルの文字コード、サーバー接続関連の文字コード定数（character_set_client、character_set_connection、character_set_results）の文字コードがsjisになっていたとしても、データベースの文字コード（character_set_database）がsjisになっていないと文字化けが起きてしまいます。
　
例１）データベースの文字コード（character_set_database）がutf8の場合
　

①サーバー接続関連の文字コードをsjisに変更
mysql> set names sjis;
Query OK, 0 rows affected (0.00 sec)
　
mysql> show variables like 'character_set%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| character_set_client     | sjis     |
| character_set_connection | sjis     |
| character_set_database   | utf8     |
| character_set_filesystem | binary   |
| character_set_results    | sjis     |
| character_set_server     | utf8     |
| character_set_system     | utf8     |
+--------------------------+----------+
8 rows in set (0.00 sec)
　
②CSVファイルインポート
mysql> LOAD DATA LOCAL INFILE "C:\\work\\sjis.csv" INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"';
　
mysql> select * from sjis;
+-------+-----------------+
| id    | ken             |
+-------+-----------------+
| 94702 | ?e?X?g?f?[?^?Q
| 94703 | ?e?X?g?f?[?^?R  |
+-------+-----------------+

　
例２）データベースの文字コード（character_set_database）をsjisに変更した場合
　

①データベースの文字コードをsjisに変更
mysql> ALTER DATABASE db_name DEFAULT CHARACTER SET sjis;
　
mysql> show variables like 'character_set%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| character_set_client     | sjis      |
| character_set_connection | sjis      |
| character_set_database   | sjis      |
| character_set_filesystem | binary    |
| character_set_results    | sjis      |
| character_set_server     | utf8      |
| character_set_system     | utf8      |
+--------------------------+-----------+
　
②CSVファイルインポート
mysql> LOAD DATA LOCAL INFILE "C:\\work\\sjis.csv" INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"';

mysql> select * from sjis;
+-------+-----------------+
| id    | ken             |
+-------+-----------------+
| 94702 | ?e?X?g?f?[?^?Q
| 94703 | ?e?X?g?f?[?^?R  |
| 94705 | テストデータ２
| 94706 | テストデータ３  |
+-------+-----------------+

CSVファイルエクスポート

１）構文
　
SELECT文 into outfile “ファイル名” fields terminated by ‘,’;
　
２）ファイル出力先
　
データベースのデータディレクトリ内の該当データベースの名前のディレクトリ内。
　
データディレクトリが”C:\mysql_data\data\”でデータベース名が”test”の場合は、”C:\mysql_data\data\test”。
　
※データベースのデータディレクトリの確認方法
　
MySQLの設定ファイルを確認する
　
（Windowsの場合の例）my.ini
datadir=”C:\mysql_data\data\”
　
（Linuxの場合の例）/etc/my.cnf
datadir=/var/lib/mysql