知识储备：
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会支持报错。