MySQL 5.7 で SQL_MODE を設定する方法

sql_mode は見落とされやすい変数です。5.5 のデフォルト値は null です。この設定では、不正なデータの挿入を許可するなど、一部の不正な操作が許可される可能性があります。

この値の設定は 5.6 で強化され、5.7 ではセキュリティ仕様にさらに注意が払われています。この値のデフォルトは厳密モードです。

1. sql_mode は次のタイプの問題を解決するために使用されます

SQL モードを設定すると、さまざまな厳密性レベルでのデータ検証を完了して、データの準備が効果的に確保できるようになります。

SQL モードをリラックス モードに設定することで、ほとんどの SQL が標準 SQL 構文に準拠するようになります。このようにして、アプリケーションが異なるデータベース間で移行されるときに、データベースに大きな変更を加える必要はありません。ビジネス SQL を使用して、ターゲット データベースに簡単に便利に移行できます。

2. MySQL5.7 の sql_mode パラメータのデフォルト値の説明 (以下は MySQL 5.7.27 バージョンです)

• ONLY_FULL_GROUP_BY

クエリに GROUP BY を使用する SQL では、GROUP BY に現れないフィールドを SELECT 部分に含めることはできません。つまり、SELECT クエリ内のフィールドは、 GROUP BY に表示されるか、集計関数を使用するか、または固有のプロパティを持ちます。

create table test(name varchar(10),value int);
insert into test values ('a',1),('a',20),('b',23),('c',15),('c',30);
#默认情况是可能会写出无意义或错误的聚合语句：
SET sql_mode='';
select * from test group by name;
select value,sum(value) from test group by name;
# 使用该模式后，写法必须标准
SET sql_mode='ONLY_FULL_GROUP_BY';
select name,sum(value) from test group by name;
-- 错误写法则报错
select value,sum(value) from test group by name;
# 报错终止
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• STRICT_TRANS_TABLES

このオプションはトランザクション ストレージ エンジンでのみ機能し、非トランザクション ストレージ エンジンでは無効です。 、その機能は厳密な SQL モードを有効にすることです。厳密な SQL モードでは、要件を満たさないフィールド値が INSERT または UPDATE ステートメントで挿入または更新されると、エラーが直接報告され、操作は中断されます。

• MySQL に挿入された時間フィールド値では、日付と月をゼロにすることはできません

  create table test(value int(1));
  SET sql_mode=''; #默认只要第一个值
   
  insert into test(value) values('a'),(1); #不报错
  insert into test(value) values(2),('a'); #不报错
  select * from test;
  +------------+
  | value      |
  +------------+
  |          0 |
  |          1 |
  |          2 |
  |          0 |
  +------------+
  #后面删除表不再说明！
  drop table test; 
  create table test(value int(1));
   
  SET sql_mode='STRICT_TRANS_TABLES'; #每个值都判断
   
  insert into test(value) values('a'),(1);
  #报错，第一行'a'错误。
  ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'value' at row 1

  ログイン後にコピー

• MySQL に挿入された時刻フィールド値には、‘0000-00-00’ の日付を挿入することはできません

  create table test(value date);
  SET sql_mode='';
  insert into test(value) values('2020-00-00'); #结果为 '2020-00-00'
   
  SET sql_mode='NO_ZERO_IN_DATE';
  insert into test(value) values('2021-00-00'); #不符合,转为 '0000-00-00'

  ログイン後にコピー

##ERROR_FOR_DIVISION_BY_ZERO

• INSERT または UPDATE ステートメントで、データが 0 で除算されると、警告 (非厳密 SQL モードの場合) またはエラーが発生します。 (厳密な SQL モードの場合) が表示されます。

• このオプションがオフの場合SQL モードでは、数値を 0 で割ると NULL が得られますが、警告が生成されます。

• このオプションがオンで、厳密な場合SQL モードでは、数値が 0 で除算されると、エラーが生成され、操作が中断されます

create table test(value date);
 
SET sql_mode='';
insert into test(value) values('0000-00-00'); #无警告 warning
 
SET sql_mode='STRICT_TRANS_TABLES';
insert into test(value) values('0000-00-00'); #无警告 warning
 
SET sql_mode='NO_ZERO_DATE';
insert into test(value) values('0000-00-00'); #有警告 warning
 
SET sql_mode='NO_ZERO_DATE,STRICT_TRANS_TABLES'
insert into test(value) values('0000-00-00');
# 报错终止
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'value' at row 1

ログイン後にコピー
NO_AUTO_CREATE_USER

• ##GRANT が空のパスワードを持つユーザーを作成することを禁止します<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;">create table test(value int); SET sql_mode=&amp;#39;&amp;#39;; select 10/0; #无警告 warning insert into test(value) values(10/0); #无警告 warning SET sql_mode=&amp;#39;STRICT_TRANS_TABLES&amp;#39;; select 10/0; #无警告 warning insert into test(value) values(10/0); #无警告 warning SET sql_mode=&amp;#39;ERROR_FOR_DIVISION_BY_ZERO&amp;#39;; select 10/0; #有警告 warning insert into test(value) values(10/0); #有警告 warning SET sql_mode=&amp;#39;ERROR_FOR_DIVISION_BY_ZERO,STRICT_TRANS_TABLES&amp;#39;; select 10/0; #有警告 warning insert into test(value) values(10/0); #报错：ERROR 1365 (22012): Division by 0</pre><div class="contentsignin">ログイン後にコピー</div></div>

• 次の場合CREATE TABLE または ALTER TABLE 構文を使用してストレージ エンジンを実行すると、設定されたストレージ エンジンが無効になっているか無効になっていない場合、コンパイルでエラーが発生します。

  SET sql_mode='';
  grant all on test.* to test01@'localhost';  #不报错（无需要设置密码）
  SET sql_mode='NO_AUTO_CREATE_USER';
  # 报错
  ERROR 1133 (42000): Can't find any matching row in the user table
  
  #正确 写法，需要设置密码
  grant all on test.* to test01@'localhost' identified by 'test01...';

  ログイン後にコピー
  3. sql_mode の設定と変更

# 查看当前支持的存储引擎
show engines;

set sql_mode='';
create table test(id int) ENGINE="test";
Query OK, 0 rows affected, 2 warnings (0.03 sec)

select table_name,engine from information_schema.tables where table_schema='test' and table_name='test'; # 转为默认存储引擎
+------------+--------+
| table_name | engine |
+------------+--------+
| test       | InnoDB |
+------------+--------+
SET sql_mode='NO_ENGINE_SUBSTITUTION';
create table test(id int) ENGINE=test;
# 报错
ERROR 1286 (42000): Unknown storage engine 'test'

りー

以上がMySQL 5.7 で SQL_MODE を設定する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。