mysql踩坑记录

navicate执行mysql的sql语句报错
[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. 找到my.ini配置文件，加个配置

-- 查看数据库的存储地址
SELECT @@datadir

[mysql]
sql_mode=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
SELECT @@sql_mode;

-- 修改SQL_MODE
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

比较奇葩的是，可能只是navicate工具报这个问题，使用idea内置的数据库工具就不会有事，所以换可视化工具吧，IDEA连接mysql数据库