MySQL严格模式及非严格模式实验.doc

SQL Mode简介
在Mysql中,sql mode可以用来解决以下问题
(1).通过设置不同的sql mode,可以在不同的严格程度对数据进行校验.有效地保证了数据准确性.
(2).通过设置sql mode为ANSI模式,来保证大多数SQL符合标准SQL的语法,这样在不同数据库之间迁移时,不需要对业务 修改太多.
查询默认的sql mode(sql mode参数)有:real_as_float,pipes_as_concat,ansi_quotes,gnore_space和ANSI。在这些模式下可以插入超过字段定义长度的数据，或是在字段中没有定义的元素数据(如,enum)。不过在插入后会有一个warning(可以用 show warnings来查看)。

可以通过设置sql mode为STRICT_TRANS_TABLES(严格模式)来实现数据的严格校检,使错误数据不能插入，从而保证数据准确性。

TRADITIONAL模式也属于严格模式，同样可以实现严格校检,使错误数据不能插入，从而保证数据准确性。
SQL中的枚举
ENUM简介
ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。
在某些情况下，ENUM值也可以为空字符串('')或NULL：
如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。
每个枚举值有一个索引：
来自列规定的允许的值列中的值从1开始编号。

例如: browsertype enum('ie','firefox','360browser'),
该枚举类型定义中枚举值'ie','firefox','360browser'都是有索引的，’ie’ = 1, 'firefox' = 2,
'360browser'=3所以当给该列中插入’ie’枚举值时，可以通过如下语句进行查询;
select * from test where browsertype=1;
空字符串错误值的索引值是0。这说明你可以使用下面的SELECT语句来找出分配了非法ENUM值的行：
select * from test where browsertype=0;

严格模式
1. 查看MySQL的默认的模式：
select @@sql_mode;
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+-
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
解释：
NO_AUTO_CREATE_USER： 不允许在该模式下自动创建用户
2. 在严格模式下验证严格模式的语法要求：(以枚举类型举例)
2.1 建表语句
mysql> create table test(
-> id int not null auto_increment primary key,
-> browsertype enum('ie','firefox','360browser'),
-> version int
-> );
2.2 插入数据
insert into test(browsertype) values('ie');
2.3 查询数据
select * from test;
+----+-------------+---------+
| id | browsertype | version |
+----+-------------+---------+
| 1 | ie | NULL |
+----+-------------+---------+
2.4 给第二列插入非枚举值
insert into test(browsertype) values('maxthon');
在严格模式下抛出如下异常：
ERROR 1265 (01000): Data truncated for column 'browsertype' at row 1
2.5 给第二列插入错误的数据类型值
正确的数据类型：
insert into test(browsertype,version) values('firefox',2);
非法的数据类型：
insert into test(browsertype,version) values('360browser','a'); // ‘2’可以自动转型
在严格模式下会抛出如下异常：
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'version' at row 1
总结：在严格模式下，服务器拒绝超出范围的值 (如：插入的数据超出枚举值的范围)和不正确的数据类型(如：表中定义的类型是int给的字符串数据)
非严格模式
1. 修改当前MySQL的语法模式为非严格模式
mysql> set sql_mode=ansi;
2. 查看当前模式是否被修改
mysql> select @@sql_mode;
+-------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
3. 使用以上的表在非严格模式下对超出枚举类型范围的数据进行插入：
mysql> insert into test(browsertype,version) values('QQbrowser',3);
Query OK, 1 row affected, 1 warning (0.00 sec)
注意：在非严格模式下超出枚举类型范围的数据值也可以插入，但是有警告！
4. 查看插入的数据：
mysql> select * from test;
+----+-------------+---------+
| id | browsertype | version |
+----+-------------+---------+
| 1 | ie | NULL |
| 2 | firefox | 2 |
| 3 | | 3 |
+----+-------------+---------+
注意：当插入的枚举值超出范围时，语句执行成功，但是插入的值不是超范围的值，而是MySQL默认插入的空字符串。

思考：如何查看刚才插入的非法数据行。
select * from test where browsertpty=0;

总结：严格模式下对SQL语法要求非常严格，使得错误的数据无法进行插入到表中。而非严格模式则采用较为宽松的语法校验SQL语法，允许插入不满足条件的数据(如：枚举值超出范围)。

开发习惯：在实际的项目开发中如何来决定到底采用那种SQL模式？
鉴于在开发中应该将错误的数据排除在DB系统之外，保证持久化的数据一定是符合用户要求的，所以推荐使用严格模式(设置方式：set sql_mode=’ STRICT_TRANS_TABLES’或
’ STRICT_ALL_TABLES’)。但是对于SQL的初学者为了避免SQL的语法错误，可以使用非严格模式(ANSI)。

归根到低要把 mysql 从严格模式下 改为宽松模式 只要找到my.ini文件 搜索查到
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
把sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
改为sql-mode="ANSI" 重启Mysq服务即可