科普文：软件架构数据库系列之【MySQL的sql_mode参数】

组合SQL模式名称

解释

DB2

等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTION

重要

从MySQL 5.7.22开始，DB2就被弃用了，并且在MySQL 8.0中被删除。

MSSQL

等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

POSTGRESQL

等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTION

重要

从MySQL 5.7.22开始，POSTGRESQL已被弃用，并且在MySQL 8.0中被删除。

ORACLE

等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

重要

从MySQL 5.7.22开始，ORACLE已被弃用，并且在MySQL 8.0中被删除。

MAXDB

等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

重要

从MySQL 5.7.22开始，MAXDB已被弃用，并且在MySQL 8.0中被删除。

MYSQL323

等同于HIGH_NOT_PRECDENCE加上一些特定于MYSQL323的SHOW CREATE TABLE行为：

• TIMESTAMP列显示不包括DEFAULT或ON UPDATE属性。

• 字符串列显示不包括字符集和排序规则属性。对于CHAR和VARCHAR列，如果排序规则是二进制的，则会将binary附加到列类型中。

• ENGINE=engine_name表选项显示为TYPE=engine_name。

• 对于MEMORY表，存储引擎显示为HEAP。

重要

从MySQL 5.7.22开始，MYSQL323已被弃用，并且在MySQL 8.0中被删除。

MYSQL40

等同于HIGH_NOT_PRECDENCE加上一些特定于MYSQL323的SHOW CREATE TABLE行为：

• TIMESTAMP列显示不包括DEFAULT或ON UPDATE属性。

• 字符串列显示不包括字符集和排序规则属性。对于CHAR和VARCHAR列，如果排序规则是二进制的，则会将binary附加到列类型中。

• ENGINE=engine_name表选项显示为TYPE=engine_name。

重要

从MySQL 5.7.22开始，不推荐使用MYSQL40，并且在MySQL 8.0中被删除。

ANSI

等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY

TRADITIONAL

• 对于MySQL 5.7.3及之前版本，以及MySQL 5.7.8及更高版本，TRADITIONAL等同于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到MySQL 5.7.7之间的版本，TRADITIONAL等同于STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_AUTO_CREATE _USER,NO_ENGINE_SUBSTITUTION。

参数值

MySQL 5.6

MySQL 5.7

MySQL 8.0

含义

ALLOW_INVALID_DATES

支持

支持

支持

允许插入或更新无效的日期值，即不符合日期格式的值。

在默认的严格模式下，MySQL要求日期值必须符合指定的格式，例如YYYY-MM-DD。如果插入或更新的日期值格式不正确，MySQL会报错并拒绝这个操作。

但是，当设置sql_mode为ALLOW_INVALID_DATES时，MySQL允许插入或更新不符合日期格式的值，不会报错。

说明

此模式适用于DATE和DATETIME列，不适用于 TIMESTAMP列， 因为TIMESTAMP始终需要一个有效的日期。

示例：

假设有一个日期字段"birthday"，在严格模式下，只能插入YYYY-MM-DD格式的值，否则会报错。但是，当设置sql_mode为ALLOW_INVALID_DATES时，可以插入如"2022-13-45"这样的无效日期值，MySQL会将其存储为"0000-00-00"。这在某些特定的业务场景下可能是需要的，例如需要记录一些无效日期的情况。

重要

ALLOW_INVALID_DATES参数值在某些情况下可能会引起数据一致性问题，因此在使用时需要谨慎考虑，并确保了解其使用场景和潜在影响。

ANSI_QUOTES

支持

支持

支持

用于启用ANSI_QUOTES模式，该模式下MySQL将使用双引号（""）或反引号（``）来引用标识符。

在使用sql_mode默认值时，MySQL使用单引号（''）来引用字符串值，使用反引号（``）来引用标识符（如表名、列名）。例如：

SELECT * FROM users WHERE `name` = 'John';

而当设置sql_mode为ANSI_QUOTES时，MySQL使用单引号（''）来引用字符串值，使用双引号（""）或反引号（``）来引用标识符，以符合ANSI SQL标准。例如：

SELECT * FROM "users" WHERE `name` = 'John';

启用ANSI_QUOTES模式可以增加与ANSI SQL标准的兼容性，特别是在与其他数据库系统交互时，可以更好地保证语句的可移植性和一致性。

