MySql sql_mode

sql_mode默认值是空值，在默认值设置下是可以允许一些非法操作，比如允许写入一些非法数据，在生产环境中，必须将sql_mode设置为严格模式

生产环境 未启用严格模式案列

 

问题描述：添加一个超长的文章的时候，文章内容字段被MySQL截取后插入，导致文章内容不完整。(ANSI模式即宽松模式)
debug：经排查确认业务程序插入的SQL没有问题。
问题原因：MySQL使用了松散模式，造成自动截取。给debug带来困难

 

解决办法：

　　1、开启MySQL 严格模式，对开发有好处；
　　2、更换MySQL的字段类型，减少被截断的概率。

参考：MySQL中和text相关的类型最大长度限制，请根据业务类型进行合理选择：
　　TINYTEXT 最大长度是 255 (2^8 – 1) 个字符。
　　TEXT 最大长度是 65535 (2^16 – 1) 个字符。
　　MEDIUMTEXT 最大长度是 16777215 (2^24 – 1) 个字符。
　　LONGTEXT 最大长度是 4294967295 (2^32 – 1) 个字符。

mysql支持的sql_mode模式：ANSI、TRADITIONAL、STRICT_ALL_TABLES和STRICT_TRANS_TABLES。

　　ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
　　TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
　　STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。只对支持事务的表有效。
　　STRICT_ALL_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。对所有表都有效。

5.6 https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_mode

Command-Line Format	--sql-mode=name
System Variable	sql_mode
Scope	Global, Session
Dynamic	Yes
Type	Set
Default Value	NO_ENGINE_SUBSTITUTION
Valid Values	ALLOW_INVALID_DATES ANSI_QUOTES ERROR_FOR_DIVISION_BY_ZERO HIGH_NOT_PRECEDENCE IGNORE_SPACE NO_AUTO_CREATE_USER NO_AUTO_VALUE_ON_ZERO NO_BACKSLASH_ESCAPES NO_DIR_IN_CREATE NO_ENGINE_SUBSTITUTION NO_FIELD_OPTIONS NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_UNSIGNED_SUBTRACTION NO_ZERO_DATE NO_ZERO_IN_DATE ONLY_FULL_GROUP_BY PAD_CHAR_TO_FULL_LENGTH PIPES_AS_CONCAT REAL_AS_FLOAT STRICT_ALL_TABLES STRICT_TRANS_TABLES

 

5.7 https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode

Command-Line Format	--sql-mode=name
System Variable	sql_mode
Scope	Global, Session
Dynamic	Yes
Type	Set
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
Valid Values	ALLOW_INVALID_DATES ANSI_QUOTES ERROR_FOR_DIVISION_BY_ZERO HIGH_NOT_PRECEDENCE IGNORE_SPACE NO_AUTO_CREATE_USER NO_AUTO_VALUE_ON_ZERO NO_BACKSLASH_ESCAPES NO_DIR_IN_CREATE NO_ENGINE_SUBSTITUTION NO_FIELD_OPTIONS NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_UNSIGNED_SUBTRACTION NO_ZERO_DATE NO_ZERO_IN_DATE ONLY_FULL_GROUP_BY PAD_CHAR_TO_FULL_LENGTH PIPES_AS_CONCAT REAL_AS_FLOAT STRICT_ALL_TABLES STRICT_TRANS_TABLES

8.0 https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_mode

Command-Line Format	--sql-mode=name
System Variable	sql_mode
Scope	Global, Session
Dynamic	Yes
SET_VAR Hint Applies	Yes
Type	Set
Default Value	ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION
Valid Values	ALLOW_INVALID_DATES ANSI_QUOTES ERROR_FOR_DIVISION_BY_ZERO HIGH_NOT_PRECEDENCE IGNORE_SPACE NO_AUTO_VALUE_ON_ZERO NO_BACKSLASH_ESCAPES NO_DIR_IN_CREATE NO_ENGINE_SUBSTITUTION NO_UNSIGNED_SUBTRACTION NO_ZERO_DATE NO_ZERO_IN_DATE ONLY_FULL_GROUP_BY PAD_CHAR_TO_FULL_LENGTH PIPES_AS_CONCAT REAL_AS_FLOAT STRICT_ALL_TABLES STRICT_TRANS_TABLES TIME_TRUNCATE_FRACTIONAL

sql_mode值说明如下：

　　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。