mysql8 设置sql模式 mysql5.7 sql_mode

最近碰到了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之后，严格的模式是分组聚合的正确选择。