和群友讨论，测试了一下ONLY_FULL_GROUP_BY，供大家参考（基于mysql 8.0.27)。
代码语言：javascript复制mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)
mysql> use aaa;
Database changed
mysql> create table aa( a int, b varchar(50) );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into aa(a,b) value(1,"aa");
Query OK, 1 row affected (0.01 sec)
mysql> insert into aa(a,b) value(2,"ba");
Query OK, 1 row affected (0.01 sec)
mysql> select b, group_concat(b) from aa;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'aaa.aa.b'; this is incompatible with sql_mode=only_full_group_by
mysql> select b, group_concat(b) from aa group by b;
------ -----------------
| b | group_concat(b) |
------ -----------------
| aa | aa |
| ba | ba |
------ -----------------
2 rows in set (0.00 sec)
mysql> show variables like '%sql_mode%';
--------------- -----------------------------------------------------------------------------------------------------------------------
| Variable_name | Value |
--------------- -----------------------------------------------------------------------------------------------------------------------
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
--------------- -----------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql> set global sql_mode=' ';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%sql_mode%';
--------------- -------
| Variable_name | Value |
--------------- -------
| sql_mode | |
--------------- -------
1 row in set (0.00 sec)
mysql> select b, group_concat(b) from aa ;
------ -----------------
| b | group_concat(b) |
------ -----------------
| aa | aa,ba |
------ -----------------
1 row in set (0.00 sec)
# 情况2
mysql> show create table aaG
*************************** 1. row ***************************
Table: aa
Create Table: CREATE TABLE `aa` (
`a` int DEFAULT NULL,
`b` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> alter table aa add column c varchar(10) default 'c';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select b, c, group_concat(b) from aa group by a,b;
------ ------ -----------------
| b | c | group_concat(b) |
------ ------ -----------------
| aa | c | aa |
| ba | c | ba |
------ ------ -----------------
2 rows in set (0.00 sec)
mysql> show variables like '%sql_mode%';
--------------- -------
| Variable_name | Value |
--------------- -------
| sql_mode | |
--------------- -------
1 row in set (0.00 sec)
mysql> show variables like '%sql_mode%';
--------------- -----------------------------------------------------------------------------------------------------------------------
| Variable_name | Value |
--------------- -----------------------------------------------------------------------------------------------------------------------
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
--------------- -----------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql> select b, c, group_concat(b) from aa group by a,b;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'aaa.aa.c' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
ps： 后台回复"技术群"，可加本公众号交流群。
