Ich habe mein System aktualisiert und MySql 5.7.9 mit PHP für eine Webanwendung installiert, an der ich arbeite. Ich habe eine Abfrage, die dynamisch erstellt wird, und wenn sie in älteren Versionen von MySql ausgeführt wird, funktioniert sie einwandfrei. Seit dem Upgrade auf 5.7 erhalte ich folgende Fehlermeldung:
Ausdruck Nr. 1 der SELECT-Liste befindet sich nicht in der GROUP BY-Klausel und enthält die nicht aggregierte Spalte 'support_desk.mod_users_groups.group_id', die funktional nicht von den Spalten in der GROUP BY-Klausel abhängig ist. Dies ist nicht kompatibel mit sql_mode = only_full_group_by
Beachten Sie die Handbuchseite für MySQL 5.7 zum Thema Server SQL Modes .
Dies ist die Abfrage, die mir Probleme bereitet:
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
Ich habe ein bisschen über das Problem gegoogelt, aber ich verstehe only_full_group_by
nicht genug, um herauszufinden, was ich tun muss, um die Abfrage zu beheben. Kann ich die Option only_full_group_by
einfach ausschalten oder muss ich noch etwas tun?
Lassen Sie mich wissen, wenn Sie weitere Informationen benötigen.
Ich würde einfach group_id
zum GROUP BY
hinzufügen.
Wenn Sie eine Spalte SELECT
verwenden, die nicht Teil des GROUP BY
ist, können mehrere Werte für diese Spalte in den Gruppen vorhanden sein, in den Ergebnissen ist jedoch nur Platz für einen einzelnen Wert. Der Datenbank normalerweise muss also genau mitgeteilt werden, wie diese mehreren Werte in einen Wert umgewandelt werden sollen. In der Regel geschieht dies mit einer Aggregatfunktion wie COUNT()
, SUM()
, MAX()
etc ... Ich sage normalerweise, weil die meisten anderen gängigen Datenbanksysteme darauf bestehen . In MySQL vor Version 5.7 war das Standardverhalten jedoch nachsichtiger, da es sich nicht beschwert und dann willkürlich einen beliebigen Wert auswählt. Es hat auch eine ANY_VALUE()
-Funktion, die als eine andere Lösung für diese Frage verwendet werden kann, wenn Sie wirklich dasselbe Verhalten wie zuvor benötigen. Diese Flexibilität ist mit Kosten verbunden, da sie nicht deterministisch ist. Daher würde ich sie nur empfehlen, wenn Sie einen sehr guten Grund dafür haben, sie zu benötigen. MySQL aktiviert jetzt aus guten Gründen standardmäßig die Einstellung only_full_group_by
. Es ist daher am besten, sich daran zu gewöhnen und Ihre Abfragen damit abzustimmen.
Warum also meine einfache Antwort oben? Ich habe ein paar Annahmen gemacht:
1) Der group_id
ist einzigartig. Scheint vernünftig, es ist doch ein "Ausweis".
2) Der group_name
ist ebenfalls einzigartig. Dies ist möglicherweise keine so vernünftige Annahme. Wenn dies nicht der Fall ist und Sie ein Duplikat von group_names
haben und Sie dann meinem Rat folgen, group_id
zum GROUP BY
hinzuzufügen, stellen Sie möglicherweise fest, dass Sie jetzt mehr Ergebnisse als zuvor erhalten, weil das Gruppen mit demselben Namen haben jetzt separate Zeilen in den Ergebnissen. Für mich wäre dies besser, als wenn diese doppelten Gruppen ausgeblendet wären, da die Datenbank im Hintergrund einen Wert willkürlich ausgewählt hat!
Es empfiehlt sich auch, alle Spalten mit ihrem Tabellennamen oder Alias zu qualifizieren, wenn mehr als eine Tabelle beteiligt ist ...
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
Sie können versuchen, die Einstellung only_full_group_by
zu deaktivieren, indem Sie Folgendes ausführen:
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 akzeptiert NO_AUTO_CREATE_USER
nicht und muss daher entfernt werden.
sie können die Warnmeldung deaktivieren, wie in den anderen Antworten erläutert, oder Sie können verstehen , was passiert , und sie beheben.
Ab MySQL 5.7.5 enthält der Standard-SQL-Modus ONLY_FULL_GROUP_BY . Wenn Sie Zeilen gruppieren und dann etwas aus diesen Gruppen auswählen, müssen Sie explizit sagen Aus welcher Zeile soll diese Auswahl getroffen werden?
Mysql muss wissen, welche Zeile in der Gruppe Sie suchen, was Ihnen zwei Möglichkeiten bietet
group by rect.color, rect.value
hinzufügen. Dies kann in einigen Fällen auch der Fall sein. Andernfalls werden doppelte Ergebnisse mit derselben Farbe zurückgegeben, die Sie möglicherweise nicht möchtenAVG()
MIN()
MAX()
vollständige ListeANY_VALUE()
verwenden, wenn Sie sicher sind, dass alle Ergebnisse in der Gruppe gleich sind. docWenn Sie keine Änderungen an Ihrer aktuellen Abfrage vornehmen möchten, führen Sie die folgenden Schritte aus:
Sudo vim /etc/mysql/my.cnf
A
ein, um den Einfügemodus zu aktivierenKopieren und Einfügen
[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
Geben Sie esc
ein, um den Eingabemodus zu verlassen
:wq
ein, um vim zu speichern und zu schließen.Sudo service mysql restart
ein, um MySQL neu zu starten. Verwenden Sie ANY_VALUE()
, um auf die nicht aggregierte Spalte zu verweisen.
From MySQL 5.7 docs :
Sie können den gleichen Effekt erzielen, ohne
ONLY_FULL_GROUP_BY
zu deaktivieren, indem SieANY_VALUE()
verwenden, um auf die nicht aggregierte Spalte zu verweisen....
Diese Abfrage ist möglicherweise bei aktiviertem
ONLY_FULL_GROUP_BY
ungültig, da die nicht aggregierte Adressenspalte in der Auswahlliste in derGROUP BY
-Klausel nicht benannt ist:SELECT name, address, MAX(age) FROM t GROUP BY name;
...
Wenn Sie wissen, dass für einen bestimmten Datensatz jeder Namenswert tatsächlich den Adresswert eindeutig bestimmt, ist die Adresse praktisch vom Namen abhängig. Um MySQL anzuweisen, die Abfrage zu akzeptieren, können Sie die Funktion
ANY_VALUE()
verwenden:SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
Ich werde versuchen, Ihnen zu erklären, worum es bei diesem Fehler geht.
Ab MySQL 5.7.5 ist die Option ONLY_FULL_GROUP_BY
standardmäßig aktiviert.
Daher laut Standard SQL92 und früher:
erlaubt keine Abfragen, bei denen die Auswahlliste, die HAVING-Bedingung oder die ORDER BY-Liste auf nicht aggregierte Spalten verweisen, die weder in der GROUP BY-Klausel benannt sind noch funktional von (eindeutig durch) GROUP BY-Spalten abhängen
Also zum Beispiel:
SELECT * FROM `users` GROUP BY `name`;
Sie erhalten eine Fehlermeldung, nachdem Sie die obige Abfrage ausgeführt haben.
# 1055 - Ausdruck Nr. 1 der SELECT-Liste befindet sich nicht in der GROUP BY-Klausel und enthält die nicht aggregierte Spalte 'testsite.user.id', die funktional nicht von den Spalten in der GROUP BY-Klausel abhängig ist. Dies ist nicht kompatibel mit sql_mode = only_full_group_by
Warum?
Da MySQL nicht genau versteht, welche bestimmten Werte aus gruppierten Datensätzen abgerufen werden sollen, und das ist der Punkt.
I.E. Nehmen wir an, Sie haben diese Datensätze in Ihrer users
-Tabelle:
Und Sie werden eine ungültige Abfrage ausführen, die oben angezeigt wurde.
Und Sie werden die oben gezeigte Fehlermeldung erhalten, weil es 3 Datensätze mit dem Namen John
gibt und es Nice ist, aber alle von ihnen haben unterschiedliche email
Feldwerte.
MySQL versteht also einfach nicht, welche von ihnen in den resultierenden gruppierten Datensätzen zurückgegeben werden sollen.
Sie können dieses Problem beheben, indem Sie einfach Ihre Abfrage wie folgt ändern:
SELECT `name` FROM `users` GROUP BY `name`
Möglicherweise möchten Sie auch weitere Felder zum SELECT-Abschnitt hinzufügen, können dies jedoch nicht, wenn sie nicht aggregiert sind, Sie jedoch eine Krücke verwenden können (dies wird jedoch dringend empfohlen):
SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`
Nun fragen Sie sich vielleicht, warum die Verwendung von ANY_VALUE
nicht unbedingt empfohlen wird.
Da MySQL nicht genau weiß, welchen Wert gruppierte Datensätze abrufen sollen, und Sie diese Funktion verwenden, fordern Sie sie auf, einen der Datensätze abzurufen (in diesem Fall wurde die E-Mail des ersten Datensatzes mit dem Namen = John abgerufen). .
Genau, ich kann mir keine Ideen einfallen lassen, warum dieses Verhalten existieren soll.
Bitte, wenn Sie mich nicht verstehen, lesen Sie mehr darüber, wie das Gruppieren in MySQL funktioniert. Es ist sehr einfach.
Und zum Schluss noch eine einfache, aber gültige Abfrage.
Wenn Sie die Gesamtzahl der Benutzer nach dem verfügbaren Alter abfragen möchten, möchten Sie diese Abfrage möglicherweise aufschreiben
SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;
Welches ist voll gültig, nach MySQL-Regeln.
Und so weiter.
Es ist wichtig zu verstehen, was genau das Problem ist und erst dann die Lösung aufzuschreiben.
Ich verwende Laravel 5.3, mysql 5.7.12, auf laravel Homestead (0.5.0, glaube ich)
Auch nach der expliziten Einstellung der Bearbeitung von /etc/mysql/my.cnf
, um Folgendes widerzuspiegeln:
[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
Ich habe immer noch den Fehler erhalten.
Ich musste config/database.php
von true
in false
ändern:
'mysql' => [
'strict' => false, //behave like 5.6
//'strict' => true //behave like 5.7
],
Weitere Lektüre:
https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-Homesteadhttps://mattstauffer.co/blog/strict-mode- und-andere-mysql-anpassungen-in-laravel-5-2
Wenn Sie wamp 3.0.6 oder eine andere höhere Version als stable 2.5 verwenden, könnte dieses Problem auftreten. Zunächst liegt das Problem bei sql. Sie müssen die Felder entsprechend benennen. aber es gibt einen anderen Weg, wie Sie es lösen können. Klicken Sie auf das grüne Wamp-Symbol. mysql-> mysql settings-> sql_mode-> none oder über die Konsole können Sie die Standardwerte ändern.
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';
Hinzufügen von Zeilen (siehe unten) in der Datei: /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
Arbeit gut für mich. Serverversion: 5.7.18-0ubuntu0.16.04.1 - (Ubuntu)
Für Mac:
1. Kopieren Sie die Standarddatei my-default.cnf nach /etc/my.cnf
Sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf
2.Ändern Sie sql_mode in my.cnf mit Ihrem bevorzugten Editor und stellen Sie dies ein
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.Starten Sie den MySQL-Server neu.
mysql.server restart
Für localhost/wampserver 3 können wir sql-mode = user_mode setzen, um diesen Fehler zu entfernen:
click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode
starte dann wamp oder Apache neu
Dies hat mir geholfen, das gesamte Problem zu verstehen:
Und im Folgenden noch ein Beispiel für eine problematische Abfrage.
Problematisch:
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 = #
Gelöst durch Hinzufügen dieses am Ende:
GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress
Hinweis: In der Fehlermeldung in PHP erfahren Sie, wo das Problem liegt.
Beispiel:
MySQL-Abfragefehler 1140: In einer aggregierten Abfrage ohne GROUP BY enthält Ausdruck Nr. 4 der SELECT-Liste die nicht aggregierte Spalte 'db.gameplay.timestamp'. Dies ist nicht kompatibel mit sql_mode = only_full_group_by - Abfrage: SELECT COUNT (*) als Versuche, SUM (verstrichen) als verstrichene Summe, Benutzer-ID, Zeitstempel, Frage-ID, Antwort-ID, SUM (korrekt) als korrekt, verstrichen, IP-Adresse FROM gameplay WHERE Zeitstempel> = DATE_SUB (JETZT (), INTERVALL 1 TAG) UND Benutzer-ID = 1
In diesem Fall fehlte der Ausdruck 4 in GROUP BY.
Sie können unique index
zu group_id
hinzufügen; wenn Sie sicher sind, dass group_id
eindeutig ist.
Es kann Ihren Fall lösen, ohne die Abfrage zu ändern.
Eine späte Antwort, die aber noch nicht in den Antworten erwähnt wurde. Vielleicht sollte es die bereits vorhandenen umfassenden Antworten vervollständigen. Zumindest löste es meinen Fall, als ich eine Tabelle mit zu vielen Feldern aufteilen musste.
Wenn Sie diesen Fehler mit Symfony using Doctrine Query Builder haben und dieser Fehler durch orderBy verursacht wird:
Achten Sie auf select
die Spalte, die Sie groupBy
möchten, und verwenden Sie addGroupBy
anstelle von groupBy
:
$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');
Funktioniert auf Symfony -
Entschuldigung, dass Sie nicht Ihr genaues SQL verwendet haben
Ich habe diese Abfrage verwendet, um die Mysql-Warnung zu überwinden.
SELECT count(*) AS cnt, `regions_id`
FROM regionables
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1
notiere den Schlüssel für mich
count(*) AS cnt