SQL数据类型与三值逻辑_JAVA_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > JAVA > SQL数据类型与三值逻辑

SQL数据类型与三值逻辑

 2010/12/25 0:04:35  womendu  http://womendu.javaeye.com  我要评论(0)
  • 摘要:数据类型在数据库理论中,关系模型和数据类型这两部分内容是正交的(参看《程序员修炼之道》第8节关于“正交性”的讨论),互不依赖。换言之,关系模型并不关心每个表的字段的数据类型是什么,是整数、字符串等基本类型也好,是组合类型、类等自定义类型也好,关系模型只要求每个字段是原子的。在数据库理论中,数据类型又被称为域,但域是更为严格的定义。比如一个班级的学生个数和学生平均分可能都是int类型,但这是两个不同的域,“学生个数*学生平均分=学生总分”,但“学生个数+学生平均分”是没有意义的
  • 标签:数据类型 数据 SQL

数据类型

在数据库理论中,关系模型和数据类型这两部分内容是正交的(参看《程序员修炼之道》第8节关 于“正交性”的讨论),互不依赖。换言之,关系模型并不关心每个表的字段的数据类型是什么,是整数、字符串等基本类型也好,是组合类型、类等自定义类型也 好,关系模型只要求每个字段是原子的。

在数据库理论中,数据类型又被称为域,但域是更为严格的定义。比如一个班级的学生个数和学生平均分 可能都是int类型,但这是两个不同的域,“学生个数 * 学生平均分 = 学生总分”,但“学生个数 + 学生平均分”是没有意义的。目前的主流DBMS似乎尚未对域有很好的支持,但未来的情况可能会有所改变,而且,设计自定义类型也需要对这一问题有充分的认 识。详见《深度探索关系数据库》第2章。

对于数据库和SQL的应用来说,除掌握关系模型的原理,还需要对DBMS支持的数据类型及其转换规则有所认识。

1. 基本数据类型

一个DBMS通常都会支持以下几类基本数据类型(以SQL Server为例):
- 精确数字:整数(bigint/int/smallint/tinyint/bit),定点小数(decimal),货币(money/smallmoney)
- 近似数字:浮点数(float)
- 日期和时间:datetime/smalldatetime; date, time, datetime2, datetimeoffset(后4种为SQL Server 2008的新增类型)
- 字符串和Unicode字符串:varchar/nvarchar, char/nchar(text/ntext已不再建议使用,用varchar(max)/nvarchar(max)代替)
- 二进制串(即字节流):varbinary, binary(image已不再建议使用,用varbinary(max)代替)
- 其他数据类型:具有特殊功能的类型(sql_variant, timestamp, uniqueidentifier, xml),不能用于表的特殊类型(cursor, table)

2. 关于数据类型 需要注意的问题

a. 两类特殊的数据类型
- 日期和时间类型 的数据存储方式和可用值范围、相关的计算、比较、显示(转换为指定格式的字符串)都比较复杂,还涉及一组日期时间函数。参看datetime类型分析 一帖。
- 字符串类型 涉及到字符编码和排序规则,比较操作还包含LIKE匹配(未来还可能会支持正则表达式匹配),非常需要注意。参看理解字符编码 和SQLServer中文处理 二帖。

b. 如果对不同排序规则的两个字符串进行计算或比较,将会根据排序规则优先顺序 来决定计算结果的排序规则或比较的方法。

c. 如果对不同类型的两个值进行计算或比较,将会根据数据类型优先级 进行隐式转换 。数据类型优先级基本规则如下:
- 大 > 小(>指优先级高于,下同):如bigint > int > smallint > tinyint > bit,varchar(20) > varchar(10),datetime > smalldatetime,等等。
- 可变 > 固定:如float > decimal,varchar > char,nvarchar > nchar,varbinary > binary。
- 各类型大类的优先级:datetime > float > decimal > integer > unicode string > ansi string > binary。
- 特殊数据类型的优先级和转换规则需要特殊考虑,详见联机丛书。

d. 如果对不同大小的两个值进行计算,将会根据精度、小数位数和长度 的规则来产生新的类型大小。

