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

2024-08-31 01:04:23

字體：大中小

供稿：網(wǎng)友

前言

前面幾篇文章我們講解了索引有關知識，這一節(jié)我們再繼續(xù)我們下面內(nèi)容講解，簡短的內(nèi)容，深入的理解。

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

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

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

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

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

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

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

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

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

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

此時我們將firstname，插入五個中文試試如下：

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

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

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

也就是說在常規(guī)字符類型如上述VARVHAR中定義為五個字符，此時我們插入五個中文字符則會被截取，當然也插入不進去。因為上述已經(jīng)明確講了1個非英語字符串相當于兩個字節(jié)，此時中文所占用的是十個字節(jié)，而此時VARCHAR才五個字符，所以出現(xiàn)警告。我們再來將firstname插入兩個中文兩個英文或者數(shù)字看看

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

此時插入進去為出現(xiàn)警告，因為此時兩個中文字符即四個字節(jié)加上一個數(shù)字字節(jié)剛好五個字節(jié)，所以能正常插入，我們再來看看lastname，由上知，既然英文或者數(shù)字被當做一個字節(jié)，那么我們對lastname插入四個中文字符和兩個英文字節(jié)剛好十個字節(jié)應該是好使的。我們看看：

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

oh，shit，此時居然出錯了，如下：

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

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

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

結(jié)果正確了，實踐是檢驗真理的唯一標準，從這里我們可以看出：在常規(guī)字符中，一個中文會當做是兩個字節(jié)來使用，一個英文會當做是一個字節(jié)使用，但是在Unicode中，一個中文也是會當做兩個字節(jié)來使用，但是一個英文也會當做是兩個字節(jié)來使用。至此我們可以得出結(jié)論，個人一直以為在Unicode中，將英文是作為一個字節(jié)存儲，見識短啊。

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

字符串函數(shù)

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

LEN與DATALENGTH比較

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

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

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

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

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

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

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

好極了，出錯了。LEN函數(shù)無法對TEXT進行操作。我們接著往下看。

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

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

此時未報錯誤，結(jié)果顯示為47個字節(jié)大小。既然LEN對文本無效，我們不對文本操作就是。

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

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

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

結(jié)論：DATALENGTH函數(shù)是針對于TEXT，而LEN是針對于VARCHAR，對TEXT無效會報錯。

到這里我們還有一個特殊值未進行處理，那就是NULL。那么問題來了，LEN和DATALENGTH對NULL，它的長度大小是多少呢，是0還是不是0尼？

是我們來測試下：

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

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

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

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

我們再來看看二者差異的一個小地方：

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

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

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

CHARINDEX與PATINDEX比較

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

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

USE AdventureWorks2012;GOSELECT CHARINDEX('Worn', DocumentSummary) AS 'CHARINDEX'FROM Production.DocumentWHERE ChangeNumber = 55;GOSELECT PATINDEX('Worn', DocumentSummary) AS 'PATINDEX'FROM Production.DocumentWHERE ChangeNumber = 55;

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

為何CHARINDEX函數(shù)查找到了，而PATINDEX沒有查詢到呢？此時就說說二者的區(qū)別，二者都有兩個參數(shù)，第二個參數(shù)都是要匹配的字符串，但是PATINDEX函數(shù)必須在需要匹配的字符串之前或者之后添加百分號即通配符，而CHARINDEX函數(shù)則不需要。如下即可：

USE AdventureWorks2012;GOSELECT CHARINDEX('Worn', DocumentSummary) AS 'CHARINDEX'FROM Production.DocumentWHERE ChangeNumber = 55;GOSELECT PATINDEX('%Worn%', DocumentSummary) AS 'PATINDEX'FROM Production.DocumentWHERE ChangeNumber = 55;

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