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： 不允许在该模式下自动创建用户NO_ENGINE_SUBSTITUTION: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 12.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