sql-mode

sql_mode是一组语法校验规则。 MySQL服务器能够工作在不同的SQL模式下，并能针对不同的客户端以不同的方式应用这些模式。这样，应用程序就能对服务器操作进行量身定制以满足自己的需求。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。这样，就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。可以使用“--sql-mode="modes"”选项，通过启动mysqld来设置默认的SQL模式。从MySQL 4.1开始，也能在启动之后，使用SET [SESSION|GLOBAL] sql_mode='modes'语句，通过设置sql_mode变量更改模式。

通常在linux下安装完mysql后，默认的sql-mode值是空，在这种情形下mysql执行的是一种不严格的检查，例如日期字段可以插入’0000-00-00 00:00:00’这样的值，还有如果要插入的字段长度超过列定义的长度，那么mysql不会终止操作，而是会自动截断后面的字符继续插入操作。

应用场景

SQL Mode 还可以实现对数据效验和转移等功能如：

• 校验日期数据合法性.
• 在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误
• 将‘"’视为识别符引号(‘`’引号字符)
• 禁用反斜线字符(‘\’)做为字符串内的退出字符。启用NO_BACKSLASH_ESCAPES模式，反斜线则成为普通字符。
• 将||视为字符串连接操作符（＋）(同CONCAT())，而不视为OR。

修改值

使用命令 select @@sql_mode; 可以查看sql_mode的值.

在命令行中输入

set sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE','')); 

可以修改sql_mode。

使用命令

select @@global.sql_mode; 

可以查看全局sql_mode的值。 输入以下命令

set  @@global.sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE','')); 

修改全局的值。

还可以通过在配置文件中进行设置(推荐)

在/etc/my.cnf的[mysqld]下设置

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

常见的sql_mode值的介绍

取值	含义
ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中
STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制
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	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。
NO_AUTO_VALUE_ON_ZERO	该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE	这个模式影响了是否允许日期中的月份和日包含0。如果开启此模式，2016-01-00是不允许的，但是0000-02-01是允许的。它实际的行为受到 strict mode是否开启的影响。
NO_ZERO_DATE	设置该值，mysql数据库不允许插入零日期。它实际的行为受到 strictmode是否开启的影响。
ERROR_FOR_DIVISION_BY_ZERO	在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER	禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT	将”
ANSI_QUOTES	启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

TIP

在生产环境下设置为 sql_mode='NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER' 可以减少较少的错误

注意：

MySQL8以上已经取消了NO_AUTO_CREATE_USER，故将sql_mode修改为如下即可：

sql-mode = NO_ENGINE_SUBSTITUTION