MySQL 错误收集

收集一下使用mysql时出现的错误，方便之后进行查找并解决问题。

1055 - Expression #1 of ORDER BY clause is nt in GROUP BY clause and contains nonaggregated column …

今天使用GROUP BY 函数时，出现了一下问题：

• SQL语句：

select 
	job,sum(num) as cnt
from
	resume_info
where 
	YEAR(date) = '2025'
GROUP BY job
order by num desc

• 错误提示：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY 
clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' 
which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

• 错误原因：在SQL_mode中开启了 only_full_group_by 模式

ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode，通过这个sql_mode来提供SQL语句GROUP BY合法性的检查，在MySQL的sql_mode是非ONLY_FULL_GROUP_BY语义时。一条select语句，MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式，这个表达式的值可能在经过group by操作后变成undefined

• 解决方法：
  • 将SQL_MODE中的only_full_group_by删除掉。（MySQL重启后会失效）

1. 查看SQL_MODE

select @@global.sql_mode

默认值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

2. 修改SQL_MODE

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';