mysql SQL_MODE 参数作用-阿里云开发者社区

mysql SQL_MODE 参数作用

2017-11-15 986

简介：

+关注继续查看

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)