load data local infile で文字化け対応

MySQLのload data local infileの際に文字化けする対策
ポイントはcharacter_set_databaseパラメタを確認することでした

環境

MySQL 5.5.43
文字コード utf8

テーブル作成

まずはUTF8でテーブルを作成します。なおデータベースエンジンにはInnodbを使用します

create table TEST(
  id integer not null,
  name varchar(32) null,
  kana varchar(32) null
)engine=innodb default charset=utf8
;

create table TEST(

id integer not null,

name varchar(32) null,

kana varchar(32) null

)engine=innodb default charset=utf8

;

ファイルの確認

インポートするファイルの文字コードをUTF8にします

$ head TEST.txt |nkf --guess
UTF-8 (LF)

1 2	$ head TEST.txt \|nkf --guess UTF-8 (LF)

UTF8でない場合には以下のコマンドで変換します

$ cat TEST.txt |nkf -w > TEST.txt.utf8
$ mv TEST.txt.utf8 TEST.txt

1 2	$ cat TEST.txt \|nkf -w > TEST.txt.utf8 $ mv TEST.txt.utf8 TEST.txt

データベースの設定

データベースの設定を確認します。なおmysqlコマンド起動時に –local-infile をつけないとload data local infileコマンドがエラーになります。５．５あたりからの仕様変更でしたっけ？

$ mysql -uroot -hlocalhost -ppassword --local-infile=1
mysql> show global variables like "character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

</pre＞
このグローバル変数のうちcharacter_set_databaseがutf8になっていることを確認します
なっていない場合には、以下のコマンドで変更します
<pre>
mysql> set global  character_set_database=utf8;

$ mysql -uroot -hlocalhost -ppassword --local-infile=1

mysql> show global variables like "character%";

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.01 sec)

</pre＞

このグローバル変数のうちcharacter_set_databaseがutf8になっていることを確認します

なっていない場合には、以下のコマンドで変更します

<pre>

mysql> set global character_set_database=utf8;

一時的に変更したい場合にはこちらのコマンドです。このセッションのみで有効です

mysql> set character_set_database=utf8;

1	mysql> set character_set_database=utf8;

インポート

それではインポートします

mysq>load data local infile "/path/to/file/TEST.txt" into table TEST;

1	mysq>load data local infile "/path/to/file/TEST.txt" into table TEST;

これで文字化けせずインポートできます

mysql