MYSQL 5.7 sqlmode 行为

最近碰到了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,ONLY_FULL_GROUP_BY

 

上述7个默认行为是mysql5.7.8+的

参考官方文档进行理解：

第一个：NO_ENGINE_SUBSTITUTION， 可以简单理解为 默认开启控制引擎行为的参数 

Control automatic substitution of the default storage engine when a statement 
such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in. 
By default, NO_ENGINE_SUBSTITUTION is enabled. 
Because storage engines can be pluggable at runtime, unavailable engines are treated the same way: 
With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable.
 For ALTER TABLE, a warning occurs and the table is not altered. 
With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable. 

   结论1: 在sql_mode中包涵no_engine_subtitution 且create table 中engine子句指定的存储引擎不被支持时，mysql会报错，而且不会执行语句成功

   结论2：在sql_mode中不包涵no_engine_subtitution 且create table 中engine子句指定的存储引擎不被支持时，mysql会把表的引擎改为innodb。

 

 

 

 

 

第二个：ONLY_FULL_GROUP_BY 与sql 聚合写法有关系。

今天遇到了这个问题，记录下。

mysql5.7报错如下：

 

官方文档的说法：

不兼容的更改：在MySQL 5.7.5中，进行了以下SQL模式更改：

• ONLY_FULL_GROUP_BY SQL模式的 实现 变得更加复杂，不再拒绝先前被拒绝的确定性查询。因此，ONLY_FULL_GROUP_BY 现在默认情况下启用，以禁止包含不保证在组内唯一确定的表达式的非确定性查询。

• 到默认的SQL模式导致违约的变化sql_mode 与这些模式的系统变量值启用： ONLY_FULL_GROUP_BY
  

• 该 ONLY_FULL_GROUP_BY 模式现在也包含在ANSISQL模式所包含的模式中。

如果您发现已 ONLY_FULL_GROUP_BY 启用导致对现有应用程序的查询被拒绝，则这些操作之一应该还原操作：

• 如果可以修改有问题的查询，请执行此操作，以便非确定性非聚合列在功能上依赖于GROUP BY列，或者通过引用非聚合列使用 ANY_VALUE()。

• 如果无法修改有问题的查询（例如，如果它是由第三方应用程序生成），请sql_mode 在服务器启动时将系统变量设置为不启用 ONLY_FULL_GROUP_BY。

马上做个试验复现，然后再做总结：

 

 

 

 

 总结：mysql 的group by 语句，5.7之前并没有完美匹配 ANSI的sql语法模式，而且非聚合列不一定需要出现在group by 语句之后，因为语法要求的不严格，导致开发过程中出现

此类问题，而5.7之后，严格的模式是分组聚合的正确选择。