コミュニティ 学ぶ ツールライブラリ レジャー
検索
日本語
ホームページ > データベース > mysql チュートリアル > MySQL を使用する場合、SQL_MODE の落とし穴は何ですか?

MySQL を使用する場合、SQL_MODE の落とし穴は何ですか?

青灯夜游
リリース： 2019-11-29 16:11:20
転載
2850 人が閲覧しました

MySQL を使用する場合、SQL_MODE の落とし穴は何ですか?

SQL_MODE は MySQL のシステム変数 (変数) であり、複数の MODE で構成できます。各 MODE は、約数が 0 であることを許可するかどうかなどの動作を制御します。日付では使用できます。0000-00-00' の値。

SQL_MODE に注意を払う必要があるのはなぜですか?

まず、3 つの簡単なデモ (MySQL 5.6) を見てみましょう。

1.

mysql> create table t1(c1 datetime);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into t1 values('2019-02-29');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t1;
+---------------------+
| c1                  |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
row in set (0.00 sec)
ログイン後にコピー

実際に保存された値は、挿入された値と一致しません。

2. 

mysql> create table t2(c1 varchar(10));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t2 values('a'),('b'),('c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| c1   |
+------+
| a    |
| b    |
| c    |
+------+
rows in set (0.00 sec)

mysql> alter table t2 modify column c1 int;
Query OK, 3 rows affected, 3 warnings (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> show warnings;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'a' for column 'c1' at row 1 |
| Warning | 1366 | Incorrect integer value: 'b' for column 'c1' at row 2 |
| Warning | 1366 | Incorrect integer value: 'c' for column 'c1' at row 3 |
+---------+------+-------------------------------------------------------+
rows in set (0.00 sec)

mysql> select * from t2;
+------+
| c1   |
+------+
|    0 |
|    0 |
|    0 |
+------+
rows in set (0.00 sec)
ログイン後にコピー

DDL により、元の列の内容が失われます。

3. 

mysql> create table t3(id int not null,c1 varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t3 values(null,'a');
ERROR 1048 (23000): Column 'id' cannot be null

mysql> insert into t3(c1) values('a');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'id' doesn't have a default value |
+---------+------+-----------------------------------------+
row in set (0.00 sec)

mysql> select * from t3;
+----+------+
| id | c1   |
+----+------+
|  0 | a    |
+----+------+
row in set (0.00 sec)
ログイン後にコピー

列を明示的に指定する場合と明示的に指定しない場合の処理​​ロジックは、実際には異なります。

なぜそうなるのでしょうか?これは SQL_MODE に関連します。

MySQL 5.6 では、SQL_MODE のデフォルト値は「NO_ENGINE_SUBSTITUTION」、つまり非厳密モードです。

このモードでは、データ変更操作を実行するときに、関連する列に無効な値がある場合 (日付が存在しない、データ型が間違っている、データがオーバーフローするなど)、「警告」というメッセージが表示されますが、エラーは報告されません。

上記の問題を回避したい場合は、SQL_MODE の厳密モードを有効にする必要があります。

SQL_MODE の厳密モード

いわゆる厳密モードとは、SQL_MODE で STRICT_ALL_TABLES または STRICT_TRANS_TAB LES がオンになっていることを意味します。

まだ上記のデモですが、厳密モードでの MySQL の処理ロジックを見てください。 

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('2019-02-29');
ERROR 1292 (22007): Incorrect datetime value: '2019-02-29' for column 'c1' at row 1

mysql> alter table t2 modify column c1 int;
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'c1' at row 1

mysql> insert into t3(c1) values('a');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
ログイン後にコピー

同じ SQL を厳密モードで実行すると、「警告」ではなく「エラー」が直接表示されます。

どちらも厳密モードです。STRICT_ALL_TABLES と STRICT_TRAN S_TABLES の違いを見てみましょう。

STRICT_ALL_TABLES と STRICT_TRANS_TABLES の違い

STRICT_TRANS_TABLES はトランザクション テーブルに対してのみストリクト モードをオンにし、STRICT_ALL_TABLES はすべてのテーブルに対してストリクト モードをオンにします。非トランザクション テーブルを含むトランザクション テーブルのみ。

以下のテストを見てください。

myisam テーブルに 3 つのデータを挿入します。3 番目のデータは空の文字列であり、定義された int 型と一致しません。 

mysql> create table t (c1 int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values (1),(2),('');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'c1' at row 3 |
+---------+------+------------------------------------------------------+
row in set (0.00 sec)

mysql> select * from t;
+------+
| c1   |
+------+
|    1 |
|    2 |
|    0 |
+------+
rows in set (0.00 sec)

mysql> set session sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values (1),(2),('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'c1' at row 3
ログイン後にコピー

テーブルが myisam ストレージ エンジンの場合、STRICT_ALL_TABLES がオンになっている場合にのみエラーが報告されることがわかります。

さまざまなバージョンのデフォルト SQL_MODE

MySQL 5.5: 空

MySQL 5.6: NO_ENGINE_SUBSTITUTION

MySQL 5.7: ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_pISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION

MySQL 8.0: ONLY_FULL_GROUP_BY、STRICT_TRANS_T ABLES、NO_ZERO_IN_DATE、NO_ ZERO_DATE、ERROR_FOR_pISION_BY_ZERO、NO_ENGINE_SUBSTITUTION

方法SQL_MODE の変更

SQL_MODE は、グローバル レベルとセッション レベルの両方で変更できます。複数の MODE をカンマで区切って指定できます。

グローバル レベル

set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
ログイン後にコピー

セッション レベル

set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
ログイン後にコピー

SQL_MODEの完全なリスト

##ALLOW_INVALID_DATES

厳密モー​​ドでは、日付の検出がより厳密になり、有効である必要があります。このMODEをONにすると月日の検出が比較的緩やかになります。このうち、月は 1 ～ 12、日は有効かどうかに関係なく、1 ～ 31 の間だけあればよく、たとえば、以下の「2004-02-31」のようになります。 

mysql> create table t (c1 datetime);
Query OK, 0 rows affected (0.21 sec)

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values('2004-02-31');
ERROR 1292 (22007): Incorrect datetime value: '2004-02-31' for column 'c1' at row 1

mysql> set session sql_mode='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values('2004-02-31');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+---------------------+
| c1 |
+---------------------+
| 2004-02-31 00:00:00 |
+---------------------+
row in set (0.00 sec)
ログイン後にコピー
このモードは DATE と DATETIME にのみ適用され、TIMESTAMP には適用されないことに注意してください。

ANSI_QUOTES

MySQL では、キーワードや予約語をテーブル名やフィールド名として使用することはできません。使用する必要がある場合は、バッククォート ("`") でエスケープする必要があります。 

mysql> create table order (id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (id int)' at line 1

mysql> create table `order` (id int);
Query OK, 0 rows affected (0.12 sec)
ログイン後にコピー
このモードがオンになっている場合、バックティックと同様に二重引用符でキーワードや予約語をエスケープできます。 

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table "order" (c1 int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"order" (c1 int)' at line 1

mysql> set session sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> create table "order" (c1 int);
Query OK, 0 rows affected (0.17 sec)
ログイン後にコピー
このモードをオンにすると、二重引用符を使用して文字列を引用できなくなることに注意してください。

ERROR_FOR_pISION_BY_ZERO

この MODE は、0 による除算の処理ロジックを決定します。実際の効果は、厳密モードがオンになっているかどうかによっても異なります。

1. 厳密モードをオンにし、MODE を有効にして 1/0 を挿入すると、エラーが直接報告されます。 

mysql> create table t (c1 double);
Query OK, 0 rows affected (0.04 sec)

mysql> set session sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values(1/0);
ERROR 1365 (22012): Division by 0
ログイン後にコピー
2. 厳密モードのみを有効にし、このモードを有効にせず、1/0 の挿入を許可し、警告を表示しません。1/0 は最終的に NULL に変換されます。 

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values(1/0);
Query OK, 1 row affected (0.07 sec)

mysql> select * from t;
+------+
| c1  |
+------+
| NULL |
+------+
row in set (0.00 sec)
ログイン後にコピー
3. 厳密モードを有効にせず、1/0 の挿入を許可する MODE のみを有効にしますが、警告が表示されます。

4. 厳密モードを有効にしたり、2 と同様に、警告を表示せずに 1/0 の挿入を許可する MODE を有効にしたりしません。

HIGH_NOT_PRECEDENCE

デフォルトでは、NOT の優先順位は比較演算子よりも低くなります。ただし、一部の以前のバージョンでは、NOT の方が比較演算子よりも優先されます。

この 2 つの違いを見てください。 

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select not 1 < -1;
+------------+
| not 1 < -1 |
+------------+
|          1 |
+------------+
row in set (0.00 sec)

mysql> set session sql_mode='HIGH_NOT_PRECEDENCE';
Query OK, 0 rows affected (0.00 sec)

mysql> select not 1 < -1;
+------------+
| not 1 < -1 |
+------------+
|          0 |
+------------+
row in set (0.00 sec)
ログイン後にコピー

SQL_mode が空の場合、not 1 < -1 は not (1 < -1) と同等です。'HIGH_ NOT_PRECEDENCE' が設定されている場合は、(not 1) < - 1 と同等です。 。

IGNORE_SPACE

默认情况下，函数名和左括号（“(”）之间不允许存在空格。若开启该MODE，则允许。

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
row in set (0.00 sec)

mysql> select count (*) from t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from t' at line 1

mysql> set session sql_mode='IGNORE_SPACE';
Query OK, 0 rows affected (0.01 sec)

mysql> select count (*) from t;
+-----------+
| count (*) |
+-----------+
|         2 |
+-----------+
row in set (0.01 sec)
ログイン後にコピー

NO_AUTO_VALUE_ON_ZERO

默认情况下，在对自增主键插入NULL或0时，会自动生成下一个值。若开启该MODE，当插入0时，并不会自动生成下一个值。

如果表中自增主键列存在0值，在进行逻辑备份还原时，可能会导致数据不一致。所以mysqldump在生成备份数据之前，会自动开启该MODE，以避免数据不一致的情况。

mysql> create table t (id int auto_increment primary key);
Query OK, 0 rows affected (0.11 sec)

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (0);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t;
+----+
| id |
+----+
|  1 |
+----+
row in set (0.00 sec)

mysql> set session sql_mode='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values (0);
Query OK, 1 row affected (0.09 sec)

mysql> select * from t;
+----+
| id |
+----+
|  0 |
|  1 |
+----+
rows in set (0.00 sec)
ログイン後にコピー

NO_BACKSLASH_ESCAPES

默认情况下，反斜杠“\”会作为转义符，若开启该MODE，则反斜杠“\”会作为一个普通字符，而不是转义符。

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.01 sec)

mysql> select '\\t';
+----+
| \t |
+----+
| \t |
+----+
row in set (0.00 sec)

mysql> set session sql_mode='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> select '\\t';
+-----+
| \\t |
+-----+
| \\t |
+-----+
row in set (0.00 sec)
ログイン後にコピー

NO_DIR_IN_CREATE

默认情况下，在创建表时，可以指定数据目录（DATA DIRECTORY）和索引目录（INDEX DIRECTORY），若开启该MODE，则会忽略这两个选项。在主从复制场景下，可在从库上开启该MODE。

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (id int) data directory '/tmp/';
Query OK, 0 rows affected (0.15 sec)

mysql> show create table t\G
*************************** 1. row ***************************
      Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='/tmp/'
row in set (0.00 sec)

mysql> set session sql_mode='NO_DIR_IN_CREATE';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.11 sec)

mysql> create table t (id int) data directory '/tmp/';
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show create table t\G
*************************** 1. row ***************************
      Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
row in set (0.00 sec)
ログイン後にコピー

NO_ENGINE_SUBSTITUTION

在开启该MODE的情况下，在创建表时，如果指定的存储引擎不存在或不支持，则会直接提示“ERROR”。

若不开启，则只会提示“Warning”，且使用默认的存储引擎。

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (id int) engine=federated;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1286 | Unknown storage engine 'federated'        |
| Warning | 1266 | Using storage engine InnoDB for table 't' |
+---------+------+-------------------------------------------+
rows in set (0.00 sec)

mysql> show create table t\G
*************************** 1. row ***************************
      Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
row in set (0.01 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.11 sec)

mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (id int) engine=federated;
ERROR 1286 (42000): Unknown storage engine 'federated'
ログイン後にコピー

NO_UNSIGNED_SUBTRACTION

两个整数相减，如果其中一个数是无符号位，默认情况下，会产生一个无符号位的值，如果该值为负数，则会提示“ERROR”。如，

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select cast(0 as unsigned)-1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
ログイン後にコピー

若开启该MODE，则允许结果为负数。

mysql> set session sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select cast(0 as unsigned)-1;
+-----------------------+
| cast(0 as unsigned)-1 |
+-----------------------+
|                    -1 |
+-----------------------+
row in set (0.00 sec)
ログイン後にコピー

NO_ZERO_DATE

该MODE会影响'0000-00-00'的插入。实际效果还取决于是否开启严格模式。

1. 在开启严格模式，且同时开启该MODE，是不允许'0000-00-00'插入的。

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
  Code: 3135
Message: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in
a future release.1 row in set (0.00 sec)

mysql> insert into t values ('0000-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'c1' at row 1
ログイン後にコピー

2. 只开启严格模式，不开启该MODE，允许'0000-00-00'值的插入，且不提示warning。

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values ('0000-00-00');
Query OK, 1 row affected (0.04 sec)
ログイン後にコピー

3. 不开启严格模式，只开启该MODE，允许'0000-00-00'值的插入，但提示warning。

mysql> set session sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values ('0000-00-00');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'c1' at row 1 |
+---------+------+---------------------------------------------+
row in set (0.01 sec)
ログイン後にコピー

4. 不开启严格模式，也不开启该MODE，允许'0000-00-00'值的插入，且不提示warning。

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values ('0000-00-00');
Query OK, 1 row affected (0.03 sec)
ログイン後にコピー

NO_ZERO_IN_DATE

同NO_ZERO_DATE类似，只不过NO_ZERO_DATE针对的是'0000-00-00'，而NO_ZERO_IN_DATE针对的是年不为0，但月或者日为0的日期，如，'2010-00-01' or '2010-01-00'。

实际效果也是取决于是否开启严格模式，同NO_ZERO_DATE一样。

ONLY_FULL_GROUP_BY

开启该MODE，则SELECT列表中只能出现分组列和聚合函数。

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select dept_no,emp_no,min(from_date) from dept_emp group by dept_no;
+---------+--------+----------------+
| dept_no | emp_no | min(from_date) |
+---------+--------+----------------+
| d001    |  10017 | 1985-01-01     |
| d002    |  10042 | 1985-01-01     |
| d003    |  10005 | 1985-01-01     |
| d004    |  10003 | 1985-01-01     |
| d005    |  10001 | 1985-01-01     |
| d006    |  10009 | 1985-01-01     |
| d007    |  10002 | 1985-01-01     |
| d008    |  10007 | 1985-01-01     |
| d009    |  10011 | 1985-01-01     |
+---------+--------+----------------+
rows in set (0.64 sec)

mysql> set session sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select dept_no,emp_no,min(from_date) from dept_emp group by dept_no;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.dept_emp.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
ログイン後にコピー

如果不开启该MODE，则允许SELECT列表中出现任意列，但这些列的值并不是确定的，官方文档中也提到了这一点。

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. 

In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. 

Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. 

Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
ログイン後にコピー

PAD_CHAR_TO_FULL_LENGTH

在对CHAR字段进行存储时，在Compact格式下，会占用固定长度的字节。

如下面的c1列，定义为char(10)，虽然'ab'只占用两个字节，但在Compact格式下，会占用10个字节，不足部分以空格填充。

在查询时，默认情况下，会剔除掉末尾的空格。若开启该MODE，则不会剔除，每次都会返回固定长度的字符。

mysql> create table t (c1 char(10));
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t values('ab');
Query OK, 1 row affected (0.11 sec)

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select c1, hex(c1), char_length(c1) from t;
+------+---------+-----------------+
| c1   | hex(c1) | char_length(c1) |
+------+---------+-----------------+
| ab   | 6162    |               2 |
+------+---------+-----------------+
row in set (0.00 sec)

mysql> set session sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> select c1, hex(c1), char_length(c1) from t;
+------------+----------------------+-----------------+
| c1         | hex(c1)              | char_length(c1) |
+------------+----------------------+-----------------+
| ab         | 61622020202020202020 |              10 |
+------------+----------------------+-----------------+
row in set (0.00 sec)
ログイン後にコピー

PIPES_AS_CONCAT

在Oracle中，连接字符串可用concat和管道符（"||"），但concat只能连接两个字符串（MySQL中的concat可连接多个字符），局限性太大，如果要连接多个字符串，一般用的是管道符。

开启该MODE，即可将管道符作为连接符。

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a'||'b';
+----------+
| 'a'||'b' |
+----------+
|        0 |
+----------+
row in set, 2 warnings (0.00 sec)

mysql> select concat('a','b');
+-----------------+
| concat('a','b') |
+-----------------+
| ab              |
+-----------------+
row in set (0.00 sec)

mysql> set session sql_mode='PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a'||'b';
+----------+
| 'a'||'b' |
+----------+
| ab       |
+----------+
row in set (0.00 sec)
ログイン後にコピー

REAL_AS_FLOAT

在创建表时，数据类型可指定为real，默认情况下，其会转化为double，若开启该MODE，则会转化为float。

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t ( c1 real);
Query OK, 0 rows affected (0.12 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
row in set (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.04 sec)

mysql> set session sql_mode='REAL_AS_FLOAT';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t ( c1 real);
Query OK, 0 rows affected (0.11 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
row in set (0.00 sec)
ログイン後にコピー

STRICT_ALL_TABLES

对事务表开启严格模式。

STRICT_TRANS_TABLES

对所有表开启严格模式。

TIME_TRUNCATE_FRACTIONAL

在时间类型定义了小数秒的情况下，如果插入的位数大于指定的位数，默认情况下，会四舍五入，若开启了该MODE，则会直接truncate掉。

mysql> create table t (c1 int,c2 datetime(2));
Query OK, 0 rows affected (0.04 sec)

mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1,'2018-08-08 11:12:13.125');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t;
+------+------------------------+
| c1   | c2                     |
+------+------------------------+
|    1 | 2018-08-08 11:12:13.13 |
+------+------------------------+
row in set (0.00 sec)

mysql> set session sql_mode='TIME_TRUNCATE_FRACTIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(2,'2018-08-08 11:12:13.125');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t;
+------+------------------------+
| c1   | c2                     |
+------+------------------------+
|    1 | 2018-08-08 11:12:13.13 |
|    2 | 2018-08-08 11:12:13.12 |
+------+------------------------+
rows in set (0.00 sec)
ログイン後にコピー

NO_AUTO_CREATE_USER

在MySQL 8.0之前，直接授权会隐式创建用户。

mysql> select host,user from mysql.user where user='u1';
Empty set (0.00 sec)

mysql> grant all on *.* to 'u1'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec)

mysql> select host,user from mysql.user where user='u1';
+------+------+
| host | user |
+------+------+
| %    | u1   |
+------+------+
row in set (0.00 sec)
ログイン後にコピー

同样的grant语句，在MySQL 8.0中是会报错的。

mysql> grant all on *.* to 'u1'@'%' identified by '123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123'' at line 1
ログイン後にコピー

在MySQL 8.0中，已不允许grant语句隐式创建用户，所以，该MODE在8.0中也不存在。

从字面上看，该MODE是禁止授权时隐式创建用户。但在实际测试过程中，发现其并不能禁止。

mysql> set session sql_mode='NO_AUTO_CREATE_USER';
Query OK, 0 rows affected (0.03 sec)

mysql> grant all on *.* to 'u1'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
ログイン後にコピー

其实，该MODE禁止的只是不带“identified by”子句的grant语句，对于带有“identified by”子句的grant语句，其并不会禁止。

mysql> drop user u1;
Query OK, 0 rows affected (0.00 sec)

mysql> set session sql_mode='NO_AUTO_CREATE_USER';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all on *.* to 'u1'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table

mysql> set session sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all on *.* to 'u1'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
ログイン後にコピー

SQL_MODE的常见组合

在MySQL 5.7中，还可将SQL_MODE设置为ANSI, DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, TRADITIONAL。

其实，这些MODE只是上述MODE的一种组合，目的是为了和其它数据库兼容。 

在MySQL 8.0中，只支持ANSI和TRADITIONAL这两种组合。

ANSI

等同于REAL_AS_FLOAT,  PIPES_AS_CONCAT, ANSI_QUOTES,  IGNORE_SPACE,  ONLY_FULL_GROUP_BY。

mysql> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------+
| Variable_name | Value                                                                          |
+---------------+--------------------------------------------------------------------------------+
| sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+---------------+--------------------------------------------------------------------------------+
row in set (0.03 sec)
ログイン後にコピー

TRADITIONAL

等同于STRICT_TRANS_TABLES,  STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_pISION_BY_ZERO, NO_ENGINE_SUBSTITUTION。

mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                            |
+---------------+----------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------------------------------------+
row in set (0.01 sec)
ログイン後にコピー

总结

1. SQL_MODE在非严格模式下，会出现很多意料不到的结果。建议线上开启严格模式。但对于线上老的环境，如果一开始就运行在非严格模式下，切忌直接调整，毕竟两者的差异性还是相当巨大。

2. 官方默认的SQL_MODE一直在发生变化，MySQL 5.5, 5.6, 5.7就不尽相同，但总体是趋严的，在对数据库进行升级时，其必须考虑默认的SQL_MODE是否需要调整。

3. 在进行数据库迁移时，可通过调整SQL_MODE来兼容其它数据库的语法。

 推荐学习：MySQL教程

以上がMySQL を使用する場合、SQL_MODE の落とし穴は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

関連ラベル：
mysql sql_mode
ソース：cnblogs.com
前の記事：MySQL データベースのマルチテーブル クエリに関する簡単な説明 次の記事：MySQLのバックアップとリカバリの詳しい説明
このウェブサイトの声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
著者別の最新記事
最新の問題
結果セットから最小値のみを表示する方法 (MYSQL) 次のステートメントがあります: selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommessagewhere...
から 2024-04-06 21:44:19
0
1
603
MySQL SQLクエリを使用して別のテーブルのフィールドの合計を計算する 次のようなスキーマがあります。 属性「user_id」と「username」を持つユーザーテーブルと、属性「customer_id」（user_idのFK）と「finalPrice...
から 2024-04-06 19:39:29
0
1
441
MySQL: 「<ここの変数またはサブクエリ> として...からテキストを選択します」 次のデータを含む次のテーブルがあります: id text language 1 german text german 2 english text english 私が望むのは、次...
から 2024-04-06 19:25:54
0
2
529
MySQL でグループ化してカウントするにはどうすればよいですか? 友人に送信され、削除されていないメッセージの合計数を抽出するクエリを作成しようとしています。これはテーブル構造のスクリーンショットです。 達成したい出力は次のとおりです。 idme...
から 2024-04-06 18:30:17
0
1
353
MySQL は複数のテーブルからデータを取得します 次の列を含む eg_design テーブル、および次の列を含む eg_domains テーブル、および次の列を含む eg_fonts テーブルがあります。 $domain_id に...
から 2024-04-06 18:42:44
0
2
479
関連トピック
詳細>
人気のおすすめ
人気のチュートリアル
詳細>
関連するチュートリアル
人気のおすすめ
最新のコース
最新のダウンロード
詳細>
ウェブエフェクト
公式サイト
サイト素材
フロントエンドテンプレート