MySQL の sql_mode モード例の詳細な説明

この記事では、MySQL の sql_mode モードについて例を挙げて説明します。ご参考までに、詳細は以下の通りです。

MySQL データベースには環境変数 sql_mode があり、MySQL がサポートする必要がある SQL 構文、データ検証などを定義します。現在のデータベースで使用されている sql_mode は、次の方法で確認できます。

mysql> @@sql_mode を選択します。
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+

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

ANSI モード

ルーズモードでは、挿入されたデータがチェックされます。定義されたタイプまたは長さに準拠していない場合は、保存前にデータ型が調整または切り捨てられ、警告が発行されます。

トラディショナルモード

厳密モード: MySQL データベースにデータを挿入するときに、誤ったデータが挿入されずエラーが報告されることがないように厳密なデータ検証が実行されます。トランザクションに使用すると、トランザクションはロールバックされます。

STRICT_TRANS_TABLES モード

厳密モードでは厳密なデータ検証が実行されます。不正なデータは挿入できず、エラーが報告されます。

1 ANSIモード

ANSI モードでは、データを挿入する際に列の長さの要件が満たされていない場合、データは正常に挿入されますが、列の長さを超えるフィールドは切り捨てられ、警告が報告されます。

mysql> @@sql_mode を ANSI に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> テーブル test(name varchar(4), pass varchar(4) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)
mysql> テストに値を挿入します('aaaaa','aaaaa'),('bbbb','bbbb');
クエリは正常、2 行が影響を受け、2 つの警告 (0.02 秒)
記録: 2 重複: 0 警告: 2
mysql> 警告を表示します。
+---------+------+--------------------------------------------------------+
| レベル | コード | メッセージ |
+---------+------+--------------------------------------------------------+
| 警告 | 1265 | 行 1 の列 'name' のデータが切り捨てられました |
| 警告 | 1265 | 行 1 の列 'pass' のデータが切り捨てられました |
+---------+------+--------------------------------------------------------+
セット内の 2 行 (0.00 秒)
mysql> テストから * を選択します。
+------+------+
| 名前 | パス |
+------+------+
| ああ | ああ |
| bbbb | bbbb |
+------+------+
セット内の 2 行 (0.00 秒)

2 STRICT_TRANS_TABLES モード

STRICT_TRANS_TABLES モードでは、データを挿入するときに、MySQL はデータを厳密に検証します。挿入された列の値が要件を満たしていないことが判明した場合、エラーが直接報告され、間違ったデータがデータベースに挿入されないようにします。

mysql> @@sql_mode=STRICT_TRANS_TABLES を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> テーブル test(name varchar(4), pass varchar(4) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.02 秒)
mysql> テストに値を挿入します('aaaaa','aaaaa'),('bbbb','bbbb');
エラー 1406 (22001): 行 1 の列 'name' のデータが長すぎます
mysql> エラーを表示します。
+-------+------+------------------------------------------+
| レベル | コード | メッセージ |
+-------+------+------------------------------------------+
| エラー | 1406 | 行 1 の列 'name' のデータが長すぎます |
+-------+------+------------------------------------------+
セット内の 1 行 (0.00 秒)
mysql> テストから * を選択します。
空のセット (0.00 秒)

3 TRADITIONALモードでは、まず結果が同じかどうかを確認します

mysql> @@sql_mode を TRADITIONAL に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> テーブル test(name varchar(4), pass varchar(4) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.02 秒)
mysql> テストに値を挿入します('aaaaa','aaaaa'),('bbbb','bbbb');
エラー 1406 (22001): 行 1 の列 'name' のデータが長すぎます
mysql> エラーを表示します。
+-------+------+------------------------------------------+
| レベル | コード | メッセージ |
+-------+------+------------------------------------------+
| エラー | 1406 | 行 1 の列 'name' のデータが長すぎます |
+-------+------+------------------------------------------+
セット内の 1 行 (0.00 秒)
mysql> テストから * を選択します。
空のセット (0.00 秒)

しかし、設定後に何が起こるかはわかります

mysql> @@sql_mode を TRADITIONAL に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> @@sql_mode\G を選択
************************** 1. 行 ****************************
@@sql_mode: STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、TRADITIONAL、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION
セット内の 1 行 (0.00 秒)

