mysql sql_mode 之 NO_ENGINE_SUBSTITUTION

知识储备：

　　1、mysql 有众多的存储引擎，然而只有一个默认的存储引擎，通常来说它是innodb

　　2、mysql 可以通过sql_mode 来控制mysql 数据库的行为，今天我们要讲的就是no_engine_substitution

　　3、no_engine_subtitution的作用：mysql 在create table 时可以指定engine子句；这个engine子句用于指定表的存储引擎，那么问题就来了。

　　　  如果我把引擎指定成一个并不存在的引擎！这个时候mysql可以有两种行为供选择 1、直接报错；2、把表的存储引擎替换成innodb

 

例子：

　　1、环境检查(查看mysql所支持的存储引擎，sql_mode模式)

　　001：存储引擎，由下图可以看出在这个mysql上不支持federated 引擎

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         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

　　002：sql_mode

mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

　　003：测试

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

mysql> show create table t;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

结论001：在sql_mode中不包涵no_engine_subtitution 且create table 中engine子句指定的存储引擎不被支持时，mysql会把表的引擎改为innodb。

 

例子：

　　在sql_mode包涵有no_engine_subtitution时

　　引擎支持情况：

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         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

　　sql_mode情况：

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

　　测试：

mysql> create table t2(x int) engine=federated;
ERROR 1286 (42000): Unknown storage engine 'federated'

结论002：当sql_mode中包涵no_engine_subtitution时，如果create table 时指定的engine项不被支持，这个时候mysql会支持报错。