1. 问题

用户表中有一个created_by非空字段,表示是谁创建的这个用户,因为系统默认有一个admin用户,故在创建表语句中有一条插入语句

insert into user(id,username,password) values('1','admin',MD5('admin'));

经过测试没有问题,提交之后,同事找到我说sql脚本执行报错,一看错误日志是created_by字段没有默认值

[Err] 1364 - Field `created_by` doesn't have a default value

相同的SQL语句在不同的环境中执行结果居然不一样,经过调查,是两个MySQL环境的sql_mode不一样导致的。

mysql> show variables like "sql_mode";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

sql_mode 问题: NOT NULL 列没有默认值但代码里也没给值,在非严格模式下,int列默认为0,string列默认为''了,所以没有问题;但在严格模式下,是直接返回失败的。

2.查看与设置sql_mode

-- 查看当前连接会话的sql模式:
mysql> select @@session.sql_mode;

-- 或者从环境变量里取
mysql> show variables like "sql_mode";

-- 查看全局sql_mode设置:
mysql> select @@global.sql_mode;

-- 只设置global,需要重新连接进来才会生效
-- 将sql_mode设置为严格模式
mysql> SET sql_mode='STRICT_TRANS_TABLES';

3. sql_mode常用值

官方手册专门有一节介绍SQL Mode,SQL Mode 定义了两个方面:MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查。

SQL_MODE常用值

  • ONLY_FULL_GROUP_BY:

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中

  • NO_AUTO_VALUE_ON_ZERO:

该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

  • STRICT_TRANS_TABLES:

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制

  • NO_ZERO_IN_DATE:

在严格模式下,不允许日期和月份为零

  • NO_ZERO_DATE:

设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

  • ERROR_FOR_DIVISION_BY_ZERO:

在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL

  • NO_AUTO_CREATE_USER:

禁止GRANT创建密码为空的用户

  • NO_ENGINE_SUBSTITUTION:

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

  • PIPES_AS_CONCAT:

  • 将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

  • ANSI_QUOTES:

无论何种mode,产生error之后就意味着单条sql执行失败,对于支持事务的表,则导致当前事务回滚;但如果没有放在事务中执行,或者不支持事务的存储引擎表,则可能导致数据不一致。MySQL认为,相比直接报错终止,数据不一致问题更严重。于是 STRICT_TRANS_TABLES 对非事务表依然尽可能的让写入继续,比如给个”最合理”的默认值或截断。而对于 STRICT_ALL_TABLES,如果是单条更新,则不影响,但如果更新的是多条,第一条成功,后面失败则会出现部分更新。

5.6.6 以后版本默认就是NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,5.5默认为 ‘’ 。

参考:

MySQL sql_mode 说明(及处理一起 sql_mode 引发的问题)

MySQL SQL_MODE详解

文章来源于互联网:如何解决MySQL sql_mode引发的SQL执行错误

发表评论