MySQL
Fel 1055: ONLY_FULL_GROUP_BY: något finns inte i GROUP BY-klausulen ...

Introduktion

Anmärkningar

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'

Använda och missbruka 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

kommer att visa raderna i en tabell som heter item och visa antalet relaterade rader i en tabell som heter uses . Detta fungerar bra, men tyvärr är det inte standard SQL-92.

Varför inte? eftersom SELECT klausulen (och ORDER BY klausulen) i frågor om GROUP BY måste innehålla kolumner som är

1. nämns i GROUP BY klausulen, eller
2. aggregerade funktioner som COUNT() , MIN() och liknande.

I exemplet SELECT klausulen nämns item.name , en kolumn som inte uppfyller något av dessa kriterier. MySQL 5.6 och tidigare avvisar denna fråga om SQL-läget innehåller ONLY_FULL_GROUP_BY .

Exempelfrågan kan göras för att uppfylla SQL-92-standarden genom att ändra GROUP BY klausulen, som den här.

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

Den senare SQL-99-standarden tillåter ett SELECT uttalande att utelämna oaggregerade kolumner från gruppnyckeln om DBMS kan bevisa ett funktionellt beroende mellan dem och gruppnyckelkolumnerna. Eftersom item.name är funktionellt beroende av item.item_id , är det första exemplet giltigt SQL-99. MySQL fick en funktionell beroende-prover i version 5.7. Det ursprungliga exemplet fungerar under ONLY_FULL_GROUP_BY .

Missbruk av GROUP BY för att returnera oförutsägbara resultat: Murphys lag

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

kommer att visa raderna i en tabell som heter objekt och visa antalet relaterade rader i en tabell som heter användningar. Det kommer också att visa värdet på en kolumn som heter uses.category .

Denna fråga fungerar i MySQL (innan flaggan ONLY_FULL_GROUP_BY dök upp). Den använder MySQLs icke-standardförlängning till GROUP BY .

Men frågan har ett problem: om flera rader i uses matchar ON tillstånd i JOIN klausul, MySQL returnerar category kolumnen från bara en av dessa rader. Vilken rad? Författaren till frågan och användaren av applikationen får inte veta det i förväg. Formellt sett är det oförutsägbart : MySQL kan returnera valfritt värde.

Oförutsägbar är som slumpmässig, med en betydande skillnad. Man kan förvänta sig att ett slumpmässigt val ändras då och då. Om ett val var slumpmässigt kan du därför upptäcka det under felsökning eller testning. Det oförutsägbara resultatet är sämre: MySQL returnerar samma resultat varje gång du använder frågan tills den inte gör det. Ibland är det en ny version av MySQL-servern som orsakar ett annat resultat. Ibland är det ett växande bord som orsakar problemet. Vad som kan gå fel, kommer att gå fel och när du inte förväntar dig det. Det heter Murphys lag .

MySQL-teamet har arbetat för att göra det svårare för utvecklare att göra detta misstag. Nyare versioner av MySQL i 5,7-sekvensen har en sql_mode flagga som heter ONLY_FULL_GROUP_BY . När den flaggan är inställd returnerar MySQL-servern 1055-felet och vägrar att köra den här typen av fråga.

Missbruk av GROUP BY med SELECT *, och hur du åtgärdar det.

Ibland ser en fråga ut så här med en * i SELECT klausulen.

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

En sådan fråga måste återinföras för att följa standarden ONLY_FULL_GROUP_BY .

För att göra detta, behöver vi en underkälla som använder GROUP BY korrekt för att returnera värdet number_of_uses för varje item_id . Denna delfråga är kort och koncist, eftersom det behöver bara titta på uses bordet.

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

Sedan kan vi gå att delfråga med item tabellen.

 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

Detta gör att GROUP BY klausulen är enkel och korrekt och gör att vi också kan använda * -specifikatorn.

Obs: ändå undviker kloka utvecklare att använda * -specifikationen i alla fall. Det är vanligtvis bättre att lista de kolumner du vill ha i en fråga.

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

visar raderna i en tabell som heter item , räkningen av relaterade rader och ett av värdena i den relaterade tabellen som kallas uses .

Du kan tänka på denna ANY_VALUE() -funktion som en konstig typ av en sammanlagd funktion. Istället för att returnera en räkning, summa eller max, instruerar den MySQL-servern att valfritt välja ett värde från den aktuella gruppen. Det är ett sätt att arbeta kring Error 1055.

Var försiktig när du använder ANY_VALUE() i frågor i produktionsapplikationer.

Det borde verkligen kallas SURPRISE_ME() . Det returnerar värdet på en rad i gruppen GROUP BY. Vilken rad den returnerar är obestämd. Det betyder att det helt är upp till MySQL-servern. Formellt ger det ett oförutsägbart värde.

Servern väljer inte ett slumpmässigt värde, det är sämre än så. Det returnerar samma värde varje gång du kör frågan tills den inte gör det. Det kan ändras eller inte, när en tabell växer eller krymper, eller när servern har mer eller mindre RAM, eller när servern versionen ändras, eller när Mars är i retrograd (vad det än betyder), eller utan någon anledning alls.

Du har blivit varnad.