MySQL 5.7 group by

从MySQL 5.6 升级到 MySQL 5.7,我相信大家或多或少都遇到了一点 group by 的问题,也许是 `this is incompatible with sql_mode=only_full_group_by` 的报错,也许是 group by 查询出来的结果莫名其妙的和之前版本不一样。

在开始之前我们先来造一些数据:

SET NAMES utf8mb4;
CREATE TABLE `books` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`press` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `books` (`id`, `name`, `press`) VALUES
(1, '太宰治作品精选集:潘多拉之匣', '上海译文出版社'),
(2, '霜夜', '广东花城出版社');
CREATE TABLE `prices` (
`book_id` int(11) NOT NULL,
`seller` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`price` decimal(10,2) NOT NULL,
KEY `book_id` (`book_id`),
CONSTRAINT `prices_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `prices` (`book_id`, `seller`, `price`) VALUES
(1, '亚马逊', 28.40),
(1, '新华文轩网络书店', 26.30),
(2, '亚马逊', 26.20),
(2, '新华文轩网络书店', 24.20),
(2, '中关村图书大厦专营店', 26.30),
(2, '王府井书店', 26.80);

现在我们需要查出每本书最便宜的价格和对应的卖家。

MySQL 5.6 之前的版本,我们可以这样写查询语句:

select id, name, press, seller, price from
(select id, name, press, seller, price from books join prices on (prices.book_id = books.id) order by id asc, prices.price asc)
as t group by id

但是 MySQL 5.7 直接报错了:

查询出错 (1055): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.seller' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

因为从 MySQL 5.7.5 开始 sql_mode 默认开启了 ‘ONLY_FULL_GROUP_BY’,我们关掉 sql_model, 然后再执行上面的查询语句。

虽然没有报错,但是结果也和我们预想的不一样。这是为什么呢?我们用 explain 和 show warning 来查一下 MySQL 的执行计划。

/* select#1 */ select `db`.`books`.`id` AS `id`,`db`.`books`.`name` AS `name`,`db`.`books`.`press` AS `press`,`db`.`prices`.`seller` AS `seller`,`db`.`prices`.`price` AS `price` from `db`.`books` join `db`.`prices` where (`db`.`prices`.`book_id` = `db`.`books`.`id`) group by `db`.`books`.`id`

原来 MySQL 在执行的时候会自动忽略子查询里面的 order by 语句。那要怎么写查询语句呢,仔细查询文档发现 group_concat 里面可以写 order by,然后我们再用 substring_index 取出用 , 分割的第一个字符串。

select id, name, press, substring_index(group_concat(seller order by price), ',', 1) as seller, substring_index(group_concat(price order by price), ',', 1) as price
from books join prices on (prices.book_id = books.id) group by id

问题解决了,所以 MySQL 5.7 虽好,还是要谨慎升级,特别要注意检查一下以前代码里面的 group by 语句。


参考:

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

 603 total views,  1 views today

Leave a Reply

Your email address will not be published. Required fields are marked *