MySQL 5.7之关于SQL_MODE的设置

MySQL 5.7之关于SQL_MODE的设置

在MySQL中，SQL_MODE是一个全局变量，用于控制MySQL服务器对SQL语句的解析和执行行为，它定义了MySQL应遵循的语法和数据校验规则，通过设置不同的SQL_MODE值，可以改变MySQL的行为，以满足特定的需求。

1、SQL_MODE的默认值

在MySQL 5.7中，SQL_MODE的默认值包括以下几个选项：

STRICT_TRANS_TABLES：强制使用事务表，对于非事务表的操作会失败。

NO_AUTO_CREATE_USER：禁止自动创建用户。

NO_ENGINE_SUBSTITUTION：禁止使用其他存储引擎。

NO_ZERO_IN_DATE和NO_ZERO_DATE：禁止日期字段中的零值。

ERROR_FOR_DIVISION_BY_ZERO：当除数为零时，返回错误而不是警告。

NO_AUTO_CREATE_USER：禁止自动创建用户。

NO_ENGINE_SUBSTITUTION：禁止使用其他存储引擎。

NO_ZERO_IN_DATE和NO_ZERO_DATE：禁止日期字段中的零值。

ERROR_FOR_DIVISION_BY_ZERO：当除数为零时，返回错误而不是警告。

2、如何查看当前的SQL_MODE值

可以通过以下命令查看当前的SQL_MODE值：

SELECT @@sql_mode;

3、如何设置SQL_MODE的值

可以通过以下命令设置SQL_MODE的值：

SET sql_mode = 'modes';

'modes'是一个由逗号分隔的字符串，表示要设置的SQL模式，如果要设置严格模式（STRICT_TRANSACTIONS, ONLY_FULL_GROUP_BY, ...），可以使用以下命令：

SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE';

4、如何永久修改SQL_MODE的值

要将SQL_MODE的值永久修改，需要修改MySQL配置文件my.cnf（或my.ini）中的sql_mode设置，在[mysqld]部分添加或修改以下行：

sql_mode = modes;

'modes'是一个由逗号分隔的字符串，表示要设置的SQL模式，然后重启MySQL服务使更改生效。

5、注意事项

不建议随意修改SQL_MODE的值，除非有特殊需求，因为错误的设置可能导致某些查询无法执行或产生意外的结果。

在修改SQL_MODE的值时，建议先将其设置为空字符串，然后再逐个添加需要的选项，这样可以更容易地找到导致问题的模式。

如果不确定某个模式的作用，请查阅MySQL官方文档或寻求专业人士的帮助。

相关问题与解答：

1、Q: 为什么设置了SQL_MODE的值后，某些查询仍然不起作用？

A: 可能的原因是设置的SQL模式与查询所需的模式不一致，建议检查设置的SQL模式是否正确，以及查询是否确实需要该模式，还可以尝试将SQL模式设置为空字符串，然后逐个添加需要的选项，以找到导致问题的模式。

2、Q: 如何在MySQL中查看所有的SQL模式？

A: 可以通过以下命令查看MySQL支持的所有SQL模式：

```sql

SELECT name, description FROM INFORMATION_SCHEMA.MODEL;

```