sql_mode を変更する際の MySQL エラーの解決方法

ERR 1067による殺人事件

今日、Navicat で SQL ステートメントを実行してデータ テーブルを作成したときに、エラー Err 1067 が発生しました。このステートメントは一部の同僚の MySQL では正しいのですが、他の同僚の MySQL ではエラーになります。腹立たしくないですか？

理由は、タイムスタンプのデフォルト値が間違っていたためであることが判明しました。

情報によると、MySQL 5.7 には STRICT モードがあります。このモードでは、日付の値はデフォルトですべて 0 値に設定できません。

この問題を解決するには、sql_mode の値を変更する必要があります。

2番目の問題の原因はsql_modeです

sql_mode が何であるかを確認するには、mysql にアクセスします。まず、MySQL インストール ディレクトリの下の bin ディレクトリに入り、管理者ユーザーとして MySQL データベースにログインします。

コマンド mysql –h localhost –u root–p を使用します。ここで、-h は指定されたホスト名または IP アドレス、-u は指定されたユーザー、-p はパスワードを使用してログインすることです。

sql_mode の値を表示するには、コマンド select @@sql_mode; を使用します。コマンドを入力しても何も起こらず、->だけが表示される場合は、

おそらく私のように「;」を入力しなかったのでしょう。

上の図の結果から、sql_modeにはNO_ZERO_IN_DATEとNO_ZERO_DATEがあることがわかります。コマンドラインに入力します。

set sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE','')); sql_mode を変更できます。

その後、sql_mode の値を確認できます。 NO_ZERO_IN_DATE と NO_ZERO_DATE が正常に削除されたことがわかります。

テーブルを作成するための SQL ステートメントを再実行しましたが、機能せず、Err 1067 が引き続き発生しました。間違った姿勢の問題について考える必要はありません。グローバル sql_mode が設定されていないだけであり、ここで設定された sql_mode は全体的な状況に影響を与えません。

グローバル sql_mode の値を表示するには、コマンド select @@global.sql_mode; を使用します。

残りの操作は、図に示すように、以前の sql_mode が @@global.sql_mode に置き換えられることを除いて、以前の sql_mode 設定と同じです。

設定が完了したら、Navicat で SQL ステートメントを再実行できますが、その前にデータベースに再接続する必要があります。そうしないと、やはり失敗します。

理由を聞かないで、ただ雷鋒と呼んでください。結果は次のとおりです。

OK、今回はテーブルが正常に作成され、エラー Err 1055 がスローされました。これは、「非集計列 information_schema を含むテーブルを作成できません」という意味です。

PROFILING.SEQがグループ化されているため、この機能はグループ化に依存しなくなり、新しいルールsql_mode=only_full_group_byと互換性がありません。また、これは

これは、sql_mode の「ONLY_FULL_GROUP_BY」によって発生します。 sql_mode は再度変更できます。

以前作成したテーブルを削除し、データベースに再接続して SQL ステートメントを実行すると、すべて正常になります。

もちろん、この解決策は一時的な解決策に過ぎません。MySQL データベースを再起動すると、せっかく設定した値はすべて解放前の状態に復元されます。

これには解決策があり、以下で紹介します。

3. sql_modeを設定する

構成ファイルを変更して sql_mode を設定すると、データベースの再起動後に sql_mode の値が変更されなくなります。

まず、MySQL 構成ファイルがロードされる順序を知る必要があります。データベースインストールディレクトリのbinディレクトリに移動し、次のコマンドを使用します。

mysqld --verbose --help を実行すると確認できますが、このコマンドの出力は長すぎるため、これを表示するのに適したコマンドはまだ見つかっていません。

読み込み順序は次のとおりです。

これらの設定ファイルが読み込まれると、後で読み込まれた設定ファイルによって、以前に読み込まれた設定ファイル内の同じ値が上書きされます。しかし、私はmysqlのインストールディレクトリでしか見つけられませんでした

非常に類似した名前の設定ファイルが 1 つあり、残りは見つかりませんでした。

このファイルをバックアップした後、構成ファイルの読み込み順序に従ってファイルに対応するように名前を my.ini に変更します。次にファイルを開きます。私の設定はここです

ファイルにはsql_modeの値が2つだけあります。

データベースを再起動した後、コマンドを使用して sql_mode の値を確認し、構成ファイルとまったく同じであることを確認します。完了です。

いくつかの一般的な sql_mode 値の追加紹介:

いくつかの一般的なモードの紹介

• ONLY_FULL_GROUP_BY : SELECT ステートメント、HAVING 条件、および ORDER BY ステートメントに表示される列は、GROUP BY 列または GROUP BY 列に依存する関数列である必要があります。
• NO_AUTO_VALUE_ON_ZERO : この値は自動インクリメント列の挿入に影響します。デフォルトでは、0 または NULL を挿入すると、次の自動インクリメント値が生成されます。このオプションは、ユーザーが値 0 を挿入し、列が自動増分されている場合に便利です。
• STRICT_TRANS_TABLES : このモードでは、トランザクション テーブルに値を挿入できない場合、現在の操作が中断され、非トランザクション テーブルには制限が課されません。
• NO_ZERO_IN_DATE : このモードは、日付の月と日にゼロを含めることができるかどうかに影響します。このモードをオンにすると、2016-01-00 は許可されませんが、0000-02-01 は許可されます。実際の動作は、厳密モードが有効になっているかどうかによって影響を受けます。
• NO_ZERO_DATE : この値を設定すると、MySQL データベースではゼロ日付の挿入が許可されません。実際の動作は、strictmode が有効になっているかどうかによって影響を受けます。
• ERROR_FOR_DIVISION_BY_ZERO : INSERT または UPDATE 中にデータがゼロで除算されると、警告ではなくエラーが生成されます。このモードが指定されていない場合、値がゼロで除算されると、MySQL は NULL を返します。
• NO_AUTO_CREATE_USER : GRANT が空のパスワードを持つユーザーを作成することを防止します
• NO_ENGINE_SUBSTITUTION : 必要なストレージ エンジンが無効になっているか、コンパイルされていない場合はエラーをスローします。この値が設定されていない場合は、代わりにデフォルトのストレージ エンジンが使用され、例外がスローされます。
• PIPES_AS_CONCAT : "||" を OR 演算子ではなく文字列連結演算子として扱います。これは Oracle データベースと同じで、文字列連結関数 Concat に似ています。
• ANSI_QUOTES : ANSI_QUOTES が有効な場合、文字列は識別子として解釈されるため、二重引用符を使用して文字列を引用することはできません。

これで、mysql sql_mode を変更する際のエラーを解決する方法についての記事は終了です。mysql sql_mode に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM を応援していただければ幸いです。