MySQLサブクエリでorder byが効かない問題の解決方法

偶然にも、SQL ステートメントを異なる MySQL インスタンスで実行すると、異なる結果が生成されることがわかりました。

問題の説明

ビジネスシナリオをシミュレートするために、製品テーブル product_tbl と製品操作記録テーブル product_operation_tbl の 2 つのテーブルを作成します。構造とデータは次のとおりです。

次に、次のステートメントを使用して、すべての製品の最新の変更時刻を照会する必要があります。

product_tbl から t1.id、t1.name、t2.product_id、t2.created_at を選択し、t1 を左結合します (product_operation_log_tbl から * を選択し、created_at desc で順序付けします)。t2 を t1.id = t2.product_id でグループ化し、t1.id でグループ化します。

結果から、サブクエリは最初に product_operation_log_tbl 内のすべてのレコードを作成時刻 (created_at) の逆順に並べ替え、次にそれらを product_tbl と結合して製品の最新の変更時刻を調べていることがわかります。

リージョン A の MySQL インスタンスでは、製品の最新の変更時刻をクエリすると正しい結果が得られます。ただし、リージョン B の MySQL インスタンスでは、取得される変更時刻は最新ではなく、最も古いものになります。ステートメントを簡略化することで、サブクエリ内の order by created_at desc ステートメントがリージョン B のインスタンスでは効果がないことがわかりました。

トラブルシューティングのプロセス

リージョンが MySQL の動作に影響を与える可能性はありますか? DBA 調査の結果、エリア A の MySQL はバージョン 5.6、エリア B の MySQL はバージョン 5.7 であることが判明し、次の記事が見つかりました。

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

記事の説明によると、MySQL バージョン 5.7 ではサブクエリ内の order by ステートメントが無視されるとのことです。しかし、不可解なのは、ビジネスシナリオをシミュレートするために使用した MySQL バージョンが 8.0 であり、この問題は発生しないことです。 Docker を使用して MySQL 5.6、5.7、8.0 インスタンスをそれぞれ起動し、上記の操作を繰り返します。結果は次のとおりです。

ご覧のとおり、サブクエリ内の order by を無視するのは MySQL バージョン 5.7 のみです。 5.7 でバグが発生し、それ以降のバージョンで修正された可能性はありますか?

問題の根本原因

ドキュメントと情報の検索を続けると、公式フォーラムで次の説明を見つけました。

「テーブル」(および FROM 句のサブクエリ) は、SQL 標準によれば、順序付けされていない行のセットです。テーブル内の行 (または FROM 句のサブクエリ) は、特定の順序で並んでいません。そのため、オプティマイザは、指定した ORDER BY 句を無視できます。実際、SQL 標準では、このサブクエリに ORDER BY 句を含めることさえ許可されていません (ORDER BY ... LIMIT ... は、順序だけでなく、結果、つまり行のセットも変更するため、許可されています)。FROM 句のサブクエリを、指定も定義もされていない順序の行のセットとして扱い、最上位レベルの SELECT に ORDER BY を配置する必要があります。

問題の原因は明らかです。SQL 標準では、テーブルの定義はソートされていないデータセットであり、SQL サブクエリは一時テーブルであることが判明しました。この定義によると、サブクエリ内の order by は無視されます。同時に、公式の回答では、サブクエリの order by を最も外側の SELECT ステートメントに移動する、という解決策も示されました。

要約する

SQL 標準では、サブクエリ内の order by は無効です。

MySQL 5.7では現時点でSQL標準に準拠しているため問題が露呈しているが、この書き方はMySQL 5.6/8.0でも有効である。

MySQL サブクエリで order by が有効にならない問題についての記事はこれで終わりです。MySQL サブクエリの order by が有効にならないことに関する関連コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。