Zaktualizowałem mój system i zainstalowałem MySql 5.7.9 z php dla aplikacji internetowej, nad którą pracuję. Mam zapytanie, które jest tworzone dynamicznie i kiedy jest uruchamiane w starszych wersjach MySql, działa dobrze. Od czasu aktualizacji do wersji 5.7 dostaję ten błąd:
Wyrażenie #1 z listy SELECT nie jest w klauzuli GROUP BY i zawiera. niezagregowaną kolumnę 'support_desk.mod_users_groups.group_id', która jest nie jest funkcjonalnie zależna od kolumn w klauzuli GROUP BY; jest to niekompatybilne z sql_mode=only_full_group_by
Zwróć uwagę na stronę Manual dla Mysql 5.7 na temat Tryby SQL serwera.
To jest zapytanie, które sprawia mi problemy:
SELECT mod_users_groups.group_id AS 'value',
group_name AS 'text'
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id
WHERE mod_users_groups.active = 1
AND mod_users_groups.department_id = 1
AND mod_users_groups.manage_work_orders = 1
AND group_name != 'root'
AND group_name != 'superuser'
GROUP BY group_name
HAVING COUNT(`user_id`) > 0
ORDER BY group_name
Poszperałem trochę w googlach na ten temat, ale nie rozumiem only_full_group_by
na tyle, aby dowiedzieć się, co muszę zrobić, aby naprawić zapytanie. Czy mogę po prostu wyłączyć opcję only_full_group_by
, czy jest coś jeszcze, co muszę zrobić?
Daj mi znać, jeśli potrzebujesz więcej informacji.
Możesz spróbować wyłączyć ustawienie only_full_group_by
wykonując następujące czynności:
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
MySQL 8 nie akceptuje NO_AUTO_CREATE_USER
więc to musi zostać usunięte.
Po prostu dodałbym group_id
do GROUP BY
.
Podczas SELECT
ing kolumny, która nie jest częścią GROUP BY
może być wiele wartości dla tej kolumny w grupach, ale będzie tylko miejsce na pojedynczą wartość w wynikach. Tak więc, baza danych zwykle musi być poinformowana dokładnie jak zrobić z tych wielu wartości jedną wartość. Zazwyczaj robi się to za pomocą funkcji agregujących takich jak COUNT()
, SUM()
, MAX()
itd. Mówię zwykle, ponieważ większość innych popularnych systemów baz danych nalega na to. Jednakże, w MySQL przed wersją 5.7 domyślne zachowanie było bardziej wyrozumiałe, ponieważ nie będzie narzekać, a następnie arbitralnie wybierać dowolną wartość! Posiada również funkcję ANY_VALUE()
, która może być użyta jako inne rozwiązanie tego problemu, jeśli naprawdę potrzebujesz takiego samego zachowania jak poprzednio. Ta elastyczność wiąże się z kosztami, ponieważ jest niedeterministyczna, więc nie polecam jej, chyba że masz bardzo dobry powód, aby jej potrzebować. MySQL włącza teraz ustawienie only_full_group_by
domyślnie z dobrych powodów, więc najlepiej jest się do tego przyzwyczaić i sprawić, aby twoje zapytania były zgodne z tym.
Więc dlaczego moja prosta odpowiedź powyżej? Przyjąłem kilka założeń:
group_id
jest unikalny. Wydaje się rozsądne, w końcu jest to 'ID'.
group_name
jest również unikalny. To może nie być takie rozsądne założenie. Jeśli tak nie jest i masz kilka zduplikowanych group_names
, a następnie podążasz za moją radą, aby dodać group_id
do GROUP BY
, może się okazać, że teraz otrzymasz więcej wyników niż wcześniej, ponieważ grupy o tej samej nazwie będą teraz miały oddzielne wiersze w wynikach. Według mnie, byłoby to lepsze niż posiadanie tych zduplikowanych grup ukrytych, ponieważ baza danych cicho wybrała wartość arbitralnie!
Dobrą praktyką jest również kwalifikowanie wszystkich kolumn za pomocą ich nazw tabel lub aliasów, gdy w grę wchodzi więcej niż jedna tabela...
SELECT
g.group_id AS 'value',
g.group_name AS 'text'
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id
WHERE g.active = 1
AND g.department_id = 1
AND g.manage_work_orders = 1
AND g.group_name != 'root'
AND g.group_name != 'superuser'
GROUP BY
g.group_name,
g.group_id
HAVING COUNT(d.user_id) > 0
ORDER BY g.group_name
Jeśli nie chcesz wprowadzać żadnych zmian w swoim obecnym zapytaniu, wykonaj poniższe kroki.
vagrant ssh do twojej skrzynki
Wpisz: sudo vim /etc/mysql/my.cnf
.
Przewiń do końca pliku i wpisz A
aby wejść w tryb wstawiania
Skopiuj i wklej
[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Wpisz esc
aby wyjść z trybu wprowadzania danych
Wpisz :wq
, aby zapisać i zamknąć vima.
Wpisz sudo service mysql restart
aby zrestartować MySQL.