MySQL记一次获取会话列表与sql_mode=only_full_group_by错误

场景：

MySQL存储即时通讯聊天信息，需要从聊天表中抽取会话列表。

问题描述：

在使用Group by语句的时候，Mysql报错：only_full_group_by，当时的语句：

select conversationid,content,create_time
from (
select peerid as conversationid,content,create_time from demo.msg where uid = 100 and peerid <> 100 
union 
select uid as conversationid,content,create_time from demo.msg  where uid <> 100 and peerid = 100 
order by 
create_time desc)as tableother group by conversationid;

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘newtable.content’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

---

分析：

通过查找资料,得到以下几点结论：

1. only_full_group_by规则类似于oracle的group，select查询列需要在group里面，或者本身是函数
2. MySQL5.7.5之前的版本没有对功能依赖的检测，所以不会出现此问题
3. MySQL5.7.5及更高版本默认开启了only_full_group_by模式，如果不遵守第一条规定则出现此错误（配置查询：select @@sql_mode;）

---

解决方案：

1. 修改配置文件

$ vim /usr/local/etc/my.cnf
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2. SQL命令

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

3. 通过ANY_VALUE（推荐）
   贴一下MySQL官网的案例解说：

ANY_VALUE
This function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.
For example, if name is a nonindexed column, the following query fails with ONLY_FULL_GROUP_BY enabled:

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

The failure occurs because address is a nonaggregated column that is neither named among GROUP BY columns nor functionally dependent on them. As a result, the address value for rows within each name group is nondeterministic. There are multiple ways to cause MySQL to accept the query:
Alter the table to make name a primary key or a unique NOT NULL column. This enables MySQL to determine that address is functionally dependent on name; that is, address is uniquely determined by name. (This technique is inapplicable if NULL must be permitted as a valid name value.)

Use ANY_VALUE() to refer to address:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

最终解决方案

select conversationid,any_value(content) as content,any_value(create_time) as create_time 
from (
select peerid as conversationid,content,create_time from demo.msg where uid = 100 and peerid <> 100 
union 
select uid as conversationid,content,create_time from demo.msg  where uid <> 100 and peerid = 100 
order by 
create_time desc)as tableother group by conversationid;