MySQL5.7中的sql_mode默认值带来的坑及解决方法

MySQL5.7中的sql_mode默认值带来的坑及解决方法

在MySQL5.7版本中，sql_mode的默认值发生了变化，从之前的空值改成了ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION这些选项的组合。这种默认值存在一些问题，比较常见的有以下两种情况：

1. 对旧版本的代码迁移会有影响，出现Illegal mix of collations（字符集不一致）等错误；
2. 自动生成时间戳的值不再支持0000-00-00这种情况，而且对于无效或不合法的日期，MySQL将抛出错误而不再返回NULL。

为了避免这些问题，在使用MySQL5.7之前，需要对sql_mode进行设置，或者在升级后适时调整sql_mode的值。

设置方法：

可以通过以下两种方法设置sql_mode的值：

1. 修改MySQL配置文件：打开MySQL配置文件my.cnf，并在[mysqld]部分中加入sql_mode设置，例如：

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

然后重启MySQL服务使其生效。

1. 直接在MySQL命令行下执行SET sql_mode命令，例如：

SET sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

这种方法可以动态设置sql_mode的值，并不需要重启MySQL服务。

示例说明：

1. 案例1：旧版代码迁移失败

假设存在一个表test，其中有两个字段id和name，定义如下：

CREATE TABLE test(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

然后执行以下语句：

SELECT name FROM test GROUP BY name;

在MySQL5.6版本中，上述语句没有问题，可以正常执行。但在MySQL5.7版本中，会遇到以下错误：

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

出错原因是在默认sql_mode中包含了ONLY_FULL_GROUP_BY选项，导致在GROUP BY语句中必须将所有字段都列出，否则会抛出错误。在这种情况下，可以通过修改sql_mode来解决问题。

1. 案例2：生成时间戳失败

在MySQL5.7中，如果要插入一个0000-00-00日期，或者一个非法日期，例如2017-02-31（2月只有28/29天），MySQL会抛出以下错误：

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'c' at row 1

这是由于默认sql_mode中包含了NO_ZERO_IN_DATE和NO_ZERO_DATE选项，导致MySQL不再支持0000-00-00这种日期格式，而且对于无效或不合法的日期，仅返回错误而不是NULL。

解决方法可以将这两个选项从sql_mode中删掉，例如：

SET sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这样，在插入0000-00-00日期或非法日期时，MySQL会返回NULL而不再抛出错误。