重要

并非所有的数据库都支持使用双引号引用标识符，因此在使用ANSI_QUOTES模式时，需要注意与目标数据库的兼容性。

ERROR_FOR_DIVISION_BY_ZERO

支持

支持

支持

用于在进行除零运算时发出警告（或者错误）而不是返回NULL值。

ERROR_FOR_DIVISION_BY_ZERO模式影响除零操作的处理，其中包括MOD(N,0)。

对于数据更改操作（INSERT、UPDATE），其效果还取决于是否启用了严格的SQL模式（strict SQL mode)：

• 如果未启用该模式，则除以零会插入NULL并不会产生警告。

• 如果启用了该模式，则除以零会插入NULL并产生警告。

• 如果启用了该模式和严格的SQL模式，则除以零会产生错误，除非同时使用了IGNORE。对于INSERT IGNORE和UPDATE IGNORE，除以零会插入NULL并产生警告。

对于SELECT操作，除以零会返回NULL。启用ERROR_FOR_DIVISION_BY_ZERO会产生警告，而不管是否启用了严格的SQL模式。

ERROR_FOR_DIVISION_BY_ZERO不属于严格的SQL模式的一部分，但应与严格模式一起使用，并且默认情况下该参数是启用的。如果在未启用严格模式的情况下启用了ERROR_FOR_DIVISION_BY_ZERO，或者未启用ERROR_FOR_DIVISION_BY_ZERO的情况下，启用了严格模式，将产生警告。

重要

启用ERROR_FOR_DIVISION_BY_ZERO模式可能会导致一些查询或计算中断，因此在使用时需要谨慎考虑，并确保了解其使用场景和潜在影响。

HIGH_NOT_PRECDENCE SQL

支持

支持

支持

用于提高NOT运算符的优先级。

默认情况下，NOT的优先级低于比较运算符，例如，NOT a BETWEEN b AND c表达式被解析为NOT（a BETWEEN b AND c）。

在一些旧版本的MySQL中，NOT a BETWEEN b AND c表达式被解析为（NOT a）BETWEEN b AND c。

可以通过HIGH_NOT_PRECDENCE SQL模式来使NOT运算符的优先级更高。

IGNORE_SPACE

支持

支持

支持

允许函数名称和（字符之间有空格。这会导致内置函数名称被视为保留字。

IGNORE_SPACE SQL模式适用于内置函数，而不适用于可加载函数或存储函数。无论是否启用IGNORE_SPACE，始终允许在可加载函数或存储函数名称后面有空格。

NO_AUTO_VALUE_ON_ZERO

支持

支持

支持

用于禁止将自动递增列的默认值设置为0。

在默认情况下，当插入一行数据时，如果自动递增列的值被设置为0，MySQL会自动将其替换为下一个可用的自动递增值。然而，当设置sql_mode为NO_AUTO_VALUE_ON_ZERO时，MySQL将禁止将自动递增列的默认值设置为0。

这个参数的作用是为了避免意外地将0作为自动递增列的默认值。在某些情况下，将自动递增列的默认值设置为0可能会导致数据不一致或错误的结果。

例如：

