Błąd związany tylko z pełną grupą przez podczas wykonywania zapytania w MySql

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 trzeba to usunąć.

Możesz wyłączyć komunikat ostrzegawczy, jak wyjaśniono w innych odpowiedziach lub zrozumieć co się dzieje i naprawić go.

Począwszy od MySQL 5.7.5, domyślny tryb SQL zawiera ONLY_FULL_GROUP_BY co oznacza, że gdy grupujesz wiersze, a następnie wybierasz coś z tych grup, musisz jawnie powiedzieć, z którego wiersza należy dokonać tego wyboru.

Mysql musi wiedzieć, którego wiersza w grupie szukasz, co daje dwie opcje

• Możesz również dodać żądanÄ… kolumnÄ™ do instrukcji group group by rect.color, rect.value, która może być tym, czego chcesz w niektórych przypadkach, w przeciwnym razie zwróci zduplikowane wyniki o tym samym kolorze, którego możesz nie chcieć
• możesz również użyć funkcji agregujÄ…cych mysql, aby wskazać, którego wiersza szukasz w grupach takich jak AVG() MIN() MAX() complete list
• i wreszcie możesz użyć ANY_VALUE() jeÅ›li jesteÅ› pewien, że wszystkie wyniki wewnÄ…trz grupy sÄ… takie same. doc

Jeśli nie chcesz wprowadzać żadnych zmian w bieżącym zapytaniu, wykonaj poniższe kroki -

1. vagrant SSH into your box
2. Typ: sudo vim /etc/mysql/my.cnf
3. przewiÅ„ do doÅ‚u pliku i wpisz A, aby przejść do trybu wstawiania

4. Kopiuj 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
   

5. Type esc to exit input mode

6. wpisz :wq Aby zapisać i zamknąć Vima.
7. wpisz sudo service mysql restart aby ponownie uruchomić MySQL.

Użyj ANY_VALUE(), aby odnieść siÄ™ do kolumny nieagregowanej.

SELECT name,           address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works

From MySQL 5.7 docs:

Możesz osiÄ…gnąć ten sam efekt bez wyÅ‚Ä…czania ONLY_FULL_GROUP_BY używajÄ…c ANY_VALUE() w odniesieniu do kolumny nieagregowanej.

...

To zapytanie może być nieprawidÅ‚owe z wÅ‚Ä…czonÄ… ONLY_FULL_GROUP_BY, ponieważ kolumna adresu nieagregowanego na liÅ›cie select nie jest nazwana w klauzuli GROUP BY:

SELECT name, address, MAX(age) FROM t GROUP BY name;

...

JeÅ›li wiesz, że, dla biorÄ…c pod uwagÄ™ zbiór danych, każda wartość nazwy w rzeczywistoÅ›ci jednoznacznie okreÅ›la wartość adresu, adres jest efektywnie funkcjonalnie zależny od nazwy. Aby powiedzieć MySQL, aby zaakceptowaÅ‚ zapytanie, możesz użyć funkcji ANY_VALUE():

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Postaram się wyjaśnić, o co chodzi z tym błędem.
PoczÄ…wszy od MySQL 5.7.5, opcja ONLY_FULL_GROUP_BY jest domyÅ›lnie wÅ‚Ä…czona.
Tak więc, zgodnie ze standardem SQL92 i wcześniejszymi:

Nie pozwala na zapytania, dla których lista select, mając warunek, lub kolejność według listy odnoszą się do nieagregowanych kolumn, które nie są nazwane w grupie przez klauzulę nor są funkcjonalnie zależne od (jednoznacznie określona przez) grupa według kolumn

(Czytaj więcej w docs )

Więc, na przykład:

SELECT * FROM `users` GROUP BY `name`;

Po wykonaniu powyższego zapytania otrzymasz komunikat o błędzie.

# 1055-wyrażenie # 1 z listy SELECT nie znajduje się w klauzuli GROUP BY i zawiera nieagregowaną kolumnę 'testsite.user.id" co nie jest funkcjonalnie zależny od kolumn w klauzuli GROUP BY; jest to niezgodny z sql_mode=only_full_group_by

Powiedzmy, że masz te rekordy w tabeli users:

I wykonasz niepoprawne zapytanie pokazane powyżej.
I pojawi siÄ™ bÅ‚Ä…d pokazany powyżej, ponieważ istniejÄ… 3 Rekordy o nazwie John, i jest to miÅ‚e, ale wszystkie z nich majÄ… różne wartoÅ›ci pola email.
Tak więc MySQL po prostu nie rozumie, który z nich zwrócić w wynikowym rekordzie zgrupowanym.

Możesz rozwiązać ten problem, po prostu zmiana zapytania w ten sposób:

SELECT `name` FROM `users` GROUP BY `name`

Możesz również dodać więcej pól do sekcji SELECT, ale nie możesz tego zrobić, jeśli nie są one zagregowane, ale istnieje crutch, którego możesz użyć (ale wysoce nie polecany):

SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`

Teraz możesz zapytać, dlaczego używanie ANY_VALUE jest wysoce nie zalecane?
Ponieważ MySQL nie do końca wie, jaką wartość zgrupowanych rekordów pobierać, A korzystając z tej funkcji, prosi się go o pobranie dowolnego z nich (w tym przypadku, e-mail pierwszego rekordu z name = John został pobrany).
Dokładnie nie mogę wymyślić żadnego pomysłu, dlaczego chcesz, aby takie zachowanie istniało.

Proszę, Jeśli mnie nie rozumiesz, przeczytaj więcej o tym, jak grupowanie w MySQL działa, jest to bardzo proste.

I na koniec, oto jeszcze jedno proste, ale poprawne zapytanie.
Jeśli chcesz zapytać całkowitą liczbę użytkowników według dostępnego wieku, możesz zapisać to zapytanie

SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;

Który jest w pełni poprawny, zgodnie z MySQL Zasady.
I tak dalej.

Ważne jest, aby zrozumieć, na czym dokładnie polega problem i dopiero wtedy zapisać rozwiązanie.

Używam Laravel 5.3, mysql 5.7.12, na Laravel homestead (0.5.0, I believe)

Nawet po jawnym ustawieniu edycji /etc/mysql/my.cnf na odzwierciedlenie:

[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

MusiaÅ‚em zmienić config/database.php z true na false:

    'mysql' => [
        'strict' => false, //behave like 5.6
        //'strict' => true //behave like 5.7
    ], 

Czytaj dalej:

Https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2

Jeśli używasz wamp 3.0.6 lub dowolnej wyższej wersji innej niż stabilna 2.5, możesz napotkać ten problem, po pierwsze problem dotyczy sql . musisz odpowiednio nazwać pola. ale jest inny sposób, w jaki możesz to rozwiązać. kliknij na zieloną ikonę wamp. mysql - > mysql settings - > sql_mode - > none. lub z konsoli można zmienić wartości domyślne.

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';

Przejdź do mysql lub phpmyadmin i wybierz bazę danych następnie po prostu wykonaj to zapytanie i będzie działać. To działa dobrze dla mnie.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Dodanie linii (wzmianka poniżej) w pliku: / etc / mysql / my.cnf

[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

Dla mac:

1.Skopiuj domyślne my-default.cnf do / etc / my.cnf

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

2.Zmień sql_mode w moim.cnf używając swojego ulubionego edytora i ustaw go na to

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

3.Uruchom ponownie serwer MySQL.

mysql.server restart

To właśnie pomogło mi zrozumieć cały problem:

1. https://stackoverflow.com/a/20074634/1066234
2. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

I w kolejnym przykładzie problematycznego zapytania.

Problematyczne:

SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
                        WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
                        AND cookieid = #

Rozwiązano dodając to do końca:

  GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress

Uwaga: Zobacz komunikat o błędzie w PHP, mówi, gdzie problem kłamstwa.

Przykład:

Błąd zapytania MySQL 1140: w zapytaniu zbiorczym bez grupy BY wyrażenie # 4 z listy SELECT zawiera nieagregowaną kolumnę ' db.rozgrywki.timestamp'; jest to niezgodne z sql_mode=only_full_group_by - Query: SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM (correct) as correct, elapsed, ipaddress FROM gameplay Gdzie timestamp > = DATE_SUB (NOW (), interwał 1 dzień) AND userid = 1

W tym przypadku, wyrażenie #4 brakowało w grupie BY.

Dla localhost / wampserver 3 możemy ustawić SQL-mode = user_mode aby usunąć ten błąd:

click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode

Następnie uruchom ponownie wamp lub apache

Możesz dodać unique index do group_id; jeÅ›li jesteÅ› pewien, że group_id jest unikalna.

Może rozwiązać Twoją sprawę bez modyfikowania zapytania .

Jeśli masz ten błąd z Symfony używając doctrine query builder , i jeśli ten błąd jest spowodowany przez orderBy:

Zwróć uwagÄ™ na select kolumnÄ™, którÄ… chcesz groupBy, i użyj addGroupBy zamiast groupBy:

$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');

Działa na Symfony3 -

Przepraszamy za nieużywanie dokładnego SQL

Użyłem tego zapytania, aby pokonać Ostrzeżenie Mysql.

SELECT count(*) AS cnt, `regions_id`
FROM regionables 
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1

Zwróć uwagę, że klucz do mnie jest

count(*) AS cnt

Musiałem edytować poniższy plik na moim Ubuntu 18.04:

/etc/mysql/mysql.conf.d/mysqld.cnf

Z

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

I

sudo service mysql restart

Powyższa odpowiedź jest dobra, ale jeśli masz problemy z uruchamianiem zapytań w procedurach przechowywanych po naprawieniu swojego my.plik cnf, a następnie spróbuj ponownie załadować SPs.

Podejrzewam, że MySQL musiał skompilować SPS z domyślnym only_full_group_by ustawionym pierwotnie. Dlatego, nawet kiedy zmieniłem swoje.cnf i ponownie uruchomiony mysqld nie miał wpływu na SPs, i nadal nie udało im się z " SELECT list nie jest w klauzuli GROUP BY i zawiera nieagregowaną kolumnę ... który nie jest funkcjonalny zależne od kolumn w klauzuli GROUP BY; jest to niezgodne z sql_mode=only_full_group_by".

Ponowne załadowanie SPs musiało spowodować ich rekompilację z wyłączonym only_full_group_by. Po tym, wydają się działać zgodnie z oczekiwaniami.