MYSQL(五)

服务器
  目录
  1. 1. 运行模式
    1. 1.1. 模式说明
      1. 1.1.1. 模式选项
      2. 1.1.2. 常用模式
    2. 1.2. 对比分析
    3. 1.3. ONLY_FULL_GROUP_BY
      1. 1.3.1. 问题分析
      2. 1.3.2. any_value
      3. 1.3.3. 聚合函数
      4. 1.3.4. primary/unique

mysql学习笔记整理-运行模式

运行模式

MYSQL 运行时可以使用不同的模式，这是很多语言中都有的特性比如 JS/PHP 等。在 MYSQL5.7 前 SQL 运行在宽松模式，会以开放不严谨的方式运行。
在 5.7 后默认运行模式较为严格，这会造成在升级数据库时，数据库的操作出现问题。所以有必要理解 MYSQL 的运行模式机制。

模式说明

查看当前运行模式

1
SELECT @@sql_mode

模式选项

不同运行模式即对不同模式选项的配置，下面是常用的模式选项

选项 说明
ONLY_FULL_GROUP_BY 结果中的字段需要在 GROUP BY 中出现
STRICT_TRANS_TABLES 如果一个值不能插入到一个事务表中，则中断当前的操作
NO_ZERO_IN_DATE 不能插入为 0 的日期和月份
NO_ZERO_DATE 不能添加 0000-00-00 格式的日期
ERROR_FOR_DIVISION_BY_ZERO 除数不能为零，禁止该模式后插入的结果为 NULL

常用模式

常用模式是系统将不同的模式选项过行的组合

模式 说明
ANSI 宽松模式：对长度超过字段定义等错误进行截取等操作。报 WARNING 警告错误
TRADITIONAL 严格模式：对数据进行严格校验。事务处理中会进行事务回滚操作。非事务时，发生错误时就立即报错终止，会造成有部分数据插入。

设置当前会话为宽松模式

1
2
3
set session sql_mode=ANSI;
# 或
SET sql_mode = ANSI

设置全局模式限制

1
SET GLOBAL sql_mode='';

设置为严格模式

1
set session sql_mode=TRADITIONAL;

对比分析

我们来看下在不同模式下的表现，首先宽松模式可以插入’0000-00-00’的日期格式

1
2
3
SET sql_mode = ANSI

INSERT INTO stu SET birthday  = '0000-00-00'

当使用严格模式时将不可以插入

1
2
3
set sql_mode=TRADITIONAL;

INSERT INTO stu SET birthday  = '0000-00-00'

内容超过字段长度时宽松模式会过行截断并可以正常插入，下面的字段 rank 使用的是 smallint

1
2
3
SET sql_mode = ANSI

INSERT INTO article SET rank =1000000;

当改为严格模式时不能插入数据会报错

1
2
3
set sql_mode=TRADITIONAL;

INSERT INTO article SET rank =1000000;

ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY 要求 SELECT 中的字段是在与 GROUP BY 中使用的字段

  • 如果 GROUP BY 是主键或 UNIQUE NOT NULL 时可以在 SELECT 中列出其他字段
  • 使用 max/min/avg/count 等聚合函数时不受 ONLY_FULL_GROUP_BY 模式影响

问题分析

下面获取男生和女生的人

  • 下面的班级编号 class_id 是男/女两个组中的，这个值是不确定的也是无意义的
  • 使用 ONLY_FULL_GROUP_BY 模式后将报错
1
SELECT class_id,count(*) AS c FROM stu u GROUP BY sex

去除 ONLY_FULL_GROUP_BY 模式后可以读到 class_id ，但针对这个 SQL 来讲 class_id 结果是不确定的也是无意义的

1
2
3
SELECT @@sql_mode

SELECT class_id,count(*) AS c FROM stu u GROUP BY sex

any_value

有些情况下确实要取到非 GROUP BY 中的字段，使用 any_value 函数可以从组中读取第一个值，解决使用 ONLY_FULL_GROUP_BY 报错的问题
any_value 会读取使用 group by 分组后的每组中第一个数据

1
2
3
SET sql_mode = 'ONLY_FULL_GROUP_BY,TRADITIONAL'

SELECT any_value(class_id),count(*) AS c FROM stu u GROUP BY sex;

聚合函数

在 SELECT 中使用 max/min/avg/count 等聚合函数时不受 ONLY_FULL_GROUP_BY 模式影响

1
2
3
SET sql_mode = 'ONLY_FULL_GROUP_BY,TRADITIONAL'

SELECT max(class_id),count(*) AS c FROM stu u GROUP BY sex

primary/unique

GROUP BY 使用主键或 unique not null 字段时，不受 ONLY_FULL_GROUP_BY 的约束。下面是获取每班的人数的示例

1
2
3
4
SELECT c.id,c.cname,count(*) FROM stu u
INNER JOIN class c
ON c.id = u.class_id
GROUP BY c.id;

  MYSQL笔记