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

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データベース設計に役立つことを願っています。

以下もご興味があるかもしれません:
  • MySQL sql_mode の変更が有効にならない理由と解決策
  • MySQL sql_modeクエリと設定の詳細な説明
  • MySQL での SQL モードの表示と設定の詳細な説明
  • 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 の使用に関する詳細な説明

<<:  Linux リモートログイン実装チュートリアル分析

>>:  クールなページング効果を実現するネイティブJS

推薦する

React-vscode で jsx 構文を使用する際の問題と解決策

問題の説明プラグインをインストールした後、ES7 React/Redux/GraphQL/React...

Vue で配列パラメータを渡すための get / delete メソッド

フロントエンドとバックエンドがやり取りする場合、get または delete を介してバックエンドに...

よくある Linux 英語エラーの中国語翻訳 (初心者必見)

1.コマンドが見つかりません コマンドが見つかりません2. そのようなファイルまたはディレクトリは...

Linux システムでの gcc コマンドの使用法の詳細な説明

目次1. 前処理2. コンパイル3. コンパイル4. リンク1. gccのインストール(Ubuntu...

Vueが初めて要素を取得できなかったときの解決記録

序文Vue で要素を初回取得できない問題の解決方法は、ポップアップ ウィンドウで要素を取得するために...

DockerプライベートライブラリHarborのアーキテクチャとコンポーネントの説明

この記事では、Harbor アーキテクチャの構成と、実行時に各コンポーネントを使用する方法について説...

Windows Server 2019 で NAS を構成する方法

序文このチュートリアルでは最新バージョンをインストールします。 NAS は非常に安定して動作するので...

ページング効果を実装するミニプログラム

この記事の例では、ページング効果表示を実現するためのミニプログラムの具体的なコードを参考までに共有し...

MySQL 8.0.25 のインストールと設定方法のグラフィックチュートリアル

MySQL 8.0.25の最新のダウンロードとインストールのチュートリアルは参考になります。具体的な...

Linuxファイアウォールiptablesの詳細な紹介、設定方法と事例

1.1 iptablesファイアウォールの概要Netfilter/Iptables (以下、Ipta...

DockerコンテナでLNMPをコンパイルする例

目次1. プロジェクトの説明2. Nginxイメージの作成3. MySQLイメージの作成4. PHP...

NginxとLuaによるグレースケールリリースの実装

memcachedをインストールする yum インストール -y memcached #memcac...

MySQLはデフォルトのエンジンと文字セットの詳細を変更します

目次1. データベースエンジン1.1 ビューデータベースエンジン1.2 デフォルトのデータベースエン...

テーブルの作成、フィールドの追加、フィールドの変更、インデックスの追加によく使用される MySQL の SQL 文の概要

この記事では、テーブルの作成、フィールドの追加、フィールドの変更、インデックスの追加を行う一般的な ...

1つのSQL文でMySQLの重複排除が完了し、1つが保持されます。

数日前、ある要件に取り組んでいたとき、MySQL で重複レコードをクリーンアップする必要がありました...