mysql select后边的column不在group by里会怎样

最新推荐文章于 2023-12-15 13:52:58 发布

领取

最新推荐文章于 2023-12-15 13:52:58 发布

阅读量3.4k

点赞数

本文链接：https://blog.csdn.net/qq125537082/article/details/52319059

版权

mysql 5.7 的manual：

http://dev.mysql.com/doc/refman/5.7/en/counting-rows.html

4.3.4.8 Counting Rows

If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is indeterminate. The server is free to select the value from any row:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        8 | 
+--------+----------+
1 row in set (0.00 sec)

但是我们的mysql 供应商提供的中间件不支持这个写法，表分片后owner会返回null....