MySQL Sql_mode's no_engine_substitution

Knowledge Reserve:

1, MySQL has a large number of storage engines, but only a default storage engine, usually it is InnoDB

2, MySQL can be sql_mode to control the behavior of MySQL database, today we want to talk about is no_engine_substitution

3, no_engine_subtitution function: MySQL can specify the engine clause when CREATE TABLE, this engine clause is used to specify the storage engine of the table, then the problem comes.

If I designate the engine as an engine that does not exist! This time MySQL can have two kinds of behavior to choose 1, direct error; 2. Replace the table's storage engine with InnoDB

Example:

1. Environment check (see MySQL supported storage engine, Sql_mode mode)

001: Storage engine, as can be seen on this MySQL does not support the federated engine

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 ofIdentical MyISAM tables|NO|NO|NO||Blackhole|YES| /Dev/NULLStorage engine (anything you write toIt disappears)|NO|NO|NO||MEMORY|YES|Hash based, storedinchMemory, useful for TemporaryTables|NO|NO|NO||Performance_schema|YES|PerformanceSchema                                             |NO|NO|NO||ARCHIVE|YES|Archive Storage Engine|NO|NO|NO||InnoDB| DEFAULT |Supports transactions, row- LevelLocking and ForeignKeys|YES|YES|YES||Federated|NO|Federated MySQL Storage Engine| NULL         | NULL | NULL       |+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+9Rowsinch Set(0.00Sec

002:sql_mode

Mysql>Show variables like 'Sql_mode';+---------------+---------------------+|Variable_name|Value|+---------------+---------------------+|Sql_mode|Strict_trans_tables|+---------------+---------------------+1Rowinch Set(0.00Sec

003: Test

Mysql> Create TableT (xint) engine=Federated; Query OK,0Rows affected,2Warnings (0.11sec) MySQL>ShowCreate TableT;+-------+--------------------------------------------------------------------------------------+| Table | Create Table                                                                         |+-------+--------------------------------------------------------------------------------------+|T| CREATE TABLE' t ' (' X 'int( One)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=Latin1|+-------+--------------------------------------------------------------------------------------+1Rowinch Set(0.00sec) MySQL> 

Conclusion 001: when the Sql_mode does not contain no_engine_subtitution and the storage engine specified in CREATE TABLE is not supported, MySQL will change the engine of the table to InnoDB.

Example:

When Sql_mode has no_engine_subtitution,

Engine support situation:

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 ofIdentical MyISAM tables|NO|NO|NO||Blackhole|YES| /Dev/NULLStorage engine (anything you write toIt disappears)|NO|NO|NO||MEMORY|YES|Hash based, storedinchMemory, useful for TemporaryTables|NO|NO|NO||Performance_schema|YES|PerformanceSchema                                             |NO|NO|NO||ARCHIVE|YES|Archive Storage Engine|NO|NO|NO||InnoDB| DEFAULT |Supports transactions, row- LevelLocking and ForeignKeys|YES|YES|YES||Federated|NO|Federated MySQL Storage Engine| NULL         | NULL | NULL       |+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+9Rowsinch Set(0.00Sec

Sql_mode situation:

Mysql>Show variables like 'Sql_mode';+---------------+--------------------------------------------+|Variable_name|Value|+---------------+--------------------------------------------+|Sql_mode|Strict_trans_tables,no_engine_substitution|+---------------+--------------------------------------------+1Rowinch Set(0.00Sec

Test:

MySQL>createtableint) engine=1286 (42000  'Federated'

Conclusion 002: if the engine item specified when CREATE TABLE is not supported when Sql_mode includes No_engine_subtitution, MySQL will support the error.

MySQL Sql_mode's no_engine_substitution