どもー、みなさんこんちわ！某Ａ省のHiroです！
今回は、MySQLにCSVファイルのデータを取り込もうとして詰まった時のことを備忘録と情報共有を兼ねて書いておこうと思います。
郵便番号と住所のデータベースを使って色々と作って勉強しようと思い、日本郵便のサイトからデータをダウンロードして、前準備のつもりで始めた作業だったのですが、思いのほか詰まってしまいました。「MySQL csv インポート」などで調べてみると結構みんな詰まってしまう作業のようです。他の人が同じような状況になった時に、少しでも早く問題を解決できる助けになれば幸いです。
環境
OS: Windows 7
DBServer: MySQL 8.0
そして・・・千葉！！
詰まりポイントは三つ！！
① LOAD DATA LOCAL INFILE ... を実行するが、”ERROR 1148: The used command is not allowed with this MySQL version”とエラーメッセージが出る。
② ①は解決したが、”ERROR 1300 (HY000): Invalid utf8mb4（ここは人によって違う） character string: '"'（ここも人によって違う）”
③ queryは異常なく発行して、エラーもなく終わっているのにデータベースに正常に取り込まれていない。データの表示がおかしい。
それぞれの問題に対しての解決法を書いていきたいと思います。
１つ目！The used command is not allowed with this MySQL version.
このエラーの原因は、MySQLの仕様にあります。詳しくはこちら。
以下、私の拙い英語力で要約したものになります。
”LOAD DATA INFILE文を使用する時に、LOCALキーワードを使うことは潜在的に以下の二つのセキュリティー上の問題があります。
１、MySQLサーバーは、クライエントではなくサーバーが指定したファイルをクライエント側のプログラムに渡させる可能性があります。また、このようなサーバーはクライエントが閲覧権を持っているあらゆるクライアントのホスト上のファイルにアクセス出来る可能性があります。
２、ユーザーがWebサーバーを通じて接続している環境では、Webサーバーが閲覧権を持つあらゆるファイルに対して、ユーザーはLOAD DATA LOCALを使って読み込むことが可能です。このような環境では、MySQLサーバーに対してクライアントサイドは、実質Webサーバーと同等です。”
以上の理由から、MySQLでは仕様として、サーバーサイドとクライアントサイドの両方でLOAD DATA LOCAL INFILEをデフォルトでは使用できないようにしているようです。
で、どうすればLOCALキーワードを使用できるようになるかなのですが、それにはいくつか方法があります。
まずは、サーバーサイドにおいて、(クライアント側のプログラムとライブラリが、ビルド時や実行時にどのように規定したかに関わらず）mysqldコマンドをlocal_infileの環境変数をONにして実行すると、クライアント側で特にいじらずともLOAD DATA LOCALを使っても期待通りの結果が得られるようです。
因みに、これを明示的にOFFにしておけば、クライアント側でどのようにいじってもLOAD DATA LOCALは使えなくなるようです。
今回私は、これは使っておりません。(あまり使わないほうがいい設定な気がします）
次に、クライアントサイドにおいて、
① mysqlクライアントにおいて、--local-infile=1のオプションを指定して起動する方法。
② mysqlimportクライアントにおいて、--local=1のオプションを指定して起動する方法。
③ MySQLのオプションファイルの[client]の欄に、loose-local-infile=1を書き足す方法。
の３つ（ソースファイルをコンパイルする際に指定する方法を加えれば４つ）ありますが、今回私は③の方法を使いました。
これらの方法のうちのどれかを使えばいいのですが、ネットで見た感じでは、大体の人が③の方法を使っているようです。なので私も③の方法を使いました。
因みにオプションファイルの探し方が分からない人はこちら（準備中）をどうぞ。
２つ目！ERROR 1300 (HY000): Invalid utf8mb4（ここは人によって違う） character string: '"'（ここも人によって違う）
これは、MySQL側のサーバサイドおよびクライアントサイドの文字コードの指定とデータを取り出すcsvファイルの文字コードを確認していじることで解決できます。
さて、具体的にはまず
SHOW VARIABLES LIKE 'char%';
を実行してもらって、MySQL側の文字コードを確認します。
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | utf8mb4
|
| character_set_connection | utf8mb4
|
| character_set_database | utf8mb4
|
| character_set_filesystem | binary
|
| character_set_results | utf8mb4
|
| character_set_server | utf8mb4
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
私の環境では、最終的にこんな感じになりました。
オプションファイルで次のように、指定します。
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
ネットで調べるとほとんどの人が、utf8でセットしてるようですが、私はテーブル名やカラム名に４バイト文字を使って女子受けを狙っていくエンジニアになりますので、utf8mb4をセットしております。因みにあまり詳しくは調べていませんが、character_set_systemは読み込み専用の変数で、識別子を保存するためのものだそうです。
ここら辺の話で面白い話を書いている方がいたので興味がある方は、こちらの記事をどうぞ読んでみてください。
では次にデータを取り出すcsvファイルの文字コードを調べます。が、Windows環境の人向けの方法しか書いておりません。他の環境の人は別に調べる必要があります。
私が紹介する方法はすごく簡単で、該当のファイルをメモ帳で開いて、名前を付けて保存を選択し、その時に別の文字コードを指定してください。ファイルにもよると思いますが、私が日本郵便からダウンロードしたcsvファイルはANSIになっておりました。
私は、utf8を指定しました。
MySQLとcsvファイルの文字コードの設定が合致すればあと少しです！頑張りましょう！
３つ目！queryは異常なく発行して、エラーもなく終わっているのにデータベースに正常に取り込まれていない。データの表示がおかしい。
この現象の原因は、取り扱うcsvファイルによって微妙に「方言」が違うことにあるようです。
その為、このようにすれば全部オッケーというものはありません。この記事と私の書いた次のqueryが参考になればと思います。
LOAD DATA LOCAL INFILE
'C:/Users/yoshi/Documents/lib_Java/Pjt_FetchWeather/Chiba.csv'
INTO TABLE 郵便番号表
CHARACTER SET utf8mb4
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
LINES
TERMINATED BY '\r\n'
(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15)
SET 郵便番号=@3, 都道府県=@7, 市区町村=@8, 町域=@9;
まとめ
いやぁ～～～～、大変でした(^o^")
この部分についてもっと書いて欲しいということがあればコメントにてお願いします。