目录

sql_mode

概述

官方说明

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.

Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

配置项介绍

  • ONLY_FULL_GROUP_BY

    对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中。简而言之,就是SELECT后面接的列必须被GROUP BY后面接的列所包含。如: select a,b from table group by a,b,c; (正确) select a,b,c from table group by a,b; (错误) 这个配置会使得GROUP BY语句环境变得十分狭窄,所以一般都不加这个配置

  • NO_AUTO_VALUE_ON_ZERO

    该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。(不信的可以试试,默认的sql_mode你在自增主键列设置为0,该字段会自动变为最新的自增值,效果和null一样),如果用户希望插入的值为0(不改变),该列又是自增长的,那么这个选项就有用了。

  • STRICT_TRANS_TABLES

    在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制。(InnoDB默认事务表,MyISAM默认非事务表;MySQL事务表支持将批处理当做一个完整的任务统一提交或回滚,即对包含在事务中的多条语句要么全执行,要么全部不执行。非事务表则不支持此种操作,批处理中的语句如果遇到错误,在错误前的语句执行成功,之后的则不执行;MySQL事务表有表锁与行锁非事务表则只有表锁)

  • 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后,不能用双引号来引用字符串,因为它被解释为识别符

Group by 查询错误

1
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'eden.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

翻译过来就是:select list的表达式1不在group by子句中,并且包含非聚合列“test.mytable.name”,该列在功能上不依赖于group by子句中的列;这与sql _mode=only_full _group _by不兼容

解决办法

  • 使用**any_value()函数,**这个函数对不需要group by的字段有效,等同于关闭only_full_group_by,但是这样难免会遗漏某个字段,所以不推荐使用。

  • 暂时性关闭**(可以通过select @@sql_mode查出sql_mode以后去掉**ONLY_FULL_GROUP_BY后复制过来内容):

    1
    2
    
    set sql_mode=' ' //改变已经存在的数据库sql_mode
    set @@global.sql_mode=' ' //改变全局配置sql_mode
    

    暂时性关闭就不用重启服务器,直接就可以用了

  • 更改配置文件(推荐使用)

    linux系统更改/etc/my.cnf文件,使用vi命令打开,如果有sql_mode=…的注释就把注释打开,如果没有就加上sql_mode=…(可以通过select @@sql_mode查出sql_mode以后去掉ONLY_FULL_GROUP_BY后复制过来内容)

    windows下配置文件是安装目录下的my.ini文件,其余同上

    需要重启服务生效