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會支援報錯。

 

mysql sql_mode 之 NO_ENGINE_SUBSTITUTION