mysql allow_invalid_dates_mysql的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)

root@testdb 06:19:08>create tabletb2(l1 date);

Query OK,0 rows affected (0.22sec)

root@testdb 06:23:23>set session sql_mode="ALLOW_INVALID_DATES";

Query OK,0 rows affected, 1 warning (0.00sec)

root@testdb 06:23:41>insert into tb2 values("2012-02-30");

Query OK,1 row affected (0.00sec)

root@testdb 06:23:45>select * fromtb2;+------------+

| l1 |

+------------+

| 2012-02-30 |

+------------+

1 row in set (0.00sec)

root@testdb 06:23:56>insert into tb2 values("2012-04-31");

Query OK,1 row affected (0.09sec)

root@testdb 06:24:44>select * fromtb2;+------------+

| l1 |

+------------+

| 2012-02-30 |

| 2012-04-31 |

+------------+

2 rows in set (0.00sec)

root@testdb 06:24:46>set session sql_mode="TRADITIONAL";

Query OK,0 rows affected, 1 warning (0.00sec)

root@testdb 06:26:26>insert into tb2 values("2012-06-31");

ERROR1292 (22007): Incorrect date value: '2012-06-31' for column 'l1' at row 1root@testdb 06:26:38>

测试结果

ANSI_QUOTES：这个模式反人类，大致意思就是启用来这个模式，不能使用双引号来引用字符创，因为双引号也被认为是一种标识字符创，可以使用单引号。

root@testdb 06:35:17>create table tb2(name varchar(20));

Query OK,0 rows affected (0.04sec)

root@testdb 06:35:54>set session sql_mode="ANSI_QUOTES";

Query OK,0 rows affected, 1 warning (0.00sec)

root@testdb 06:36:04>insert into tb2 values("puting"); #双引号不能使用

ERROR1054 (42S22): Unknown column 'puting' in 'field list'root@testdb 06:36:24>insert into tb2 values('puting'); #使用单引号引用字符串

Query OK,1 row affected (0.03sec)

root@testdb 06:36:35>insert into tb2 values('"'); #把双引号作为字符串插入

Query OK,1 row affected (0.01sec)

root@testdb 06:36:59>select * fromtb2;+--------+

| name |

+--------+

| puting |

| " |

+--------+

2 rows in set (0.00sec)

root@testdb 06:37:06>

测试结果

ERROR_FOR_DIVISION_BY_ZERO：如果这个模式未启用，那么零除操作将会插入空值并且不会产生警告；如果这个模式启用，零除操作插入空值并产生警告；如果这个模式和严格模式都启用，零除从操作将会产生一个错误，除非使用来ignore来忽略错误。例如insert ignore和update ignore，这样的话零除操作将插入空并发出警告。ERROR_FOR_DIVISION_BY_ZERO不是严格模式的一部分，应该和严格模式一起启用，默认是启用的。

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这个模式，就是启用来旧的优先级设置，表达式将被解析为后一种行为。

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

NO_AUTO_VALUE_ON_ZERO: 这个值影响自增列，正常情况下可以通过插入“0”值或者null空值来生成自增序列的下一个值。NO_AUTO_VALUE_ON_ZERO模式会抑制这种行为，仅仅当插入null值才会生成下一个序列的自增值。这个模式是非常有用的当0值已经在表的自增序列中存储时。例如，如果你dump这个表并且重新reload它，mysql可能会生成新的值当导入数值为0的值时，这样就会导致数据的不一致，启用这个模式在导入dumo出的文件之前可以解决这个不一致的问题。mysqldump会自动包含了这个模式。

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： 这个模式将会在5.7.22中删除。

NO_KEY_OPTIONS， NO_TABLE_OPTIONS:同上。

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

root@testdb 10:07:04>select cast(0 as unsigned) -1;

ERROR1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'root@testdb 10:15:14>

若启用了这个模式，结果将是负数。

root@testdb 10:15:14>set session sql_mode="NO_UNSIGNED_SUBTRACTION";

Query OK,0 rows affected (0.00sec)

root@testdb 10:16:19>select cast(0 as unsigned) -1;+------------------------+

| cast(0 as unsigned) -1 |

+------------------------+

| -1 |

+------------------------+

1 row in set (0.00sec)

root@testdb 10:16:21>

如果这个结果被用于更新一个unsigned整型的字段，结果将会被裁减为最大值或者裁减为0，如果NO_UNSIGNED_SUBTRACTION这个模式启用。在严格模式下将会发生错误。

启用NO_UNSIGNED_SUBTRACTION模式：

root@testdb 10:06:26>set session sql_mode="NO_FIELD_OPTIONS";

Query OK,0 rows affected (0.01sec)

root@testdb 10:16:21>create table tb3(c1 intunsigned);

Query OK,0 rows affected (0.04sec)

root@testdb 10:21:22>insert into tb3 select cast(0 as unsigned) -1;

Query OK,1 row affected, 1 warning (0.02sec)

Records:1 Duplicates: 0 Warnings: 1root@testdb 10:21:44>select * fromtb3;+------+

| c1 |

+------+

| 0 |

+------+

1 row in set (0.00sec)

root@testdb 10:21:54>#默认已经启用了严格模式，在新的连接中插入此值。

root@(none)10:22:07>usetestdb;Databasechanged

root@testdb 10:22:12>insert into tb3 select cast(0 as unsigned) -1;

ERROR1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'root@testdb 10:22:19>

测试结果

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语句字段之外的其余字段。