After mysql 5.7, the processing of group by is different. Here we do some research based on a demo.

Official documents:

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

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

 

demand

Statistical user login times, display user Id and name, as follows:

Based on the above requirements, it is assumed that only one'User Log-in Log Table'is currently available:

DROP TABLE
IF EXISTS user_login;

CREATE TABLE `user_login` (
	`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT 'Self increasing primary key',
	`user_id` INT (11) DEFAULT NULL COMMENT 'user Id',
	`user_name` VARCHAR (100) DEFAULT NULL COMMENT 'User name (redundancy: little variability, lax business requirements)',
	`login_time` datetime DEFAULT NULL COMMENT 'login time',
	PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = 'User login log table';

INSERT INTO `user_login` ( `user_id`, `user_name`, `login_time`) VALUES ( '1', 'Small A', '2017-09-13 22:06:49');
INSERT INTO `user_login` ( `user_id`, `user_name`, `login_time`) VALUES ( '1', 'Small A', '2017-09-14 12:06:51');
INSERT INTO `user_login` ( `user_id`, `user_name`, `login_time`) VALUES ( '2', 'Small B', '2017-09-14 16:16:54');

The intuitive data are as follows:

 

problem

Start with the following sql to implement the requirements:

-- SELECT The following contains GROUP BY A column that does not follow user_name
SELECT user_id,user_name,count(*) FROM user_login GROUP BY user_id;

The first impression of the above sql is that the grammar will report errors, especially for some people who transit from oracle to mysql.

What is the real situation?

By default, before mysql 5.7, the above sql grammar is correct, and later versions will report errors, as follows:

 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

-- SELECT The following contains non-aggregated columns user_name，and user_name and GROUP BY The following columns have no functional dependencies; this violates only_full_group_by Of sql Pattern

Of course, in GROUP BY with a list of user_name, affirmative ok, let's not say, here we explore mysql 5.7 after the solution given. As you can see from the error message, if the user_name and user_id are functionally dependent, ok. What is functionally dependent? Let's talk about mysql's only_full_group_by mode.

 

only_full_group_by

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

-- select,HAVING,ORDER BY Can't appear later'Non-aggregated columns'(GROUP BY Columns that are not available later, unless these non-aggregated columns and GROUP BY Between the following columns'Function dependency'(The only decision) .

After mysql 5.7, the sql mode of only_full_group_by is turned on by default, which can be viewed by command:

-- View Global sql_mode
SELECT @@GLOBAL .sql_mode;
-- Viewing the current session sql_mode
SELECT @@SESSION.sql_mode;
-- The results are as follows. The first one is ONLY_FULL_GROUP_BY
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

Because only_full_group_by constraints, sql will report errors, closing can solve the problem.

Remove ONLY_FULL_GROUP_BY from the value of sql_mode queried above and reset it. There are two ways to close it:

-- Set up current session Of sql_mode
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
-- After setup, in the current session Next, the following sql No more errors:
SELECT user_id,user_name,count(*) FROM user_login GROUP BY user_id;

In addition, you can modify sql_mode through the configuration file, which is not explored here. Next, let's briefly talk about the functional dependencies mentioned above.

 

functionally dependent

functionally dependent is the same as uniquely determined. Simply understood, a field depends entirely on another field, that is, its value is determined by another field. For example, if the non-primary key column of a table is uniquely determined by the primary key column, we can call them functional dependencies.

Based on the test scenario above, let's assume that we have a User Table:

CREATE TABLE `user` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR (64) DEFAULT NULL COMMENT 'Name (and primary key) id Functional dependency)',
	PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT 'User table';

INSERT INTO `user` VALUES (1, 'Small A');
INSERT INTO `user` VALUES (2, 'Small B');

Obviously, the user name column is functionally dependent on the user id column, so the following sql is grammatically correct at all times:

SELECT
	ul.user_id,
	u.name, -- This column is not an aggregate column, but it and the primary key u.id It's functionally dependent, so the whole thing sql all ok
	count(*)
FROM
	user_login ul
INNER JOIN user u ON ul.user_id = u.id
GROUP BY
	ul.user_id;

.

any_value

If the extra non-aggregated columns do not have functionally dependent features, and we do not want to modify the default sql_mode, mysql also provides us with another solution, that is, bypassing only_full_group_by mode by any_value function, as follows:

-- any_value You will choose one at will. user_name Value, usually the first
SELECT user_id,any_value(user_name),count(*) FROM user_login GROUP BY user_id;

.

Standard

Someone will say, what's the hard work, just add the user_name column at the end of it. And it's natural to write like this.

-- accord with SQL99 Canonical group by
SELECT user_id,user_name,count(*) FROM user_login GROUP BY user_id,user_name;

Indeed, in practice, we need to consider how to solve these problems unless we find some historical sql errors after updating the version of mysql. Normally, we just need to develop it according to the only_full_group_by specification, that is, the columns after the keywords select, have, order by must appear after group by at the same time, unless the extra non-aggregated columns and the columns behind group by have functional dependencies.