【数据库】MySql的sql_mode模式说明

什么是严格模式

mysql5.7+的版本中，sql_mode为严格模式。比如必须给字段默认值，更新数据的长度必须符合限制等。

参考：MySQL中和text相关的类型最大长度限制，请根据业务类型进行合理选择：

TINYTEXT 最大长度是 255 (2^8 – 1) 个字符。

TEXT 最大长度是 65535 (2^16 – 1) 个字符。

MEDIUMTEXT 最大长度是 16777215 (2^24 – 1) 个字符。

LONGTEXT 最大长度是 4294967295 (2^32 – 1) 个字符。

sql_mode的常见设置

ANSI模式 ：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。

TRADITIONAL模式 ：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

STRICT_TRANS_TABLES模式 ：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。只对支持事务的表有效。

STRICT_ALL_TABLES模式 ：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。对所有表都有效。

不同版本的Mysql的sql_mode默认值

Mysql 5.6 ：Default Value 为 NO_ENGINE_SUBSTITUTION

Mysql 5.7 ：Default Value 为 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 8.0 ：Default Value 为 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

各参数值的说明

ALLOW_INVALID_DATES ：字母意思允许不合法的数据。不对日期做全面的检查，仅仅检查月份是否在1~12之间，天数是否在1~31之间；这种模式可能是有用的对web应用来说去获取年，月，日在三个不同的字段并且准确存储用户的输入数值，没有验证数据的合法性。这种模式对date和datetime类型有作用，但是对timestamp类型不起作用，timestamp总是要合法的数据。当ALLOW_INVALID_DATES启用时，服务端要求年和月时合法的。如果strict模式禁用，不合法的数据如”2004-04-31″被存储为”0000-00-00″并且审查警告；若严格模式启用则会生成错误。(测试的时候，数据时原样插入的，没有转换为0000-00-00)

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

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

HIGH_NOT_PRECEDENCE ： (高not优先级):在如下一个表达式中not a between b and c,在当前设置下被解析为not (a between b and c)，在旧的行为中同一个表达式被解析为(not a) between b and c;启用了HIGH_NOT_PRECEDENCE这个模式，就是启用来旧的优先级设置，表达式将被解析为后一种行为。

IGNORE_SPACE ：用于忽略mysql系统函数名与之后的括号之间的空格、列如：count () 通过设置ignore_space 这个sql_mode 就可以把空格给忽略变成count()

NO_AUTO_CREATE_USER ：禁止grant语句自动创建用户，除非认证信息被指定，语句必须包含一个非空的密码使用identified by或使用认证插件identified with.

NO_AUTO_VALUE_ON_ZERO ：该模式影响自增列的插入。在默认设置下，插入 0 或 NULL 代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长值，那么该模式就起作用了。

NO_BACKSLASH_ESCAPES ：(禁用反斜线转义)这个模式启用，反斜线将会变成一个普通的字符串。

NO_DIR_IN_CREATE ：在创建表时忽略所有的index directory和data directory选项。

NO_ENGINE_SUBSTITUTION ：此模式指定当执行create语句或者alter语句指定的存储引擎没有启用或者没有编译时，控制默认默认存储引擎的自动切换。默认是启用的。当NO_ENGINE_SUBSTITUTION被禁用，当create表时的默认存储引擎不可使用则产生警告信息，对于alter语句产生警告并且表不会被alter。当NO_ENGINE_SUBSTITUTION启用，会生成错误并且表不会被创建或alter如果期望的存储引擎不可用。

NO_FIELD_OPTIONS ：不要在SHOW CREATE TABLE的输出中打印MySQL专用列选项。该模式在可移植模式（portability mode）下用于mysqldump。

NO_KEY_OPTIONS ：不要在SHOW CREATE TABLE的输出中打印MySQL专用索引选项。该模式在可移植模式（portability mode）下用于mysqldump。

NO_TABLE_OPTIONS ：不要在SHOW CREATE TABLE的输出中打印MySQL专用表选项（例如ENGINE）。该模式在可移植模式（portability mode）下用于mysqldump。

NO_UNSIGNED_SUBTRACTION ：在整型数值之间的减法，一个值得类型是unsigned的，那么默认结果也是unsigned的。若是结果是一个负数，则会产生一个错误。

NO_ZERO_DATE ：这个模式影响着插入的’0000-00-00’值是否作为合法的数值，这个结果和是否启用严格模式有关。如果这个模式禁用，则’0000-00-00’被允许并且插入没有警告，如果这个模式启用，则’0000-00-00’被运行并且插入产生一个警告信息；如果这个模式和严格模式被启用，则’0000-00-00’不被允许并且插入产生错误，除非ignore被使用。

NO_ZERO_DATE 不是严格模式的一部分，应该和严格模式一起被使用。因为NO_ZERO_DATE将会被放弃在将来的mysql中，它的影响将会被包含进严格模式中。 NO_ZERO_IN_DATE：这个模式影响着日期中的月份和天数是否可以为0(注意年份是非0的)，这个模式也取决于严格模式是否被启用。如果这个模式未启用，date中的零部分被允许并且插入没有警告。如果这个模式启用，dates中的零部分插入被作为“0000-00-00”并且产生一个警告。如果这个模式和严格模式被启用，则dates中的零部分不被允许并且插入产生错误，除非ignore也被使用。这个模式也不是严格模式的一部分，应该和严格模式一起被使用。

ONLY_FULL_GROUP_BY ：这个模式对查询的影响有点大。mysql5.7默认启用这个模式，这个模式是指在mysql的select查询不能出现除group by语句字段之外的其余字段。

PAD_CHAR_TO_FULL_LENGTH ：虽然char 和 varchar 的存储方式不太相同，但是对两个字符串的比较，都只比较其值，忽略CHAR值存在的右填充，即使将SQL_MODE设置为PAD_CHAR_TO_FULL_LENGTH 也一样，但这不适用于like

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

REAL_AS_FLOAT ：将real作为float的同义词。默认的，mysql将real作为double的同义词

TIME_TRUNCATE_FRACTIONAL ：控制当时间的毫秒精度超出时，是进行四设五入，还是直接舍弃多余的位数。

举个例子，当将’14:52:12.15’插入到time(1)的时候，是插入’14:52:12.2’还是插入’14:52:12.1′

当为OFF时，进行四设五入，插入14:52:12.2；

当为ON时，舍弃多余的位数，插入14:52:12.1；

默认为OFF，建议OFF。