Mysql cannot select non-aggregate columns

1. Introduction

I recently upgraded my blog and added two buttons at the bottom of the article page, which can jump directly to the previous and next article.

As shown in the following figure:

The difficulty in implementing this function lies in: how does the database select the two adjacent records before and after a record?

2. Database Design

The design of my article database is shown below:

As you can see, the identity of each record is the index Id. Because many article records were deleted before, the IDs are not continuous.

If the index value of the current article is 33, you can use the following command to get the two adjacent articles:

select * from passage where id in
(select
case
when SIGN(id - 32 )>0 THEN MIN(id)
when SIGN(id - 32 )<0 THEN MAX(id)
end
from passage
where id != 34
GROUP BY SIGN(id - 32 )
ORDER BY SIGN(id - 32 )
)
ORDER BY id;

3. Unable to select aggregate columns

When executing the above command, Mysql gave me an error: SELECT list is not in GROUP BY clause ... I learned from Google that in MySQL 5.7 and above, only_full_group_by is enabled by default, and MySQL will reject queries referenced by select lists, conditions, or sequence lists.

The following is the original text:

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. As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

Therefore, we should set sql_mode not to include only_full_group_by option. After MySQL 5.7.5, only_full_group_by becomes one of the default options of sql_mode, which may cause some SQL statements to fail.

Enter the mysql configuration file, add the following configuration in the [mysqld] section, and restart mysql.

[mysqld]
# ... other config
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATEERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # delete 'only_full_group_by'
# ... other config

Run the mysql command in the second part of this article, and the result is as shown below:

4. Related links

only_full_group_by

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.