MySQLにCSV(日本語)を読み込ませたい

えらい大変だったまとめです。
##### 動作環境
macOS Sierra 10.12.6

# はじめに
### - やりたいこと
MySQLで名簿管理や受付リスト管理したい。

### - 方法
csvデータ（日本語あり）をどうにかこうにかMySQLにインポート・エクスポートさせる。
ゆくゆくはNode-REDなどで、検索、ソート、書き込み、結果吐き出し、などできるように見目よくする。

# 1.Excelデータを用意する
今回はこのように作りました。
<img width="256" alt="スクリーンショット 2018-03-27 16.01.58.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/4c387af1-e74c-0b4b-b4d4-d5d4ddbd5e6d.png">
尚不具合がでるので全て文字列にしたいと思います。
普通に001と打っても親切に数値にされてしまうので、'001 と入力することで文字列化させます。
保存は、デスクトップに
<img width="520" alt="スクリーンショット 2018-03-27 16.01.42.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/8657f4ec-e6cf-4398-181a-0a46fd9b1e33.png">
「CSV UTF-8」で保存します。


# 2.MySQL
MySQLの作業になります。
homebrewでインストールしましたが、方法は割愛します。

### - 2-1.データベースを用意する
適当にデータベースを作成してください。
これも割愛します。

###2-2.テーブルを用意する
以下のようなテーブルを用意してください。
<img width="487" alt="スクリーンショット 2018-03-27 15.51.11.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/517b3e3f-790e-e536-c610-19245c131fee.png">
id:varchar(20)
name:varchar(20)
type:varchar(20)

```:テーブルを作成する
mysql> create table テーブル名(ここではtablepkmn)
    -> (id varchar(20), name varchar(20), type varchar(20));
Query OK, 0 rows affected (0.02 sec)
```


# 3.データをアクセス可能な場所に移動する
読み込ませるためには、誰もがアクセス可能な場所にデータを移動させる必要があります。
csvデータ　: デスクトップ→/tmp

```:移動
cp ~/Desktop/hoge.csv /tmp
```
コピーができました。

# 4.データをインポートする
データベースはuseされていることを前提にしています。
テーブル名はtablepkmn、csvデータ名は hoge.csv にしています。
適宜変更してください。

###4-1.インポート
```:インポート指示
mysql> load data infile "/tmp/hoge.csv"
    -> into table tablepkmn
    -> fields terminated by ','
    -> enclosed by '"'
    -> lines terminated by '\n';
```
<img width="487" alt="スクリーンショット 2018-03-27 16.24.40.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/87c80bde-45dc-9753-83ae-08f0869db30e.png">
これが出ればOKです。
確認してみます。

###4-2.テーブルを確認する
`select * from tablepkmn \G`
と打つと文字化けなく表示されます。
<img width="482" alt="スクリーンショット 2018-03-27 16.27.14.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/3dcb0a9e-b42d-f0ca-05ce-f54c571f1da6.png">


#5.エラーがでる

インポートの時点でとにかく沢山エラーが出たので、まとめます。

# - secure-file-privがうんたら

<img width="489" alt="スクリーンショット 2018-03-27 16.30.34.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/7bbf6e80-806a-1b52-9170-25cff177135a.png">

** ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement **

■解決法
参考：[[ MySQL ] データをCSVでエクスポートしようとして、secure-file-privに引っかかった件](https://qiita.com/bohebohechan/items/207e87786b1e30f60abe)
secure_file_privを空にすることで、解決できるようです。

```:現在のsecure_file_privを確認する
mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)
```
NULLではなく、空にします。
これはmy.cnfに記述をするようです。

####① my.cnfを探す
参考：[Hemebrewで入れたMySQLのmy.cnfを設定](http://starzero.hatenablog.com/entry/2012/11/10/103047)

```:新しいターミナル
$ mysql --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 
```
結果が4つ返ってきました。
これは左から読み込まれますが、読み込まれた順にどんどん上書きされていきます。
なので有効な一番右の ~/.my.cnf を書き加えます。
参考:[my.cnf の読み込む順番でハマったのでまとめる](https://qiita.com/sasarkyz/items/6a3af872417294fa5d30)

####② my.cnfに書き加える
vimで書き換えます。
参考:[よく使う Vim のコマンドまとめ](https://qiita.com/hide/items/5bfe5b322872c61a6896)

```:vimで[mysqld]を以下のようにします。コメントアウトでも可。
[mysqld]
secure-file-priv = ""
```

####③ サーバーを再起動
contro+Dで抜けて、`mysql.server restart`で再起動します。
データベースに入り直して、`SELECT @@global.secure_file_priv;`確認すると、無事空になっていました。
<img width="486" alt="スクリーンショット 2018-03-27 16.53.07.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/7303b3a3-f6d1-b1d8-e64a-72f6e34a6c95.png">

# - 文字化け祭

<img width="241" alt="68747470733a2f2f71696974612d696d6167652d73746f72652e73332e616d617a6f6e6177732e636f6d2f302f3139323838372f39663965386331632d313532352d323738612d623236652d3530613131333962346632362e706e67.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/db494022-e5f2-e7b5-8b5c-341ba89eed67.png">

どうやら文字コード問題らしいです。

■解決法
参考：[mysqlで文字コードをutf8にセットする](https://qiita.com/YusukeHigaki/items/2cab311d2a559a543e3a)
character_set_database をutf-8にしていきます。

####① 文字コードを確認する
`show variables like "chara%";`と入力すると以下のようにでます。

```
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       | 
| character_set_connection | utf8                       | 
| character_set_database   | latin1                     | ←コレをutf8にする
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
```

####② my.cnfで変更する
先ほど変えた ~/.my.cnf に再度vimします。

```:~/.my.cnf
[mysqld]
...
character-set-server=utf8 #mysqldセクションの末尾に追加

[client]
default-character-set=utf8 #clientセクションを追加
```

####③ サーバーを再起動
contro+Dで抜けて、`mysql.server restart`で再起動します。
`show variables like "chara%";`と入力すると無事書き換わっていました。
<img width="681" alt="スクリーンショット 2018-03-27 17.06.58.png" src="https://qiita-image-store.s3.amazonaws.com/0/192887/a99a276b-c478-5305-6273-4c48f8a21366.png">