MySQL
Fout 1055: ONLY_FULL_GROUP_BY: er staat iets niet in de GROUP BY-clausule ...
Zoeken…
Invoering
GROUP BY te GROUP BY , of op zijn minst om het moeilijker te maken voor ontwikkelaars van het schrijven van zoekopdrachten.
Opmerkingen
MySQL bevat al geruime tijd een beruchte niet-standaard extensie van GROUP BY , die vreemd gedrag mogelijk maakt in naam van efficiëntie. Met deze extensie hebben talloze ontwikkelaars over de hele wereld GROUP BY in productiecode kunnen gebruiken zonder volledig te begrijpen wat ze aan het doen waren.
Het is met name een slecht idee om SELECT * te gebruiken in een GROUP BY query, omdat een standaard GROUP BY clausule het opsommen van de kolommen vereist. Veel ontwikkelaars hebben dat helaas gedaan.
Lees dit. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
Het MySQL-team heeft geprobeerd dit probleem op te lossen zonder de productiecode te verpesten. Ze voegden een vlag sql_mode in 5.7.5 met de naam ONLY_FULL_GROUP_BY om standaardgedrag ONLY_FULL_GROUP_BY te dwingen. In een recente release hebben ze die vlag standaard ingeschakeld. Toen u uw lokale MySQL opwaardeerde naar 5.7.14, werd de vlag ingeschakeld en werkte uw productiecode, afhankelijk van de oude extensie, niet meer.
Als u recentelijk 1055-fouten krijgt, wat zijn uw keuzes?
- repareer de aanstootgevende SQL-vragen, of laat hun auteurs dat doen.
- terugdraaien naar een versie van MySQL-compatibel out-of-the-box met de applicatiesoftware die u gebruikt.
- wijzig de
sql_modevan uw server om de nieuw ingestelde modusONLY_FULL_GROUP_BY.
U kunt de modus wijzigen met een SET opdracht.
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'
zou het moeten doen als je het doet direct nadat je applicatie verbinding maakt met MySQL.
Of u kunt het init-bestand in uw MySQL-installatie vinden, de sql_mode= regel vinden en wijzigen om ONLY_FULL_GROUP_BY weg te laten en uw server opnieuw op te starten.
GROUP BY gebruiken en misbruiken
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
toont de rijen in een tabel genaamd item , en toont het aantal gerelateerde rijen in een tabel met de naam uses . Dit werkt goed, maar helaas is het geen standaard SQL-92.
Waarom niet? omdat de SELECT component (en de ORDER BY component) in GROUP BY query's kolommen moeten bevatten die dat zijn
- vermeld in de
GROUP BYclausule, of - geaggregeerde functies zoals
COUNT(),MIN(), en dergelijke.
De SELECT clausule van dit voorbeeld vermeldt item.name , een kolom die aan geen van deze criteria voldoet. MySQL 5.6 en eerder weigert deze zoekopdracht als de SQL-modus ONLY_FULL_GROUP_BY bevat.
Deze voorbeeldquery kan worden gemaakt om te voldoen aan de SQL-92-standaard door de clausule GROUP BY als volgt te wijzigen.
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
Met de latere SQL-99-standaard kan een SELECT instructie niet-samengevoegde kolommen uit de groepssleutel weglaten als het DBMS een functionele afhankelijkheid tussen deze en de groepssleutelkolommen kan aantonen. Omdat item.name functioneel afhankelijk is van item.item_id , is het eerste voorbeeld geldige SQL-99. MySQL kreeg een functionele afhankelijkheidsprover in versie 5.7. Het originele voorbeeld werkt onder ONLY_FULL_GROUP_BY .
GROUP BY misbruiken om onvoorspelbare resultaten terug te geven: de wet van Murphy
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
toont de rijen in een tabel genaamd item, en toont het aantal gerelateerde rijen in een tabel met de naam use. Het toont ook de waarde van een kolom met de naam uses.category .
Deze zoekopdracht werkt in MySQL (voordat de vlag ONLY_FULL_GROUP_BY verscheen). Het maakt gebruik van MySQL's niet-standaard extensie voor GROUP BY .
Maar de query heeft een probleem: als meerdere rijen in de uses overeenkomen met de ON voorwaarde in de clausule JOIN , retourneert MySQL de category uit slechts één van die rijen. Welke rij De schrijver van de zoekopdracht en de gebruiker van de toepassing, weten dat niet van tevoren. Formeel gezien is het onvoorspelbaar : MySQL kan elke gewenste waarde retourneren.
Onvoorspelbaar is als willekeurig, met één significant verschil. Je zou verwachten dat een willekeurige keuze van tijd tot tijd verandert. Als een keuze dus willekeurig was, kunt u deze tijdens het opsporen of testen detecteren. Het onvoorspelbare resultaat is slechter: MySQL retourneert hetzelfde resultaat telkens wanneer u de query gebruikt, totdat dit niet het geval is. Soms is het een nieuwe versie van de MySQL-server die een ander resultaat veroorzaakt. Soms is het een groeiende tafel die het probleem veroorzaakt. Wat er mis kan gaan, zal misgaan, en wanneer je het niet verwacht. Dat wordt de wet van Murphy genoemd.
Het MySQL-team heeft gewerkt om het moeilijker te maken voor ontwikkelaars om deze fout te maken. Nieuwere versies van MySQL in de volgorde 5.7 hebben een sql_mode vlag met de naam ONLY_FULL_GROUP_BY . Wanneer die vlag is ingesteld, retourneert de MySQL-server de 1055-fout en weigert dit soort query uit te voeren.
GROUP BY met SELECT * misbruiken en oplossen.
Soms ziet een query er zo uit, met een * in de SELECT clausule.
SELECT item.*, /* nonstandard */
COUNT(*) number_of_uses
FROM item
JOIN uses ON item.item_id, uses.item_id
GROUP BY item.item_id
Een dergelijke zoekopdracht moet opnieuw worden uitgevoerd om te voldoen aan de norm ONLY_FULL_GROUP_BY .
Om dit te doen, moeten we een subquery die gebruik maakt van GROUP BY correct op de terugkeer number_of_uses waarde voor elke item_id . Deze subquery is kort en krachtig, omdat deze alleen naar de uses hoeft te kijken.
SELECT item_id, COUNT(*) number_of_uses
FROM uses
GROUP BY item_id
Dan kunnen we meedoen dat subquery met de item tafel.
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
Hierdoor kan de GROUP BY clausule eenvoudig en correct zijn en kunnen we ook de * specifier gebruiken.
Opmerking: verstandige ontwikkelaars vermijden echter in ieder geval het gebruik van de * specifier. Het is meestal beter om de gewenste kolommen in een query te vermelden.
ELKE WAARDE()
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
toont de rijen in een tabel met de naam item , het aantal gerelateerde rijen en een van de waarden in de gerelateerde tabel met de naam uses .
Je kunt deze ANY_VALUE() -functie beschouwen als een vreemde, soort geaggregeerde functie. In plaats van een telling, som of maximum te retourneren, geeft het de MySQL-server de opdracht om willekeurig één waarde uit de betreffende groep te kiezen. Het is een manier om Fout 1055 te omzeilen.
Wees voorzichtig wanneer u ANY_VALUE() in query's in productietoepassingen.
Het zou echt SURPRISE_ME() moeten heten. Het retourneert de waarde van een rij in de groep GROUP BY. Welke rij het retourneert is onbepaald. Dat betekent dat het volledig aan de MySQL-server ligt. Formeel geeft het een onvoorspelbare waarde.
De server kiest geen willekeurige waarde, het is nog erger. Het retourneert dezelfde waarde telkens wanneer u de query uitvoert, totdat dit niet het geval is. Het kan veranderen of niet, wanneer een tabel groeit of krimpt, of wanneer de server meer of minder RAM heeft, of wanneer de serverversie verandert, of wanneer Mars in retrograde is (wat dat ook betekent), of helemaal zonder reden.
Je bent gewaarschuwd.