MySQL中SQL_MODE的使用

這篇文章將為大家詳細(xì)講解有關(guān)MySQL中SQL_MODE的使用，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

SQL_MODE是MySQL中的一個(gè)系統(tǒng)變量（variable），可由多個(gè)MODE組成，每個(gè)MODE控制一種行為，如是否允許除數(shù)為0，日期中是否允許'0000-00-00'值。

首先，看三個(gè)簡(jiǎn)單的Demo（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)

實(shí)際存儲(chǔ)值與插入值不符。

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導(dǎo)致原列內(nèi)容丟失。

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)

顯式指定列和不顯式指定的處理邏輯竟然不一樣。

為什么會(huì)這樣呢？這個(gè)即與SQL_MODE有關(guān)。

在MySQL 5.6中， SQL_MODE的默認(rèn)值為"NO_ENGINE_SUBSTITUTION"，非嚴(yán)格模式。

在這種模式下，在進(jìn)行數(shù)據(jù)變更操作時(shí)，如果涉及的列中存在無效值（如日期不存在，數(shù)據(jù)類型不對(duì)，數(shù)據(jù)溢出），只會(huì)提示"Warning"，并不會(huì)報(bào)錯(cuò)。

如果要規(guī)避上述問題，需開啟SQL_MODE的嚴(yán)格模式。

SQL_MODE的嚴(yán)格模式

所謂的嚴(yán)格模式，即SQL_MODE中開啟了STRICT_ALL_TABLES或STRICT_TRANS_TAB LES。

還是上面的Demo，看看嚴(yán)格模式下，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，在嚴(yán)格模式下，直接提示"ERROR"，而不是"Warning"。

同是嚴(yán)格模式，下面看看STRICT_ALL_TABLES或STRICT_TRAN S_TABLES的區(qū)別。

STRICT_ALL_TABLES與STRICT_TRANS_TABLES的區(qū)別

STRICT_TRANS_TABLES只對(duì)事務(wù)表開啟嚴(yán)格模式，STRICT_ALL_TABLES是對(duì)所有表開啟嚴(yán)格模式，不僅僅是事務(wù)表，還包括非事務(wù)表。

看下面這個(gè)測(cè)試。

對(duì)myisam表插入3條數(shù)據(jù)，其中，第3條數(shù)據(jù)是空字符串，與定義的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存儲(chǔ)引擎的情況下，只有開啟STRICT_ALL_TABLES才會(huì)報(bào)錯(cuò)。

不同版本默認(rèn)的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_TABLES, NO_ZERO_IN_DATE,  NO_ZERO_DATE, ERROR_FOR_pISION_BY_ZERO, NO_ENGINE_SUBSTITUTION

如何修改SQL_MODE

SQL_MODE既可在全局級(jí)別修改，又可在會(huì)話級(jí)別修改?？芍付ǘ鄠€(gè)MODE，MODE之間用逗號(hào)隔開。

全局級(jí)別

set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';

會(huì)話級(jí)別

set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';

SQL_MODE的完整列表

ALLOW_INVALID_DATES

在嚴(yán)格模式下，對(duì)于日期的檢測(cè)較為嚴(yán)格，其必須有效。若開啟該MODE，對(duì)于month和day的檢測(cè)會(huì)相對(duì)寬松。其中，month只需在1~12之間，day只需在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)

注意，該MODE只適用于DATE和DATETIME，不適用于TIMESTAMP。

ANSI_QUOTES

在MySQL中，對(duì)于關(guān)鍵字和保留字，是不允許用做表名和字段名的。如果一定要使用，必須使用反引號(hào)（"`"）進(jìn)行轉(zhuǎn)義。

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)

若開啟該MODE，則雙引號(hào)，同反引號(hào)一樣，可對(duì)關(guān)鍵字和保留字轉(zhuǎn)義。

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)

需要注意的是，在開啟該MODE的情況下，不能再用雙引號(hào)來引字符串。

ERROR_FOR_pISION_BY_ZERO

該MODE決定除數(shù)為0的處理邏輯，實(shí)際效果還取決于是否開啟嚴(yán)格模式。

1. 開啟嚴(yán)格模式，且開啟該MODE，插入1/0，會(huì)直接報(bào)錯(cuò)。

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. 只開啟嚴(yán)格模式，不開啟該MODE，允許1/0的插入，且不提示warning，1/0最后會(huì)轉(zhuǎn)化為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. 不開啟嚴(yán)格模式，只開啟該MODE，允許1/0的插入，但提示warning。

4. 不開啟嚴(yán)格模式，也不開啟該MODE，允許1/0的插入，且不提示warning，同2一樣。

HIGH_NOT_PRECEDENCE

默認(rèn)情況下，NOT的優(yōu)先級(jí)低于比較運(yùn)算符。但在某些低版本中，NOT的優(yōu)先級(jí)高于比較運(yùn)算符。

看看兩者的區(qū)別。

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相當(dāng)于not (1 < -1)，如果設(shè)置了'HIGH_ NOT_PRECEDENCE'，則相當(dāng)于(not 1) < -1。

IGNORE_SPACE

