詳解SQL Server中的數(shù)據(jù)類型

前言

前面幾篇文章我們講解了索引有關(guān)知識(shí)，這一節(jié)我們?cè)倮^續(xù)我們下面內(nèi)容講解，簡(jiǎn)短的內(nèi)容，深入的理解。

數(shù)據(jù)類型

SQL Server支持兩種字符數(shù)據(jù)類型，一種是常規(guī)，另外一種則是Unicode。常規(guī)數(shù)據(jù)類型包括CHAR和VARCHAR，Unicode數(shù)據(jù)類型包括NCAHR和NVARCHAR。常規(guī)字符的每個(gè)字符使用1個(gè)字節(jié)存儲(chǔ)，而Unicode數(shù)據(jù)的每個(gè)字符要求2個(gè)字節(jié)。常規(guī)字符列限制為僅僅只針對(duì)于英語(yǔ)，而Unicode則是針對(duì)于多種語(yǔ)言。兩種字符數(shù)據(jù)類型的文本表示方式也不相同，在表示常規(guī)字符文本時(shí)，只需要使用單引號(hào)，比如'Hello,my name is JeffckyWang,I'm from cnblogs'，而對(duì)于Unicode字符文本時(shí)，需要指定字符N作為前綴，即N‘Hello,my name is JeffckyWang,I'm from cnblogs'。

名稱中沒有VAR元素的任何數(shù)據(jù)類型（CHAR、NCHAR）具有固定長(zhǎng)度，即SQL Server按照列定義大小保留行空間，而不是按照字符中的實(shí)際字符保留空間。比如某列定義大小為CHAR(25)，則SQL Server在該行保留25個(gè)字符的空間，而不管存儲(chǔ)字符串的長(zhǎng)度。

名稱中含有VAR元素的數(shù)據(jù)類型（VARCHAR、NVARCHAR）具有可變長(zhǎng)度，即SQL Server根據(jù)存儲(chǔ)需要，在行中使用盡可能多的存儲(chǔ)空間存儲(chǔ)字符串，同時(shí)外加兩個(gè)額外的字節(jié)偏移數(shù)據(jù)。例如，如果將某列定義為VARCHAR(25)，此時(shí)支持的最大字符數(shù)為25，但實(shí)際上按照字符串中實(shí)際字符確定存儲(chǔ)量。-摘抄自SQL Server 2012 T-SQL基礎(chǔ)教程。

這里關(guān)于Unicode字符數(shù)據(jù)類型我們需要重點(diǎn)理解下。我們先創(chuàng)建一個(gè)表，如下：

CREATE TABLE UnicodeType
(
 firstname VARCHAR(5) NOT NULL,
 lastname NVARCHAR(5) NOT NULL
);

此時(shí)我們手動(dòng)插入數(shù)據(jù)，正常插入，如下：

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '11111', -- firstname - varchar(5)
   N'啊的發(fā)個(gè)好' -- lastname - nvarchar(5)
   )

字符都完全插入表中，如下：

此時(shí)我們將firstname，插入五個(gè)中文試試如下：

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '達(dá)得到讓人', -- firstname - varchar(5)
   N'達(dá)得到讓人' -- lastname - nvarchar(5)
   )

此時(shí)出現(xiàn)如下結(jié)果：

也就是說(shuō)在常規(guī)字符類型如上述VARVHAR中定義為五個(gè)字符，此時(shí)我們插入五個(gè)中文字符則會(huì)被截取，當(dāng)然也插入不進(jìn)去。因?yàn)樯鲜鲆呀?jīng)明確講了1個(gè)非英語(yǔ)字符串相當(dāng)于兩個(gè)字節(jié)，此時(shí)中文所占用的是十個(gè)字節(jié)，而此時(shí)VARCHAR才五個(gè)字符，所以出現(xiàn)警告。我們?cè)賮?lái)將firstname插入兩個(gè)中文兩個(gè)英文或者數(shù)字看看

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '達(dá)得1', -- firstname - varchar(5)
   N'達(dá)得到讓人' -- lastname - nvarchar(5)
   )

此時(shí)插入進(jìn)去為出現(xiàn)警告，因?yàn)榇藭r(shí)兩個(gè)中文字符即四個(gè)字節(jié)加上一個(gè)數(shù)字字節(jié)剛好五個(gè)字節(jié)，所以能正常插入，我們?cè)賮?lái)看看lastname，由上知，既然英文或者數(shù)字被當(dāng)做一個(gè)字節(jié)，那么我們對(duì)lastname插入四個(gè)中文字符和兩個(gè)英文字節(jié)剛好十個(gè)字節(jié)應(yīng)該是好使的。我們看看：

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '達(dá)得1', -- firstname - varchar(5)
   N'達(dá)得到讓ab' -- lastname - nvarchar(5)
   )

oh，shit，此時(shí)居然出錯(cuò)了，如下：

我們上述分析的不是有理有據(jù)么，難道這里英文不是占用一個(gè)字節(jié)么，我們插入一個(gè)英文試試。

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '達(dá)得1', -- firstname - varchar(5)
   N'達(dá)得到讓b' -- lastname - nvarchar(5)
   )

結(jié)果正確了，實(shí)踐是檢驗(yàn)真理的唯一標(biāo)準(zhǔn)，從這里我們可以看出：在常規(guī)字符中，一個(gè)中文會(huì)當(dāng)做是兩個(gè)字節(jié)來(lái)使用，一個(gè)英文會(huì)當(dāng)做是一個(gè)字節(jié)使用，但是在Unicode中，一個(gè)中文也是會(huì)當(dāng)做兩個(gè)字節(jié)來(lái)使用，但是一個(gè)英文也會(huì)當(dāng)做是兩個(gè)字節(jié)來(lái)使用。至此我們可以得出結(jié)論，個(gè)人一直以為在Unicode中，將英文是作為一個(gè)字節(jié)存儲(chǔ)，見識(shí)短啊。

