sql_mode

• 设置当前server的sql mode
  • 有效值及其对应的含义如下：
    • ALLOW_INVALID_DATES：不检查完整格式的日期。仅检查月份和日期。此模式适用于DATE和DATETIME列。不适用于始终需要有效日期的TIMESTAMP列，服务器加你差月和日要求值合法，而不仅仅是月份在1到12、日期在1到31之间就可以的。当禁用严格模式时，如“2004-04-31”等无效日期会被转换为“0000-00-00”插入，并产生警告。当启用严格模式后，无效日期会生成错误并拒绝插入。如果要允许此类无效日期写入，请启用ALLOW_INVALID_DATES值
    • ANSI_QUOTES：对于"，把它当作一个标识符，而不是当作一个字符串的引号，当开启了这个sql mode值之后，你就不能使用"来当作字符串的引号，因为此时会把"当作一个sql mode标识符
    • ERROR_FOR_DIVISION_BY_ZERO：该模式影响除零操作的处理，包括MOD（N，0）。对于数据写入操作（INSERT，UPDATE），其表现行为还取决于是否启用了严格的SQL模式（是否启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES）：如果未启用此sql mode，那么除零操作将插入NULL并且不产生警告，如果启用此sql mode，除零操作将插入NULL并产生警告，如果启用此sql mode和严格模式，除零操作将产生错误并拒绝写入，除非给出了IGNORE关键字。对于INSERT IGNORE和UPDATE IGNORE，除零操作将插入NULL并产生警告。对于SELECT，无论是否启用严格模式除零操作都返回NULL并产生警告。从MySQL 5.6.17开始，不建议使用ERROR_FOR_DIVISION_BY_ZERO sql mode。在5.7.8版本之后默认开启，并且不推荐去掉，在将来的版本中将删除该模式而内部直接开启该模式。
    • HIGH_NOT_PRECEDENCE：是否开启NOT运算符的高优先级模式，如果不使用该模式，那么如NOT BETWEEN b AND c之类的表达式被解析为NOT（BETWEEN b AND c），not不如between优先级高（如：SET sql_mode =''; SELECT NOT 1 BETWEEN -5 AND 5; 将优先执行1 BETWEEN -5 AND 5，结果为1，然后再执行not，最后返回结果为0）。在一些较旧版本的MySQL中，表达式被解析为（NOT a）BETWEEN b AND c，not的优先级比between高，那么not a优先解析，假如Not a返回0，那么0 BETWEEN b AND c有可能就返回1了，因为1有可能包含在b和c之间（如： SET sql_mode ='HIGH_NOT_PRECEDENCE'; SELECT NOT 1 BETWEEN -5 AND 5; 先执行not 1返回0，而0 BETWEEN -5 AND 5会返回1） 。通过启用HIGH_NOT_PRECEDENCE SQL模式将not设置为高优先级。
    • IGNORE_SPACE：该模式允许函数名和小括号之间存在空格，这样在函数名与小括号之间就算有空格，也仍然可以将内部函数名称作为保留字来处理，因此与函数名称相同的标识符必须使用反撇（如：name）来引用，示例：因为有一个COUNT（）函数，在以下语句中使用count作为表名会导致错误：（CREATE TABLE count（i INT）;），但是，在默认的模式下，在count与括号之间加一个空格，就允许创建了：CREATE TABLE count （i INT）; 此时，如果使用IGNORE_SPACE模式，那么无论在函数名与小括号之间使用多少个空格，仍然会忽略这些空格，然后把函数名当作一个保留字来处理。注意：IGNORE_SPACE SQL模式适用于内置函数，不适用于用户定义的函数或存储的函数。无论IGNORE_SPACE是否启用，在UDF或存储的函数名称之后始终允许留出空格且不会把 UDF或存储名字当作保留字。
    • NO_AUTO_CREATE_USER：该模式下，除非指定了认证信息，否则拒绝GRANT语句自动创建新用户，该模式下grant语句必须使用IDENTIFIED BY指定一个非空密码或使用IDENTIFIED WITH指定认证插件，否则不会执行创建用户的功能而直接去mysql.user表中查找用户并赋权，也就是说，肯定找不到用户直接报错（ERROR 1133 (42000): Can't find any matching row in the user table）。该模式在5.7.8版本之后默认设置，并且不推荐去掉，在将来的版本中将删除该模式而内部直接开启该模式。另外，也不推荐使用grant和revoke语句来创建用户，在将来的版本中将移除grant语句创建用户的功能，使用create user和drop user语句代替创建用户，然后再使用grant语句赋予权限（不带identified by 指定密码的语句）。
    • NO_AUTO_VALUE_ON_ZERO：该模式影响AUTO_INCREMENT列的处理。通常，对自增列插入NULL或0值时，会生成自增序列号插入自增列。NO_AUTO_VALUE_ON_ZERO模式开启之后将限制0值插入自增列，只有NULL才能生成自增序列号。如果在带有自增属性的表中的自增列已经存在0值，则此模式可能很有用。 （存储0值是不推荐的做法。）例如，如果您使用mysqldump导出表，然后重新导入实例，则MySQL通常会在遇到0值时生成新的序列号代替0值，这就导致导入的数据与导出的数据不一样了。此时可以在导入之前导出的备份文件之前启用NO_AUTO_VALUE_ON_ZERO模式可以解决此问题。 目前mysqldump导出的文件中会自动包含一个NO_AUTO_VALUE_ON_ZERO模式（在mysqldump生成的备份文件开头的地方有类似：/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /; 把sql mode设置为该模式，在文件末尾即数据sql导入完成之后使用/!40101 SET SQL_MODE=@OLD_SQL_MODE */;把sql mode修改回原来的值），以避免此问题
    • NO_BACKSLASH_ESCAPES：该模式禁止在字符串中使用反斜杠字符（\）作为转义字符。启用此模式后，反斜杠成为普通字符
    • NO_DIR_IN_CREATE：该模式启用时，在创建表时，忽略所有INDEX DIRECTORY和DATA DIRECTORY指令。此选项在复制架构中对从库很有用
    • NO_ENGINE_SUBSTITUTION：在不使用此模式时（禁用NO_ENGINE_SUBSTITUTION），执行如CREATE TABLE或ALTER TABLE之类的语句指定的存储引擎被禁用或指定的存储引擎不支持时将自动转换为默认的存储引擎，并发出警告。对于CREATE TABLE语句(会发出警告，并使用默认的存储引擎创建表；对于ALTER TABLE，会发生警告并忽略更改操作（可以执行语句，但并不会发生修改动作）)。启用NO_ENGINE_SUBSTITUTION后，如果CREATE TABLE或ALTER TABLE语句所需的引擎不可用，则会发生错误，并且拒绝执行创建或更改表的语句
    • NO_FIELD_OPTIONS：在该模式下，SHOW CREATE TABLE的输出中不打印MySQL特定的列选项。在异构数据库之间移植数据库时mysqldump在该模式导出数据使用
    • NO_KEY_OPTIONS：在该模式下，SHOW CREATE TABLE的输出中不打印MySQL特定的索引选项。 在异构数据库之间移植数据库时mysqldump在该模式导出数据使用
    • NO_TABLE_OPTIONS：在该模式下，SHOW CREATE TABLE的输出中不打印MySQL特定的表选项（如ENGINE）。 在异构数据库之间移植数据库时mysqldump在该模式导出数据使用
    • NO_UNSIGNED_SUBTRACTION：在该模式下，无符号限制被忽略，无符号的减法在0-N的情况下将产生负数结果值，另外还有一种建表时使用减法的将产生很诡异的结果，示例：（select示例：SET sql_mode = ''; SELECT CAST(0 AS UNSIGNED) - 1; 此时直接报错：ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'，修改sql mode：SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; SELECT CAST(0 AS UNSIGNED) - 1; 此时返回-1。建表示例：SET sql_mode=''; CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL); CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test; DESCRIBE t1; 此时看到t1表的的bigint 带有unsigned属性（bigint(21) unsigned），修改sql mode：SET sql_mode='NO_UNSIGNED_SUBTRACTION'; CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test; DESCRIBE t2; 此时看到t2表的bigint不带unsigned属性（ bigint(21)））。
    • NO_ZERO_DATE：该模式影响服务器是否允许“0000-00-00”作为有效日期插入。其具体行为还取决于是否启用严格的SQL模式。如果未启用此模式，则允许零值“0000-00-00”插入，并且插入不产生警告，如果启用此模式，则允许零值“0000-00-00”插入，并且插入会产生警告。如果启用此模式和严格sql模式，则不允许零值“0000-00-00”插入，插入会产生错误并拒绝执行插入操作，除非包含了IGNORE关键字。对于INSERT IGNORE和UPDATE IGNORE，允许零值'0000-00-00'插入，插入会产生警告。从MySQL 5.6.17版本起，NO_ZERO_DATE已被弃用。在5.7.8及其之后的版本中，默认的sql_mode包含该模式，在后续的版本规划中将在严格的SQL模式中默认包含此模式的行为，注意：该模式仅仅只是限制时间格式全为0的时候，像“0000-00-01”这样的值，或者对于datetime数据类型，类似'0000-00-00 00:00:01'值仍然可以插入。
    • NO_ZERO_IN_DATE：该模式的作用、后续的版本规划与NO_ZERO_DATE类似，但是该模式只会限制日期中的月和日部分，当启用该模式时，日期中的月和日不能为零值。只要月和日期部分不为零值就允许插入。
    • ONLY_FULL_GROUP_BY：开启该模式之后，select target list、order by target list、having target list中引用的列要么都来自于group by list（如：select id,test from test group by id,test; select id,test from test group by id,test having id is null order by test,id; select target list、order by target list、having target list在不使用聚合函数的情况下，引用的列必须来自于group by list，否则报错），要么非group by list都来自于聚合函数的值（如：select count(id),test from test group by test;），另外：select target list、order by target list、having target list和group by list还可以使用表达式或者别名，但必须严格匹配（如：select 1+id from test group by 1+id; select 1+id as a from test group by a; 注意：select 1+id from test group by id+1;这种把1+反过来写的被人为是不严格匹配，即非法的），再者：前面介绍的三种情况，还可以混用（如：select id+1,count(test) from test group by id+1; select id+1 as a,count(test) from test group by a;），除此之外，其他情况都人为是非法的（报错信息类似：ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xiaoboluo.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by），开启ONLY_FULL_GROUP_BY之后会拒绝执行查询。参考资料：http://www.wtoutiao.com/p/19dh3ec.html、https://www.ywnds.com/?p=8184
    • PAD_CHAR_TO_FULL_LENGTH：默认sql mode下，在数据检索时，返回结果修剪了CHAR列尾部空格。如果启用了PAD_CHAR_TO_FULL_LENGTH模式，则在数据检索时不会进行尾部空格修剪，而是将CHAR值填充到其列值定义的长度（检索时保留尾随空格，注意：此模式不适用于VARCHAR列），示例：CREATE TABLE t1 (c1 CHAR(10)); INSERT INTO t1 (c1) VALUES('xy'); SET sql_mode = ''; SELECT c1, CHAR_LENGTH(c1),length(c1) FROM t1; 此时返回结果集中CHAR_LENGTH(c1)和length(c1)结果集是列值尾部被修剪之后的值，都返回2，现在修改sql mode：SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; SELECT c1, CHAR_LENGTH(c1),length(c1) FROM t1; 此时返回结果集中CHAR_LENGTH(c1)和length(c1)结果集是列值尾部空格未被修剪的值，都返回10
    • PIPES_AS_CONC：该模式将|| 操作法视为连接操作符（与concat()函数作用一样），而不是视为逻辑或操作符OR
    • REAL_AS_FLOAT：该模式下将REAL视为FLOAT的同义词，默认情况下是将REAL视为DOUBLE的同义词
    • STRICT_ALL_TABLES：该模式表示为所有的存储引擎开启严格SQL模式，在该模式下会拒绝无效的值插入
    • STRICT_TRANS_TABLES：该模式下表示为支持事务的存储引擎开启严格SQL模式，也可能会为不支持事务的存储引擎开启严格SQL模式。
  • 以上sql mode有效值可以根据不同的DB设置不同的组合值，有效组合值如下；
    • ANSI：在5.6.x版本中等同于REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE。在5.7.x版本中等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY。标准ansi sql中，where子句中的查询无法聚合引用外部表的字段，如：select a.* from test as a where id in (select max(a.id) from test as b where b.id=?); 所以，实际上你设置sql_mode=ansi时，show variables看到的sql_mode除了等同值之外，还多了一个ansi模式
    • DB2：5.6.x版本中和5.7.x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,DB2,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS，实际上你设置sql_mode=db2时，show variables看到的sql_mode除了等同值之外，还多了一个db2模式
    • MAXDB：5.6.x版本中和5.7.x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER，实际上你设置sql_mode=MAXDB时，show variables看到的sql_mode除了等同值之外，还多了一个MAXDB模式
    • MSSQL：5.6.x版本中和5.7.x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS，实际上你设置sql_mode=MSSQL时，show variables看到的sql_mode除了等同值之外，还多了一个MSSQL模式
    • MYSQL323：5.6.x版本中和5.7.x版本中都等同于MYSQL323,HIGH_NOT_PRECEDENCE，实际上你设置sql_mode=MYSQL323时，show variables看到的sql_mode除了等同值之外，还多了一个MYSQL323模式
    • MYSQL40：5.6.x版本中和5.7.x版本中都等同于MYSQL40,HIGH_NOT_PRECEDENCE，实际上你设置sql_mode=MYSQL40时，show variables看到的sql_mode除了等同值之外，还多了一个MYSQL40模式
    • ORACLE：5.6.x版本中和5.7.x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER，实际上你设置sql_mode=ORACLE时，show variables看到的sql_mode除了等同值之外，还多了一个ORACLE模式
    • POSTGRESQL：5.6.x版本中和5.7.x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS，实际上你设置sql_mode=POSTGRESQL时，show variables看到的sql_mode除了等同值之外，还多了一个POSTGRESQL模式
    • TRADITIONAL：5.6.x版本中和5.7.x版本中都等同于STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，实际上你设置sql_mode=TRADITIONAL时，show variables看到的sql_mode除了等同值之外，还多了一个TRADITIONAL模式
  • 常用的最重要的sql mode值有：ANSI、STRICT_TRANS_TABLES、TRADITIONAL
  • 如果使用的表是Innodb表，则还同时会受到innodb_strict_mode参数的影响。该参数对于Innodb表会开启额外的错误检查。
  • 全局，会话，动态变量，set类型，5.6.5及其之前的版本默认值为空，大于等于5.6.6之后的5.6.x版本默认值为NO_ENGINE_SUBSTITUTION（5.6.x版本中有一些默认值可能为STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION，实测5.6.21版本是这个默认值）， 小于等于5.7.4之前的5.7.x版本默认值为NO_ENGINE_SUBSTITUTION，大于等于5.7.5 and 小于等于5.7.6版本默认值为ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION，5.7.7版本默认值为ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION，大于等于5.7.8之后的5.7.x版本默认值为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
  • 注意：在其他类型的数据库数据迁移到mysql时，需要设置对用类型数据库的sql mode，此时可以参考组合值，mysql与mysql之间迁移数据时，需要留意数据源数据库中的sql mode值是多少，迁移到新的数据库中之后，留意是否需要在新数据库中修改为相同的sql mode。另外，NO_ZERO_DATE、NO_ZERO_IN_DATE、ERROR_FOR_DIVISION_BY_ZERO模式在后续的版本中，将被合并到严格sql mode中，即，不设置该模式的情况下，启用严格sql mode的效果也等于启用了这些模式。
  • PS：什么是严格的SQL模式？
    • 严格SQL模式控制MySQL如何处理数据变更语句（如INSERT或UPDATE）中的无效或缺失值。值无效的原因有：插入值可能有与表定义中的列属性不对应，写入了错误的数据类型值，或者可能插入值超出范围。缺失值可能是在插入值没有显式定义了DEFAULT not NULL列的值 （对于定义了default NULL属性的列，如果插入时没有显式指定列值，则默认插入NULL）。严格模式还会影响DDL语句（如CREATE TABLE）
    • 如果严格SQL模式不启用或不生效时，MySQL会为无效或缺失值插入一个内部调整之后的值，并产生警告。如果需要，在严格模式下，您可以使用INSERT IGNORE或UPDATE IGNORE关键字在严格模式下插入无效值
    • 对于不更改数据的SELECT语句，无效值会在严格模式下生成警告，但不会返回错误
    • 从MySQL 5.6.11起，尝试为超过最大索引长度的列创建索引时，在严格SQL模式下会产生一个错误并拒绝执行。在这个版本之前，会给出警告并按照最大索引长度截断来创建一个前缀索引（这与严格SQL模式未启用时的行为相同）
    • 严格SQL模式不影响是否检查外键约束。如果需要关闭外键约束，可以使用foreign_key_checks=0
    • 如果启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES，严格SQL模式即生效，但两者有些区别：对于事务表，当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES时，数据更改语句中的无效或缺失值会返回错误并中止执行语句并回滚。对于非事务性表，如果无效值出现在要插入或更新的第一行数据中，则对于两个严格SQL模式行为相同，该语句都会被中止，并且不会对该表做任何数据变更操作。如果该语句同时插入或修改多行时，并且无效值发生在第二行或及其之后的行，则结果取决于启用了哪种严格SQL模式(启用了STRICT_ALL_TABLES严格模式时，MySQL返回一个错误，忽略剩下未做数据更改的行操作。但是，由于发生错误之前的行已被插入或更新，所以最终结果是发生了部分更新。为了避免这种情况，请使用等值匹配。启用了STRICT_TRANS_TABLES严格模式时，MySQL将无效值转换为与列定义的属性最接近的有效值，并插入内部调整后的值。如果是缺少某列值，MySQL会为缺少值的列插入对应列的数据类型的隐式默认值。在STRICT_TRANS_TABLES模式下无效值和缺失值时，MySQL都会生成一个警告而不是错误，并继续处理该语句）
    • 结合ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE模式，严格模式还会影响零值，零日期值的处理。

---

上一篇：auto_increment_increment | 下一篇：read_only