MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。 DBA可以根据服务器要求设置全局SQL Mode，并且每个应用程序可以根据自己的需求，设置会话级别的SQL Mode。
SQL Mode会影响MySQL支持的SQL语法以及数据校验等。 这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务器一起使用变得更加容易。
设置SQL Mode的3种方式：
1、 服务器启动时加入参数： --sql-mode="modes"
2、 My.ini配置文件中加入：sql-mode="modes"
3、 SET [GLOBAL|SESSION] sql_mode = 'modes';
查看当前的SQL Mode：
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
重要提醒：
SQL模式和用户定义的分区。 在创建数据并将数据插入分区表后，更改服务器SQL模式可能会导致此类表的行为发生重大更改，并可能导致数据丢失或损坏。 强烈建议您在创建使用用户定义分区的表后永远不要更改SQL模式。
复制分区表时，主服务器和从服务器上的不同SQL模式也会导致问题。 为获得最佳结果，应始终在主服务器和从服务器上使用相同的服务器SQL模式。
SQL Modes列表：
1、ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。
2、NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，该列又是自增长的，那么这个选项就有用了。
3、STRICT_TRANS_TABLES
在该模式下，如果一个值不能插入到一个事物表中，则中断当前的操作，对非事物表不做限制
4、NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零
5、NO_ZERO_DATE
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
6、ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL
7、NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
8、NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
9、PIPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
10、ANSI_QUOTES
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
11、ALLOW_INVALID_DATES
不对日期进行全面检查，只检查月份1-12、日期1-31范围
12、IGNORE_SPACE
允许函数名和（字符 之间的空格。
13、NO_BACKSLASH_ESCAPES
禁止使用反斜杠字符（\）作为字符串和标识符中的转义字符
14、PAD_CHAR_TO_FULL_LENGTH
默认情况下，在检索时从CHAR列值中删除末端空格。如果启用了该模式，则不会进行删除，并且检索到的CHAR值将填充到其全长。 此模式不适用于VARCHAR列，在检索时仍保留末端空格。
15、REAL_AS_FLOAT
将REAL视为FLOAT的同义词。 默认情况下，MySQL将REAL视为DOUBLE的同义词。
16、STRICT_ALL_TABLES
为所有存储引擎启用严格SQL模式。无效的数据值被拒绝。
组合SQLMode
1、ANSI
等同于REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE 和 (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY.组合模式。
这种模式使语法和行为更符合标准的SQL。
2、TRADITIONAL
在MySQL 5.7.4以前、以及在MySQL 5.7.8 及以后版本：
等同于STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION.
从MySQL 5.7.4 到5.7.7版本：
等同于STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION.
严格模式，对于插入不正确的值会给出错误而不是警告。可以应用于事务表和非事务表，用于事务表时，出现错误就会立即回滚
3、DB2
等同于PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS.
4、MAXDB
等同于PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER.
5、MSSQL
等同于PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、
NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS.
6、ORACLE
等同于PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER.
7、POSTGRESQL
等同于PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS.
网友评论