MySQL查詢列必須和group by欄位一致嗎？

  • 2021 年 12 月 23 日
  • 筆記

@

目錄

MySQL版本：8.0.27

場景：查詢各部門薪水最高的員工。

CREATE TABLE `employee`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `dept` int NOT NULL COMMENT '部門',
  `user` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '員工',
  `salary` int NULL DEFAULT NULL COMMENT '薪水',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否刪除',
  `remark` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '備註',
  `modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改時間',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '員工' ROW_FORMAT = Dynamic;

INSERT INTO `employee` VALUES (1, 1, '張三', 1000, 0, NULL, '2021-12-23 09:20:19.606');
INSERT INTO `employee` VALUES (2, 1, '李四', 1500, 0, NULL, '2021-12-23 09:20:21.679');
INSERT INTO `employee` VALUES (3, 1, '王五', 2000, 0, NULL, '2021-12-23 09:20:23.371');
INSERT INTO `employee` VALUES (4, 2, '趙六', 1000, 0, NULL, '2021-12-23 09:21:59.373');
INSERT INTO `employee` VALUES (5, 2, '孫七', 1500, 0, NULL, '2021-12-23 09:22:15.000');

SELECT * FROM employee;
在这里插入图片描述

方法一：

SELECT
	t1.* 
FROM
	employee t1
	LEFT JOIN employee t2 ON t2.dept = t1.dept AND t1.salary < t2.salary 
WHERE
	t2.salary IS NULL;

在这里插入图片描述

方法二：

SELECT
	* 
FROM
	( SELECT * FROM `employee` ORDER BY dept, salary DESC LIMIT 1000 ) t 
GROUP BY
	dept;

（不加limit可能會失效）
在这里插入图片描述
看起來結果是一樣的，但第二種其實會有問題的。

MySQL group by是如何決定哪一條數據留下的？

MySQL通過sql_mode來提供SQL語句的合法性檢查，
在默認情況下，MySQL允許查詢列target list中出現除了group by column、聚集函數等以外的表達式。
但是，那些不參與group by的欄位具體會返回哪條數據的值在MySQL中是處於未定義規則的狀態，
MySQL不承諾一定會返回哪條數據。

分組前的數據：

SELECT * FROM employee ORDER BY dept, salary DESC LIMIT 1000;
在这里插入图片描述
看起來方法二返回的是每個分組中的第一條的數據，
但實際上還會與存儲引擎、物理位置、索引等有關，
如果是InnoDB的話，取決於在B+Tree上命中的第一條索引，
這裡不展開說明，畢竟不是安全的用法，
有的時候可能返回的結果並不是我們想要的。
關於B+Tree，可以看下這篇文章：
通過B+Tree平衡多叉樹理解InnoDB引擎的聚集和非聚集索引
所以對於target list中出現的不明確的列，MySQL是不確定哪一條數據留下的。

對於語法限制比較嚴格的資料庫，都不支援target list中出現語義不明確的列，
MySQL中提供了一個修正的sql_mode，ONLY_FULL_GROUP_BY。

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

再執行方法二的SQL就被拒絕了：

SELECT
	* 
FROM
	( SELECT * FROM `employee` ORDER BY dept, salary DESC LIMIT 1000 ) t 
GROUP BY
	dept
> 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> 時間: 0s

'only_full_group_by'模式下MySQL會對target list和group by column中的基礎列、表達式、別名列進行嚴格匹配。

那麼target list和group by column不匹配就一定不能執行嗎？

我們看下另外一條SQL：

# 訂單
CREATE TABLE `order`  (
  `order_id` int NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
  `order_amount` int NULL DEFAULT NULL COMMENT '訂單金額',
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '訂單' ROW_FORMAT = DYNAMIC;

INSERT INTO `order` VALUES (1, 100);
INSERT INTO `order` VALUES (2, 103);
INSERT INTO `order` VALUES (3, 100);

# 訂單明細
CREATE TABLE `order_detail`  (
  `order_detail_id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `order_id` int NOT NULL COMMENT '訂單ID',
  `goods` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名稱',
  `goods_amount` int NOT NULL COMMENT '商品金額',
  PRIMARY KEY (`order_detail_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '訂單明細' ROW_FORMAT = DYNAMIC;

INSERT INTO `order_detail` VALUES (1, 1, '蘋果', 10);
INSERT INTO `order_detail` VALUES (2, 1, '橙子', 20);
INSERT INTO `order_detail` VALUES (3, 1, '香蕉', 70);
INSERT INTO `order_detail` VALUES (4, 2, '橘子', 50);
INSERT INTO `order_detail` VALUES (5, 2, '菠蘿', 53);

查詢訂單中所有商品

SELECT
	t1.order_id,
	t1.order_amount,
	GROUP_CONCAT( t2.goods, t2.goods_amount ) 
FROM
	`order` t1
	LEFT JOIN order_detail t2 ON t2.order_id = t1.order_id 
GROUP BY
	t1.order_id;

在这里插入图片描述
這條SQL的target list和group by column並不是嚴格匹配的，但是也可以執行，
注意
t1.order_id是訂單表的主鍵。

所以在'only_full_group_by'模式下，如果MySQL可以確定target list中所有列的返回值，
那麼，即使target list和group by column中的基礎列、表達式、別名列等不嚴格匹配，
MySQL也會認為它的語義是明確的，因此該條語句可以順利通過。

Tags:

VirMach 便宜 VPS