mysql SQL_mode参数_mysql SQL_MODE 参数作用

mysql 5.5 中，该值默认为空值：mysql> SELECT VERSION();

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

| VERSION() |

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

| 5.5.29-log |

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

1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%SQL_MODE%';

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

| Variable_name | Value |

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

| sql_mode | |

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

1 row in set (0.00 sec)

mysql 5.6中：mysql> SELECT VERSION();

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

| VERSION() |

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

| 5.6.11 |

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

1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%SQL_MODE%';

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

| Variable_name | Value |

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

| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

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

1 row in set (0.00 sec)

关于这两个值的含义，我引用姜承尧http://weibo.com/insidemysql《mysql技术内幕：Innodb存储引擎》这本书中的要丢安描述：

验证NO_ENGINE_SUBSTITUTION:mysql> SELECT VERSION();

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

| VERSION() |

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

| 5.5.29-log |

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

1 row in set (0.00 sec)

mysql> SHOW ENGINES;

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

| Engine | Support | Comment | Transactions | XA | Savepoints |

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

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

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

6 rows in set (0.00 sec)

mysql> SELECT DATABASE();

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

| DATABASE() |

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

| clovem |

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

1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (id int) ENGINE=clovem;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> SHOW CREATE TABLE t1;

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

| Table | Create Table |

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

| t1 | CREATE TABLE `t1` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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

可以看出，mysql5.5中，在创建表的时候任意指定一个存储引擎，均不会报错，只不过如果该存储引擎系统不支持，则设置为默认存储引擎。下面看mysql5.6中设置了SQL_MODE的情况mysql> SELECT VERSION();

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

| VERSION() |

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

| 5.6.11 |

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

1 row in set (0.00 sec)

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 |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

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

9 rows in set (0.00 sec)

mysql> CREATE TABLE t1 (id int) ENGINE=FEDERATED;

ERROR 1286 (42000): Unknown storage engine 'FEDERATED'

mysql> CREATE TABLE t2 (id int) ENGINE=clovem;

ERROR 1286 (42000): Unknown storage engine 'clovem'

mysql> CREATE TABLE t3 (id int) ENGINE=CSV;

ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

mysql> CREATE TABLE t3 (id int) ENGINE=MyISAM;

Query OK, 0 rows affected (0.03 sec)

mysql> SHOW TABLES;

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

| Tables_in_test |

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

| t3 |

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

1 row in set (0.00 sec)

可以看出，系统不支持的存储引擎以及不存在的存储引擎或者该存储引擎不支持表列特性的均不能创建成功。