怎么修改MySQL的SQL_MODE

这篇文章主要讲解了“怎么修改MySQL的SQL_MODE”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么修改MySQL的SQL_MODE”吧！

查看我们当前数据库的sql_mode

mysql> select @@sql_mode;

+-----------------------------------------------------------------------------------------------------------------------+

| @@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 row in set (0.00 sec)

不同版本默认的SQL_MODE

MySQL 5.5：空

MySQL 5.6：非严格模式

NO_ENGINE_SUBSTITUTION

MySQL 5.7：严格模式

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

MySQL 8.0：严格模式

ONLY_FULL_GROUP_BY,

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,

NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

如何修改SQL_MODE

SQL_MODE既可在全局级别修改，又可在会话级别修改。可指定多个MODE，MODE之间用逗号隔开。

全局级别

set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';

会话级别

set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';

SQL_MODE的完整列表

ALLOW_INVALID_DATES

在严格模式下，对于日期的检测较为严格，其必须有效。若开启该MODE，对于month和day的检测会相对宽松。其中，month只需在1~12之间，day只需在1~31之间，而不管其是否有效，如下面的'2004-02-31'

ERROR_FOR_DIVISION_BY_ZERO

该MODE决定除数为0的处理逻辑，实际效果还取决于是否开启严格模式。

1. 开启严格模式，且开启该MODE，插入1/0，会直接报错。

2. 只开启严格模式，不开启该MODE，允许1/0的插入，且不提示warning，1/0最后会转化为NULL。

3. 不开启严格模式，只开启该MODE，允许1/0的插入，但提示warning。

4. 不开启严格模式，也不开启该MODE，允许1/0的插入，且不提示warning，同2一样。

NO_ENGINE_SUBSTITUTION

在开启该MODE的情况下，在创建表时，如果指定的存储引擎不存在或不支持，则会直接提示“ERROR”。

若不开启，则只会提示“Warning”，且使用默认的存储引擎。

NO_ZERO_DATE

该MODE会影响'0000-00-00'的插入。实际效果还取决于是否开启严格模式。

1. 在开启严格模式，且同时开启该MODE，是不允许'0000-00-00'插入的。

2. 只开启严格模式，不开启该MODE，允许'0000-00-00'值的插入，且不提示warning。

3. 不开启严格模式，只开启该MODE，允许'0000-00-00'值的插入，但提示warning。

4. 不开启严格模式，也不开启该MODE，允许'0000-00-00'值的插入，且不提示warning。

NO_ZERO_IN_DATE

同NO_ZERO_DATE类似，只不过NO_ZERO_DATE针对的是'0000-00-00'，而NO_ZERO_IN_DATE针对的是年不为0，但月或者日为0的日期，如，'2010-00-01' or '2010-01-00'。

ONLY_FULL_GROUP_BY

开启该MODE，则SELECT列表中只能出现分组列和聚合函数。

STRICT_ALL_TABLES

对所有表开启严格模式。

STRICT_TRANS_TABLES

对事务表开启严格模式。

NO_AUTO_CREATE_USER

在MySQL 8.0之前，直接授权会隐式创建用户。

在MySQL 8.0中，已不允许grant语句隐式创建用户，所以，该MODE在8.0中也不存在。

其实，该MODE禁止的只是不带“identified by”子句的grant语句，对于带有“identified by”子句的grant语句，其并不会禁止。

SQL_MODE的常见组合

在MySQL 5.7中，还可将SQL_MODE设置为ANSI, DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, TRADITIONAL。

其实，这些MODE只是上述MODE的一种组合，目的是为了和其它数据库兼容。

在MySQL 8.0中，只支持ANSI和TRADITIONAL这两种组合。

ANSI：等同于

REAL_AS_FLOAT, PIPES_AS_CONCAT,ANSI_QUOTES, IGNORE_SPACE,ONLY_FULL_GROUP_BY

TRADITIONAL：等同于

STRICT_TRANS_TABLES, STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION。

范例：

mysql> set session sql_mode='TRADITIONAL';

Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode';

+---------------+------------------------------------------------------------------------------------------------------+

| Variable_name | Value |

+---------------+------------------------------------------------------------------------------------------------------+

| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION |

+---------------+------------------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

感谢各位的阅读，以上就是“怎么修改MySQL的SQL_MODE”的内容了，经过本文的学习后，相信大家对怎么修改MySQL的SQL_MODE这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！