前面几篇文章我们讲解了索引有关知识,这一节我们再继续我们下面内容讲解,简短的内容,深入的理解。
SQL Server 支持两种字符数据类型,一种是常规,另外一种则是 Unicode。常规数据类型包括 CHAR 和 VARCHAR,Unicode 数据类型包括 NCAHR 和 NVARCHAR。常规字符的每个字符使用 1 个字节存储,而 Unicode 数据的每个字符要求 2 个字节。常规字符列限制为仅仅只针对于英语,而 Unicode 则是针对于多种语言。两种字符数据类型的文本表示方式也不相同,在表示常规字符文本时,只需要使用单引号,比如'Hello,my name is JeffckyWang,I'm from cnblogs',而对于 Unicode 字符文本时,需要指定字符 N 作为前缀,即 N'Hello,my name is JeffckyWang,I'm from cnblogs'。
名称中没有 VAR 元素的任何数据类型(CHAR、NCHAR)具有固定长度,即 SQL Server 按照列定义大小保留行空间,而不是按照字符中的实际字符保留空间。比如某列定义大小为 CHAR(25),则 SQL Server 在该行保留 25 个字符的空间,而不管存储字符串的长度。
名称中含有 VAR 元素的数据类型(VARCHAR、NVARCHAR)具有可变长度,即 SQL Server 根据存储需要,在行中使用尽可能多的存储空间存储字符串,同时外加两个额外的字节偏移数据。例如,如果将某列定义为 VARCHAR(25),此时支持的最大字符数为 25,但实际上按照字符串中实际字符确定存储量。- 摘抄自 SQL Server 2012 T-SQL 基础教程。
这里关于 Unicode 字符数据类型我们需要重点理解下。我们现在一个表,如下:
- CREATE TABLE UnicodeType(firstname VARCHAR(5) NOT NULL, lastname NVARCHAR(5) NOT NULL);
此时我们手动插入数据,正常插入,如下:
- INSERT dbo.UnicodeType(firstname, lastname) VALUES('11111', --firstname - varchar(5) N '啊的发个好'--lastname - nvarchar(5))
字符都完全插入表中,如下:
此时我们将 firstname,插入五个中文试试如下:
- INSERT dbo.UnicodeType(firstname, lastname) VALUES('达得到让人', --firstname - varchar(5) N '达得到让人'--lastname - nvarchar(5))
此时出现如下结果:
也就是说在常规字符类型如上述 VARVHAR 中定义为五个字符,此时我们插入五个中文字符则会被截取,当然也插入不进去。因为上述已经明确讲了 1 个非英语字符串相当于两个字节,此时中文所占用的是十个字节,而此时 VARCHAR 才五个字符,所以出现警告。我们再来将 firstname 插入两个中文两个英文或者数字看看
- INSERT dbo.UnicodeType(firstname, lastname) VALUES('达得1', --firstname - varchar(5) N '达得到让人'--lastname - nvarchar(5))
此时插入进去为出现警告,因为此时两个中文字符即四个字节加上一个数字字节刚好五个字节,所以能正常插入,我们再来看看 lastname,由上知,既然英文或者数字被当做一个字节,那么我们对 lastname 插入四个中文字符和两个英文字节刚好十个字节应该是好使的。我们看看:
- INSERT dbo.UnicodeType(firstname, lastname) VALUES('达得1', --firstname - varchar(5) N '达得到让ab'--lastname - nvarchar(5))
oh,shit,此时居然出错了,如下:
我们上述分析的不是有理有据么,难道这里英文不是占用一个字节么,我们插入一个英文试试。
- INSERT dbo.UnicodeType(firstname, lastname) VALUES('达得1', --firstname - varchar(5) N '达得到让b'--lastname - nvarchar(5))
结果正确了,实践是检验真理的唯一标准,从这里我们可以看出:在常规字符中,一个中文会当做是两个字节来使用,一个英文会当做是一个字节使用,但是在 Unicode 中,一个中文也是会当做两个字节来使用,但是一个英文也会当做是两个字节来使用。至此我们可以得出结论,个人一直以为在 Unicode 中,将英文是作为一个字节存储,见识短啊。
常规字符和 Unicode 中一个中文字符用两个字节存储,而对英文,常规字符用一个字节存储,而 Unicode 依然是用两个字节存储。
对字符串操作的函数有 SUBSTRING、LEFT、RIGHT、CHARINDEX、PATINDEX、REPLACE、REPICATE、STUFF、UPPER、LOWER、RTRIM、LTRIM、FORMAT。对于简单的函数我们略过,下面我们来讲讲几个需要注意的地方。
我们首先创建如下测试表
- CREATE TABLE StringFun(firststr VARCHAR(max) NOT NULL, secondstr TEXT NOT NULL);
我们插入测试数据
- INSERT dbo.StringFun(firststr, secondstr) VALUES('我是JeffckyWang,我来自于博客园,专注于.NET技术', --firststr - varchar(max)'我是JeffckyWang,我来自于博客园,专注于.NET技术'--secondstr - text)
我们首先利用 LEN 函数来返回 firststr 和 secondstr 的字符串长度大小
- SELECT LEN(firststr) AS VARCAHRFieldSize FROM dbo.StringFun SELECT LEN(secondstr) AS TEXTFieldSize FROM dbo.StringFun
好极了,出错了。LEN 函数无法对 TEXT 进行操作。我们接着往下看。
- SELECT DATALENGTH(firststr) AS VARCAHRFieldSize FROM dbo.StringFun SELECT DATALENGTH(secondstr) AS TEXTFieldSize FROM dbo.StringFun
此时未报错误,结果显示为 47 个字节大小。 既然 LEN 对文本无效,我们不对文本操作就是。
- SELECT LEN(firststr) AS VARCAHRFieldSize FROM dbo.StringFun SELECT DATALENGTH(secondstr) AS TEXTFieldSize FROM dbo.StringFun
此时类型为 VARCAHR 的 firststr 字节大小却为 31,为何,看到这里我们想必恍然大悟,在上述我们讲到常规字符会对中文以一个字符两个字节大小存储,但是这里实际上返回的是实际字符大小,当然一个是存储,一个是检索,还是有点不同,同时我们也不会将中文存储到 VARCHAR 中。到这里我们可以得出结论。
结论:DATALENGTH 函数是针对于 TEXT,而 LEN 是针对于 VARVHAR,对 TEXT 无效会报错。
到这里我们还有一个特殊值未进行处理,那就是 NULL。那么问题来了,LEN 和 DATALENGTH 对 NULL,它的长度大小是多少呢,是 0 还是不是 0 尼?
是我们来测试下:
- DECLARE@MyVar VARCHAR(10) SET@MyVar = NULL IF(LEN(@MyVar) = 0) PRINT 'LEN of NULL is 0'
- ELSE PRINT 'LEN of NULL is NULL'
我们上述得到的结果是 LEN of NULL is NULL,DATALENGTH 就不再演示了。
结论:LEN 和 DATALENGTH 对于 NULL 计算的结果就是 NULL。
CHARINDEX 和 PATINDEX 字符串函数都是查询返回指定匹配字符串的开始位置。
我们先查询一个字符串,此字符串在表中存在,如下:
- USE AdventureWorks2012;
- GO SELECT CHARINDEX('Worn', DocumentSummary) AS 'CHARINDEX'FROM Production.Document WHERE ChangeNumber = 55;
- GO SELECT PATINDEX('Worn', DocumentSummary) AS 'PATINDEX'FROM Production.Document WHERE ChangeNumber = 55;
为何 CHARINDEX 函数查找到了,而 PATINDEX 没有查询到呢?此时就说说二者的区别,二者都有两个参数,第二个参数都是要匹配的字符串,但是 PATINDEX 函数必须在需要匹配的字符串之前或者之后添加百分号即通配符,而 CHARINDEX 函数则不需要。如下即可:
- USE AdventureWorks2012;
- GO SELECT CHARINDEX('Worn', DocumentSummary) AS 'CHARINDEX'FROM Production.Document WHERE ChangeNumber = 55;
- GO SELECT PATINDEX('%Worn%', DocumentSummary) AS 'PATINDEX'FROM Production.Document WHERE ChangeNumber = 55;
结论:PATINDEX 匹配字符串必须在字符串前面或者后面或者前后添加通配符,而 CHARINDEX 无需添加。
本节我们主要讲解了 SQL 中的数据类型以及几个需要注意的地方,简短的内容,深入的理解,我们下节再会。
来源: http://www.cnblogs.com/CreateMyself/p/6123586.html