MySQL日志2016.11.14

今天搞搞mysql，发现中文的CSV不能用import wizard倒入，修改成utf-8保存也没有用，究其原因是mysql本身的编码问题。

登录：
mysql -u root -p

查询：
show variables like 'character_set_%';

<pre>
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.9-osx10.9-x86_64/share/charsets/ |
</pre>

可以看到有两个是latin1

查询：
stauts;

<pre>
Connection id: 20
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.9 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 8 days 1 hour 37 min 24 sec
</pre>

更加确定，因此修改参数，查找我的mysql在哪里：
which mysql
让其显示原身，得到：
/usr/local/mysql-5.7.9-osx10.9-x86_64
不过话说为什么我又是那么多mysql嘛，简直和python一样，吐

Snip20161114_32.png

然后进入目录复制文件：
/usr/local/mysql-5.7.9-osx10.9-x86_64/support_files/my-default.cnf
到：
/etc
然后修改文件，在以下行下添加内容：
<pre>
[client]
default-character-set=gbk
[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
</pre>
把文件名修改为：
my.cnf
保存，重新启动mysql，结果：
<pre>
Connection id: 21
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.9 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: gbk
Conn. characterset: gbk
UNIX socket: /tmp/mysql.sock
Uptime: 8 days 2 hours 13 min 29 sec
</pre>
What???这不科学，不过也就是说预设是满足了，但是当前的DB还是老样子，所以要重新设置DB？查了一下stackoverflow，如果要修改当前的DB的话可以输入：
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
搞定。

虽然好像搞定了。。但是依然不能导入带中文的csv。。ORZ

而且发现DATETIME字段不能为空值，有一片文章讲了：
http://panduit.blog.163.com/blog/static/86790462011101524945408/