NO_ENGINE_SUBSTITUTION 是干啥的，这个还真没注意过以前。
研究了一下，原来这个是在创建表指定engine子句的时候，让mysql对此DDL做判断用的。看一个例子：
mysql> show variables like '%sql_mode%';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.01 sec)
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> use test ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table t_e (it int ) engine=FEDERATE1;
ERROR 1286 (42000): Unknown storage engine 'FEDERATE1' #FEDERATE1引擎不存在，直接报错。
当前 sql_mode="NO_ENGINE_SUBSTITUTION"，表示不进行引擎替换。就是说，如果DDL的engine子句指定的引擎不存在，直接报错。
再看下面的例子。
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t_e (it int ) engine=FEDERATE1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
1 row in set (0.00 sec)
mysql> show warnings ;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1286 | Unknown storage engine 'FEDERATE1' |
| Warning | 1266 | Using storage engine InnoDB for table 't_e' |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)
mysql> show create table t_e;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------+
| t_e | CREATE TABLE `t_e` (
`it` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------+
#引擎已被替换成InnoDB
去掉 NO_ENGINE_SUBSTITUTION ，此时，依然指定一个不存在的引擎，mysql自动将引擎替换成默认的innodb引擎。
去掉 NO_ENGINE_SUBSTITUTION ，对于alter操作的行为是：alter操作并不成功。
mysql> show create table t_e;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------+
| t_e | CREATE TABLE `t_e` (
`it` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#以前是MyISAM引擎。
mysql> alter table t_e engine=FEDERATE1; #修改成不存在的FEDERATE1引擎
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show warnings ;
+---------+------+------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------+
| Warning | 1286 | Unknown storage engine 'FEDERATE1' |
+---------+------+------------------------------------+
1 row in set (0.00 sec)
mysql> show create table t_e;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------+
| t_e | CREATE TABLE `t_e` (
`it` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | #引擎并没修改。
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
如下是官网解释：
NO_ENGINE_SUBSTITUTION
Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.
The default SQL mode includes NO_ENGINE_SUBSTITUTION.
Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:
With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.
With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.