mysql mode模式_MySQL 的 mysql_mode 模式设置

数据表中某字段没有设置了默认值，所以在写 insert 语句时没有添加该字段，就出现了问题，告诉我该字段没有插入数据，我不是设置了默认值了吗？按理说插入的时候不添加该字段也可以啊，但是报错了，后来排查因为 sql_mode 设置的问题。

什么是 sql_mode，官方手册中有一节介绍：https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html 的意思。简单理解下就是：mysql 服务器可以在不同的 sql 模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 sql_mode 系统变量的值。我们可以设置全局 sql 模式以匹配站点服务器操作要求，并且每个应用程序可以将其会话 sql 模式设置为其自己的要求。

查看 sql_mode 设置mysql> show variables like 'sql_mode';

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

| Variable_name | Value                                                                                                                                     |

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

| 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 |

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

sql_mode 各参数说明：ONLY_FULL_GROUP_BY

对于 group by 操作，如果在 select 中的列，没有在 group by 中出现，那么将认为这个 sql 是不合法的，因为列不在 group by 从句中，会报一个 ERROR 1055 (42000): ...

STRICT_TRANS_TABLES

存储引擎启用严格 sql 模式

NO_ZERO_IN_DATE

在严格模式，不接受月或日部分为 0 的日期。如果使用 IGNORE 选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告

NO_ZERO_DATE

在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用 IGNORE 选项插入零日期。在非严格模式，可以接受该日期，但会生成警告

ERROR_FOR_DIVISION_BY_ZERO

在严格模式，在 insert 或 update 过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时 mysql 返回 null。如果用到 INSERT IGNORE 或 UPDATE IGNORE 中，mysql 生成被零除警告，但操作结果为NULL

NO_AUTO_CREATE_USER

防止 GRANT 自动创建新用户，除非还指定了密码。

NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

修复 INSERT 语句报错的问题

经过翻阅官方手册得知 STRICT_TRANS_TABLES 启用严格 sql 模式，如果 insert 语句不包含该列的值，则会抛出一个错误，为了便于 sql 的灵活性，我们把这个 STRICT_TRANS_TABLES 删除。mysql> set sql_mode=(select replace(@@sql_mode,'STRICT_TRANS_TABLES',''));

查看 sql_mode 设置mysql> show variables like 'sql_mode';

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

| Variable_name | Value                                                                                                                 |

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

| sql_mode      | ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

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

我们已经去掉了 STRICT_TRANS_TABLES 这个参数，下面执行插入语句返回 Query OK, 0 rows affected, 1 warning (0.00 sec)。成功了，但是呢，等我返回浏览器执行添加操作却再次失败，纳尼？刚才测试的不是成功了吗？为什么在 mysql 命令行中可以执行成功，但是浏览器中不可以呢？原来这里设置的 sql_mode 对全局没有影响，还有一个 global.sql_mode，那我们也得把 global.sql_mode 设置下。mysql> show global variables like 'sql_mode';

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

| Variable_name | Value                                                                                                                                     |

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

| 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 |

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

果然！STRICT_TRANS_TABLES 它还在，也把它去掉。mysql> set @@global.sql_mode=(select replace(@@global.sql_mode,'STRICT_TRANS_TABLES',''));

查看全局 sql_mode 设置mysql> show global variables like 'sql_mode';

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

| Variable_name | Value                                                                                                                 |

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

| sql_mode      | ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

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

我们已经去掉全局中的 STRICT_TRANS_TABLES 这个参数，返回浏览器中执行添加，添加成功，你以为这就完了吗？其实没有，如果你不重启 mysql 服务的话那这样就可以了。那如果重启呢？还是不行，所以我们直接改 my.cnf 配置文件吧。# my.cnf 配置文件

.

.

.

[mysqlhotcopy]

interactive-timeout

sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

打开 my.cnf 文件，发现我的 sql_mode 并没有设置 STRICT_TRANS_TABLES 这个参数啊，为什么不行呢？原来这个设置没有放在 [mysqld] 下面，我们把它挪下位置，直接在后面追加就好。[mysqld]

.

.

.

sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

保存后重启 mysql 服务，返回浏览器执行添加，成功。因为默认 mysql 没有配置 STRICT_TRANS_TABLES 参数，所以我只是挪动了下位置，大家可以根据实际情况来修改 sql_mode 的参数值。