TRADITIONAL モードでは、すべてのトランザクションストレージエンジンと非トランザクションストレージエンジンがチェックされます。日付型の月と日の部分に 0 を含めることはできず、0 (0000-00-00) のような日付は存在できず、データは 0 で割ることはできず、許可によって新しいユーザーが自動的に作成されることは禁止されます。

やっと：

set @@はセッションレベルでのみ設定されます。すべてを有効にするには、設定ファイルを設定する必要があります。

vi /etc/my.cnf

[mysqld]の下に次の行を追加します。

sql_mode=NO_ENGINE_SUBSTITUTION、STRICT_TRANS_TABLES

#NO_ENGINE_SUBSTITUTION は存在しないエンジンに対してエラーを報告します。追加されていない場合は、サポートされていないエンジンを指定するとデフォルトの innodb が指定されます。

さらに、sql_mode にはONLY_FULL_GROUP_BY 構成もあり、これは、group by を使用してデータを選択する場合、新しいグループ内の情報のみを表示できることを意味します。

モード変更前の操作

mysql> 従業員グループから投稿別に * を選択します。
+----+---------+--------+-----------+------------+------------------------------------------+--------------+------------+------------+
| ID | 名前 | 性別 | 年齢 | 入社日 | 職位 | 投稿コメント | 給与 | 勤務先 | 退職ID |
+----+---------+--------+-----------+------------+------------------------------------------+--------------+------------+------------+
| 14 | 張 イエ | 男性 | 28 | 2016-03-11 | 操作 | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪| 女性 | 48 | 2015-03-11 | セール | NULL | 3000.13 | 402 | 2 |
| 2 | alex | 男性 | 78 | 2015-03-02 | 教師 | NULL | 1000000.31 | 401 | 1 |
| 1 | egon | 男性 | 18 | 2017-03-01 | 沙河の旧少年院の大使 | NULL | 7300.33 | 401 | 1 |
+----+---------+--------+-----------+------------+------------------------------------------+--------------+------------+------------+
セット内の 4 行 (0.00 秒)

現時点では、sql_mode:

mysql> @@sql_mode を選択します。
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
セット内の 1 行 (0.00 秒)

変更して終了し、再度入力すると有効になります

mysql> グローバル sql_mode を 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY' に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> @@sql_mode を選択します。
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
セット内の 1 行 (0.00 秒)
mysql>終了
さよなら

再入力

mysql> @@sql_mode を選択します。
+-----------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+
セット内の 1 行 (0.00 秒)

修正された結果は以下をご覧ください

mysql> select * from employee group by post; //投稿のみ閲覧可能
エラー 1055 (42000): 't1.employee.id' は GROUP BY にありません
mysql> post ごとに従業員グループから投稿を選択します。
+-----------------------------------------+
| 投稿 |
+-----------------------------------------+
| 操作 |
| セール |
| 先生 |
| 沙河のOld Boys Officeの外交大使 |
+-----------------------------------------+
セット内の 4 行 (0.00 秒)
mysql> post ごとに従業員グループから id、post を選択します。
エラー 1055 (42000): 't1.employee.id' は GROUP BY にありません
mysql> select name,post from employee group by post,name; //group by 後の選択に従って表示します +------------+------------------------------------------+
| 名前 | 役職 |
+------------+------------------------------------------+
| 張野| 作戦 |
| 程耀進 | オペレーション |
| チェン・ヤオティエ | オペレーション |
| チェン・ヤオトン | オペレーション |
| チェン・ヤオイン | オペレーション |
| ディンディン | セール |
| ヤヤ | セール |
| スター | セール |
| ゲゲ | セール |
| ワイワイ | セール |
| アレックス | 先生 |
| jinliyang | 先生 |
| ジンシン | 先生 |
| liwenzhou | 教師 |
| wupeiqi | 先生 |
| xiaomage | 先生 |
| yuanhao | 先生 |
| egon | 沙河のOld Boys Officeの外交大使 |
+------------+------------------------------------------+
セット内の行数は 18 です (0.00 秒)

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキル」、および「MySQL データベースロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: