mysql sql_mode 汇总整理

-- ANSI 使sql符合标准sql
This mode changes syntax and behavior to conform more closely to standard SQL


-- STRICT_TRANS_TABLES 如果事务语句有错，则使事务失败
If a value could not be inserted as given into a transactional table, abort the statement


-- TRADITIONAL 使用error替代warning
this mode is “give an error instead of a warning” when inserting an incorrect value into a column


-- ALLOW_INVALID_DATES 允许非法日期
Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31
This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date
With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error.


-- ANSI_QUOTES 使双引号当在一个标识符，而不 是一个字符串标识
Treat "" as an identifier quote character (like the "`" quote character) and not as a string quote character
--启用 ANSI_QUOTES 表名加双引号正常执行
mysql>  SET sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.01 sec)
mysql> select "id",id from "t" limit 3;
+----+----+
| id | id |
+----+----+
|  1 |  1 |
|  2 |  2 |
+----+----+
3 rows in set (0.00 sec
--关闭 ANSI_QUOTES 表名加双引报错
mysql>  SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select "id",id from "t" limit 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"t" limit 10' at line 1


-- ERROR_FOR_DIVISION_BY_ZERO 决定被0整除是后的行为? 
If this mode is not enabled, division by zero inserts NULL and produces no warning.
If this mode is enabled, division by zero inserts NULL and produces a warning.
If this mode and strict mode are enabled, division by zero produces an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, division by zero inserts NULL and produces a warning.


-- HIGH_NOT_PRECEDENCE 控制 not 的优化级
The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c)
mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
-> 0
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
-> 1



-- NO_AUTO_CREATE_USER 阻止grant自动创建用户，如果没有提供密码或空密码是不认的
Prevent the GRANT statement from automatically creating new user accounts if it would otherwise do so, unless authentication information is specified
The statement must specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH


-- NO_AUTO_VALUE_ON_ZERO 当插入null或0者是否对 AUTO_INCREMENT 产生下一个值
NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. 
NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.


-- NO_DIR_IN_CREATE 在创建表或索引时是否忽略目录参数，注意如果主从的目录结构不同，则可使用此参数
When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.


-- NO_ENGINE_SUBSTITUTION 当创建表或修改表时使用了一个非法或disable的存储引擎时，是否使用其它替代的引擎
Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in


-- NO_ZERO_DATE 控制00是否可做为一个日期类型
The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.
If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.
If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.
If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, 
unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

-- NO_ZERO_IN_DATE 控制在年份不为0，但月或天是否可为0，影响如上
The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0


-- ONLY_FULL_GROUP_BY 使group操作符合标准聚合操作
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns


-- PIPES_AS_CONCAT 使双竖线成为连接符，类似于postgresql和oracle中的||
Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR.


-- STRICT_ALL_TABLES 使严格模式使用于所有的表，非法数据被拒绝，推荐启用
Enable strict SQL mode for all storage engines. Invalid data values are rejected

-- STRICT_TRANS_TABLES 使严格事务模式使用于所有的存储引擎，推荐启用
Enable strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines


-- mysql还针对不同的数据库提供了组合模式
ANSI,DB2,ORACLE,POSTGRESQL,MSSQL