e. 常量 的数据类型(可以通过SELECT col = 常量值 INTO testdt然后查看testdt表col字段的数据类型来观察)
- 如果不显式指定和隐式转换,NULL会按int类型处理。
- '', N'', 1, 0x01, 1.0, 1E0, $1分别对应varchar, nvarchar, int, varbinary, decimal, float, money类型,并且长度是存储相应值所需要的最小长度。

f. 在软件开发领域众所周知:“隐式转换是bug的源泉” 。因此,有两个建议:
- 使用常量时,最好使用对应类型的常量。 比如,如果table.col是varchar类型,那么WHERE table.col = 10的查询将不能使用索引,而且当遇到col中存放有不能转换为数字的值时将出错。
- 除非相应值的隐式转换非常直观,否则宁可用CAST()/CONVERT()指定明确的显式转换

以上内容中,加下划线的粗体是联机丛书的标题。详细分析参看《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》第1章。


8. NULL与三值逻辑

三值逻辑(3VL, Three-valued Logic)绝对是SQL修炼中的一个紧要关卡,值得特别注意,闭关静修。待冲破这一关卡之后,SQL中的NULL与NOT NULL将别无二致。

关于SQL是否应该允许NULL,在数据库领域已经近乎一个信仰式的争论。E.F.Codd认为NULL有存在的必要,但他的好友C.J.Date认为NULL完全可以取消。最终结果是,SQL标准支持NULL。

理论上的争论且不管。但在实践中,一定要知道NULL的三值逻辑会带来很多困扰的问题。
a. 不使用NULL的理由:
- NULL会引入复杂的三值逻辑。
- NULL在查询条件、外键和CHECK约束、唯一约束、GROUP BY、ORDER BY中的行为都是不一致的。
b. 使用NULL的理由:
- 当需要表示一个未知的、不确定的值时,用NULL更自然。比如一个现在职员工的离职时间、顶级员工(BOSS)的上级员工,等等。
- 外联接通常会引入NULL,即使所有表的字段都定义为NOT NULL。

首先,如果可能,尽量让所有字段都声明为NOT NULL 。除非是更适合使用NULL的场合(从业务出发)。

其次,在使用NULL时,一定要搞清楚三值逻辑和数据库引擎对NULL的处理
(SQLServer有一个选项SET ANSI_DEFAULTS,默认为ON,即与SQL标准一致。设为OFF的效果详见联机丛书。)

1. NULL与别的值进行+-*/等计算操作(包括在大多数函数中使用NULL)后,结果是NULL(标量表达式)。NULL与别的值进行=、>、<等比较操作后,结果是Unknown(断言)。
Unknown相关的逻辑运算:
[code=sql]
NOT Unknown --> Unknown
Unknown AND/OR Unknown --> Unknown
Unknown OR TRUE --> TRUE
Unknown AND TRUE --> Unknown
Unknown OR FALSE --> Unknown
Unknown AND FALSE --> FALSE
[/code]
具体可查三值逻辑的真值表。

2. 在where/on/having和if/case when中,只有True才使条件成立(即Unknown当作False来处理)。比如:
where column = value:表中column为NULL的行永远不会返回,即使value是NULL;
case value when NULL then XXX when ... end:XXX永远不会执行,即使value是NULL;
if <Unknown> XXX else YYY end或case when <Unknown> then XXX else YYY end:这两种情况下,YYY会执行。

3. 包含外键约束和Check约束的字段允许NULL(即约束只当条件为False时出错,Unknown是不管的)。
4. 包含唯一约束(unique index)的字段只允许一个NULL的行,再插入或更新该字段为NULL的行会报字段重复的错误
5. GROUP BY时,所有NULL被视为一组。
6. ORDER BY时,所有NULL排在一起,但NULL排在非空值的前面(如SQL Server)还是后面(如Oracle),SQL标准未规定。
7. 聚集函数(SUM/AVG/MAX/MIN/COUNT)忽略NULL的行。
8. declare的变量,在未赋值之前为NULL。
9. 与NULL处理相关的运算符和函数:
- IS NULL/IS NOT NULL:用这两个运算符来判断一个值是否为NULL,而不是=或<>。
- ISNULL/COALESCE:取第一个非空值(注意两个函数的数据类型转换规则不同)。
- NULLIF(a,b):等价于CASE WHEN a = b THEN NULL ELSE a END。

发表评论
用户名: 匿名