mysql SQL_MODE 参数作用

mysql 5.5 中，该值默认为空值：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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中：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

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的情况

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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)

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

本文转自 暗黑魔君 51CTO博客，原文链接：http://blog.51cto.com/clovemfong/1204765，如需转载请自行联系原作者