MySQL sql_modeの適切な設定に関する詳細な説明

MySQL sql_modeの適切な設定

sql_mode は見落とされやすい変数です。デフォルト値は null です。この設定では、不正なデータの挿入を許可するなど、不正な操作が許可される可能性があります。実稼働環境では、この値を厳密モードに設定する必要があり、開発およびテストフェーズで問題を検出できるように、開発およびテスト環境のデータベースも設定する必要があります。

1. SQL モデルは、次のような種類の問題を解決するためによく使用されます。

（1）SQLモードを設定することで、さまざまな厳密さでデータ検証を完了し、データの準備状況を効果的に確保できます。

（2）SQLモデルをルーズモードに設定することで、ほとんどのSQL文が標準SQL構文に準拠していることを保証できます。これにより、異なるデータベース間でアプリケーションを移行するときに、ビジネスSQL文に大きな変更を加える必要がなくなります。

（3）異なるデータベース間でデータを移行する前に、SQLモードを設定すると、MySQL上のデータをターゲットデータベースに簡単に移行できるようになります。

2. sql_modeの一般的な値は次のとおりです。

1. ONLY_FULL_GROUP_BY:
GROUP BY 集計操作の場合、SELECT 内の列が GROUP BY に表示されない場合は、その列が GROUP BY 句にないため、この SQL は無効です。
2. NO_AUTO_VALUE_ON_ZERO:
この値は、自動インクリメント列への挿入に影響します。デフォルトでは、0 または NULL を挿入すると、次の自動インクリメント値が生成されます。このオプションは、ユーザーが値 0 を挿入し、列が自動増分されている場合に便利です。
3. STRICT_TRANS_TABLES: 厳密モード
このモードでは、トランザクションテーブルに値を挿入できない場合、現在の操作が中断され、非トランザクションテーブルには制限がありません。
4. 日付にゼロがない:
厳密モードでは、日と月の値がゼロになることはできません。

5. 日付なし:
この値を設定すると、MySQL データベースはゼロ日付の挿入を許可しなくなり、ゼロ日付を挿入すると警告ではなくエラーがスローされます。

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

7. NO_AUTO_CREATE_USER:
GRANT が空のパスワードを持つユーザーを作成しないようにする
8. エンジン代替なし:
必要なストレージエンジンが無効になっているか、コンパイルされていない場合は、エラーがスローされます。この値が設定されていない場合は、代わりにデフォルトのストレージエンジンが使用され、例外がスローされます。
9. パイプAS_CONCAT:
「||」を OR 演算子ではなく文字列連結演算子として扱います。これは Oracle データベースと同じであり、文字列連結関数 Concat に似ています。
10. ANSI_QUOTES:
ANSI_QUOTES が有効になっている場合、二重引用符は識別子として解釈されるため、文字列を引用符で囲むことはできません。
ORACLE の sql_mode 設定は、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER と同等です。
MySQL を使用する場合、Oracle を使用するという全員の習慣を引き続き維持するために、MySQL の sql_mode を次のように設定できます。
my.cnfに次の設定を追加します
[mysqld]
sql_mode = 'ONLY_FULL_GROUP_BY、NO_AUTO_VALUE_ON_ZERO、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、
ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、PIPES_AS_CONCAT、ANSI_QUOTES'

3. ルーズモードとストリクトモードに関する注意事項:

【1】注意：MySQL 5.6とMySQL 5.7のデフォルトのsql_modeパラメータは異なります。
5.6 のモードは NO_ENGINE_SUBSTITUTION です。これは実際には null 値を表し、モード設定がないことに相当し、緩いモードとして理解できます。
5.7 のモードは STRICT_TRANS_TABLES で、厳密モードです。

[2] ルーズモードが設定されている場合、データを挿入するときに間違ったデータを入力しても受け入れられ、エラーは報告されません。

たとえば、テーブルを作成すると、テーブルに name というフィールドがあり、name に設定されているフィールドタイプは char(10) です。データを挿入し、name フィールドに対応するデータの長さが 10 を超える場合 (「1234567890abc」など)、設定されたフィールド長の 10 を超える場合、エラーは報告されず、最初の 10 文字が格納されます。つまり、データは「1234567890」として保存され、「abc」は消えます。ただし、フィールド長を超えているため、指定したデータが間違っていることはわかっていますが、エラーは報告されず、MySQL がそれを自動的に処理して受け入れます。これがルーズモードの効果です。

実際、開発、テスト、本番などの環境では、厳密モードを使用する必要があります。このエラーが発生した場合は、エラーが報告される必要があります。そのため、MySQL 5.7 バージョンでは、sql_mode のデフォルト値が厳密モードに変更されており、MySQL 5.6 を使用する場合でも、自分で厳密モードに変更する必要があります。

さらに、MySQL などのデータベースは、データの検証を含め、すべてのデータ操作を自ら引き受けようとします。実際、多くの場合、開発するプロジェクトのプログラムレベルでこれらの検証を行う必要があります。プロジェクトの作成時にいくつかの手順を書くのは面倒ですが、その後、データベースやプロジェクトを移行するときにはるかに便利になります。データの検証に加えて、学習するにつれて、MySQL がプログラムで行う多くのことをカバーする、さらに多くのことを実行できることが分かります。

【3】ストリクトモードに切り替えた後に発生する可能性のある問題：
設定モードに NO_ZERO_DATE が含まれている場合、MySQL データベースはゼロ日付の挿入を許可せず、ゼロ日付を挿入すると警告ではなくエラーがスローされます。
たとえば、テーブル内の TIMESTAMP 列 (NULL として宣言されていない場合、または DEFAULT 句が指定されていない場合) には、自動的に DEFAULT '0000-00-00 00:00:00' (ゼロタイムスタンプ) が割り当てられます。また、このテストのテーブル内の日付列には、デフォルトでゼロ日付 '0000-00-00' COMMENT 'date' を挿入できます。これらは明らかに sql_mode の NO_ZERO_DATE を満たさず、エラーを報告します。

4. モードの設定と変更（上記の問題を解決する例）

【方法1】：
まず select @@sql_mode を実行し、クエリされた値をコピーして NO_ZERO_IN_DATE、NO_ZERO_DATE を削除し、次に set sql_mode = 'modified value' または set session sql_mode = 'modified value'; を実行します。

例えば、set session sql_mode='STRICT_TRANS_TABLES'; を厳密モードに変更します。

注意: このメソッドは現在のセッションでのみ有効であり、現在のセッションが閉じられている場合は有効になりません。

【方法2】：
まず select @@global.sql_mode を実行し、クエリされた値をコピーして NO_ZERO_IN_DATE と NO_ZERO_DATE を削除し、次に set global sql_mode = 'modified value' を実行します。
注意: この方法は現在のサービスでは有効であり、MySQL サービスを再起動すると無効になります。

【方法3】：
1. MySQLインストールディレクトリ、またはmy.cnfファイル（Windowsの場合はmy.iniファイル）にsql_mode =を追加します。
「ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION」
my.ini に以下を追加します。

[mysqld]

sql モード = "ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION"
2. 次に、mysql を再起動します。管理者として CMD を実行し、net stop mysql を実行してから、net start mysql を実行します。
注意：この方法は永続的です。もちろん、本番環境ではMySQLサービスの再起動は禁止されているため、「方法2 + 方法3」を使用してオンラインの問題を解決します。そうすれば、ある日MySQLサービスが本当に再起動されたとしても、永続的になります。