文章归档友情连接照片地图

sql_mode严格模式和ENUM类型

分类:数据库技术  作者:rming  时间:2014-02-19

1.sql mode简介

在Mysql中,sql mode可以用来解决以下问题

(1).通过设置不同的sql mode,可以在不同严格程序进行数据校验.有效地保证了数据准确性.

(2).通过设置sql mode为ANSI模式,来保证大多数SQL符合标准SQL的语法,这样在不同数据库之间迁移时,不需要对业务修改太多.

sql_mode严格模式

经常使用的sql_mode值如下:

ANSI #更改语法和行为,使其更符合标准SQL。
STRICT_TRANS_TABLES #如果不能将给定的值插入到事务表中,则放弃该语句。对于非事务表,如果值出现在单行语句或多行语句的第1行,则放弃该语句。
TRADITIONAL #也是严格模式,对于插入不正确的值给出错误而不是警告。用在事务时,只要发生错误就立即回滚
说明:如果把sql_mode的值设置成后面的两个值(也就是我们说的严格模式),那么当在列中插入或更新不正确的值时,mysql将会给出错误,并且放弃insert/update操作。在我们的一般应用中建议使用这两种模式,而不是使用默认的空或ANSI模式。但是需要注意的问题是,如果数据库运行在严格模式下,并且你的存储引擎不支持事务,那么有数据不一致的风险存在,比如一组sql中有两个dml语句,如果后面的一个出现了问题,但是前面的已经操作成功,那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失,从而得到一个合适的选择。

举个例子:

INSERT INTO Survey_UserHistory(userId,jobId,type,action,startTime,endTime) VALUES(’17′,”,’User’,'Login’,”,’2008-11-23 14:33:56′);

严格模式下

如果`jobId`字段没有设置默认值,命令运行会报错

MySQL error: 1366 (Incorrect integer value: ” for column ‘jobId’ at row 1)

此时,解决这个问题的方法有两个:1、将sql_mode设置为宽松模式 2、设置jobId字段的默认值

sql_mode宽松模式

通常在linux下安装完mysql后,默认的sql-mode值是空,在这种情形下mysql执行的是一种不严格的检查,如果插入了不合适数据(错误类型或超常),mysql会将数据设为“最好的可能数据”而不报错(插入后会有一个warning),如:

/数字 设为:0/可能最小值/可能最大值

/字符串 设为:空串/能够存储的最大容量字符串

/表达式 设为:返回一个可用值(1/0-null)

所以,在sql_mode宽松模式下,所有列都要采用默认值,既增强了对不完整sql命令的兼容性,还对性能提高有所帮助。

sql_mode设置查看

查看sql_mode设置

mysql>select @@sql_mode; 

命令行设置sql_mode

仅本次连接有效:

mysql>set session sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI'; 

本次连接不生效,以后的连接都生效

mysql>set global sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI'; 

修改 my.ini 文件.

# Set the SQL mode to strict
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

2.ENUM简介

ENUM是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值。

在某些情况下,ENUM值也可以为空字符串('')或NULL:

1、如果你将一个非法值插入ENUM(也就是说,允许的值列之外的字符串),将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同,该字符串有数值值0。

2、如果将ENUM列声明为允许NULL,NULL值则为该列的一个有效值,并且 默认值为NULL。如果ENUM列被声明为NOT NULL,其默认值为允许的值列的第1个元素。

每个枚举值有一个索引:

1、来自列规定的允许的值列中的值从1开始编号。

2、NULL值的索引是NULL。

3、空字符串错误值的索引值是0。这说明你可以使用下面的SELECT语句来找出分配了非法ENUM值的行:select * from test where browsertype=0;

例如,定义为ENUM的列('one','two','three')可以有下面所示任何值。还显示了每个值的索引:

值 索引

NULL NULL

1 'one'

2 'two'

3 'three'

文章参考:

SQL Mode及相关问题
mysql 严格模式sql_mode
mysql的sql_mode


提交评论