えらい大変だったまとめです。
動作環境
macOS Sierra 10.12.6
はじめに
- やりたいこと
MySQLで名簿管理や受付リスト管理したい。
- 方法
csvデータ（日本語あり）をどうにかこうにかMySQLにインポート・エクスポートさせる。
ゆくゆくはNode-REDなどで、検索、ソート、書き込み、結果吐き出し、などできるように見目よくする。
1.Excelデータを用意する
今回はこのように作りました。
尚不具合がでるので全て文字列にしたいと思います。
普通に001と打っても親切に数値にされてしまうので、'001 と入力することで文字列化させます。
保存は、デスクトップに
「CSV UTF-8」で保存します。
2.MySQL
MySQLの作業になります。
homebrewでインストールしましたが、方法は割愛します。
- 2-1.データベースを用意する
適当にデータベースを作成してください。
これも割愛します。
###2-2.テーブルを用意する
以下のようなテーブルを用意してください。
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';
これが出ればOKです。
確認してみます。
###4-2.テーブルを確認する
select * from tablepkmn \G
と打つと文字化けなく表示されます。
#5.エラーがでる
インポートの時点でとにかく沢山エラーが出たので、まとめます。
- secure-file-privがうんたら
** 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に引っかかった件
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を設定
$ mysql --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
結果が4つ返ってきました。
これは左から読み込まれますが、読み込まれた順にどんどん上書きされていきます。
なので有効な一番右の ~/.my.cnf を書き加えます。
参考:my.cnf の読み込む順番でハマったのでまとめる
####② my.cnfに書き加える
vimで書き換えます。
参考:よく使う Vim のコマンドまとめ
[mysqld]
secure-file-priv = ""
####③ サーバーを再起動
contro+Dで抜けて、mysql.server restartで再起動します。
データベースに入り直して、SELECT @@global.secure_file_priv;確認すると、無事空になっていました。
- 文字化け祭
どうやら文字コード問題らしいです。
■解決法
参考：mysqlで文字コードをutf8にセットする
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します。
[mysqld]
...
character-set-server=utf8 #mysqldセクションの末尾に追加
[client]
default-character-set=utf8 #clientセクションを追加
####③ サーバーを再起動
contro+Dで抜けて、mysql.server restartで再起動します。
show variables like "chara%";と入力すると無事書き換わっていました。
