[乐意黎原创]mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by

古城微笑少年丶 2022-05-15 15:06 113阅读 0赞

如题： MySQL执行查询时，抛SQL语句执行失败：  
SELECT gzh\_en, artID, count(\*) as count FROM \`\*\*\*\*\` group by \`ori\_url\` having count>1 ORDER BY \`id\` DESC LIMIT 0,50000  
  
失败原因：Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '\*\*\*\*\*' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

如图:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FlcmNoaQ_size_16_color_FFFFFF_t_70][]  
解决方法:

修改my.ini 配置（my.cnf配置问题）

在 \[mysqld\]和\[mysql\]下添加:

\[mysqld\]  
\#  
\# \* Basic Settings  
lower\_case\_table\_names=1  
character-set-server=utf8  
sql\_mode=STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION;

如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FlcmNoaQ_size_16_color_FFFFFF_t_70 1][]

乐意黎原创:

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FlcmNoaQ_size_16_color_FFFFFF_t_70]: /images/20220503/4b03175867e54a268146b4b62ee06e08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FlcmNoaQ_size_16_color_FFFFFF_t_70 1]: /images/20220503/5d83a7b1c48a48588cb7b4c1ed8dd2f6.png