详细分析Mysql中的SQL_MODE基本知识（附Demo讲解）_mysql sqlmode

前言

了解Mysql内部的机制有助于辅助开发以及形成整体的架构思维

对于基本的命令行以及优化推荐阅读：

1. 数据库中增删改常用语法语句（全）
2. Mysql优化高级篇（全）

1. 基本知识

SQL_MODE 是 MySQL 中用于设置 SQL 语法和行为的系统变量

控制 MySQL 的 SQL 解析和执行的方式，使其与 SQL 标准或其他数据库系统的行为一致
通过设置 SQL_MODE，可以改变 MySQL 处理特定 SQL 操作的方式

• MySQL 5.7
  默认 SQL_MODE 包括：ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION

• MySQL 8.0
  默认 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 8.0 中没有太大变化，但 NO_AUTO_CREATE_USER 被移除了，因为 MySQL 8.0 取消了自动创建用户的特性

2. Demo讲解

常见的 SQL_MODE 设置和示例，以帮助理解其影响

2.1 ONLY_FULL_GROUP_BY

控制对于 GROUP BY 子句的处理方式
在默认情况下，MySQL 允许在 SELECT 查询中使用 GROUP BY 子句时，对于不在 GROUP BY 子句中的非聚合列进行隐式处理，可能导致意外的结果

在 ONLY_FULL_GROUP_BY 关闭的情况下，MySQL可能会随意选择一行来代表每个分组，而不是严格按照 SQL 标准进行操作，如下：

 this is incompatible with sql_mode=only_full_group_by
1

截图如下：

对此设置为ONLY_FULL_GROUP_BY 模式

-- 开启 ONLY_FULL_GROUP_BY 模式
SET sql_mode = 'ONLY_FULL_GROUP_BY';


-- 查询每个学生的平均成绩
SELECT name, AVG(salary) AS avg_salary FROM employees GROUP BY name;
1
2
3
4
5
6

2.2 STRICT_TRANS_TABLES

在 STRICT_TRANS_TABLES 模式下，如果插入的数据有问题（如超出字段长度或类型不匹配），MySQL 会报错并回滚事务

-- 设置 SQL_MODE 为 STRICT_TRANS_TABLES
SET sql_mode = 'STRICT_TRANS_TABLES';

-- 创建表
CREATE TABLE demo_strict (
    id INT,
    name VARCHAR(5)
);

-- 尝试插入超长数据
INSERT INTO demo_strict VALUES (1, 'TooLongName');
1
2
3
4
5
6
7
8
9
10
11

执行结果下：

INSERT INTO demo_strict VALUES (1, 'TooLongName');
[Err] 1406 - Data too long for column 'name' at row 1
1
2

截图如下：

2.3 NO_ZERO_IN_DATE

-- 设置 SQL_MODE 为 NO_ZERO_IN_DATE, NO_ZERO_DATE
SET sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE';

-- 创建表
CREATE TABLE demo_date (
    id INT,
    date_field DATE
);

-- 尝试插入无效日期
INSERT INTO demo_date VALUES (1, '2020-00-00');
1
2
3
4
5
6
7
8
9
10
11

但在执行的过程中是可以成功的，但查询的时候日期显示未0000-00-00
为了避免这种插入无效日期，应该更改为：

-- 确保启用严格模式和日期模式
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';

-- 创建表
CREATE TABLE demo_date (
    id INT,
    date_field DATE
);

-- 尝试插入无效日期
INSERT INTO demo_date VALUES (1, '2020-00-00');
1
2
3
4
5
6
7
8
9
10
11

最终结果如下：

[Err] 1292 - Incorrect date value: '2020-00-00' for column 'date_field' at row 1
1

截图如下：

确保输出包含 STRICT_TRANS_TABLES 和日期相关的模式：

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,...
1

2.4 NO_ENGINE_SUBSTITUTION

指定的存储引擎不可用，则会报错，而不是使用默认的存储引擎

-- 设置 SQL_MODE 为 NO_ENGINE_SUBSTITUTION
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';

-- 尝试创建不存在的存储引擎的表
CREATE TABLE demo_engine (
    id INT
) ENGINE=NON_EXISTENT_ENGINE;
1
2
3
4
5
6
7

报错结果如下：[Err] 1286 - Unknown storage engine 'NON_EXISTENT_ENGINE'

截图如下：

2.5 ANSI_QUOTES

双引号用于标识符，而不是字符串

-- 设置 SQL_MODE 为 ANSI_QUOTES
SET sql_mode = 'ANSI_QUOTES';

-- 尝试使用双引号作为标识符
CREATE TABLE "demo_quotes" (
    "id" INT,
    "name" VARCHAR(50)
);

-- 插入数据
INSERT INTO "demo_quotes" ("id", "name") VALUES (1, 'John Doe');
1
2
3
4
5
6
7
8
9
10
11

截图如下：

成功插入