默認(rèn)情況下，函數(shù)名和左括號(hào)（“(”）之間不允許存在空格。若開啟該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

默認(rèn)情況下，在對(duì)自增主鍵插入NULL或0時(shí)，會(huì)自動(dòng)生成下一個(gè)值。若開啟該MODE，當(dāng)插入0時(shí)，并不會(huì)自動(dòng)生成下一個(gè)值。

如果表中自增主鍵列存在0值，在進(jìn)行邏輯備份還原時(shí)，可能會(huì)導(dǎo)致數(shù)據(jù)不一致。所以mysqldump在生成備份數(shù)據(jù)之前，會(huì)自動(dòng)開啟該MODE，以避免數(shù)據(jù)不一致的情況。

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

默認(rèn)情況下，反斜杠“\”會(huì)作為轉(zhuǎn)義符，若開啟該MODE，則反斜杠“\”會(huì)作為一個(gè)普通字符，而不是轉(zhuǎn)義符。

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

默認(rèn)情況下，在創(chuàng)建表時(shí)，可以指定數(shù)據(jù)目錄（DATA DIRECTORY）和索引目錄（INDEX DIRECTORY），若開啟該MODE，則會(huì)忽略這兩個(gè)選項(xiàng)。在主從復(fù)制場(chǎng)景下，可在從庫上開啟該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的情況下，在創(chuàng)建表時(shí)，如果指定的存儲(chǔ)引擎不存在或不支持，則會(huì)直接提示“ERROR”。

若不開啟，則只會(huì)提示“Warning”，且使用默認(rèn)的存儲(chǔ)引擎。

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

兩個(gè)整數(shù)相減，如果其中一個(gè)數(shù)是無符號(hào)位，默認(rèn)情況下，會(huì)產(chǎn)生一個(gè)無符號(hào)位的值，如果該值為負(fù)數(shù)，則會(huì)提示“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，則允許結(jié)果為負(fù)數(shù)。

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會(huì)影響'0000-00-00'的插入。實(shí)際效果還取決于是否開啟嚴(yán)格模式。

1. 在開啟嚴(yán)格模式，且同時(shí)開啟該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. 只開啟嚴(yán)格模式，不開啟該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. 不開啟嚴(yán)格模式，只開啟該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. 不開啟嚴(yán)格模式，也不開啟該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針對(duì)的是'0000-00-00'，而NO_ZERO_IN_DATE針對(duì)的是年不為0，但月或者日為0的日期，如，'2010-00-01' or '2010-01-00'。

實(shí)際效果也是取決于是否開啟嚴(yán)格模式，同NO_ZERO_DATE一樣。

ONLY_FULL_GROUP_BY

開啟該MODE，則SELECT列表中只能出現(xiàn)分組列和聚合函數(shù)。

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列表中出現(xiàn)任意列，但這些列的值并不是確定的，官方文檔中也提到了這一點(diǎn)。

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

在對(duì)CHAR字段進(jìn)行存儲(chǔ)時(shí)，在Compact格式下，會(huì)占用固定長(zhǎng)度的字節(jié)。

如下面的c1列，定義為char(10)，雖然'ab'只占用兩個(gè)字節(jié)，但在Compact格式下，會(huì)占用10個(gè)字節(jié)，不足部分以空格填充。

在查詢時(shí)，默認(rèn)情況下，會(huì)剔除掉末尾的空格。若開啟該MODE，則不會(huì)剔除，每次都會(huì)返回固定長(zhǎng)度的字符。

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只能連接兩個(gè)字符串（MySQL中的concat可連接多個(gè)字符），局限性太大，如果要連接多個(gè)字符串，一般用的是管道符。

開啟該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

在創(chuàng)建表時(shí)，數(shù)據(jù)類型可指定為real，默認(rèn)情況下，其會(huì)轉(zhuǎn)化為double，若開啟該MODE，則會(huì)轉(zhuǎn)化為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

對(duì)事務(wù)表開啟嚴(yán)格模式。

STRICT_TRANS_TABLES

對(duì)所有表開啟嚴(yán)格模式。

TIME_TRUNCATE_FRACTIONAL

在時(shí)間類型定義了小數(shù)秒的情況下，如果插入的位數(shù)大于指定的位數(shù)，默認(rèn)情況下，會(huì)四舍五入，若開啟了該MODE，則會(huì)直接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之前，直接授權(quán)會(huì)隱式創(chuàng)建用戶。

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中是會(huì)報(bào)錯(cuò)的。

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語句隱式創(chuàng)建用戶，所以，該MODE在8.0中也不存在。

從字面上看，該MODE是禁止授權(quán)時(shí)隱式創(chuàng)建用戶。但在實(shí)際測(cè)試過程中，發(fā)現(xiàn)其并不能禁止。

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)

其實(shí)，該MODE禁止的只是不帶“identified by”子句的grant語句，對(duì)于帶有“identified by”子句的grant語句，其并不會(huì)禁止。

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設(shè)置為ANSI, DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, TRADITIONAL。

其實(shí)，這些MODE只是上述MODE的一種組合，目的是為了和其它數(shù)據(jù)庫兼容。

在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)

總結(jié)

1. SQL_MODE在非嚴(yán)格模式下，會(huì)出現(xiàn)很多意料不到的結(jié)果。建議線上開啟嚴(yán)格模式。但對(duì)于線上老的環(huán)境，如果一開始就運(yùn)行在非嚴(yán)格模式下，切忌直接調(diào)整，畢竟兩者的差異性還是相當(dāng)巨大。

2. 官方默認(rèn)的SQL_MODE一直在發(fā)生變化，MySQL 5.5, 5.6, 5.7就不盡相同，但總體是趨嚴(yán)的，在對(duì)數(shù)據(jù)庫進(jìn)行升級(jí)時(shí)，其必須考慮默認(rèn)的SQL_MODE是否需要調(diào)整。

3. 在進(jìn)行數(shù)據(jù)庫遷移時(shí)，可通過調(diào)整SQL_MODE來兼容其它數(shù)據(jù)庫的語法。

到此為止, 關(guān)于MySQL中SQL_MODE有了一個(gè)基礎(chǔ)的認(rèn)識(shí), 但是對(duì)于具體的使用方法還是需要多加鞏固和練習(xí)，如果想了解更多相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊。