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

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

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

CSVファイルインポートの使用方法、注意点
1)構文
 
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 ‘”‘;
 
2)ユニークキーによる重複エラー
 
デフォルトでIGNOREオプションが有効になっているようで、ユニークキーによる重複エラーが発生してもそのデータはスキップして処理を継続してくれます。
 
3)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になっていないと文字化けが起きてしまいます。
 
例1)データベースの文字コード(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  |
+-------+-----------------+

 
例2)データベースの文字コード(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 | テストデータ2
| 94706 | テストデータ3  |
+-------+-----------------+

CSVファイルエクスポート
1)構文
 
SELECT文 into outfile “ファイル名” fields terminated by ‘,’;
 
2)ファイル出力先
 
データベースのデータディレクトリ内の該当データベースの名前のディレクトリ内。
 
データディレクトリが”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

関連記事の目次

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です