MySQL: нет в GROUP BY

Сайт создает результаты, но с запросом SELECT COUNT и SELECT с GROUP BY, имеющим два разных результата. Вероятно, это связано с ошибкой, отображаемой в phpmyadmin, но не на сайте.

Запросы:

SELECT count(DISTINCT `name`) as `numrows` FROM `users` WHERE `verified` = '1'

SELECT `name`, `type`, `language`, `code` FROM `users` WHERE `verified` = '1' GROUP BY `name` ORDER BY `count` DESC LIMIT 0, 25

PhpMyAdmin предоставляет следующую ошибку:

1055 - 'main.users.type' не находится в GROUP BY

При чтении документов MySQL я все еще не понимаю, что мне нужно исправить. Я не могу понять этого.

Ответ 1

Вам нужно иметь полную группу:

SELECT `name`, `type`, `language`, `code` 
FROM `users` 
WHERE `verified` = '1' 
GROUP BY `name`, `type`, `language`, `code` 
ORDER BY `count` DESC LIMIT 0, 25

SQL92 требует, чтобы все столбцы (кроме агрегатов) в предложении select были частью предложения group by. SQL99 немного ослабляет это ограничение и заявляет, что все столбцы в предложении select должны быть функционально зависимыми от предложения group by. MySQL по умолчанию допускает частичную группу, и это может привести к недетерминированным ответам, например:

create table t (x int, y int);
insert into t (x,y) values (1,1),(1,2),(1,3);
select x,y from t group by x;
+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
+------+------+

т.е. случайный y выбирается для группы x. Это можно предотвратить, установив @@sql_mode:

set @@sql_mode='ONLY_FULL_GROUP_BY';
select x,y from t group by x; 
ERROR 1055 (42000): 'test.t.y' isn't in GROUP BY

Ответ 2

Лучшим решением этой проблемы является, конечно, использование полного выражения GROUP BY.

Но есть другое решение, которое работает вокруг блокировки ONLY_FULL_GROUP_BY старого расширения MySQL до GROUP BY.

SELECT name, 
       ANY_VALUE(type) type,
       ANY_VALUE(language) language,
       ANY_VALUE(code) code 
  FROM users  
 WHERE verified = '1' 
 GROUP BY name 
 ORDER BY count DESC LIMIT 0, 25

ANY_VALUE() явно объявляет, что подразумевалось в неявных операциях MySQL GROUP BY, что сервер может выбрать, ну, любое, значение для возврата.

Ответ 3

Еще одно решение, упомянутое несколько раз выше, - это отключить это раздражающее 'ONLY_FULL_GROUP_BY', например. как в этом посте: Отключить ONLY_FULL_GROUP_BY

Я думаю, что это решение очень полезно, если вы не хотите реорганизовывать весь проект в течение нескольких часов. И если вы не заботитесь о непредсказуемых значениях столбцов, которые не являются списком GROUP BY.

Ответ 4

Просто отключите строгость запроса.