SQL学习 之SQL数据类型与三值逻辑

本文转载至: http://topic.csdn.net/u/20100826/18/7b81012a-b5c4-48b1-b5d1-40a92f3e0388.html

 

第三部分：SQL数据类型与三值逻辑


7. 数据类型

在数据库理论中，关系模型和数据类型这两部分内容是正交的（参看《程序员修炼之道》第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。

相关讨论帖：
- 关于 not in的疑问
- varchar类型是存储''值好一点还是NULL好一点

 

问题一:

 

if object_id('tb') is not null
drop table tb
create table tb
(
id int
)
insert tb select 1
union all
select null
union all
select 2
if object_id('t1') is not null
drop table t1
create table t1 
(
col int
)
insert t1 select 1
union all
select 3

select * from t1 where col not in (select * from tb)

这个为什么会没有值啊 

t1表中的 col = 3 这条记录明明符合要求的啊

 

回答 一:

 

当NOT IN后面括号中的值列表中包含NULL时，结果会出人意料。
三值逻辑(3VL, Three-valued Logic)绝对是SQL修炼中的一个紧要关卡，值得特别注意，闭关静修。待冲破这一关卡之后，SQL中的NULL与NOT NULL将别无二致。

 

 

回答 二:

select * from t1 where col not in (select * from tb where id is not null)

这样是可以出结果的。

 

回答 三:

提示：

1. 在逻辑上，
column IN (v1,...,vN) 等价于 column = v1 OR ... OR column = vN
column NOT IN (v1,...,vN) 等价于 column <> v1 AND ... AND column <> vN
（即使是在考虑NULL的三值逻辑情况下）

2. NULL与别的值进行+-*/等运算操作后，结果是NULL。NULL与别的值进行=、>、<等比较操作后，结果是Unknown。
NOT Unknown --> Unknown
Unknown AND Unknown --> Unknown
Unknown OR TRUE --> TRUE
Unknown AND TRUE --> Unknown
Unknown OR FALSE --> Unknown
Unknown AND FALSE --> FALSE
具体可查三值逻辑的真值表。

3. 在where/on/having条件中，Unknown当作FLASE处理。

 

 

回答 四:

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

理论上的争论且不管。但在实践中，一定要知道NULL的三值逻辑会带来很多困扰的问题。

首先，除非必要，尽量使表中字段为NOT NULL。

其次，在使用NULL时，一定要搞清楚三值逻辑和数据库引擎对NULL的处理：
（SQLServer有一个选项SET ANSI_DEFAULTS，默认为ON，即与SQL标准一致。设为OFF的效果详见文档。）

1. NULL与别的值进行+-*/等运算操作后，结果是NULL（一个值，类似0,1.0,'abc'）。NULL与别的值进行=、>、<等比较操作后，结果是Unknown（比较结果，类似True,False）。

2. 在where/on/having和if/case when中，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。

4. 包含唯一约束（unique index）的字段只允许一个NULL的行，再插入或更新该字段为NULL的行会报字段重复的错误。

5. 聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行。

6. declare的变量，在未赋值之前为NULL。

7. 与NULL处理相关的函数：ISNULL/COALESCE/NULLIF。


暂时就想到这些。

 

 

回答 五:

 

NULL表示一个不确定或无意义的值。

LZ的这个问题可以这样理解：

1 in (1,2,3,NULL) = True：可以确定1在列表中。
0 in (1,2,3,NULL) = Unknown：NULL是一个不确定的值，所以不确定0是不是在列表中。
1 not in (1,2,3,NULL) = False：in为True，not in则为False。
0 not in (1,2,3,NULL) = Unknown：in不确定，not in也不确定。

结果是，只要not in后面的列表中包含NULL，结果总是当作False处理。

 

问题二:

 

varchar 类型 当为NULL时,是存储""值好一点,还是NULL值好一点呢,存那种方式有优势点呢,我们现在都是存"",就是担心会不会占多余的空间.

 

 

回答 一:

如果在你的数据库设计中，NULL没有明确的含义，建议你把所有字段都加上默认值，不要使用NULL。另外NULL占的空间比空字符串要多。

 

 

回答 二:

建表有个原则：尽量在字段定义后面显示声明 Not null。。
NULL带来的逻辑处理很麻烦

 

回答 三:

 

引用楼主 zhengyingcan 的回复:
varchar 类型 当为NULL时,是存储""值好一点,还是NULL值好一点呢,存那种方式有优势点呢,我们现在都是存"",就是担心会不会占多余的空间.



从业务逻辑角度看：
NULL用来表示一个未知的、不确定的值。跟''空字符串是两回事。


从数据库物理引擎角度：
NULL不是一个值，而是一个特殊标记。如果某行记录的某个字段为NULL，则需要一个额外的标记来注明这个值为NULL。因此，一个NULL的字段所占用的空间大于等于一个NOT NULL的字段。
具体空间使用情况要看不同厂商数据库引擎的具体实现。

对SQLServer 2005来说：
数据行：一个表的字段，不管是声明为NOT NULL还是NULL，每行记录总是需要CEILING(1.0 * 字段个数 / 8)个字节（即每个字段1bit）来表示NULL标记（称为空值位图）。
索引行：当索引键中包含可空字段时，每个索引行需要2个字节存储索引行字段个数和CEILING(1.0 * 索引行字段个数 / 8)个字节来表示空值位图。当索引键所有字段都为NOT NULL时，这些空间可以省下来。
而且，以后的版本中，SQLServer可能会把NOT NULL的字段浪费的空位图节省出来。

总的来说就是：字段允许NULL，可能会占用更多空间。而且判断字段是否为NULL需要计算空值位图，比直接查值要多一些运算。


从数据库设计角度：
从设计上来看，允许为NULL带来的那点空间和时间开销基本可以忽略（远没有合理设计字段大小更省空间，远没有合理设计索引更省时间），这不是拒绝NULL的关键理由。

不使用NULL的理由：
1. NULL会引入复杂的三值逻辑。
2. NULL在查询条件、外键和CHECK约束、唯一约束、GROUP BY、ORDER BY中的行为都是不一致的。

使用NULL的理由：
1. 当需要表示一个未知的、不确定的值时，用NULL更自然。比如一个现在职员工的离职时间、顶级员工（BOSS）的上级员工，等等。
2. 外联接通常会引入NULL，即使所有表的字段都定义为NOT NULL。


结论：
1. 如果可能，尽量让所有字段都声明为NOT NULL。除非是更适合使用NULL的场合（从业务出发）。
2. 深入理解三值逻辑和NULL在不同情况下的行为。只要SQL允许NULL的存在，你总归难免会遇到它。