CREATE TABLE mytable (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO mytable (id, name) VALUES (0, 'John');

如果NO_AUTO_VALUE_ON_ZERO参数被禁用（即未设置），则MySQL会将id列的值自动替换为下一个可用的自动递增值（也就是1）。

然而，当设置sql_mode为NO_AUTO_VALUE_ON_ZERO时：

SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE mytable (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO mytable (id, name) VALUES (0, 'John');

在这种情况下，MySQL将返回错误并拒绝将0作为自动递增列的默认值。

说明

NO_AUTO_VALUE_ON_ZERO参数只适用于自动递增列的默认值设置为0的情况。它不会影响手动插入或更新自动递增列的操作。

NO_BACKSLASH_ESCAPES

支持

支持

支持

用于禁止使用反斜杠（\）进行转义。

在默认情况下，MySQL允许使用反斜杠作为转义字符，在字符串中插入特殊字符或转义字符本身。例如，可以使用\'表示单引号，\"表示双引号，\\表示反斜杠本身。

然而，当设置sql_mode为NO_BACKSLASH_ESCAPES时，MySQL将禁止使用反斜杠进行转义。这意味着反斜杠字符将被视为普通字符，而不是转义字符。

这个参数的作用是为了避免使用反斜杠进行转义可能引起的混淆和错误。有时，使用反斜杠进行转义可能会导致意外的结果，特别是在处理包含大量反斜杠的数据时。

例如：

SET sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT 'It\'s a test';

在默认情况下，上述查询中的两个单引号之间的反斜杠是用于转义的。然而，当设置sql_mode为NO_BACKSLASH_ESCAPES时，反斜杠将被视为普通字符，而不是转义字符。因此，上述查询将返回It\'s a test，而不是It's a test。

说明

NO_BACKSLASH_ESCAPES参数只影响使用反斜杠进行转义的情况。它不会影响其他转义字符（如双引号或其他特殊字符）的处理。

NO_DIR_IN_CREATE

支持

支持

支持

用于在CREATE TABLE语句中禁止使用DIRECTORY选项。

在默认情况下，CREATE TABLE语句允许指定数据目录（DATA DIRECTORY）和索引目录（INDEX DIRECTORY）。然而，当设置sql_mode为NO_DIR_IN_CREATE时，MySQL将禁止在CREATE TABLE语句中使用DIRECTORY选项。

该参数的作用是为了限制或防止在CREATE TABLE语句中指定特定的目录用于存储表数据。目录选项可能会引发安全风险或造成数据管理上的混乱。

例如：

SET sql_mode = 'NO_DIR_IN_CREATE';
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50)
) DIRECTORY = '/path/to/directory';

如果NO_DIR_IN_CREATE参数被启用（即设置），则MySQL将返回错误并拒绝使用DIRECTORY选项。

在主从复制场景下，可在从库上开启该mode。

说明

NO_DIR_IN_CREATE参数只影响CREATE TABLE语句中的DIRECTORY选项，不会影响其他操作或其他与表的目录相关的设置。

NO_ENGINE_SUBSTITUTION

支持

支持

支持

用于在创建或修改表时禁止使用默认存储引擎的替代。

在MySQL中，如果在创建或修改表时指定的存储引擎不可用或不支持，MySQL会自动替换为默认的存储引擎。然而，当设置sql_mode为NO_ENGINE_SUBSTITUTION时，MySQL将禁止使用默认存储引擎的替代。

该参数的作用是为了确保使用指定的存储引擎，并避免意外或不一致的存储引擎替换。

例如：

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50)
) ENGINE = 'NonexistentEngine';

如果NO_ENGINE_SUBSTITUTION参数被启用（即设置），则MySQL将返回错误并拒绝使用指定的不存在的存储引擎。它将不会自动替换为默认的存储引擎，并且不会创建或更改表。

说明

NO_ENGINE_SUBSTITUTION参数仅在创建或修改表时生效。它不会影响使用已存在的表或其他与存储引擎相关的操作。

NO_UNSIGNED_SUBTRACTION

支持

支持

支持

用于禁止在无符号整数之间执行结果为负数的减法运算。

整数值之间的相减（其中一个为UNSIGNED类型）默认情况下会产生无符号结果。如果结果是负数，则会出现错误。

然而，当设置sql_mode为NO_UNSIGNED_SUBTRACTION时，结果可以为负数。

该参数的作用是为了避免在无符号整数之间执行减法运算时产生意外的结果。在某些情况下，无符号整数之间的减法运算可能会导致负数的结果，这可能与预期不符。

例如：

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
select cast(0 as unsigned)-1;

如果NO_UNSIGNED_SUBTRACTION参数被启用（即设置），则结果为-1。

重要

启用NO_UNSIGNED_SUBTRACTION时，即使任何操作数都是无符号的，减法结果也是有符号的。同时这意味着BIGINT UNSIGNED并非在所有上下文中都可以100%使用。

NO_ZERO_DATE

支持

支持

支持

用于禁止在日期或日期时间列中使用“0000-00-00”作为合法的零日期值。

在默认情况下，MySQL允许在日期或日期时间列中使用“0000-00-00”作为合法的零日期值。然而，当设置sql_mode为NO_ZERO_DATE时，MySQL将禁止使用“0000-00-00”作为合法的日期值。其效果还取决于是否启用了严格的SQL模式：

