MySQL
Ошибка 1055: ONLY_FULL_GROUP_BY: что-то не в условии GROUP BY ...

Вступление

замечания

SET  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Использование и неправильное использование GROUP BY

 SELECT item.item_id, item.name,     /* not SQL-92 */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

покажет строки в таблице, называемой item , и покажет количество связанных строк в таблице с именем uses . Это хорошо работает, но, к сожалению, это не стандартный SQL-92.

Почему бы и нет? потому что предложение SELECT (и предложение ORDER BY ) в запросах GROUP BY должно содержать столбцы, которые

1. указанных в предложении GROUP BY , или
2. совокупные функции, такие как COUNT() , MIN() и тому подобное.

В предложении SELECT этого примера упоминается item.name , столбец, который не соответствует ни одному из этих критериев. MySQL 5.6 и ранее отклонят этот запрос, если режим SQL содержит ONLY_FULL_GROUP_BY .

Этот примерный запрос может быть выполнен в соответствии со стандартом SQL-92, изменив предложение GROUP BY , как это.

SELECT item.item_id, item.name, 
       COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id, item.name

Более поздний стандарт SQL-99 позволяет SELECT пропускать неагрегированные столбцы из группового ключа, если СУБД может доказать функциональную зависимость между ними и столбцами группового ключа. Поскольку item.name функционально зависит от item.item_id , исходный пример действителен SQL-99. MySQL получил версию функциональной зависимости в версии 5.7. Исходный пример работает под ONLY_FULL_GROUP_BY .

Неправильная GROUP BY возвращает непредсказуемые результаты: закон Мерфи

SELECT item.item_id, uses.category,   /* nonstandard */ 
       COUNT(*) number_of_uses 
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

покажет строки в таблице, называемой item, и покажет количество связанных строк в таблице с именем uses. Он также покажет значение столбца, называемого uses.category .

Этот запрос работает в MySQL (до ONLY_FULL_GROUP_BY флажка ONLY_FULL_GROUP_BY ). Он использует нестандартное расширение MySQL для GROUP BY .

Но у запроса есть проблема: если несколько строк в таблице uses соответствуют условию ON в предложении JOIN , MySQL возвращает столбец category только из одной из этих строк. Какая строка? Писатель запроса и пользователь приложения не знают об этом заранее. Формально говоря, это непредсказуемо : MySQL может вернуть любое значение, которое он хочет.

Непредсказуемый случайный, с одним существенным различием. Можно ожидать случайного выбора время от времени. Поэтому, если выбор был случайным, вы можете обнаружить его во время отладки или тестирования. Непредсказуемый результат хуже: MySQL возвращает тот же результат каждый раз, когда вы используете запрос, пока он этого не сделает. Иногда это новая версия сервера MySQL, которая приводит к другому результату. Иногда это вызывает растущую таблицу, вызывающую проблему. Что может пойти не так, пойдет не так, и когда вы этого не ожидаете. Это называется законом Мерфи .

Команда MySQL работает над созданием этой ошибки для разработчиков. Более новые версии MySQL в 5,7 последовательности имеют sql_mode флаг под названием ONLY_FULL_GROUP_BY . Когда этот флаг установлен, сервер MySQL возвращает ошибку 1055 и отказывается запускать такой запрос.

Неправильная команда GROUP BY с SELECT *, и как ее исправить.

Иногда запрос выглядит так: * в предложении SELECT .

 SELECT item.*,     /* nonstandard */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

Такой запрос должен быть реорганизован для соответствия стандарту ONLY_FULL_GROUP_BY .

Для этого нам нужен подзапрос, который правильно использует GROUP BY чтобы вернуть значение number_of_uses для каждого item_id . Этот подзапрос короткий и сладкий, потому что ему нужно только посмотреть таблицу uses .

                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id

Затем мы можем присоединиться к этому подзапросу с помощью таблицы item .

 SELECT item.*, usecount.number_of_uses
   FROM item
   JOIN (
                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id
        ) usecount ON item.item_id = usecount.item_id

Это позволяет сделать предложение GROUP BY простым и правильным, а также позволяет использовать спецификатор * .

Примечание: тем не менее, мудрые разработчики избегают использования спецификатора * в любом случае. Обычно лучше перечислять столбцы, которые вы хотите в запросе.

ANY_VALUE ()

 SELECT item.item_id, ANY_VALUE(uses.tag) tag,   
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

показывает строки в таблице, называемой item , количество связанных строк и одно из значений в связанной таблице, называемой uses .

Вы можете рассматривать эту ANY_VALUE() как странную своего рода совокупную функцию. Вместо того, чтобы возвращать счет, сумму или максимум, он инструктирует сервер MySQL выбирать произвольно одно значение из рассматриваемой группы. Это способ работы с ошибкой 1055.

Будьте внимательны при использовании ANY_VALUE() в запросах в производственных приложениях.

Его действительно следует называть SURPRISE_ME() . Он возвращает значение некоторой строки в группе GROUP BY. Какая строка возвращается, является неопределенной. Это означает, что это полностью зависит от сервера MySQL. Формально он возвращает непредсказуемое значение.

Сервер не выбирает случайное значение, это хуже, чем это. Он возвращает одно и то же значение каждый раз, когда вы запускаете запрос, пока он этого не сделает. Он может измениться или нет, когда таблица растет или сжимается, или когда сервер имеет больше или меньше ОЗУ, или когда версия сервера изменяется, или когда Марс находится в ретроградном (что бы это ни значило) или вообще без причины.

Вы были предупреждены.