前言
相识Mysql内部的机制有助于辅助开发以及形成整体的架构思维
对于基本的下令行以及优化推荐阅读:
- 数据库中增编削常用语法语句(全)
- 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
复制代码 截图如下:
对此设置为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;
复制代码
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');
复制代码 实行结果下:
- INSERT INTO demo_strict VALUES (1, 'TooLongName');
- [Err] 1406 - Data too long for column 'name' at row 1
复制代码 截图如下:
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');
复制代码 但在实行的过程中是可以成功的,但查询的时候日期表现未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');
复制代码 最终结果如下:
- [Err] 1292 - Incorrect date value: '2020-00-00' for column 'date_field' at row 1
复制代码 截图如下:
确保输出包罗 STRICT_TRANS_TABLES 和日期相关的模式:
- STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,...
复制代码 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;
复制代码 报错结果如下:[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');
复制代码 截图如下:
成功插入
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |