mysql 查询提示 SELECT list is not in GROUP BY clause and contains nonaggregated column 'user_login.user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 是为什么?
mysql 5.7之后,对group by的处理有所区别,这里基于一个demo做一些探究
官方文档:
需求
统计用户登录次数,显示用户Id和姓名,如下:
基于上面的需求,假设目前只有一张'用户登录日志表'可用:
DROP TABLE
IF EXISTS user_login;
CREATE TABLE `user_login` (
`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`user_id` INT (11) DEFAULT NULL COMMENT '用户Id',
`user_name` VARCHAR (100) DEFAULT NULL COMMENT
'用户姓名(冗余:可变性不大,业务要求不严)',
`login_time` datetime DEFAULT NULL COMMENT '登录时间',
PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '用户登录日志表';
INSERT INTO `user_login` ( `user_id`, `use