• 如果未启用NO_ZERO_DATE模式，则允许使用“0000-00-00”，并且插入不会产生警告。

• 如果启用NO_ZERO_DATE模式，则允许使用“0000-00-00”，并且插入会产生警告。

• 如果启用NO_ZERO_DATE模式和严格的SQL模式，则不允许使用“0000-00-00”，并且插入会产生错误，除非也给出IGNORE。对于INSERT IGNORE和UPDATE IGNORE，允许使用“0000-00-00”，并且插入会产生警告。

该参数的作用是为了避免使用无效或不合适的日期值。“0000-00-00”并不是一个真实存在的日期，因此禁止使用它可以避免出现混乱和不正确的结果。

例如：

SET sql_mode = 'NO_ZERO_DATE';
INSERT INTO mytable (id, date_column) VALUES (1, '0000-00-00');

如果NO_ZERO_DATE参数被启用（即设置），则MySQL将返回错误并拒绝将“0000-00-00”插入到date_column列中。

说明

NO_ZERO_DATE参数只影响使用“0000-00-00”作为日期值的情况，不会影响其他日期或时间格式的处理。

NO_ZERO _IN_DATE

支持

支持

支持

用于控制是否允许年的部分为非零，但月或日的部分为0的日期。

说明

此模式会影响日期如“2010-00-01”或“2010-01-00”，但不会影响“0000-00-00”。要控制服务器是否允许“0000-00-00-00”，请使用上方的NO_ZERO_DATE模式。

NO_ZERO _IN_DATE的效果还取决于是否启用了严格的SQL模式：

• 如果未启用NO_ZERO _IN_DATE模式，则允许日期中包含零，并且插入不会产生任何警告。

• 如果启用NO_ZERO _IN_DATE模式，则包含零的日期将插入为“0000-00-00”并产生警告。

• 如果启用NO_ZERO _IN_DATE模式和严格的SQL模式，则不允许包含零的日期，并且插入会产生错误，除非也给出IGNORE。对于INSERT IGNORE和UPDATE IGNORE，包含零的日期插入为“0000-00-00”并产生警告。

ONLY_FULL_GROUP_BY

支持

支持

支持

用于设置GROUP BY语句的严格模式。

在默认情况下，MySQL允许在GROUP BY查询中，SELECT列表中的列不被GROUP BY子句包含。这种行为被认为是MySQL的非标准扩展。例如，下面的查询在默认模式下是合法的：

SELECT id, name, MAX(score)
FROM mytable
GROUP BY id;

在这个查询中，name列并没有被包含在GROUP BY子句中，但MySQL仍然返回了结果。这种行为可能导致结果的不确定性，因为在不同数据库中可能有不同的聚合规则。

当设置sql_mode为ONLY_FULL_GROUP_BY时，MySQL将启用GROUP BY的严格模式，要求在SELECT列表中的每个非聚合列都必须在GROUP BY子句中出现。如果不符合要求，MySQL将返回错误。

例如，下面的查询在ONLY_FULL_GROUP_BY模式下会返回错误：

SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT id, name, MAX(score)
FROM mytable
GROUP BY id;

在这个查询中，name列没有出现在GROUP BY子句中，因此MySQL会返回错误。

通过启用ONLY_FULL_GROUP_BY模式，可以确保编写的查询语句遵循标准的GROUP BY规则，提高查询结果的准确性和可靠性。

说明

• 从MySQL 5.7.5版本开始，默认的SQL模式包括ONLY_FULL_GROUP _BY。

• 在MySQL 5.7.5版本之前，MySQL不检测功能依赖，默认情况下ONLY_FULL_GROUP _BY未启用。

PAD_CHAR_TO_FULL_LENGTH

支持

支持

支持

用于控制不删除CHAR类型字段的尾随空格。

在对CHAR字段进行存储时，在Compact格式下，会占用固定长度的字节。默认情况下，检索时会从CHAR列值中删除尾部空格。

如果启用了PAD_CHAR_TO_FULL_LENGTH，则不会删除，并且检索到的CHAR值将填充到其全长。

重要

此模式不适用于VARCHAR列，检索时会保留其尾部空格。

PIPES_AS_CONCAT

支持

支持

支持

将||视为字符串串联运算符（作用与CONCAT()相同），而不是OR的同义词。

