MySQL sql_mode の変更が有効にならない理由と解決策

MySQL sql_mode の変更が有効にならない理由と解決策

序文

最近、sql_mode の話題については何度も話し合われ、関連する問題にも何度も遭遇しました。今日は、鉄は熱いうちに打て、sql_mode の別のケーススタディを皆さんと共有したいと思います。

シナリオシミュレーション

ビジネス上の機密性を考慮し、以下に含まれるテーブルとストアド プロシージャは実際のデータではありませんが、トラブルシューティング プロセスには影響しません。

(1)クライアント開発者は、標準のグループ化構文に厳密に従わないストアドプロシージャを作成しました。

セッション 1:
mysql> 区切り文字 //

mysql> プロシージャ test_for_group_by() を作成します
    -> 開始
    -> test.test から k、pad、count(*) を選択し、k でグループ化します。
    -> 終了 //
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> 区切り文字;

(2) クライアント開発者はストアドプロシージャを呼び出し、ERROR 1140を報告します。ストアドプロシージャは複雑で変更が困難であったため、クライアントはsql_modeを変更することを選択します。

セッション 1:
mysql> test_for_group_by() を呼び出します。
エラー 1140 (42000): GROUP BY のない集計クエリで、SELECT リストの式 #1 に非集計列 'test.test.k' が含まれています。これは sql_mode=only_full_group_by と互換性がありません。

(3)クライアントがsql_modeを変更して再度実行しても、ERROR 1140が報告される。

セッション2:
mysql> グローバル sql_mode を 'STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION' に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

セッション 1:
mysql> test_for_group_by() を呼び出します。
エラー 1140 (42000): GROUP BY のない集計クエリで、SELECT リストの式 #1 に非集計列 'test.test.k' が含まれています。これは sql_mode=only_full_group_by と互換性がありません。

(4)この時点で、システム変数の変更は新しい接続にのみ有効であり、既存の接続には有効ではないことに気付きました。そこで、クライアントに再接続してシステム変数が有効になっていることを確認し、ストアドプロシージャを再度呼び出すように依頼しましたが、エラーメッセージ ERROR 1140 がまだ表示されていました。何度か試してみましたが、結果は同じでした。

セッション3:
mysql> 'sql_mode' のような変数を表示します。
+---------------+------------------------------------------------------------------------------------------------------------------------------+
| 変数名 | 値 |
+---------------+------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------------+
セット内の1行(0.01秒)

mysql> test_for_group_by() を呼び出します。
エラー 1140 (42000): GROUP BY のない集計クエリで、SELECT リストの式 #1 に非集計列 'test.test.k' が含まれています。これは sql_mode=only_full_group_by と互換性がありません。

(5)さらに調査を進め、クライアントにセッション内で非標準のgroup by文を実行するように依頼したところ、文は正常に実行できることが判明した。

セッション3:
mysql> select user,host,count(*) mysql.user グループから user を選択;
+---------------+-----------+----------+
| ユーザー | ホスト | カウント(*) |
+---------------+-----------+----------+
| mysql.セッション | ローカルホスト | 1 |
| mysql.sys | ローカルホスト | 1 |
| ルート | ローカルホスト | 1 |
| rpl_user | % | 1 |
| テスト | % | 1 |
+---------------+-----------+----------+
セット内の行数は 5 です (0.00 秒)

(6)調査を続けると、ストアドプロシージャのsql_modeにまだONLY_FULL_GROUP_BYが含まれているため、実行時にエラーが報告されました。

セッション2:
mysql> ルーチンから、routine_catalog、routine_schema、routine_name、routine_type、created、last_altered、sql_mode を選択します。ここで、routine_name は 'test_for_group_by' です。
+-----------------+----------------+-------------------+--------------+---------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| ルーチンカタログ | ルーチンスキーマ | ルーチン名 | ルーチンタイプ | 作成済み | 最終変更日 | sql_mode |
+-----------------+----------------+-------------------+--------------+---------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| def | test | test_for_group_by | 手順 | 2020-12-24 12:12:10 | 2020-12-24 12:12:10 | ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION |
+-----------------+----------------+-------------------+--------------+---------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
セット内の 1 行 (0.00 秒)

(7)ここで、システム変数の変更は新しく作成されたオブジェクトにのみ有効であり、既存のオブジェクトには有効ではないこともわかります。解決策は非常に簡単で、ストアドプロシージャを再構築するだけです。

セッション3:
mysql> プロシージャ test_for_group_by を削除します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> 区切り文字 //

mysql> プロシージャ test_for_group_by() を作成します
    -> 開始
    -> test.test から k、pad、count(*) を選択し、k でグループ化します。
    -> 終了 //
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> 区切り文字;

