mysql no_engine_substitution_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.11sec)

mysql> show create tablet;+-------+--------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------+

| t | CREATE TABLE`t` (

`x`int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+--------------------------------------------------------------------------------------+

1 row in set (0.00sec)

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;

ERROR1286 (42000): Unknown storage engine 'federated'

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