I. Issues
The version of MySql I am using is 8.0
MySql 5.7 and above may report errors when executing SQL statements with ORDER BY.
For example, execute the following mysql statement:
SELECT id, user_id, title FROM m_article WHERE user_id>=100 AND user_id <=200 GROUP BY user_id;
The SQL error message is as follows:
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.m_article.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
II. Analyzing the causes
SQL-92 and earlier queries do not allow the use of SELECT lists, HAVING conditions, or ORDER BY lists to reference non-aggregate columns that are not named in the GROUP BY clause.
Simply put: Since the sql-mode parameter is configured with ONLY_FULL_GROUP_BY, the selected field is not in the group by, and the selected field does not use the aggregate function (SUM, MAX, MIN, etc.), then this SQL query is considered illegal by MySql.
MySql official documentation:/doc/refman/8.0/en/
III. Solutions
1. Without modifying the parameters of sql-mode
1.1. Modify the example sentence in this article to add one more primary key after the original ORDER BY.
1.2. Modify the example sentence in this article to use the ANY_VALUE() function to add fields in non-GROUP BY columns and those that do not use the aggregate function. Use ANY_VALUE () does not check whether the function results in ONLY_FULL_GROUP_BY SQL mode.
MySql official documentation:/doc/refman/8.0/en/#function_any-value
2. Modify the parameters of sql-mode
2.2. Use SQL statement to modify temporarily.
SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
2.3, modify the MySql configuration file, this way to restart MySql after the completion of the configuration.
2.3.1, Linux to find the MySql configuration file, the file name is generally called [ ], the file path is generally in: /etc/, /etc/mysql/. After opening the [ ] file, just add a line under [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
2.3.2. In Windows, find the [ ] file, open it and add a line under [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
