数据表结构和数据如下:
- CREATE TABLE `commun_message_chat_single` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `chat_id` int(11) DEFAULT '0' COMMENT '会话 id',
- `from_id` varchar(11) DEFAULT NULL COMMENT '发送者 用户 id',
- `to_id` varchar(11) DEFAULT NULL COMMENT '接收者 用户 id',
- `content` text COMMENT '消息内容',
- `type` tinyint(1) DEFAULT '1' COMMENT '消息类型 1: 文字 2: 图片 3: 文件',
- `send_time` datetime DEFAULT NULL COMMENT '消息发送时间',
- PRIMARY KEY (`id`) USING BTREE
- ) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='单聊 聊天记录';
-- 插入数据的 sql
- INSERT INTO `commun_message_chat_single` VALUES (60, 10, '11', 'md_1', '123', 1, '2019-10-16 08:25:50');
- INSERT INTO `commun_message_chat_single` VALUES (61, 10, '11', 'md_1', '456', 1, '2019-10-28 08:25:59');
- INSERT INTO `commun_message_chat_single` VALUES (62, 10, '11', 'md_2', '789', 1, '2019-10-01 08:26:21');
- INSERT INTO `commun_message_chat_single` VALUES (63, 10, '11', 'md_2', '哈哈哈', 1, '2019-10-27 08:26:34');
- INSERT INTO `commun_message_chat_single` VALUES (64, 10, '11', 'md_2', '测试测试', 1, '2019-10-10 08:28:27');
目前数据表所有数据如下:
- MySQL> select * from commun_message_chat_single where from_id = '11';
- +----+---------+---------+-------+--------------+------+---------------------+
- | id | chat_id | from_id | to_id | content | type | send_time |
- +----+---------+---------+-------+--------------+------+---------------------+
- | 60 | 10 | 11 | md_1 | 123 | 1 | 2019-10-16 08:25:50 |
- | 61 | 10 | 11 | md_1 | 456 | 1 | 2019-10-28 08:25:59 |
- | 62 | 10 | 11 | md_2 | 789 | 1 | 2019-10-01 08:26:21 |
| 63 | 10 | 11 | md_2 | 哈哈哈 | 1 | 2019-10-27 08:26:34 |
| 64 | 10 | 11 | md_2 | 测试测试 | 1 | 2019-10-10 08:28:27 |
- +----+---------+---------+-------+--------------+------+---------------------+
- 5 rows in set (0.00 sec)
需求: 查询 from_id 为 11 的数据 并且 和 每一个 to_id 按照时间排序显示最新的一条数据 (也就是显示: to_id 是 md_1 的, 按照时间排序 id 为 61 的符合结果; to_id 是 md_2 的, 按照时间排序 id 为 63 的符合结果)
符合该需求的 2 条数据如下:
- +----+---------+---------+-------+-----------+------+---------------------+
- | id | chat_id | from_id | to_id | content | type | send_time |
- +----+---------+---------+-------+-----------+------+---------------------+
- | 61 | 10 | 11 | md_1 | 456 | 1 | 2019-10-28 08:25:59 |
| 63 | 10 | 11 | md_2 | 哈哈哈 | 1 | 2019-10-27 08:26:34 |
+----+---------+---------+-------+-----------+------+---------------------+
实现该需求的 sql 语句如下 (利用 sql 中的子查询):
- SELECT * FROM (SELECT * FROM commun_message_chat_single WHERE from_id = '11' ORDER BY send_time DESC ) as
- temp_table GROUP BY temp_table.to_id;
-- 大概解释下该条 sql 语句: 括号内的子查询是查询 from_id 为 11 的数据并且按照 send_time 从高到低排序, 这里的子查询的结果会生成一个临时表, 临时表这里取名为 temp_table, 然后外部查询将 temp_table 的结果进行分组.
mysql5.7 版本以下执行结果如下 (只在 5.5 和 5.6 版本试过):
- +----+---------+---------+-------+-----------+------+---------------------+
- | id | chat_id | from_id | to_id | content | type | send_time |
- +----+---------+---------+-------+-----------+------+---------------------+
- | 61 | 10 | 11 | md_1 | 456 | 1 | 2019-10-28 08:25:59 |
| 63 | 10 | 11 | md_2 | 哈哈哈 | 1 | 2019-10-27 08:26:34 |
- +----+---------+---------+-------+-----------+------+---------------------+
- 2 rows in set (0.00 sec)
mysql5.7 版本执行结果如下:
- +----+---------+---------+-------+---------+------+---------------------+
- | id | chat_id | from_id | to_id | content | type | send_time |
- +----+---------+---------+-------+---------+------+---------------------+
- | 60 | 10 | 11 | md_1 | 123 | 1 | 2019-10-16 08:25:50 |
- | 62 | 10 | 11 | md_2 | 789 | 1 | 2019-10-01 08:26:21 |
- +----+---------+---------+-------+---------+------+---------------------+
- 2 rows in set (0.00 sec)
what? 为啥 5.7 以下的版本是我们想要的结果, 而 5.7 版本的执行结果居然不是我们期待的结果! 高版本居然执行的结果不正确..
为什么 mysql5.7 和 5.7 以下的版本会有不同的结果呢?
可以分别查看一下这条 sql 语句在两个不同版本数据库的 sql 执行计划:
MySQL 5.7.21:
mysql5.5.62 和 5.6.44:
对比可以发现 5.7 版本的 MySQL 在执行这条 sql 语句的时候缺少了一个 derived 的操作, 通过查阅相关资料了解到 mysql5.7 对子查询进行了优化, 认为子查询中的 order by 可以进行忽略, 只要 Derived table 里不包含如下条件就可以进行优化:
- ,UNION clause
- ,GROUP BY
- ,DISTINCT
- ,Aggregation
- ,LIMIT or OFFSET
看到了吧, 如果要在 mysql5.7 中实现先排序后分组, 这里可以加个 limit, 不过你的 limit 要足够大
mysql5.7 解决办法如下:
- SELECT * FROM (SELECT * FROM commun_message_chat_single WHERE from_id = '11' ORDER BY send_time DESC LIMIT
- 10000 ) as temp_table GROUP BY temp_table.to_id;
mysql8.0 及以上版本没试过, 不过应该和 5.7 是一样的效果, 都进行了优化.
PS: 子查询不是 MySQL 独有的, sqlserver 等数据库也可以使用子查询