REAL_AS_FLOAT

支持

支持

支持

将REAL视为FLOAT的同义词。

默认情况下，MySQL将REAL视为DOUBLE的同义词。

STRICT_ALL_TABLES

支持

支持

支持

为所有存储引擎启用严格的SQL模式，无效的数据值会被拒绝。

在默认情况下，MySQL允许一些隐式的数据类型转换和插入、更新操作中的警告。但是，当设置sql_mode为STRICT_ALL_TABLES时，MySQL会启用更严格的模式，要求更严格的数据类型匹配和插入、更新操作。

启用STRICT_ALL_TABLES模式后，包括但不限于以下情况将触发错误：

• 数据类型不匹配：如果在插入或更新操作中对列赋值，数据类型不匹配，MySQL将返回错误。

• 非空列：如果在插入或更新操作中，非空列没有提供值，MySQL将返回错误。

• 无效的日期或时间：如果在插入或更新操作中提供了无效的日期或时间值，MySQL将返回错误。

• 非零默认值：如果在插入操作中，列的默认值不是0或NULL，并且没有为该列提供值，则MySQL将返回错误。

使用STRICT_ALL_TABLES模式可以帮助开发者在数据库操作中更好地遵循数据类型规范和约束，从而提高数据的完整性和一致性。

重要

启用STRICT_ALL_TABLES模式可能会导致现有的应用程序出现错误或需要进行修改。在启用该模式之前，请确保已经评估了应用程序的兼容性和影响。

说明

从MySQL 5.7.4到5.7.7，STRICT_ALL_TABLES包括ERROR_FOR_DIVISION_BY_ZERO、NO_ZERO_DATE和NO_ZERO_IN_DATE模式。

STRICT_TRANS_TABLES

支持

支持

支持

为事务存储引擎启用严格的SQL模式。具体解释参见上文的STRICT_ALL_TABLES。

重要

启用STRICT_TRANS_TABLES模式可能会导致现有的应用程序出现错误或需要进行修改。在启用该模式之前，请确保已经评估了应用程序的兼容性和影响。

说明

从MySQL 5.7.4到5.7.7，STRICT_TRANS_TABLES包括ERROR_FOR_DIVISION_BY_ZERO、NO_ZERO_DATE和NO_ZERO_IN_DATE模式。

NO_AUTO_CREATE_USER

支持

支持

不支持

用于禁止在GRANT语句中自动创建用户。

在默认情况下，MySQL允许在GRANT语句中创建新用户，即当使用GRANT语句指定的用户不存在时，MySQL会自动创建该用户。然而，当设置sql_mode为NO_AUTO_CREATE_USER时，MySQL将禁止自动创建用户。

该参数的作用是在安全性方面提供更严格的控制。通过禁止自动创建用户，可以确保只有已经存在的用户才能被授权访问数据库。

例如：

GRANT SELECT ON mydb.* TO 'new_user'@'localhost';

如果new_user用户不存在，但NO_AUTO_CREATE_USER参数被禁用（即未设置），则MySQL会自动创建new_user用户并授予SELECT权限。

然而，当设置sql_mode为NO_AUTO_CREATE_USER时，如果new_user用户不存在，MySQL将返回错误并拒绝创建该用户。

SET sql_mode = 'NO_AUTO_CREATE_USER';
GRANT SELECT ON mydb.* TO 'new_user'@'localhost';

需要注意的是，NO_AUTO_CREATE_USER参数仅适用于GRANT语句中的自动用户创建，不会影响手动创建用户的操作或其他与用户相关的操作。

说明

在MySQL 8.0中，已不允许GRANT语句隐式创建用户。所以，该参数在MySQL 8.0中也不存在。

NO_FIELD_OPTIONS

支持

支持

不支持

不允许在SHOW CREATE TABLE的输出中打印MySQL特定的列选项。此模式由mysqldump在可移植模式下使用。

NO_KEY_OPTIONS

支持

支持

不支持

不允许在SHOW CREATE TABLE的输出中打印MySQL特定的索引选项。此模式由mysqldump在可移植模式下使用。

NO_TABLE_OPTIONS

支持

支持

不支持

不允许在SHOW CREATE TABLE的输出中打印MySQL特定的表选项（如ENGINE）。此模式由mysqldump在可移植模式下使用。