MySQL sql_mode の分析と設定の説明

昨夜、MySQL データベースにデータセットを挿入したときにエラーが発生しました。データベースは容赦なくエラーを報告しました: ERROR 1365（22012）：Division by 0 。これは、0 による除算を含む演算の結果をデータベースに挿入できないことを意味します。そこで Google で調べて、ようやく理由がわかりました。それは、MySQL の sql_mode モードが、いわゆる「違法な」操作を制限していたためでした。

分析

つまり、sql_mode は、MySQL がサポートする SQL 構文やデータ検証などを定義します。 。

現在のデータベースで使用されている sql_mode を表示する方法:

mysql> @@sql_mode を選択します。

私のデータベースの現在のスキーマは次のとおりです。

MySQL サーバーはさまざまな SQL モードで動作し、さまざまなクライアントに対してさまざまな方法でこれらのモードを適用できます。これにより、アプリケーションはニーズに合わせてサーバー操作をカスタマイズできます。このようなモードは、MySQL がサポートする必要がある SQL 構文と、データに対して実行する必要がある検証チェックを定義します。これにより、さまざまな環境で他のデータベース サーバーと共に MySQL を簡単に使用できるようになります。 MySQL 4.1 以降では、起動後にSET[SESSION|GLOBAL]sql_mode='mode1,mode2… ' ステートメントを使用して sql_mode 変数を設定することでモードを変更することもできます。

sql_mode 共通値

完全グループ化のみ

GROUP BY 集計操作の場合、SELECT、HAVING、または ORDER BY 句の列が GROUP BY に表示されない場合は、SQL は無効です。

不正な SQL ステートメントの場合、実行時に次のエラーが報告されます。

ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'postscan.verifyDelayLog.auditor' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ゼロ以外

この値は、自動インクリメント列への挿入に影響します。デフォルトでは、0 または NULL を挿入すると、次の自動インクリメント値が生成されます。このオプションは、ユーザーが値 0 を挿入し、列が自動増分されている場合に便利です。

STRICT_TRANS_TABLES

このモードでは、トランザクション テーブルに値を挿入できない場合、現在の操作が中断され、非トランザクション テーブルには制限が課されません。

日付にゼロがない

厳密モードでは、月または日の部分が 0 の日付は受け入れられません。 IGNORE オプションを使用すると、同様の日付に「0000-00-00」が挿入されます。非厳密モードでは、日付は受け入れられますが、警告が生成されます。

日付なし

厳密モードでは、「0000-00-00」を有効な日付として考慮しません。 IGNORE オプションを使用すると、ゼロの日付を挿入することもできます。非厳密モードでは、日付は受け入れられますが、警告が生成されます。

ゼロ除算エラー

INSERT または UPDATE 中にデータがゼロで除算されると、警告ではなくエラーが生成されます。このモードが指定されていない場合、値がゼロで除算されると、MySQL は NULL を返します。

いいえ

GRANT が空のパスワードを持つユーザーを作成しないようにする

エンジン代替なし

必要なストレージ エンジンが無効になっているか、コンパイルされていない場合は、エラーがスローされます。この値が設定されていない場合は、代わりにデフォルトのストレージ エンジンが使用され、例外がスローされます。

パイプASCONCAT

「||」をOR演算子ではなく文字列連結演算子として扱います。これはOracleデータベースと同じで、文字列連結関数Concatに似ています。

ANSI_QUOTES

ANSI_QUOTES が有効になっている場合、二重引用符は識別子として解釈されるため、文字列を引用符で囲むことはできません。

MySQL 5.0 以降のバージョンでは、ANSI、TRADITIONAL、STRICT_TRANS_TABLES の 3 つの sql_mode モードがサポートされています。

1. ANSI モード: ルーズ モード。標準 SQL との一貫性を高めるために構文と動作を変更します。挿入されたデータを確認します。定義されたタイプまたは長さに準拠していない場合は、保存する前にデータ型を調整するか切り捨て、警告を発します。この記事の冒頭で述べたエラーについては、まず sql_mode を ANSI モードに設定してデータを挿入できるようにし、除数が 0 であるフィールド値についてはデータベースによって NULL 値に置き換えられます。

2. 伝統的モード: 厳密モード。MySQL データベースにデータを挿入するときに、誤ったデータが挿入されないように厳密なデータ検証が実行され、警告だけでなくエラーが報告されます。トランザクションに使用すると、トランザクションはロールバックされます。 注意: エラーが見つかった場合は、INSERT/UPDATE を直ちに中止してください。非トランザクション ストレージ エンジンを使用している場合、エラー発生前に行われたデータ変更は「ロールオーバー」されず、「部分的に実行された」更新となるため、これは望ましいことではありません。

3. STRICT_TRANS_TABLES モード: 厳密なモードでは、厳密なデータ検証が実行され、不正なデータは挿入できず、エラーが報告されます。指定された値をトランザクション テーブルに挿入できない場合、ステートメントは中止されます。非トランザクション テーブルの場合、値が単一行ステートメントまたは複数行ステートメントの行 1 に表示されると、ステートメントは破棄されます。

sql_modeの設定

現在の接続セッションの SQL モードを表示します。

mysql> @@session.sql_mode を選択します。

または環境変数から

mysql> 「sql_mode」のような変数を表示します。

グローバル sql_mode 設定を表示します。

mysql> @@global.sql_mode を選択します。

グローバルに設定すると、有効にするには再接続する必要があります

mysql> グローバル sql_mode を 'NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION、NO_ZERO_DATE、NO_ZERO_IN_DATE' に設定します。

グローバルsql_modeの設定はMySQLを再起動しなくても有効になります。

設定ファイルの設定

sql_mode=NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION、NO_ZERO_DATE、NO_ZERO_IN_DATE

設定ファイルを変更したら、MySQLサービスを再起動して変更を有効にします。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。