MySQL5.7 sql_mode only_full_group_by

1 MySQL 官方文档:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

https://blog.csdn.net/kk185800961/article/details/79426041

https://blog.csdn.net/wangyunfeis/article/details/77911704

2 执行的SQL:

select t3.attribute_value_id,
 t3.attribute_value_code
 from category_rel_attribute t1
 INNER JOIN
 category_rel_attribute_rel_value t2 on t1.category_attribute_id=
 t2.category_attribute_id
 INNER JOIN
 category_attribute_value_code_config t3 on
 t2.category_attribute_value_id= t3.attribute_value_id
 where
 t1.category_id= 1
 and t1.is_sku_attr= 'Y'
 GROUP BY
 t2.category_attribute_value_id

3 报错

Expression #2 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 
'pms.t3.attribute_value_code' which is not functionally 
dependent on columns in GROUP BY clause; this is 
incompatible with sql_mode=only_full_group_by

4 select @@sql_mode

 

5 去掉ONLY_FULL_GROUP_BY

mysql> 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’;

Query OK, 0 rows affected (0.01 sec)

mysql创建用户和修改非Root用户密码

1 创建用户

create user 'test'@'%' identified by '123456';

flush privileges;

2 赋予用户数据库权限

grant all privileges on `testdb`.* to 'test'@'%' identified by '123456';
flush privileges;

3 修改非Root用户密码

https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

1 MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

2 MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');