mysql 5.6升级5.7的一些BUG以及处理方法

问题1

Incorrect string value: ‘\xE6\xB5\x8B\xE8\xAF\x95’ for column ‘REASON’ at row 1

产生原因分析
从描述中看是出现中文字符集编码的问题,那么可以先尝试看下mysql5.7的默认字符编码集

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

很明显是默认编码字符集不是utf-8 导致的

处理方法
首先找到配置文件地址

windows 下my.ini

mysql> select @@datadir;
+---------------------------------------------+
| @@datadir                                   |
+---------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
+---------------------------------------------+
1 row in set (0.00 sec)

linux下 一般是 /etc/mysql/my.cnf

修改配置文件为
···
[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
···

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

产生原因分析
按提示应该是sql_mode的问题

处理方法
修改配置文件

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION