測試環境 CentOS 7
使用 MySQL 5.7.5 時遇到 ONLY_FULL_GROUP_BY 的錯誤.
建立測試用資料表.
mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.00 sec) mysql> USE testdb; Database changed
mysql> CREATE TABLE mytable ( id INT UNSIGNED NOT NULL PRIMARY KEY, a VARCHAR(10), b INT ); Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mytable VALUES (1, 'abc', 1000), (2, 'abc', 2000), (3, 'def', 4000); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM mytable; +----+------+------+ | id | a | b | +----+------+------+ | 1 | abc | 1000 | | 2 | abc | 2000 | | 3 | def | 4000 | +----+------+------+ 3 rows in set (0.00 sec)
這樣的 SELECT 會出現 sql_mode=only_full_group_by 的訊息.
mysql> SELECT id , a , b FROM mytable GROUP BY a; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdb.mytable.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
only_full_group_by 是什麼? 在官網 – https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html 裡面提到
SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL-92 because the nonaggregated name column in the select list does not appear in the GROUP BY:
簡單來說就是在使用 HAVING condition 或是 ORDER BY 非指定欄位不能直接 SELECT 使用, 需搭配 Aggregate Functions – https://benjr.tw/102967 ,因為 MySQL 會不知道要幫我們抓出哪筆資料.
標準 Query 方式
以下搭配 SUM , MIN 等 Aggregate Functions 方式就可以使用.
mysql> SELECT a, SUM(b) FROM mytable GROUP BY a; +------+--------+ | a | SUM(b) | +------+--------+ | abc | 3000 | | def | 4000 | +------+--------+ 2 rows in set (0.00 sec)
mysql> SELECT a , MIN(b) FROM mytable GROUP BY a; +------+--------+ | a | MIN(b) | +------+--------+ | abc | 1000 | | def | 4000 | +------+--------+ 2 rows in set (0.00 sec)
Disab ONLY_FULL_GROUP_BY
另外一種方式就是 Disab ONLY_FULL_GROUP_BY
- 臨時
mysql> SELECT @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | 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 | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | 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 | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> exit Bye
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.43 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT @@sql_mode; +------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +------------------------------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> USE testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
這樣就可以 SELECT 了.
mysql> SELECT id , a , b FROM mytable GROUP BY a; +----+------+------+ | id | a | b | +----+------+------+ | 1 | abc | 1000 | | 3 | def | 4000 | +----+------+------+ 2 rows in set (0.00 sec)
- 永久
[root@localhost ~]# vi /etc/my.cnf [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
[root@localhost ~]# systemctl restart mysqld [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.43 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT @@sql_mode; +------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +------------------------------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> USE testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
這樣就可以 SELECT 了.
Database changed mysql> SELECT id , a , b FROM mytable GROUP BY a; +----+------+------+ | id | a | b | +----+------+------+ | 1 | abc | 1000 | | 3 | def | 4000 | +----+------+------+ 2 rows in set (0.00 sec)
沒有解決問題,試試搜尋本站其他內容