mysql - 为什么执行 count() 会返回带有回溯的 PhpMyAdmin 错误

Question

我正在尝试通过 PHPMyAdmin 4.8.4 执行以下命令:

SELECT *, count(ref_id) AS c FROM `articles_test` group by ref_id order by c desc

这将返回以下错误消息:

随后是此错误回溯:

Notice in ./libraries/classes/Display/Results.php#2488 Trying to get property of non-object

Backtrace

./libraries/classes/Display/Results.php#2389: PhpMyAdmin\Display\Results->_addClass( string 'data hide ', boolean false, NULL, string '', ) ./libraries/classes/Display/Results.php#3818: PhpMyAdmin\Display\Results->_buildNullDisplay( string 'data hide ', boolean false, NULL, ) ./libraries/classes/Display/Results.php#3071: PhpMyAdmin\Display\Results->_getDataCellForNonNumericColumns( NULL, string 'data hide ', NULL, array, array, boolean false, array, array, array, boolean false, array, , NULL, ) ./libraries/classes/Display/Results.php#2695: PhpMyAdmin\Display\Results->_getRowValues( , array, integer 24, array, array, string 'grid_edit click2', array, string 'SELECT *, count(ref_id) AS c FROM articles_test group by ref_id order by c desc', array, ) ./libraries/classes/Display/Results.php#4287: PhpMyAdmin\Display\Results->_getTableBody( , array, array, array, boolean false, ) ./libraries/classes/Sql.php#1738: PhpMyAdmin\Display\Results->getTable( , array, array, boolean false, ) ./libraries/classes/Sql.php#2031: PhpMyAdmin\Sql->getHtmlForSqlQueryResultsTable( , string './themes/pmahomme/img/', NULL, array, boolean true, string '2554', integer 25, NULL, , array, ) ./libraries/classes/Sql.php#2252: PhpMyAdmin\Sql->getQueryResponseForResultsReturned( , array, string 'test', string 'articles_test', NULL, NULL, , string './themes/pmahomme/img/', string '2554', integer 25, NULL, NULL, NULL, NULL, NULL, string 'SELECT *, count(ref_id) AS c FROM articles_test group by ref_id order by c desc', NULL, ) ./import.php#736: PhpMyAdmin\Sql->executeQueryAndGetQueryResponse( array, boolean false, string 'test', string 'articles_test', NULL, NULL, NULL, NULL, NULL, NULL, string 'tbl_structure.php', string './themes/pmahomme/img/', NULL, NULL, NULL, string 'SELECT *, count(ref_id) AS c FROM articles_test group by ref_id order by c desc', NULL, NULL, )

我正在运行以下系统:

5.7.24-0ubuntu0.16.04.1 - Apache/2.4.18 (Ubuntu) Datenbank-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $ PHP-Erweiterung: mysqliDokumentation curlDokumentation mbstringDokumentation PHP-Version: 7.0.32-0ubuntu0.16.04.1

这是一个错误，还是数据库有问题？没有可见错误，表格看起来正常。

Answer 1

您使用的 MySQL 版本是什么。查询

SELECT *, count(ref_id) AS c 
FROM `articles_test` 
group by ref_id order by c desc

由于默认的 sql_mode only_full_group_by，在 MySQL 5.7 及更高版本中不会在默认设置下运行。问题是您将所有列符号 (*) 与聚合函数混合在一起。较旧的 MySQL 版本允许这种(错误的)查询，但默认情况下 MySQL 5.7 更加严格。

如果您想列出articles_test中的所有行以及每个ref_id的计数(相同ref_id重复相同的值)，请使用子查询:

SELECT a.*, count(ref_id) AS c 
FROM articles_test a
  JOIN (
    SELECT at.ref_id, count(*) as refid_count
    FROM articles_test at
    GROUP BY at.ref_id
  ) q as q.ref_id=a.ref_id