石景山郑州阳网站建设,wordpress 文章页幻灯片,西安企业建站公司,东莞房价2024年房价走势目录 前言1. 基本知识2. Demo讲解2.1 ONLY_FULL_GROUP_BY2.2 STRICT_TRANS_TABLES2.3 NO_ZERO_IN_DATE2.4 NO_ENGINE_SUBSTITUTION2.5 ANSI_QUOTES 前言
了解Mysql内部的机制有助于辅助开发以及形成整体的架构思维
对于基本的命令行以及优化推荐阅读#xff1a;
数据库中增… 目录 前言1. 基本知识2. Demo讲解2.1 ONLY_FULL_GROUP_BY2.2 STRICT_TRANS_TABLES2.3 NO_ZERO_IN_DATE2.4 NO_ENGINE_SUBSTITUTION2.5 ANSI_QUOTES 前言
了解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_modeonly_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
) ENGINENON_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);截图如下 成功插入
