pymysql.err.InternalError) (1366, "Incorrect string value解决方法

2020-04-19

今天尝试用了下flask，结果连mysql报错，报错信息如下：pymysql.err.InternalError) (1366, "Incorrect string value:，看起来像是字符集的问题，也Google了下

确实是字符集的问题，我这里定义的字段是String，insert中文就不OK。Google结论是MySQL默认的charset utf8 为 utf8mb3，需要调整为utf8mb4，具体的大家看这个回答https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc。

怎么修改？尝试步骤如下

$ mysql> show variables like 'character%';
+--------------------------+-----------------------------------------------------------+
| Variable_name            | Value                                                     |
+--------------------------+-----------------------------------------------------------+
| character_set_client     | utf8                                                   
|
| character_set_connection | utf8                                                   
|
| character_set_database   | latin1                                                    
|
| character_set_filesystem | binary                                                    
|
| character_set_results    | utf8                                                   
|
| character_set_server     | utf8                                                   
|
| character_set_system     | utf8                                                      |
| character_sets_dir       | /usr/local/mysql-5.7.25-macos10.14-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.00 sec)

$ alter database test charset utf8mb4;
$ show variables like 'character%';
+--------------------------+-----------------------------------------------------------+
| Variable_name            | Value                                                     |
+--------------------------+-----------------------------------------------------------+
| character_set_client     | utf8                                                   
|
| character_set_connection | utf8                                                   
|
| character_set_database   | utf8mb64                                                  
|
| character_set_filesystem | binary                                                    
|
| character_set_results    | utf8                                                   
|
| character_set_server     | utf8                                                   
|
| character_set_system     | utf8                                                      |
| character_sets_dir       | /usr/local/mysql-5.7.25-macos10.14-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.00 sec)

然后尝试insert中文，还是不行，后来又alert了table，最后alert表里具体的column，但是这么搞不能一劳永逸啊，怎么办？这里回到最开始，我们需要修改的是mysql的字符集，所以应该改mysql的配置文件。

网上搜索了下，Mac上MySQL的配置文件路径为/usr/local/mysql/support-files/my-default.cnf，可是我这并没有这个文件，遂新建了/etc/my.cnf，内容如下

[client]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4

新建前需要stop下MySQL，新建完成后重启MySQL，然后就ok了吗？按理应该是的，不过我这里并没有ok啊！！！

想了一想，刚才新建的test数据库应该需要重建，或者重建对应的table。粗暴了一点，drop database test;，然后再create，然后然后就ok了，哈哈哈😝

$ mysql> select * from role;
+----+-----------------+-------+---------------------+
| id | name            | auths | addtime             |
+----+-----------------+-------+---------------------+
|  1 | 超级管理员      |       | 2020-04-18 10:07:48 |
+----+-----------------+-------+---------------------+
1 row in set (0.00 sec)

这里仅做记录，遇到相同问题的同学可以参考下。

生产项目没这么麻烦，直接云数据库就好😂