MySQL的SQL模式

严格模式

严格模式指将 SQL_MODE 变量设置为 STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES 中的至少一种。

严格模式控制MySQL如何处理非法或丢失的输入值。

有几种原因可以使一个值为非法。例如，数据类型错误，不适合列，或超出范围。

当新插入的行不包含某列没有显示定义 DEFAULT 子句的值，则该值被丢失。

对于事务表，当启用STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES 模式时，如果语句中有非法或丢失值，则会出现错误。语句被放弃并回滚。

对于非事务表，如果插入或更新的第1行出现坏值，两种模式的行为相同。语句被放弃，表保持不变。

如果语句插入或修改多行，并且坏值出现在第2或后面的行，结果取决于启用了哪个严格选项：

对于STRICT_ALL_TABLES，MySQL返回错误并忽视剩余的行。但是，在这种情况下，前面的行已经被插入或更新。这说明你可以部分更新，这可能不是你想要的。要避免这点，最好使用单行语句，因为这样可以不更改表即可以放弃。 

对于STRICT_TRANS_TABLES，MySQL将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失，MySQL在列中插入隐式默认值。在任何情况下，MySQL都会生成警告而不是给出错误并继续执行语句。

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


ERROR_FOR_DIVISION_BY_ZERO
在 INSERT 或 UPDATE 过程中，如果数据被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，那么数据被零除时 MySQL 返回 NULL。如果用到 INSERT IGNORE 或 UPDATE IGNORE 中，MySQL生成被零除警告，但操作结果为NULL。


HIGH_NOT_PRECEDENCE

操作符的优先顺序是表达式。

例如，NOT a BETWEEN b AND c 被解释为 NOT(a BETWEEN b AND c)，在一些旧版本MySQL中， 前面的表达式被解释为 (NOT a)BETWEEN b AND c。启用 HIGH_NOT_PRECEDENCE SQL 模式，可以获得比以前旧版本的更高优先级的结果。

ALLOW_INVALID_DATES
该选项并不完全对日期的合法性进行检查，只检查月份是否在1～12之间，日期是否在1～31之间。该模式仅对DATE和DATETIME类型有效，而对TIMESTAMP无效，因为TIMESTAMP总是要求一个合法的输入。


IGNORE_SPACE
允许函数名和(之间有空格。强制将所有函数名视为保留字。结果是，如果你想要访问保存为字的数据库、表或列名，你必须引用它。例如，因为有 USER() 函数，MySQL 数据库中的 user 表名和该表内的 User 列被保存下来，因此你必须引用它们：SELECT "User" FROM mysql."user"。


NO_AUTO_CREATE_USER
禁止 GRANT 创建密码为空的用户。


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


ONLY_FULL_GROUP_BY
对于 GROUP BY 聚合操作，如果在 SELECT 中的列没有在 GROUP BY 中出现，那么这句 SQL 是不合法的。


NO_ZERO_IN_DATE
在严格模式，不接受月或日部分为0的日期。如果使用 IGNORE 选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。


NO_ZERO_DATE
在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用 IGNORE 选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。


NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。


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


NO_BACKSLASH_ESCAPES
禁用反斜线字符(‘\’)做为字符串内的退出字符。启用该模式，反斜线则成为普通字符。


NO_DIR_IN_CREATE
创建表时，忽视所有 INDEX DIRECTORY 和 DATA DIRECTORY 指令。该选项对从复制服务器有用。


NO_FIELD_OPTIONS
不要在 SHOW CREATE TABLE 的输出中打印MySQL专用列选项。


NO_KEY_OPTIONS
不要在 SHOW CREATE TABLE 的输出中打印MySQL专用索引选项。


NO_TABLE_OPTIONS
不要在 SHOW CREATE TABLE 的输出中打印MySQL专用表选项（例如ENGINE）。


NO_UNSIGNED_SUBTRACTION
在减运算中，如果某个操作数没有符号，不要将结果标记为 UNSIGNED。请注意这样使 UNSIGNED BIGINT 不能100%用于上下文中。


REAL_AS_FLOAT

将 REAL 视为 FLOAT 的同义词，而不是 DOUBLE 的同义词。

PAD_CHAR_TO_FULL_LENGTH

对于 char 类型字段，不要截断空洞数据。空洞数据就是自动填充值为 0x20 的数据。

下面的几种 SQL_MODE 设置是之前讨论的几种选项的组合。

ANSI, ORACLE, TRADITIONAL, MSSQL, DB2, MYSQL323, MYSQL40, MAXDB

详细的组合选项可以参看手册或者《MySQL技术内幕：SQL编程》这本书。