mysql sql_mode 使用

mysql>use lixora;

mysql> CREATE TABLE t5(id int(11)) ENGINE=InnoDBdddddddd;

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

mysql> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

NO_ENGINE_SUBSTITUTION  这个sql_mode 的意思是当create \ alert table 指定的engine 不存在，不自动继承mysql server 缺省的存储引擎

以报错ERROR 1286 (42000): 展现

---去掉NO_engine_substitution sql mode 后再来测试下：

mysql> SET sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)


mysql>  CREATE TABLE t5(id int(11)) ENGINE=InnoDBdddddddd;    该存储引擎不存在，但是依然可以成功执行sql！！！
Query OK, 0 rows affected, 2 warnings (0.00 sec)

---查看刚才建表成功的语句
mysql> show create table t5;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |     缺省使用的了mysql server 缺省的innodb 引擎
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

----常见的几个 sql mode

• STRICT_TRANS_TABLES, STRICT_ALL_TABLES: Without these modes, MySQL is 
forgiving with values that are missing, out of range, or malformed. Enabling 

STRICT_TRANS_TABLES sets “strict mode” for transactional tables; it is also enabled in 
the default  my.cnf file. Enabling STRICT_ALL_TABLES sets strict mode for all tables.

• TRADITIONAL: Enable this SQL mode to enforce restrictions on input data values that 
are similar to those of other database  servers. With this mode, using the GRANT
statement to create users requires that you specify a password.

• IGNORE_SPACE: By default, you must invoke functions with no space between the 
function name and the following parenthesis. Enabling this mode allows such spaces, 
and causes function names to be reserved words.

• ERROR_FOR_DIVISION_BY_ZERO: By default, division by zero produces a result of 
NULL. A division by zero when inserting data with this mode enabled causes a warning, 
or an error in strict mode.

• ANSI: Use this composite mode to cause the MySQL server to be more “ANSI-like.” 
That is, it enables behaviors that are more like standard SQL, such as  ANSI_QUOTES
and  PIPES_AS_CONCAT.

• NO_ENGINE_SUBSTITUTION: When you specify an unavailable storage engine while 
creating or altering a table, MySQL substitutes the default storage engine unless this 
mode is enabled. This is the default SQL mode.