Summary of solutions for MySQL not supporting group by

I downloaded and installed the latest version of MySQL 5.7.x. By default, the only_full_group_by mode was enabled. However, after enabling this mode, the original group by statement gave an error, so I removed it.

Once only_full_group_by is turned on, it feels like group by will become the same as distinct, which can only obtain the field information affected by it, and cannot coexist with other fields not affected by it. In this way, the function of group by will become very narrow.

It is better to enable only_full_group_by mode.

Because there is a function in MySQL: any_value(field) allows the appearance of non-grouping fields (which has the same effect as turning off only_full_group_by mode).

Specific error message:

[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.1. 1. Check sql_mode

select @@global.sql_mode;

1.2. The queried value is:

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

2. Remove ONLY_FULL_GROUP_BY and reset the value.

select @@global.sql_mode;

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';
set global 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.1. 3. The above changes the global sql_mode and is valid for newly created databases. For an existing database, you need to execute the following command under the corresponding data:

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';

There are two general solutions:

1: Using the any_value() function on a field that does not require a group by in a SQL query statement is not suitable for projects that have already developed a lot of functions. After all, the original SQL must be modified.

3.1.

2: Modify the my.cnf (my.ini under Windows) configuration file and delete the only_full_group_by item. If our project's MySQL is installed on Ubuntu, find this file and open it. There is no sql_mode configuration item in it, so you can't delete it even if you want to.

Of course, there are other ways. Open the MySQL command line and execute the command

select @@sql_mode;
In this way, you can find out the value of sql_mode, copy this value, and add the configuration item in my.cnf (delete the only_full_group_by option from the queried value and copy the others):

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
If the [mysqld] line is commented out, remember to uncomment it. Then restart the mysql service

Note: Use the command

set sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This allows you to modify configuration items in one session without taking effect in other sessions.

If the above is not clear enough, please refer to the following statement

Enter the mysql command line and execute the following two statements

set global 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 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';

But when restarting mysql the problem will reappear

Find my.cnf, edit it, put the following command in the appropriate location, restart the mysql service, OK, no error reported.

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note that the sql_model statement must be placed in the [mysqld] content area.

Solution to mysql5.7 not supporting group by

1. Check sql_mode

select @@global.sql_mode

The queried value is:

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

2. Remove ONLY_FULL_GROUP_BY and reset the value.

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql5.7+ in linux uses group by and order by and reports an error ONLY_FULL_GROUP_BY without modifying the configuration file

1. Enter mysql
2. Query sql_mode:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SELECT @@sql_mode;

Found that there is ONLY_FULL_GROUP_BY in front

3. Enter in the command line
set @@GLOBAL.sql_mode=(select replace(@@GLOBAL.sql_mode,'ONLY_FULL_GROUP_BY',''));
Set the above three sql_homes once.

4. Exit mysql and re-enter to check sql_mode;

That’s basically it.