windows安装 mysql5.7 ,客户端执行delete，update语句 报错 [Err] 1055 - Expression #1 of ORDER BY claus...

之前使用的mysql5.6，在升级到5.7后执行 delete，update语句只能可以成功，但是客户端有个报错

[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

解决方法

1、查看sql模式

SHOW VARIABLES LIKE '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

2、重新设置sql模式

客户端执行sql设置（重启数据库会失效）

重新设置sql模式把ONLY_FULL_GROUP_BY 去掉即可

SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

修改服务端配置文件，永久生效

windows系统 msi安装包，配置文件默认在 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini；
my.ini 111行的注释掉 ,把ONLY_FULL_GROUP_BY,去掉保存，重启数据库，再次执行不在显示该错误

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"