MySQL5.7新特性：SQL mode changes

sql_mode:

这个系统变量在不同的mysql版本发生了下面的变化。

Permitted Values (<= 5.7.4) NO_ENGINE_SUBSTITUTION
Permitted Values (>= 5.7.5, <= 5.7.6) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION
Permitted Values (5.7.7) ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION

Permitted Values (>= 5.7.8) 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

5.7文档新特性中关于sql mode changes的说明

SQL mode changes.  Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default.Implementation for the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, this mode is now enabled by default, to prohibit only nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are now deprecated but enabled by default. The long term plan is to have them included in strict SQL mode and to remove them as explicit modes in a future MySQL release. See SQL Mode Changes in MySQL 5.7.The changes to the default SQL mode result in a default sql_mode system variable value with these modes enabled: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

严格的事务性存储引擎SQL模式(STRICT_TRANS_TABLES)现在是默认启用。
实现ONLY_FULL_GROUP_BY SQL模式使更有经验，不再拒绝之前被拒绝的确定性查询。结果,这种模式现在是默认启用,只禁止包含的表达式在一个组里且不能保证唯一确定的不确定性查询。
ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE SQL模式现在已经弃用,但默认启用。长期的计划是让他们包含在严格的SQL模式和删除它们明确的模式在未来的MySQL版本。
更改默认的SQL模式导致默认的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, and NO_ENGINE_SUBSTITUTION.

更详细的关于5.7的sql mode可以查看官方文档：

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

说明：

在mysql高的版本中对sql要求越来越严格（mysql数据库越来越严谨）：

例如下面的语句在5.7会报错（这样的sql在之前的mysql中被支持，一直很让我困惑）：

mysql> select id,name from t group by name;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selec id,name from t group by name' at line 1

下面是在5.5中的测试：

mysql> select * from t;+------+--------+------+| id   | name   | age  |+------+--------+------+|    1 | panda1 |   17 ||    2 | panda2 |   18 ||    3 | panda3 |   19 ||    3 | panda3 |   19 ||    3 | panda3 |   19 ||    3 | panda3 |   19 ||    3 | panda3 |   23 |+------+--------+------+7 rows in set (0.00 sec)mysql> select id,name from t group by name;+------+--------+| id   | name   |+------+--------+|    1 | panda1 ||    2 | panda2 ||    3 | panda3 |+------+--------+3 rows in set (0.00 sec)