当使用MySQL的时候,我们可能会遇到以下错误提示:
Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
造成这个错误的原因是MySQL默认开启了严格模式(strict mode),只有满足特定条件的GROUP BY查询才会通过,否则会抛出此错误。这在一些旧版本的MySQL中不会出现,但在较新的版本中却经常会出现。为了解决这个问题,我们需要进行如下操作。
解决办法
- 修改MySQL的配置文件
可以编辑MySQL配置文件,将sql_mode的值修改为其他值。比如我们可以将sql_mode修改为:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
此时,MySQL的严格模式就被关闭了。要注意的是,这种方式需要登录MySQL的管理员账户,需要操作系统或服务器的管理员权限,如果使用的是共享主机,则可能无法进行这种修改。
- 使用GROUP BY时明确指定聚合函数
在进行GROUP BY查询时,我们需要确保SELECT列表中的所有列都是被聚合函数包裹的,或者都被列在GROUP BY之中。否则,可以手动指定聚合函数或者将列列在GROUP BY之中。比如以下查询是错误的:
SELECT column1, column2 FROM table1 GROUP BY column1;
正确的做法是:
SELECT column1, MAX(column2) FROM table1 GROUP BY column1;
或者:
SELECT column1, column2 FROM table1 GROUP BY column1, column2;
示例说明
示例1
使用GROUP BY时未聚合的字段未被列在GROUP BY之中,导致了该错误。假设我们有以下的查询语句:
SELECT department, AVG(salary) FROM employees GROUP BY department;
这一查询语句会计算每个部门(department)的平均工资(salary)。但是这个语句存在问题:我们在SELECT中选择了department和AVG(salary)这两列,其中salary并没有被聚合函数包裹。这个语句在严格模式下会失败,并且抛出上述的错误。
我们可以修改这个语句,让salary被包裹在AVG函数之中,或者将salary列在GROUP BY之中:
SELECT department, AVG(salary) FROM employees GROUP BY department, salary;
示例2
严格模式下的MySQL不允许在INSERT或UPDATE语句中插入空字符串(''),这时会抛出错误:Error: Data too long for column 'xxxxx' at row 1。我们需要将所有的空字符串都替换为NULL,以便解决这个问题。比如:
UPDATE table1 SET column1=NULL WHERE column1='';
这个语句会将表table1中的column1=''的记录全部更新为NULL。此时我们可以插入或更新NULL值了。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MySQL出现this is incompatible with sql_mode=only_full_group_by错误的解决办法 - Python技术站