问题总结:
1. sql_mode=only_full_group_by 问题
问题描述
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
过程
查看sql_mode:
select @@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
解决方法
mysql5.7.x版本,默认是开启了 only_full_group_by 模式的,但开启这个模式后,原先的 group by 语句就报错*
mysql 配置文件(/etc/my.cnf)赋值sql_mode参数,移掉ONLY_FULL_GROUP_BY即可,建议采用mysql中 any_value(field) 替代(还未尝试)
sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
使用如下命令只在当前会话有效,服务重启后失效
set sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
重启服务使配置生效 service mysqld restart
2. 初始化root密码
mysql>update user set password=PASSWORD(‘123456’) where User='root';
3. 允许mysql远程访问,可以使用以下方式:
- 改表(据说不推荐)
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
- 授权
你想root使用密码123456从任何主机连接到mysql服务器。
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
或者赋予任何主机访问数据的权限
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
最后
//使修改失效
mysql>FLUSH PRIVILEGES
//退出MySQL服务器
mysql>EXIT