MySQL
Fehler 1055: ONLY_FULL_GROUP_BY: etwas ist nicht in der GROUP BY-Klausel enthalten ...

Einführung

Bemerkungen

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'

Verwendung und Missbrauch von 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

zeigt die Zeilen in einer Tabelle namens item und die Anzahl der zugehörigen Zeilen in einer Tabelle mit dem Namen uses . Das funktioniert gut, aber leider nicht Standard-SQL-92.

Warum nicht? weil die SELECT Klausel (und die ORDER BY Klausel) in GROUP BY Abfragen Spalten enthalten müssen, die sind

1. in der GROUP BY Klausel erwähnt, oder
2. Aggregatfunktionen wie COUNT() , MIN() und dergleichen.

Die SELECT Klausel dieses Beispiels erwähnt item.name , eine Spalte, die keines dieser Kriterien erfüllt. MySQL 5.6 und frühere ONLY_FULL_GROUP_BY lehnen diese Abfrage ab, wenn der SQL-Modus ONLY_FULL_GROUP_BY enthält.

Diese Beispielabfrage kann so gemacht werden, dass sie dem SQL-92-Standard entspricht, indem die GROUP BY Klausel wie GROUP BY wird.

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

Der spätere SQL-99-Standard ermöglicht einer SELECT Anweisung, nicht aggregierte Spalten aus dem Gruppenschlüssel auszulassen, wenn das DBMS eine funktionale Abhängigkeit zwischen diesen und den Gruppenschlüsselspalten nachweisen kann. Da item.name funktional von item.item_id , ist das ursprüngliche Beispiel gültiges SQL-99. MySQL hat in Version 5.7 einen funktionellen Abhängigkeitsbeweiser erhalten . Das ursprüngliche Beispiel funktioniert unter ONLY_FULL_GROUP_BY .

Missbrauch von GROUP BY zur Rückgabe unvorhersehbarer Ergebnisse: Murphy's Law

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

zeigt die Zeilen in einer Tabelle namens item und die Anzahl der zugehörigen Zeilen in einer Tabelle mit dem Namen use an. Es zeigt auch den Wert einer Spalte mit der Bezeichnung " uses.category .

Diese Abfrage funktioniert in MySQL (bevor das Flag ONLY_FULL_GROUP_BY ). Es verwendet die nicht standardmäßige Erweiterung von MySQL für GROUP BY .

Aber die Abfrage hat ein Problem: wenn mehrere Zeilen in der uses die übereinstimmen ON Zustand in der JOIN - Klausel, MySQL gibt die category Spalte von nur einer dieser Zeilen. Welche reihe Der Verfasser der Abfrage und der Benutzer der Anwendung erfahren dies nicht im Voraus. Formal gesehen ist es unvorhersehbar : MySQL kann jeden gewünschten Wert zurückgeben.

Unvorhersehbar ist wie zufällig, mit einem signifikanten Unterschied. Man könnte erwarten, dass sich eine zufällige Wahl von Zeit zu Zeit ändert. Wenn eine Auswahl zufällig war, können Sie sie daher beim Debuggen oder Testen finden. Das unvorhersehbare Ergebnis ist schlimmer: MySQL gibt jedes Mal dasselbe Ergebnis zurück, wenn Sie die Abfrage verwenden, bis dies nicht der Fall ist. Manchmal führt eine neue Version des MySQL-Servers zu einem anderen Ergebnis. Manchmal ist es eine wachsende Tabelle, die das Problem verursacht. Was schief gehen kann, wird schief gehen und wenn Sie es nicht erwarten. Das nennt man Murphys Gesetz .

Das MySQL-Team hat daran gearbeitet, dass Entwickler diesen Fehler noch schwieriger machen können. Neuere Versionen von MySQL in der 5.7-Sequenz haben ein sql_mode Flag namens ONLY_FULL_GROUP_BY . Wenn dieses Flag gesetzt ist, gibt der MySQL-Server den Fehler 1055 zurück und lehnt die Ausführung dieser Art von Abfrage ab.

Missbrauch von GROUP BY mit SELECT * und deren Behebung.

Manchmal sieht eine Abfrage mit einem * in der SELECT Klausel so aus.

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

Eine solche Abfrage muss ONLY_FULL_GROUP_BY werden, um den Standard ONLY_FULL_GROUP_BY erfüllen.

Dazu benötigen wir eine Unterabfrage, die GROUP BY korrekt verwendet, um den Wert number_of_uses für jede item_id . Diese Unterabfrage ist kurz und süß, weil es nur auf suchen braucht uses Tabelle.

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

Dann können wir diese Unterabfrage mit der Join - item - Tabelle.

 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

Dadurch kann die GROUP BY Klausel einfach und korrekt sein, und wir können auch den * -Spezifizierer verwenden.

Hinweis: Trotzdem vermeiden kluge Entwickler die Verwendung des * -Spezifizierers auf jeden Fall. Es ist normalerweise besser, die gewünschten Spalten in einer Abfrage aufzulisten.

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

Zeigt die Zeilen in einer Tabelle mit dem Namen item , die Anzahl der zugehörigen Zeilen und einen der Werte in der zugehörigen Tabelle mit dem Namen uses .

Sie können sich diese ANY_VALUE() Funktion als eine Art Aggregatfunktion ANY_VALUE() . Anstatt einen Zähler, eine Summe oder ein Maximum zurückzugeben, weist es den MySQL-Server an, beliebig einen Wert aus der betreffenden Gruppe auszuwählen. Es ist eine Möglichkeit, den Fehler 1055 zu umgehen.

Seien Sie vorsichtig, wenn Sie ANY_VALUE() in Abfragen in Produktionsanwendungen verwenden.

Es sollte wirklich SURPRISE_ME() heißen. Es gibt den Wert einer Zeile in der GROUP BY-Gruppe zurück. Welche Zeile zurückgegeben wird, ist unbestimmt. Das heißt, es liegt vollständig am MySQL-Server. Formal gibt es einen unvorhersehbaren Wert zurück.

Der Server wählt keinen zufälligen Wert, der ist schlimmer. Bei jeder Ausführung der Abfrage wird derselbe Wert zurückgegeben, bis dies nicht der Fall ist. Sie kann sich ändern oder nicht, wenn eine Tabelle wächst oder schrumpft oder wenn der Server mehr oder weniger RAM hat oder wenn sich die Serverversion ändert oder wenn der Mars sich im Hintergrund befindet (was immer dies bedeutet) oder ohne Grund.

Du wurdest gewarnt.