MySQL 的 mysql_mode 模式设置

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

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

什么是sql_mode,官方手册中有一节介绍:https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html,基本定义是:MySQL服务器可以在不同的SQL模式下运行,并且可以针对不同的客户端以不同的方式应用这些模式,具体取决于sql_mode系统变量的值。DBA可以设置全局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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

参数说明:

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:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

经过翻阅官方手册得知STRICT_TRANS_TABLES启用严格SQL模式,如果INSERT语句不包含该列的值,则会抛出一个错误,为了便于SQL的灵活性,我们把这个STRICT_TRANS_TABLES删除。

去掉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它还在,也把它去掉。

去掉全局的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]下面,我们把它挪下位置,直接在后面追加就好。

修改my_cnf文件

[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的参数值。

结束语:感谢您对本网站文章的浏览,欢迎您的分享和转载,但转载请说明文章出处。
Top