mysql> test_for_group_by() を呼び出します。
+--------+-------------------------------------------------------------+----------+
| k | パッド | カウント(*) |
+--------+-------------------------------------------------------------+----------+
| 393975 | 35227182905-15234265621-59793845249-15413569710-23749555118 | 1 |
| 495688 | 09512147864-77936258834-40901700703-13541171421-15205431759 | 1 |
| 497896 | 13152283289-69561545685-52868757241-04245213425-69280254356 | 1 |
| 498573 | 43131080328-59298106536-35954612339-97546855884-75769514803 | 1 |
| 500775 | 27590239742-20204899609-34345212327-79811525340-24267764271 | 1 |
| 501885 | 63188288836-92351140030-06390587585-66802097351-49282961843 | 1 |
| 503330 | 01495266405-82925129145-92643983850-90243995398-18709399387 | 1 |
| 503666 | 40929980986-33813039690-13155419391-97985458477-39771362212 | 1 |
| 504353 | 00505722282-72931248925-57037623248-81117963809-88658076981 | 1 |
| 514246 | 21979564480-87492594656-60524686334-78820761788-57684966682 | 1 |
+--------+-------------------------------------------------------------+----------+
セット内の行数は 10 です (0.00 秒)

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

要約する

この事例から、sql_mode システム変数の変更は、新しく作成された接続と新しく作成されたオブジェクト (主に関数とストアド プロシージャを含む) に対してのみ有効であり、既存の接続と既存のオブジェクトに対しては有効ではないことがわかります。

上記は、MySQL sql_mode の変更が有効にならない理由と解決策の詳細な内容です。MySQL sql_mode の変更が有効にならないことの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL sql_modeクエリと設定の詳細な説明
  • MySQL での SQL モードの表示と設定の詳細な説明
  • MySQL の sql_mode モード例の詳細な説明
  • Django2 は MySQL に接続し、モデルテストの例を分析します。
  • MySQL sql_modeの適切な設定に関する詳細な説明
  • MySQL sql_mode の分析と設定の説明
  • MySQL 5.7 の sql_mode のデフォルト値によって生じる落とし穴と解決策
  • MySql バージョンの問題に対する完璧なソリューション sql_mode=only_full_group_by
  • MySQL 5.7.9 バージョンの sql_mode=only_full_group_by 問題を解決する
  • MySQL での SQL モードの使用法の詳細な説明
  • mysql sql_mode="" 関数の説明
  • MySQL sql_mode の使用に関する詳細な説明

<<:  CSS に基づいて MaterialUI ボタン​​クリックアニメーションを実装し、それを React コンポーネントにカプセル化します。

>>:  ウェブページのテーブルの境界線を設定する方法

推薦する

MySQL 8.0.16 圧縮版のダウンロードと Win10 システムへのインストール チュートリアル

公式サイトからダウンロード: https://www.mysql.com MySQLの公式サイトにア...

Linux で測位バックグラウンド サービスが時々クラッシュする問題の解決方法

問題の説明最近のバックグラウンドサービスでは、特定の命令の要求データをディスクに保存する新しい機能が...

MySQL における count(*)、count(1)、count(col) の違いのまとめ

序文count 関数は、テーブルまたは配列内のレコードをカウントするために使用されます。count(...

MySQL 8.0.20 圧縮版のインストールチュートリアル(画像とテキスト付き)

1. MySQL ダウンロード アドレス。 http://ftp.ntu.edu.tw/MySQL...

JavaScript タイマーの種類の概要

目次1.setInterval() 2.タイムアウトを設定する() 1.setInterval()指...

DOM操作テーブルの例(DOMはテーブルを作成します)

1. HTML タグを使用してテーブルを作成します。コードをコピーコードは次のとおりです。 <...

MySQL クエリの重複データ (重複データを削除し、ID が最も小さいデータのみを保持します)

開発の背景:最近、私はバッチ データを MySQL データベースにインポートする機能に取り組んでいま...

nginx を使用して特定のインターフェース (URL) をブロックする方法

1. はじめに場合によっては、Web プラットフォームがオンラインになった後、サービス インターフェ...

インタラクションデザインと心理学の驚くべきつながり18選

デザイナーは心理学を理解する必要があるデザイナーが知るべき心理学という本は非常に興味深いです。まず、...

ハイパーリンクを表示して開く方法

<br />インターネット上の無数の情報は基本的に HTML ドキュメントで構成されてお...

mysql8.0.18 で winx64 をインストールするための詳細なチュートリアル (画像とテキスト付き)

MySQLデータベースをダウンロードするには、https://dev.mysql.com/down...

MySQL 5.7.30 のインストールとアップグレードの問題に関する詳細なチュートリアル

くさびコンピュータにインストールされている MySQL のバージョンが比較的古く、おそらくバージョン...

MySQL サーバー ログイン エラー ERROR 1820 (HY000) の解決方法

障害サイト: MySQL サーバーにログインし、どのコマンドを実行してもこのエラーが発生します my...

MySQL ストアド プロシージャの作成、呼び出し、管理の詳細な説明

目次ストアドプロシージャの概要ストアド プロシージャを使用する理由は何ですか?ストアドプロシージャの...

MySQL の大きなデータ テーブルにフィールドを追加する方法

序文フィールドの追加は誰でもよく知っていると思います。簡単に記述できます。MySQL テーブルにフィ...