Shulou(Shulou.com)06/01 Report--

Download and install the latest version of mysql5.7.x. Only_full_group_by mode is turned on by default, but when this mode is turned on, the original group by statement reports an error and then removes it.

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

It is better to turn on only_full_group_by mode.

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

Specific error tips:

[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. View sql_mode

Select @ @ global.sql_mode

1.2. The values queried are:

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 the 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, which is valid for the newly built database. For existing databases, you need to execute 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 generally two solutions:

One: use the any_value () function on fields that do not need group by in sql query statements

This is not suitable for projects that have developed a lot of functions. After all, it is necessary to modify the original sql.

3.1.

Second: modify the configuration file of my.cnf (my.ini under windows) and delete the item only_full_group_by

If the mysql of our project is installed on ubuntu, find this file and open it. There is no sql_mode configuration item inside. Even if you want to delete it, you can't delete it.

Of course, there is another way. 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 configuration items to my.cnf (delete the only_full_group_by option from the query value, and copy everything else):

Sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Remember to open the comment if the line [mysqld] is commented out. 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, which are not valid in other sessions.

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

Go to the mysql command line and execute the following two sentences

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 you restart mysql, the problem will reappear.

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

[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 sql_model 's sentence must be placed in the [mysqld] content area.

Mysql5.7 does not support group by's solution

1. View sql_mode

Select @ @ global.sql_mode

The values queried are:

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 the 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+ uses group by and order by to report errors under linux. ONLY_FULL_GROUP_BY does not modify the configuration file.

1. Enter mysql

two。 Query the sql_mode:

SELECT @ @ GLOBAL.sql_mode

SELECT @ @ SESSION.sql_mode

SELECT @ @ sql_mode

Found that there are ONLY_FULL_GROUP_BY in front of them.

3. Enter on the command line

Set @ @ GLOBAL.sql_mode= (select replace (@ @ GLOBAL.sql_mode,'ONLY_FULL_GROUP_BY','')

Set all the above three sql_home once

4. Exit mysql and re-enter to view sql_mode

We're almost done here.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.