えらい大変だったまとめです。

動作環境

macOS Sierra 10.12.6

はじめに

- やりたいこと

MySQLで名簿管理や受付リスト管理したい。

- 方法

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

1.Excelデータを用意する

今回はこのように作りました。
スクリーンショット 2018-03-27 16.01.58.png
尚不具合がでるので全て文字列にしたいと思います。
普通に001と打っても親切に数値にされてしまうので、'001 と入力することで文字列化させます。
保存は、デスクトップに
スクリーンショット 2018-03-27 16.01.42.png
「CSV UTF-8」で保存します。

2.MySQL

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

- 2-1.データベースを用意する

適当にデータベースを作成してください。
これも割愛します。

2-2.テーブルを用意する

以下のようなテーブルを用意してください。
スクリーンショット 2018-03-27 15.51.11.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';

スクリーンショット 2018-03-27 16.24.40.png
これが出ればOKです。
確認してみます。

4-2.テーブルを確認する

select * from tablepkmn \G
と打つと文字化けなく表示されます。
スクリーンショット 2018-03-27 16.27.14.png

5.エラーがでる

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

- secure-file-privがうんたら

スクリーンショット 2018-03-27 16.30.34.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に引っかかった件
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 のコマンドまとめ

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

③ サーバーを再起動

contro+Dで抜けて、mysql.server restartで再起動します。
データベースに入り直して、SELECT @@global.secure_file_priv;確認すると、無事空になっていました。
スクリーンショット 2018-03-27 16.53.07.png

- 文字化け祭

68747470733a2f2f71696974612d696d6167652d73746f72652e73332e616d617a6f6e6177732e636f6d2f302f3139323838372f39663965386331632d313532352d323738612d623236652d3530613131333962346632362e706e67.png

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

■解決法
参考: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します。

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

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

③ サーバーを再起動

contro+Dで抜けて、mysql.server restartで再起動します。
show variables like "chara%";と入力すると無事書き換わっていました。
スクリーンショット 2018-03-27 17.06.58.png

Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.