Lösung für das Problem, dass „order by“ in MySQL-Unterabfragen nicht wirksam ist

Durch Zufall entdeckte ich, dass eine SQL-Anweisung unterschiedliche Ergebnisse lieferte, wenn sie auf verschiedenen MySQL-Instanzen ausgeführt wurde.

Problembeschreibung

Erstellen Sie zwei Tabellen, die Produkttabelle product_tbl und die Produktoperationsdatensatztabelle product_operation_tbl, um das Geschäftsszenario zu simulieren. Die Struktur und die Daten sind wie folgt:

Als nächstes müssen Sie den letzten Änderungszeitpunkt aller Produkte mit der folgenden Anweisung abfragen:

Wählen Sie t1.id, t1.name, t2.product_id, t2.created_at aus product_tbl t1 left join (wählen Sie * aus product_operation_log_tbl, sortieren Sie nach created_at desc) t2 auf t1.id = t2.product_id, gruppieren Sie nach t1.id;

Anhand der Ergebnisse können wir erkennen, dass die Unterabfrage zunächst alle Datensätze in „product_operation_log_tbl“ in umgekehrter Reihenfolge nach Erstellungszeitpunkt (created_at) sortiert und sie dann mit „product_tbl“ verknüpft, um den letzten Änderungszeitpunkt des Produkts herauszufinden.

Auf der MySQL-Instanz in Region A kann die Abfrage des neuesten Änderungszeitpunkts eines Produkts korrekte Ergebnisse liefern. Auf der MySQL-Instanz in Region B ist der ermittelte Änderungszeitpunkt jedoch nicht der neueste, sondern der älteste. Durch Vereinfachen der Anweisung haben wir festgestellt, dass die Anweisung „order by created_at desc“ in der Unterabfrage für die Instanz in Region B nicht wirksam war.

Fehlerbehebungsprozess

Könnte es sein, dass die Region das Verhalten von MySQL beeinflusst? Nach einer DBA-Untersuchung wurde festgestellt, dass MySQL in Bereich A die Version 5.6 und MySQL in Bereich B die Version 5.7 war. Außerdem wurde dieser Artikel gefunden:

https://blog.csdn.net/weixin_42121058/article/details/113588551

Laut der Beschreibung im Artikel ignoriert MySQL Version 5.7 die ORDER BY-Anweisung in der Unterabfrage. Das Rätselhafte ist jedoch, dass die MySQL-Version, die wir zur Simulation des Geschäftsszenarios verwendet haben, 8.0 ist und dieses Problem nicht auftritt. Verwenden Sie Docker, um MySQL 5.6-, 5.7- und 8.0-Instanzen zu starten und den obigen Vorgang zu wiederholen. Die Ergebnisse sind wie folgt:

Wie Sie sehen, ignoriert nur MySQL Version 5.7 die Reihenfolge in der Unterabfrage. Ist es möglich, dass 5.7 einen Fehler eingeführt hat und dieser in späteren Versionen behoben wurde?

Grundursache des Problems

Als ich weiter nach Dokumenten und Informationen suchte, fand ich im offiziellen Forum die folgende Beschreibung:

Eine „Tabelle“ (und auch eine Unterabfrage in der FROM-Klausel) ist – gemäß dem SQL-Standard – eine ungeordnete Menge von Zeilen. Zeilen in einer Tabelle (oder in einer Unterabfrage in der FROM-Klausel) kommen nicht in einer bestimmten Reihenfolge. Deshalb kann der Optimierer die von Ihnen angegebene ORDER BY-Klausel ignorieren. Tatsächlich lässt der SQL-Standard nicht einmal zu, dass die ORDER BY-Klausel in dieser Unterabfrage erscheint (wir lassen sie zu, weil ORDER BY ... LIMIT ... das Ergebnis, die Menge der Zeilen, ändert, nicht nur deren Reihenfolge). Sie müssen die Unterabfrage in der FROM-Klausel als eine Menge von Zeilen in einer nicht angegebenen und undefinierten Reihenfolge behandeln und ORDER BY auf die SELECT-Anweisung der obersten Ebene setzen.

Die Ursache des Problems ist klar. Es stellt sich heraus, dass im SQL-Standard die Definition einer Tabelle ein unsortierter Datensatz und eine SQL-Unterabfrage eine temporäre Tabelle ist. Gemäß dieser Definition wird die Reihenfolge in der Unterabfrage ignoriert. Gleichzeitig enthielt die offizielle Antwort auch eine Lösung: Verschieben Sie die Reihenfolge der Unterabfrage in die äußerste Select-Anweisung.

Zusammenfassen

Im SQL-Standard ist „order by“ in einer Unterabfrage nicht gültig.

MySQL 5.7 weist das Problem auf, da es an dieser Stelle dem SQL-Standard entspricht. Diese Schreibmethode ist jedoch in MySQL 5.6/8.0 immer noch wirksam.

Dies ist das Ende dieses Artikels über das Problem der fehlenden Wirkung von „order by“ in MySQL-Unterabfragen. Weitere relevante Inhalte zu „order by“ in MySQL-Unterabfragen finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!

Referenzdokumentation

https://stackoverflow.com/questions/26372511/mysql-mariadb-order-by-inside-subquery

https://mariadb.com/kb/en/warum-wird-order-by-in-a-from-subquery-ignored/