- SQL_MODE的严格模式
- SQL_MODE模式修改
- ONLY_FULL_GROUP_BY
- STRICT_TRANS_TABLES
- NO_ZERO_IN_DATE
- NO_ZERO_DATE
- ERROR_FOR_DIVISION_BY_ZERO
- NO_ENGINE_SUBSTITUTION
- ALLOW_INVALID_DATES
- ANSI_QUOTES
- HIGH_NOT_PRECEDENCE
- IGNORE_SPACE
- NO_AUTO_VALUE_ON_ZERO
- NO_BACKSLASH_ESCAPES
- NO_DIR_IN_CREATE
- NO_UNSIGNED_SUBTRACTION
- PAD_CHAR_TO_FULL_LENGTH
- PIPES_AS_CONCAT
- REAL_AS_FLOAT
- STRICT_ALL_TABLES
- TIME_TRUNCATE_FRACTIONAL
sql_mode用于控制SQL语句的执行方式和行为。通过设置sql_mode，你可以启用或禁用特定的功能和约束，这样可以帮助你根据应用程序的需求调整数据库的行为。
本文所有演示示例，全部基于wlnmp源安装的MySQL8.4.3版本。
MySQL8.4官方参考手册：https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html
附，MySQL my.cnf配置文件生成器：https://dbcnf.wlnmp.com/
MySQL8.4默认值
ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_ENGINE_SUBSTITUTION
可选值
ALLOW_INVALID_DATES
ANSI_QUOTES
ERROR_FOR_DIVISION_BY_ZERO
HIGH_NOT_PRECEDENCE
IGNORE_SPACE
NO_AUTO_VALUE_ON_ZERO
NO_BACKSLASH_ESCAPES
NO_DIR_IN_CREATE
NO_ENGINE_SUBSTITUTION
NO_UNSIGNED_SUBTRACTION
NO_ZERO_DATE
NO_ZERO_IN_DATE
ONLY_FULL_GROUP_BY
PAD_CHAR_TO_FULL_LENGTH
PIPES_AS_CONCAT
REAL_AS_FLOAT
STRICT_ALL_TABLES
STRICT_TRANS_TABLES
TIME_TRUNCATE_FRACTIONAL
SQL_MODE的严格模式
SQL_MODE的严格模式主要是通过STRICT_ALL_TABLES或STRICT_TRANS_TABLES来实现的。
STRICT_ALL_TABLES：会对事务表（如InnoDB）和非事务表（如MyISAM）中的插入和更新操作进行严格检查。
STRICT_TRANS_TABLES：仅对事务表（如InnoDB）中的插入和更新操作进行严格检查。
在生产环境中，建议使用严格模式（即包含STRICT_ALL_TABLES或STRICT_TRANS_TABLES），以确保数据的有效性和一致性。这有助于避免潜在的数据问题，并提高应用程序的可靠性。
SQL_MODE模式修改
基于会话临时生效
|
1 |
SET sql_mode = 'STRICT_TRANS_TABLES'; |
等同于
|
1 |
SET SESSION sql_mode = 'STRICT_TRANS_TABLES'; |
基于全局临时生效
|
1 |
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES'; |
永久生效
修改MySQL my.cnf配置文件
|
1 |
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' |
重启MySQL生效
查询当前SQL_MODE模式
|
1 |
SELECT @@sql_mode; |
ONLY_FULL_GROUP_BY
ONLY_FULL_GROUP_BY参数在默认情况下是启用的，用于确保在GROUP BY语句SELECT里的列都包含在GROUP BY里。这个模式有助于数据准确性，避免非法数据插入。
从老版本MySQL升级到新版本时，很大可能性会出现无效查询异常的sql，这是因为从MySQL5.7.5版本开始，ONLY_FULL_GROUP_BY为默认值启用，见官方说明：https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html。
启用ONLY_FULL_GROUP_BY SQL模式时，SELECT语句中的字段必须遵循以下规则：
1、分组字段：所有在SELECT中列出的字段必须是GROUP BY子句中指定的字段。
2、聚合函数：如果要在SELECT中使用的字段未出现在GROUP BY子句中，则必须使用聚合函数进行包裹。这些聚合函数包括SUM()、AVG()、COUNT()等。
演示示例
创建数据库
|
1 |
CREATE DATABASE company; |
使用数据库
|
1 |
USE company; |
创建employees表
|
1 2 3 4 5 6 |
CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10, 2) ); |
插入一些数据
|
1 |
INSERT INTO employees (name, department, salary) VALUES ('zhangsan', 'HR', 5000), ('lisi', 'IT', 6000), ('wangwu', 'HR', 7000), ('zhaoliu', 'IT', 8000); |
查询下当前表结构
|
1 |
SELECT * FROM employees; |
|
1 2 3 4 5 6 7 8 |
+----+----------+------------+---------+ | id | name | department | salary | +----+----------+------------+---------+ | 1 | zhangsan | HR | 5000.00 | | 2 | lisi | IT | 6000.00 | | 3 | wangwu | HR | 7000.00 | | 4 | zhaoliu | IT | 8000.00 | +----+----------+------------+---------+ |
启用ONLY_FULL_GROUP_BY
|
1 |
SET sql_mode = 'ONLY_FULL_GROUP_BY'; |
查看当前 sql_mode
|
1 |
SELECT @@sql_mode; |
有效的查询
通过分组字段方式查询
|
1 |
SELECT department FROM employees GROUP BY department; |
|
1 2 3 4 5 6 |
+------------+ | department | +------------+ | HR | | IT | +------------+ |
通过聚合函数方式查询
|
1 |
SELECT department, COUNT(name) AS employee_count, SUM(salary) AS total_salary FROM employees GROUP BY department; |
|
1 2 3 4 5 6 |
+------------+----------------+--------------+ | department | employee_count | total_salary | +------------+----------------+--------------+ | HR | 2 | 12000.00 | | IT | 2 | 14000.00 | +------------+----------------+--------------+ |
无效的查询
|
1 |
SELECT department, salary FROM employees GROUP BY department; |
报错ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'company.employees.salary' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
在这个查询中，salary列没有被聚合。salary可能在同一个部门中有多个不同的值，但在GROUP BY的上下文中，MySQL不知道应该如何处理这些不同的salary值。因为salary没有在GROUP BY中列出，也没有使用聚合函数（如 SUM(salary)、AVG(salary) 等），所以MySQL无法确定应该返回哪个salary值。
禁用ONLY_FULL_GROUP_BY
|
1 |
SET sql_mode = ''; |
此时我们再执行无效的查询
|
1 |
SELECT department, salary FROM employees GROUP BY department; |
|
1 2 3 4 5 6 |
+------------+---------+ | department | salary | +------------+---------+ | HR | 5000.00 | | IT | 6000.00 | +------------+---------+ |
可以看到，此时查询将不会报错，但是会导致潜在的数据不一致或逻辑错误的结果。
建议生产环境中启用ONLY_FULL_GROUP_BY。
STRICT_TRANS_TABLES
STRICT_TRANS_TABLES参数在默认情况下是启用的，它会对事务表中插入或更新操作的值进行严格检查。如果某个列的值不符合该列的数据类型或约束条件，MySQL将会拒绝该操作并抛出错误，而不是默默地将值截断或转换为默认值。
演示示例
启用STRICT_TRANS_TABLES
|
1 |
SET sql_mode = 'STRICT_TRANS_TABLES'; |
查询当前SQL模式
|
1 |
SELECT @@sql_mode; |
在数据库中创建一个users表
|
1 2 3 4 5 6 |
CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, age INT, PRIMARY KEY (id) ); |
有效的插入
|
1 |
INSERT INTO users (username, age) VALUES ('zhangsan', 30); |
无效的插入
|
1 |
INSERT INTO users (username, age) VALUES ('lisi','five'); |
报错ERROR 1366 (HY000): Incorrect integer value: 'five' for column 'age' at row 1
此时插入失败，因为age列期望一个整数，但插入的是字符串。
禁用STRICT_TRANS_TABLES
|
1 |
SET sql_mode = ''; |
此时我们再执行无效的插入或更新
|
1 |
INSERT INTO users (username, age) VALUES ('lisi','five'); |
|
1 |
UPDATE users SET age = 'five' WHERE username = 'zhangsan'; |
此时插入或更新操作都会成功，但有警告，查询下表结构
|
1 |
SELECT * FROM users; |
|
1 2 3 4 5 6 |
+----+----------+------+ | id | username | age | +----+----------+------+ | 1 | zhangsan | 0 | | 2 | lisi | 0 | +----+----------+------+ |
因为age列是INT类型，所以MySQL会自动将字符串'five'自动转换为0。
如果age列使用TINYINT类型，当插入或更新字段大于TINYINT范围时，MySQL可能会将age自动截断为127。
建议生产环境中启用STRICT_TRANS_TABLES。
NO_ZERO_IN_DATE
NO_ZERO_IN_DATE参数在默认情况下是启用的，用于允许年份为零，但是月份或日期为零（如2025-00-01或2025-01-00），MySQL会将它们视为有效并存储为'0000-00-00'。为防止插入无效的日期，建议搭配严格模式一起使用。
演示示例
启用NO_ZERO_IN_DATE
|
1 |
SET sql_mode = NO_ZERO_IN_DATE; |
查询当前SQL模式
|
1 |
SELECT @@sql_mode; |
创建一个日期测试表
|
1 2 3 4 |
CREATE TABLE test_dates ( id INT AUTO_INCREMENT PRIMARY KEY, event_date DATE ); |
插入0000年份、无效月份或日期
|
1 |
INSERT INTO test_dates (event_date) VALUES ('2025-00-15'); |
|
1 |
INSERT INTO test_dates (event_date) VALUES ('2025-01-00'); |
|
1 |
INSERT INTO test_dates (event_date) VALUES ('0000-01-01'); |
插入成功，前两条插入有警告，查询结果
|
1 |
SELECT * FROM test_dates; |
|
1 2 3 4 5 6 7 |
+----+------------+ | id | event_date | +----+------------+ | 1 | 0000-00-00 | | 2 | 0000-00-00 | | 3 | 0000-01-01 | +----+------------+ |
当年份为0000，月份和日期都不为零时，月份和日期都正常显示。
当出现月份或日期为0时，都将被存储为'0000-00-00'。
启用NO_ZERO_IN_DATE并启用严格模式
|
1 |
SET sql_mode = 'NO_ZERO_IN_DATE,STRICT_TRANS_TABLES'; |
插入0000年份、无效月份或日期
|
1 |
INSERT INTO test_dates (event_date) VALUES ('2025-00-15'); |
报错ERROR 1292 (22007): Incorrect date value: '2025-00-15' for column 'event_date' at row 1
|
1 |
INSERT INTO test_dates (event_date) VALUES ('2025-01-00'); |
报错ERROR 1292 (22007): Incorrect date value: '2025-01-00' for column 'event_date' at row 1
|
1 |
INSERT INTO test_dates (event_date) VALUES ('0000-01-01'); |
成功插入
禁用NO_ZERO_IN_DATE
|
1 |
SET sql_mode = ''; |
查询当前SQL模式
|
1 |
SELECT @@sql_mode; |
插入无效月份或日期
|
1 |
INSERT INTO test_dates (event_date) VALUES ('2025-00-15'); |
|
1 |
INSERT INTO test_dates (event_date) VALUES ('2025-01-00'); |
插入成功，查询结果
|
1 |
SELECT * FROM test_dates; |
|
1 2 3 4 5 6 |
+----+------------+ | id | event_date | +----+------------+ | 1 | 2025-00-15 | | 2 | 2025-01-00 | +----+------------+ |
你会发现月份或日期即使为零，也会存储零月份零日期。
建议将NO_ZERO_IN_DATE、STRICT_TRANS_TABLES、NO_ZERO_DATE一起使用。这样可以确保在遇到无效日期时会引发错误，而不是默默地将其存储为0000-00-00，建议生产环境中启用。
NO_ZERO_DATE
NO_ZERO_DATE参数在默认情况下启用的，用于是否允许'0000-00-00'作为有效日期，如果禁用NO_ZERO_DATE，将允许'0000-00-00'日期，前提是搭配严格模式使用。
演示示例
启用NO_ZERO_DATE并启用严格模式
|
1 |
SET sql_mode = 'NO_ZERO_DATE,STRICT_TRANS_TABLES'; |
查询当前SQL模式
|
1 |
SELECT @@sql_mode; |
创建一个日期测试表
|
1 2 3 4 |
CREATE TABLE test_dates2 ( id INT AUTO_INCREMENT PRIMARY KEY, event_date DATE ); |
插入'0000-00-00'
|
1 |
INSERT INTO test_dates2 (event_date) VALUES ('0000-00-00'); |
报错ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'event_date' at row 1
禁用NO_ZERO_DATE
|
1 |
SET sql_mode = ''; |
插入'0000-00-00'
|
1 |
INSERT INTO test_dates2 (event_date) VALUES ('0000-00-00'); |
插入成功
查询结果
|
1 |
select * from test_dates2; |
|
1 2 3 4 5 |
+----+------------+ | id | event_date | +----+------------+ | 1 | 0000-00-00 | +----+------------+ |
建议将NO_ZERO_DATE、NO_ZERO_IN_DATE、STRICT_TRANS_TABLES一起使用。可以有效地减少插入无效日期的风险，确保数据的一致性和有效性，建议生产环境中启用。
ERROR_FOR_DIVISION_BY_ZERO
ERROR_FOR_DIVISION_BY_ZERO参数在默认情况下是启用的，用于控制数据更改操作（INSERT、UPDATE）以及MOD(N,0)执行除以零操作时数据库的行为，为了帮助开发者避免潜在的计算错误，并确保数据的完整性。
演示示例
启用ERROR_FOR_DIVISION_BY_ZERO
|
1 |
SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO'; |
查询当前SQL模式
|
1 |
SELECT @@sql_mode; |
使用INSERT和UPDATE语句，先创建一个演示的表
|
1 |
CREATE TABLE numbers (value DOUBLE); |
插入除以0的结果
|
1 |
INSERT INTO numbers (value) VALUES (10 / 0); |
插入成功，值为NULL，提示警告
禁用ERROR_FOR_DIVISION_BY_ZERO
|
1 |
SET sql_mode = ''; |
插入除以0的结果
|
1 |
INSERT INTO numbers (value) VALUES (10 / 0); |
插入成功，值为NULL，没有警告
启用ERROR_FOR_DIVISION_BY_ZERO并同时开启严格模式
|
1 |
SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,STRICT_TRANS_TABLES'; |
插入除以0的结果
|
1 |
INSERT INTO numbers (value) VALUES (10 / 0); |
报错ERROR 1365 (22012): Division by 0
更新操作
|
1 |
UPDATE numbers SET value = 10 / 0; |
报错ERROR 1365 (22012): Division by 0
建议将ERROR_FOR_DIVISION_BY_ZERO、STRICT_TRANS_TABLES一起使用。这对于确保数据的完整性和避免潜在的计算错误至关重要，建议生产环境中启用。
NO_ENGINE_SUBSTITUTION
NO_ENGINE_SUBSTITUTION参数在默认情况下是启用的，控制了在SQL语句（如CREATE TABLE或ALTER TABLE）指定的存储引擎不可用时，是否自动替换默认存储引擎。
演示示例
启用NO_ENGINE_SUBSTITUTION
|
1 |
SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; |
创建库表，使用一个不存在的存储引擎
|
1 2 3 4 5 6 |
CREATE DATABASE test_db; USE test_db; CREATE TABLE test_table ( id INT PRIMARY KEY, name VARCHAR(50) ) ENGINE=MyISAM_NOEXIST; |
报错ERROR 1286 (42000): Unknown storage engine 'MyISAM_NOEXIST'
修改表，使用一个不存在的存储引擎
|
1 |
ALTER TABLE test_table ENGINE=MyISAM_NOEXIST; |
报错ERROR 1286 (42000): Unknown storage engine 'MyISAM_NOEXIST'
禁用NO_ENGINE_SUBSTITUTION
|
1 |
SET sql_mode = ''; |
创建一个表，使用一个不存在的存储引擎
|
1 2 3 4 |
CREATE TABLE test_table ( id INT PRIMARY KEY, name VARCHAR(50) ) ENGINE=MyISAM_NOEXIST; |
创建成功，提示警告
查看test_db数据库中test_table表使用了哪个存储引擎
|
1 |
SHOW CREATE TABLE test_db.test_table; |
|
1 2 3 4 5 6 7 8 9 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_table | CREATE TABLE `test_table` ( `id` int NOT NULL, `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci | +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
通过查询结果看到，我们在创建test_table表时，指定了一个不存在的存储引擎，因为MyISAM_NOEXIST引擎并不存在，所以MySQL将其转化成了默认的引擎。如果我们ALTER TABLE更改指定了一个不存在的引擎，表不会被成功修改。
我们想象一个场景，在创建时指定了MyISAM引擎，但是字母打错了，一样会创建成功，但是最终使用的引擎就不一致了，所以建议生产环境中启用NO_ENGINE_SUBSTITUTION。
ALLOW_INVALID_DATES
ALLOW_INVALID_DATES参数在默认情况下是禁用的，它仅检查月份是否在1-12范围内，以及日期是否在1-31范围内，该参数只适用于DATE和DATETIME列。
1、禁用时，非法日期（如'2025-02-31'），将被转换为'0000-00-00'。
2、禁用并启用严格模式时，非法日期将生成错误。
3、启用时，只检查月份是否在1-12范围内，日期是否在1-31范围内，并不检查日期是否正确（如'2025-02-31'被认为是正确的），超出范围将被转换为'0000-00-00'。
演示示例
禁用ALLOW_INVALID_DATES
|
1 |
SET sql_mode = ''; |
创建测试表
|
1 |
CREATE TABLE test_dates (id INT, date_value DATE); |
插入非法日期
|
1 |
INSERT INTO test_dates (id, date_value) VALUES (1, '2025-02-31'); |
|
1 |
INSERT INTO test_dates (id, date_value) VALUES (1, '2025-02-32'); |
执行成功，两条sql均提示警告
查询插入结果
|
1 |
SELECT * FROM test_dates; |
|
1 2 3 4 5 6 |
+------+------------+ | id | date_value | +------+------------+ | 1 | 0000-00-00 | | 1 | 0000-00-00 | +------+------------+ |
非法日期将被转换为'0000-00-00'。
禁用ALLOW_INVALID_DATES并开启严格模式
|
1 |
SET sql_mode = 'STRICT_TRANS_TABLES'; |
插入非法日期
|
1 |
INSERT INTO test_dates (id, date_value) VALUES (1, '2025-02-31'); |
报错ERROR 1292 (22007): Incorrect date value: '2025-02-31' for column 'date_value' at row 1
启用ALLOW_INVALID_DATES
|
1 |
SET sql_mode = 'ALLOW_INVALID_DATES'; |
插入非法日期
|
1 |
INSERT INTO test_dates (id, date_value) VALUES (1, '2025-02-31'); |
执行成功
|
1 |
INSERT INTO test_dates (id, date_value) VALUES (1, '2025-02-32'); |
执行成功，提示警告
查询插入结果
|
1 |
SELECT * FROM test_dates; |
|
1 2 3 4 5 6 |
+------+------------+ | id | date_value | +------+------------+ | 1 | 2025-02-31 | | 1 | 0000-00-00 | +------+------------+ |
未超出月份1-12范围，日期1-31范围，哪怕日期错误也会被存储，超出范围将被转换为'0000-00-00'。
建议在生产环境中禁用ALLOW_INVALID_DATES，如果需要更灵活的处理方式，可以考虑在应用层实现验证逻辑，而不是在数据库中放宽约束。
ANSI_QUOTES
ANSI_QUOTES参数在默认情况下是禁用的，用于控制SQL语法中双引号 (") 的行为。
禁用时，双引号会被当作字符串的开始和结束符号，不能用来引用标识符（如表名和列名）。
启用时，双引号会被当作标识符（如表名和列名）的引用符号，不能用来引用字符串。
演示示例
禁用ANSI_QUOTES
|
1 |
SET SESSION sql_mode = ''; |
使用双引号引用标识符创建表
|
1 2 3 4 |
CREATE TABLE "my_table2" ( "id" INT, "name" VARCHAR(100) ); |
报错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 '"my_table2" (
"id" INT,
"name" VARCHAR(100)
)' at line 1
正确创建方法
|
1 2 3 4 |
CREATE TABLE my_table2 ( id INT, name VARCHAR(100) ); |
使用双引号引用标识符插入数据
|
1 |
INSERT INTO "my_table2" ("id", "name") VALUES (1, 'zhangsan'); |
报错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 '"my_table2" ("id", "name") VALUES (1, 'zhangsan')' at line 1
使用双引号引用字符串插入数据
|
1 |
INSERT INTO my_table2 (id, name) VALUES (1, "zhangsan"); |
执行成功
使用单引号引用字符串插入数据
|
1 |
INSERT INTO my_table2 (id, name) VALUES (2, 'lisi'); |
执行成功
启用ANSI_QUOTES
|
1 |
SET SESSION sql_mode = 'ANSI_QUOTES'; |
使用双引号引用标识符创建表
|
1 2 3 4 |
CREATE TABLE "my_table3" ( "id" INT, "name" VARCHAR(100) ); |
执行成功
使用双引号引用字符串插入数据
|
1 |
INSERT INTO my_table3 (id, name) VALUES (1, "zhangsan"); |
报错ERROR 1054 (42S22): Unknown column 'zhangsan' in 'field list'
使用双引号引用标识符插入数据
|
1 |
INSERT INTO "my_table3" ("id", "name") VALUES (1, 'zhangsan'); |
执行成功
使用单引号引用字符串插入数据
|
1 |
INSERT INTO my_table3 (id, name) VALUES (2, 'lisi'); |
执行成功
建议在生产环境中，结合实际场景决定是否启用ANSI_QUOTES参数。
HIGH_NOT_PRECEDENCE
HIGH_NOT_PRECEDENCE参数在默认情况下是禁用的，影响NOT运算符与其他逻辑运算符（如 BETWEEN、AND、OR等）之间的优先级。
禁用时：NOT运算符的优先级低于BETWEEN运算符。
启用时：NOT运算符的优先级高于BETWEEN运算符。
演示示例
禁用HIGH_NOT_PRECEDENCE
|
1 |
SET sql_mode = ''; |
禁用时NOT a BETWEEN b AND c被解析为NOT (a BETWEEN b AND c)
执行以下查询
|
1 |
SELECT NOT 1 BETWEEN -5 AND 5; |
|
1 2 3 4 5 |
+------------------------+ | NOT 1 BETWEEN -5 AND 5 | +------------------------+ | 0 | +------------------------+ |
根据解析公式，这里的表达式NOT 1 BETWEEN -5 AND 5实际上被解释为NOT (1 BETWEEN -5 AND 5)。
1 BETWEEN -5 AND 5的结果是TRUE（因为1在-5和5之间），因此NOT TRUE就是FALSE，在MySQL中表示为0。
启用HIGH_NOT_PRECEDENCE
|
1 |
SET sql_mode = 'HIGH_NOT_PRECEDENCE'; |
启用时NOT a BETWEEN b AND c被解析为(NOT a) BETWEEN b AND c
执行以下查询
|
1 |
SELECT NOT 1 BETWEEN -5 AND 5; |
|
1 2 3 4 5 |
+------------------------+ | NOT 1 BETWEEN -5 AND 5 | +------------------------+ | 1 | +------------------------+ |
根据解析公式，这里的表达式NOT 1 BETWEEN -5 AND 5被解释为( NOT 1 ) BETWEEN -5 AND 5。
NOT 1的结果是FALSE（在MySQL中表示为0），0 BETWEEN -5 AND 5的结果是TRUE（因为 0 在 -5 和 5 之间），所以最终结果是0。
如果你是从MySQL 5.0.2之前的版本升级到新版本，应考虑是否启用HIGH_NOT_PRECEDENCE参数，启用后会旧老版本运算逻辑优先级保持一致，在生产环境中默认建议关闭HIGH_NOT_PRECEDENCE参数。
IGNORE_SPACE
IGNORE_SPACE参数在默认情况下是禁用的，该参数控制在函数调用时是否允许函数名和左括号之间有空格。启用该模式时，可以在函数名和左括号之间使用空格，而禁用时则不允许。
演示示例
禁用IGNORE_SPACE
|
1 |
SET sql_mode = ''; |
创建一个测试表
|
1 2 3 4 5 |
CREATE TABLE test_employees ( id INT, name VARCHAR(50), salary DECIMAL(10, 2) ); |
插入一些测试数据
|
1 2 3 4 |
INSERT INTO test_employees (id, name, salary) VALUES (1, 'zhangsan', 70000), (2, 'lisi', 50000), (3, 'wangwu', 60000); |
执行以下查询
|
1 |
SELECT COUNT (salary) FROM test_employees; |
报错ERROR 1630 (42000): FUNCTION test_db.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
这是因为，禁用IGNORE_SPACE情况下，COUNT和(之间的空格是不允许的。
正确查询方式如下
|
1 |
SELECT COUNT(salary) FROM test_employees; |
|
1 2 3 4 5 |
+----------------+ | COUNT (salary) | +----------------+ | 3 | +----------------+ |
执行成功
启用IGNORE_SPACE
|
1 |
SET sql_mode = 'IGNORE_SPACE'; |
执行以下查询
|
1 |
SELECT COUNT (salary) FROM test_employees; |
|
1 |
SELECT COUNT(salary) FROM test_employees; |
|
1 2 3 4 5 |
+----------------+ | COUNT (salary) | +----------------+ | 3 | +----------------+ |
两条sql均执行成功
启用IGNORE_SPACE参数，使函数名和左括号之间的空格成为可选项，但不影响没有空格的情况。因此无论IGNORE_SPACE是否启用，函数名和左括号之间没有空格时，都会被正常解析并执行。建议应遵循没有空格的sql写法，不建议生产环境中启用。
NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO参数在默认情况下是禁用的，它影响自增列行为的系统变量。这个参数的主要作用是控制在插入操作中，如何处理自增列的值为零（0）的情况。
注：在使用mysqldump进行逻辑备份时，如果存在自增主键列中有0的值，会存在数据不一致的风险。因此，mysqldump会自动启用NO_AUTO_VALUE_ON_ZERO模式，以避免在备份和还原过程中出现自增主键冲突。
启用时：当插入0到自增列时，系统不会将其视为自动生成的值，而是将0作为一个普通的插入值。
禁用时：当插入0到自增列时，系统会将其视为请求生成一个新的自增值，而不是使用0。
演示示例
禁用NO_AUTO_VALUE_ON_ZERO
|
1 |
SET sql_mode = ''; |
创建一个测试表，它包含一个自增列id和一个普通的列name。
|
1 2 3 4 |
CREATE TABLE test_zero_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); |
插入数据
|
1 |
INSERT INTO test_zero_table (id, name) VALUES (0, 'zhangsan'); |
|
1 |
INSERT INTO test_zero_table (id, name) VALUES (0, 'lisi'); |
两条sql执行成功，查询数据
|
1 |
SELECT * FROM test_zero_table; |
|
1 2 3 4 5 6 |
+----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ |
查询后可以看到，两个0被自动转换为下一个可用的自增值（1和2）。
启用NO_AUTO_VALUE_ON_ZERO
|
1 |
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; |
插入数据
|
1 |
INSERT INTO test_zero_table (id, name) VALUES (0, 'wangwu'); |
执行成功
|
1 |
INSERT INTO test_zero_table (id, name) VALUES (0, 'zhaoliu'); |
报错ERROR 1062 (23000): Duplicate entry '0' for key 'test_zero_table.PRIMARY'
查询数据
|
1 2 3 4 5 6 7 |
+----+----------+ | id | name | +----+----------+ | 0 | wangwu | | 1 | zhangsan | | 2 | lisi | +----+----------+ |
在启用NO_AUTO_VALUE_ON_ZERO时，插入0不会生成自增值，0会被直接插入到自增列中。但是如果已经插入了0，第二次再插入0将会导致主键冲突，出现 "Duplicate entry" 的错误。
在生产环境中，建议根据具体需求决定是否开启NO_AUTO_VALUE_ON_ZERO，建议保持禁用状态。
NO_BACKSLASH_ESCAPES
NO_BACKSLASH_ESCAPES参数在默认情况下是禁用的，它用于控制反斜杠(\)是否被视为转义字符。
启用时，反斜杠会作为普通字符，不会被视为转义字符。
禁用时，反斜杠会被用作转义字符。
演示示例
禁用NO_BACKSLASH_ESCAPES
|
1 |
SET sql_mode = ''; |
添加反斜杠查询
|
1 |
SELECT 'Hello\World'; |
|
1 2 3 4 5 |
+------------+ | HelloWorld | +------------+ | HelloWorld | +------------+ |
反斜杠作为转义字符，因此输出结果中没有反斜杠存在。
启用NO_BACKSLASH_ESCAPES
|
1 |
SET sql_mode = 'NO_BACKSLASH_ESCAPES'; |
添加反斜杠查询
|
1 |
SELECT 'Hello\World'; |
|
1 2 3 4 5 |
+-------------+ | Hello\World | +-------------+ | Hello\World | +-------------+ |
反斜杠不会转义，因此输出结果中反斜杠仍然存在。
在生产环境中，建议禁用NO_BACKSLASH_ESCAPES，以保持反斜杠作为转义字符的标准行为，确保字符串处理的兼容性和安全性。
NO_DIR_IN_CREATE
NO_DIR_IN_CREATE参数在默认情况下禁用的，主要用于控制在创建表时是否忽略INDEX DIRECTORY和DATA DIRECTORY指令。这一模式对于复制环境（如主从复制）特别有用，因为在从服务器上通常不需要指定这些目录。
启用时，在创建表时，MySQL会忽略所有的INDEX DIRECTORY和DATA DIRECTORY指令。这意味着无论你在创建表时指定了什么目录，MySQL都会使用默认的存储目录。
禁用时，在创建表时，MySQL将考虑并使用你指定的INDEX DIRECTORY和DATA DIRECTORY。这允许更灵活的存储管理，可以将数据和索引存储在不同的目录中。
在生产环境中，建议使用默认的表存储设置，而不是指定特定的目录。
NO_UNSIGNED_SUBTRACTION
NO_UNSIGNED_SUBTRACTION参数在默认情况下禁用的，用于控制无符号整数之间的减法操作行为。如果在整数之间做减法操作，其中一个类型为UNSIGNED，结果默认为无符号结果，但是该值为负，将报错。
演示示例
禁用NO_UNSIGNED_SUBTRACTION
|
1 |
SET sql_mode = ''; |
执行查询
|
1 |
SELECT CAST(0 AS UNSIGNED) - 1; |
报错ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
启用NO_UNSIGNED_SUBTRACTION
|
1 |
SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; |
执行查询
|
1 |
SELECT CAST(0 AS UNSIGNED) - 1; |
|
1 2 3 4 5 |
+-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+ |
启用后，无论操作数是有符号还是无符号，减法结果都将视为有符号整数。即使结果为负，MySQL也会返回负值，而不是抛出错误。
如果将减法结果用于更新一个无符号整数列，在启用NO_UNSIGNED_SUBTRACTION的情况下，如果结果为负，则会将其截断为0；如果启用严格模式，则会抛出错误，保持列不变。
在生产环境中，是否启用NO_UNSIGNED_SUBTRACTION应根据你的具体应用场景、数据处理需求和稳定性要求来决定。建议在做出调整前做好充分的评估，默认情况下，建议保持禁用状态。
PAD_CHAR_TO_FULL_LENGTH
PAD_CHAR_TO_FULL_LENGTH参数在默认情况下禁用的，它用于控制在检索CHAR类型列时是否保留尾部空格。
演示示例
禁用PAD_CHAR_TO_FULL_LENGTH
|
1 |
SET sql_mode = ''; |
创建测试表
|
1 |
CREATE TABLE t1 (c1 CHAR(10)); |
插入测试数据
|
1 |
INSERT INTO t1 (c1) VALUES('xy'); |
查询
|
1 |
SELECT c1, CHAR_LENGTH(c1) FROM t1; |
|
1 2 3 4 5 |
+------+-----------------+ | c1 | CHAR_LENGTH(c1) | +------+-----------------+ | xy | 2 | +------+-----------------+ |
因为xy的长度为2，所以CHAR_LENGTH(c1)返回2。
启用PAD_CHAR_TO_FULL_LENGTH
|
1 |
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; |
查询
|
1 |
SELECT c1, CHAR_LENGTH(c1) FROM t1; |
|
1 2 3 4 5 |
+------------+-----------------+ | c1 | CHAR_LENGTH(c1) | +------------+-----------------+ | xy | 10 | +------------+-----------------+ |
'xy'会被填充为'xy '（后面有8个空格），因此CHAR_LENGTH(c1)返回10。
PAD_CHAR_TO_FULL_LENGTH已弃用。预计将在未来的MySQL版本中删除，见MySQL8.4官方文档：https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sqlmode_pad_char_to_full_length
PIPES_AS_CONCAT
PIPES_AS_CONCAT参数在默认情况下是禁用的，它用于控制SQL查询中管道符”||“操作符的行为。
启用时，||操作符被视为字符串连接操作符（与CONCAT()作用相同）。
禁用时，||操作符被视为逻辑OR操作符。
演示示例
启用PIPES_AS_CONCAT
|
1 |
SET sql_mode = 'PIPES_AS_CONCAT'; |
|
1 |
select 'Hello'||'World'; |
|
1 2 3 4 5 |
+------------------+ | 'Hello'||'World' | +------------------+ | HelloWorld | +------------------+ |
等同于select CONCAT('Hello' , 'World');
禁用PIPES_AS_CONCAT
|
1 |
SET sql_mode = ''; |
|
1 |
select 'Hello'||'World'; |
|
1 2 3 4 5 |
+------------------+ | 'Hello'||'World' | +------------------+ | 0 | +------------------+ |
|
1 |
select 'Hello'||'123'; |
|
1 2 3 4 5 |
+----------------+ | 'Hello'||'123' | +----------------+ | 1 | +----------------+ |
禁用时，||将被视为逻辑运算符，而不是字符串连接符。因此，结果将是0或1，而不是'HelloWorld'。
在生产环境中，建议谨慎启用PIPES_AS_CONCAT。更常见的做法是使用CONCAT函数进行字符串连接，以提高代码的可读性和一致性。
REAL_AS_FLOAT
REAL_AS_FLOAT参数在默认情况下是禁用的，它用于控制REAL数据类型的默认行为。
禁用时，REAL数据类型默认被视为DOUBLE。
启用时，REAL数据类型将被视为FLOAT。
演示示例
禁用REAL_AS_FLOAT参数
|
1 |
SET sql_mode = ''; |
创建测试表
|
1 |
CREATE TABLE ty ( w1 real ); |
查看数据类型
|
1 |
SHOW CREATE TABLE ty\G |
|
1 2 3 4 5 6 |
*************************** 1. row *************************** Table: ty Create Table: CREATE TABLE `ty` ( `w1` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec) |
可以看到real被视为double
启用REAL_AS_FLOAT参数
|
1 |
SET sql_mode = 'REAL_AS_FLOAT'; |
创建测试表
|
1 |
CREATE TABLE ty2 ( w1 real ); |
查看数据类型
|
1 |
SHOW CREATE TABLE ty2\G |
|
1 2 3 4 5 6 |
*************************** 1. row *************************** Table: ty2 Create Table: CREATE TABLE `ty2` ( `w1` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec) |
可以看到，启用REAL_AS_FLOAT参数后，real被视为float。
在生产环境中，建议根据应用的精度需求和存储考虑，谨慎选择是否启用REAL_AS_FLOAT参数，如无特殊需求，建议保持禁用状态。
STRICT_ALL_TABLES
STRICT_ALL_TABLES参数与STRICT_TRANS_TABLES参数有些类似，关键区别在于事务表（如InnoDB）和非事务表（如MyISAM）的处理。启用时，会对所有表（包括事务表和非事务表）中的插入和更新操作进行严格检查。而STRICT_TRANS_TABLES仅对事务表中的插入和更新操作进行严格检查。
演示示例参见STRICT_TRANS_TABLES参数。
总体来说，事务表在实际应用中更为常见，因为它们能够更好地满足对数据一致性、安全性和可靠性的需求。而非事务表则在一些特定的、对数据一致性要求不高的场景中仍然可以使用，但其使用频率相对较低。
所以默认情况下STRICT_TRANS_TABLES参数启用，而STRICT_ALL_TABLES禁用。
TIME_TRUNCATE_FRACTIONAL
TIME_TRUNCATE_FRACTIONAL参数在默认情况下是禁用的，它用于控制时间类型值的小数部分，禁用时小数部分四舍五入，启用时会发生截断。
演示示例
禁用TIME_TRUNCATE_FRACTIONAL
|
1 |
SET sql_mode = ''; |
创建测试表
|
1 |
CREATE TABLE t (id INT, tval TIME(1)); |
插入测试数据
|
1 |
INSERT INTO t (id, tval) VALUES(1, 1.55); |
查询结果
|
1 |
SELECT id, tval FROM t ORDER BY id; |
|
1 2 3 4 5 |
+------+------------+ | id | tval | +------+------------+ | 1 | 00:00:01.6 | +------+------------+ |
可以看到小数部分被四舍五入了
启用TIME_TRUNCATE_FRACTIONAL
|
1 |
SET sql_mode = 'TIME_TRUNCATE_FRACTIONAL'; |
插入测试数据
|
1 |
INSERT INTO t (id, tval) VALUES(2, 1.55); |
查询结果
|
1 |
SELECT id, tval FROM t ORDER BY id; |
|
1 2 3 4 5 6 |
+------+------------+ | id | tval | +------+------------+ | 1 | 00:00:01.6 | | 2 | 00:00:01.5 | +------+------------+ |
可以看到小数部分没有被四舍五入，而是被截断了。
在生产环境中，建议禁用TIME_TRUNCATE_FRACTIONAL，应在明确需要控制时间值的截断行为时才启用它。
附，MySQL my.cnf配置文件生成器：https://dbcnf.wlnmp.com/
原文链接：MySQL sql_mode参数介绍，转载请注明来源！