常規(guī)字符和Unicode中一個(gè)中文字符用兩個(gè)字節(jié)存儲(chǔ)，而對(duì)英文，常規(guī)字符用一個(gè)字節(jié)存儲(chǔ)，而Unicode依然是用兩個(gè)字節(jié)存儲(chǔ)。

字符串函數(shù)

對(duì)字符串操作的函數(shù)有SUBSTRING、LEFT、RIGHT、CHARINDEX、PATINDEX、REPLACE、REPICATE、STUFF、UPPER、LOWER、RTRIM、LTRIM、FORMAT。對(duì)于簡(jiǎn)單的函數(shù)我們略過(guò)，下面我們來(lái)講講幾個(gè)需要注意的地方。

LEN與DATALENGTH比較

我們首先創(chuàng)建如下測(cè)試表

CREATE TABLE StringFun
(
 firststr VARCHAR(max) NOT NULL,
 secondstr TEXT NOT NULL
);

我們插入測(cè)試數(shù)據(jù)

INSERT dbo.StringFun
  ( firststr, secondstr )
VALUES ( '我是JeffckyWang，我來(lái)自于博客園，專注于.NET技術(shù)', -- firststr - varchar(max)
   '我是JeffckyWang，我來(lái)自于博客園，專注于.NET技術(shù)' -- secondstr - text
   )

我們首先利用LEN函數(shù)來(lái)返回firststr和secondstr的字符串長(zhǎng)度大小

SELECT LEN(firststr) AS VARCAHRFieldSize 
FROM dbo.StringFun
SELECT LEN(secondstr) AS TEXTFieldSize 
FROM dbo.StringFun

好極了，出錯(cuò)了。LEN函數(shù)無(wú)法對(duì)TEXT進(jìn)行操作。我們接著往下看。

SELECT DATALENGTH(firststr) AS VARCAHRFieldSize 
FROM dbo.StringFun
SELECT DATALENGTH(secondstr) AS TEXTFieldSize 
FROM dbo.StringFun

此時(shí)未報(bào)錯(cuò)誤，結(jié)果顯示為47個(gè)字節(jié)大小。 既然LEN對(duì)文本無(wú)效，我們不對(duì)文本操作就是。

SELECT LEN(firststr) AS VARCAHRFieldSize 
FROM dbo.StringFun
SELECT DATALENGTH(secondstr) AS TEXTFieldSize 
FROM dbo.StringFun

此時(shí)類型為VARCAHR的firststr字節(jié)大小卻為31，為何，看到這里我們想必恍然大悟，在上述我們講到常規(guī)字符會(huì)對(duì)中文以一個(gè)字符兩個(gè)字節(jié)大小存儲(chǔ)，但是這里實(shí)際上返回的是實(shí)際字符大小，當(dāng)然一個(gè)是存儲(chǔ)，一個(gè)是檢索，還是有點(diǎn)不同，同時(shí)我們也不會(huì)將中文存儲(chǔ)到VARCHAR中。到這里我們可以得出結(jié)論。

結(jié)論：DATALENGTH函數(shù)是針對(duì)于TEXT，而LEN是針對(duì)于VARCHAR，對(duì)TEXT無(wú)效會(huì)報(bào)錯(cuò)。

到這里我們還有一個(gè)特殊值未進(jìn)行處理，那就是NULL。那么問(wèn)題來(lái)了，LEN和DATALENGTH對(duì)NULL，它的長(zhǎng)度大小是多少呢，是0還是不是0尼？

是我們來(lái)測(cè)試下：

DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)
PRINT 'LEN of NULL is 0'
ELSE
PRINT 'LEN of NULL is NULL'

我們上述得到的結(jié)果是LEN of NULL is NULL，DATALENGTH就不再演示了。

結(jié)論：LEN和DATALENGTH對(duì)于NULL計(jì)算的結(jié)果就是NULL。

我們?cè)賮?lái)看看二者差異的一個(gè)小地方：

SELECT LEN('JeffckyWang ') AS 'LEN'
SELECT DATALENGTH('JeffckyWang ') AS 'DATALENGTH'

結(jié)論：LEN會(huì)刪除尾隨空格，而DATALENGTH不會(huì)

CHARINDEX與PATINDEX比較

CHARINDEX和PATINDEX字符串函數(shù)都是查詢返回指定匹配字符串的開始位置。

我們先查詢一個(gè)字符串，此字符串在表中存在，如下：

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函數(shù)查找到了，而PATINDEX沒有查詢到呢？此時(shí)就說(shuō)說(shuō)二者的區(qū)別，二者都有兩個(gè)參數(shù)，第二個(gè)參數(shù)都是要匹配的字符串，但是PATINDEX函數(shù)必須在需要匹配的字符串之前或者之后添加百分號(hào)即通配符，而CHARINDEX函數(shù)則不需要。如下即可：

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;

結(jié)論：PATINDEX匹配字符串必須在字符串前面或者后面或者前后添加通配符，而CHARINDEX無(wú)需添加。

總結(jié)

本節(jié)我們主要講解了SQL中的數(shù)據(jù)類型以及幾個(gè)需要注意的地方，簡(jiǎn)短的內(nèi)容，深入的理解，我們下節(jié)再會(huì)。

以上就是本文的全部?jī)?nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，同時(shí)也希望多多支持腳本之家！