mysql启动时167错误_MySQL5.7.25报1055错误解决方案

青旅半醒 2023-01-12 07:41 162阅读 0赞

数据库表结构如下:

![8058b98079cf1effdc1cc7dde1a81a8c.png][]

对MySql进行操作时报以下错误:

\[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

![a63e1fe526e20b5c671ae98b331f9623.png][]

一次性解决方案:

1. 查看sql\_mode配置

select @@sql\_mode

![8fda7778f50068ab40fd450841628198.png][]

复制查询出来的信息:

ONLY\_FULL\_GROUP\_BY,STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION

2. 修改sql\_mode配置

2.1 编辑 vi /etc/my.cnf 文件

windwos环境下,直接修改默认my.ini(解压缩版)或其他默认配置文件即可

\[\[email protected\]\_18\_167\_centos ~\]\# vi /etc/my.cnf

![2629cba081beef1e21275b892572b81f.png][]

2.2 在my.cnf 里面设置 sql\_mode

复制查询到的sql\_mode配置信息,并去掉ONLY\_FULL\_GROUP\_BY,内容如下:

STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION

设置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‘

![b15c6d9750a84dd5bf07e436596272f7.png][]

重新启动mysql:

systemctl restart mysqld

![8c21791968e20c4bb93e0f4c9939aab8.png][]

再次执行插入语句,我们可以看到已经成功操作,不再报错了

![d47698dc9b65f7ae00f204336b575082.png][]

原文：https://blog.51cto.com/1197822/2363459

[8058b98079cf1effdc1cc7dde1a81a8c.png]: /images/20221119/70ee0f317bff4375aa65e32778c327eb.png
[a63e1fe526e20b5c671ae98b331f9623.png]: /images/20221119/31f0a0bc3f6a4949a2deed82994e2197.png
[8fda7778f50068ab40fd450841628198.png]: /images/20221119/c73efd3cdefd42ea91ea233040e5fbfb.png
[2629cba081beef1e21275b892572b81f.png]: /images/20221119/aa3b162d794e41e5b714a771055b0764.png
[b15c6d9750a84dd5bf07e436596272f7.png]: /images/20221119/92ff10143ce84f41b23ad038bad059ed.png
[8c21791968e20c4bb93e0f4c9939aab8.png]: /images/20221119/8996ce790f874884aef3b0ed04101485.png
[d47698dc9b65f7ae00f204336b575082.png]: /images/20221119/42c38c0123194700944ef1918bc048d4.png