mysql之sql_mode报错以及解决合集

[重要通告]如您遇疑难杂症,本站支持知识付费业务,扫右边二维码加博主微信,可节省您宝贵时间哦!

1、在5.7版本以上mysql中使用group by语句进行分组时, 如果select的字段 , 不是完全对应的group by后面的字段 , 有其他字段 , 那么就会报这个错误

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

查看sql_mode

select @@sql_mode;

mysql> select @@sql_mode;

可以看到有一个ONLY_FULL_GROUP_BY , 这个就是严格控制group by 和select 字段的 , 在不改变sql语句的前提下 , 把这个去掉就可以了

set  @@global.sql_mode = '其他值'；

2、Mysql ERROR 1067: Invalid default value for ‘date’ 解决

如果create table 时指定的engine项不被支持，这个时候mysql会支持报错

修改sql_mode 的值,如果没有则添加 NO_ENGINE_SUBSTITUTION

set  @@global.sql_mode = '原来的值,NO_ENGINE_SUBSTITUTION'；

3、数据插入报错Incorrect integer value: '' for column

mysql版本到5以上的都会遇到这样的问题，插入空字符要使用NULL

默认为sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，  

将其修改为sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，重启mysql后即可

PS：sql_mode 的其他值说明:

mysql的sql_mode合理设置

sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题

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：

启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

ORACLE的sql_mode设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

如果使用mysql，为了继续保留大家使用oracle的习惯，可以对mysql的sql_mode设置如下：

在my.cnf添加如下配置
[mysqld]
sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

问题未解决？付费解决问题加Q或微信 2589053300 (即Q号又微信号)右上方扫一扫可加博主微信

所写所说，是心之所感，思之所悟，行之所得；文当无敷衍，落笔求简洁。 以所舍，求所获；有所依，方所成！