mysql 5.5 中，该值默认为空值：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.29-log |
+------------+
1 row
in
set
(0.00 sec)
mysql> SHOW VARIABLES LIKE
'%SQL_MODE%'
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row
in
set
(0.00 sec)
|
mysql 5.6中：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.11 |
+-----------+
1 row
in
set
(0.00 sec)
mysql> SHOW VARIABLES LIKE
'%SQL_MODE%'
;
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row
in
set
(0.00 sec)
|
关于这两个值的含义，我引用姜承尧http://weibo.com/insidemysql《mysql技术内幕：Innodb存储引擎》这本书中的要丢安描述：
验证NO_ENGINE_SUBSTITUTION:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.29-log |
+------------+
1 row
in
set
(0.00 sec)
mysql> SHOW ENGINES;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored
in
memory, useful
for
temporary tables | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows
in
set
(0.00 sec)
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| clovem |
+------------+
1 row
in
set
(0.00 sec)
mysql> CREATE TABLE t1 (
id
int) ENGINE=clovem;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`
id
` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
|
可以看出，mysql5.5中，在创建表的时候任意指定一个存储引擎，均不会报错，只不过如果该存储引擎系统不支持，则设置为默认存储引擎。下面看mysql5.6中设置了SQL_MODE的情况
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.11 |
+-----------+
1 row
in
set
(0.00 sec)
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES |
/dev/null
storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored
in
memory, useful
for
temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows
in
set
(0.00 sec)
mysql> CREATE TABLE t1 (
id
int) ENGINE=FEDERATED;
ERROR 1286 (42000): Unknown storage engine
'FEDERATED'
mysql> CREATE TABLE t2 (
id
int) ENGINE=clovem;
ERROR 1286 (42000): Unknown storage engine
'clovem'
mysql> CREATE TABLE t3 (
id
int) ENGINE=CSV;
ERROR 1178 (42000): The storage engine
for
the table doesn't support nullable columns
mysql> CREATE TABLE t3 (
id
int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t3 |
+----------------+
1 row
in
set
(0.00 sec)
|
可以看出，系统不支持的存储引擎以及不存在的存储引擎或者该存储引擎不支持表列特性的均不能创建成功。
本文转自 暗黑魔君 51CTO博客，原文链接：http://blog.51cto.com/clovemfong/1204765，如需转载请自行联系原作者
