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

MySQLの”LOAD DATA INFILE”コマンドなどでCSVファイルを使ってデータ入力、データ出力する際の文字コード、権限などの注意点についてまとめました。
 
目次
(1)CSVファイルインポート
  1)コマンド
  2)File権限
  3)文字コード
  4)ユニークキーによる重複エラー
  5)idなどをAUTO_INCREMENTにしている場合
(2)CSVファイルエクスポート
  1)コマンド
  2)ファイル出力先
 
MySQLの文字コードについての参考記事
MySQLのサーバー、データベース、テーブル、カラムの文字コード設定とクライアントからのサーバー接続時の文字コード設定
 

(1)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)File権限
 
CentOS6.5、MySQL5.1.73で実施した際、下記権限エラーが発生しました。
 
ERROR 1045 (28000): Access denied for user ‘dbuser’@’localhost’ (using password: YES)
 
下記コマンドで権限を付与してあったのですがFILE権限も付与する必要があるようです。
GRANT ALL PRIVILEGES ON ~
 
FILE権限付与のコマンド
 
GRANT FILE ON *.* TO ユーザー名@localhost;
 
3)文字コード
 
“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 |
+——-+—————–+
 
4)ユニークキーによる重複エラー
 
デフォルトでIGNOREオプションが有効になっているようで、ユニークキーによる重複エラーが発生してもそのデータはスキップして処理を継続してくれます。
 
5)idなどをAUTO_INCREMENTにしている場合
 
データを入力せず(カンマは必要)にCSVファイルを作成すると自動で入力されました。
 
(2)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

関連記事の目次

コメントを残す

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