sql server怎麼在存儲過程中模糊查詢?

為什麼這樣就查詢不到了


樓主對SQL Server中的數據類型缺乏了解所以會有這個疑問。下面跟我一起來驗證一些結論。首先我們仿照樓主的schema來定義一個table用來測試:

IF OBJECT_ID("dbo.students") IS NOT NULL
DROP TABLE dbo.students;

CREATE TABLE dbo.students
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name NCHAR(10)
);

INSERT INTO dbo.students(name)
SELECT c FROM(VALUES(N"李波"), (N"王燕"), (N"陳建"), (N"張斌")) AS DATA(c);
GO

對於NCHAR或者CHAR類型,不管你傳入的字元串到底有多長,都會在最右邊補空格,一直補到你定義的時候指定的那麼長。

我們執行下面的語句看一看students這個table中的兩個column分別佔多少位元組:

SELECT
c.name AS column_name,
column_id,
max_inrow_length,
pc.system_type_id,
leaf_offset
FROM
sys.system_internals_partition_columns pc
JOIN sys.partitions p
ON p.partition_id = pc.partition_id
JOIN sys.columns c
ON column_id = partition_column_id
AND c.object_id = p.object_id
WHERE p.object_id = OBJECT_ID(N"students");

得到的結果是:

可以看到id佔用了4個位元組,NCHAR(10)佔用了20個位元組,因為存儲的是unicode字元,每個字元需要佔用兩個位元組。

下面我們來看看NCHAR類型數據到底是怎麼存儲的,執行下面的語句:

SELECT
s.*,
"DBCC TRACEON(3604); DBCC PAGE (tempdb, " + CAST(t.file_id AS VARCHAR(20)) + ", "
+ CAST(t.page_id AS VARCHAR(20)) + ", 3);" AS command
FROM
dbo.students s
CROSS APPLY sys.fn_PhysLocCracker(s.%%physloc%%) t;

可以得到這樣的結果:

可以看到我們生成了查看具體的page內容的DBCC命令。執行第一行的DBCC命令我們得到下面的結果:

上面DBCC命令的結果清楚地表明了SQL Server是怎樣存儲數據的,其中我標識出來的數據非別是:

1. 這行數據的狀態信息

2. "這行中有幾個column"的這個信息在哪裡什麼位置保存著呢?1e也就是10進位的30,表示在偏移量30個位元組的位置,數一數也就是在上圖中5標識出的位置,佔兩個位元組

3. 這是我們的int column的值,這裡保存的是數字1

4. 這是我們的NCHAR column的值,佔20個位元組,可以看到只有前四個位元組是真正的數據,也就是「李波」這兩個字元,每個字元佔了兩個位元組,其他的部分都是2000,這是unicode的空格字元,也就是說SQL Server在保存NCHAR類型數據的時候,雖然我們的數據只有4個位元組,但是它還是在右邊加了一些unicode的空格,補夠20個位元組

5. 參見2,另外這裡還有null bit map,但是和我們的問題無關,不展開討論了。

明白了SQL Server會right padding char或者nchar column以後,樓主的問題就清楚了。你定義了一個類似這樣的存貯過程:

CREATE PROCEDURE dbo.check_address @name NCHAR(10)
AS
BEGIN
SELECT * FROM dbo.students WHERE name LIKE N"%" + @name + N"%";
END;
GO

注意參數是NCHAR(10)類型,也就是說如果你執行的時候傳入的是「李波」,那麼因為「李波」長度不夠20個位元組,SQL Server會在「李波」右邊加空格,一直加到20個位元組那麼長,當你用LIKE比較的時候,你比較的是LIKE N"%李波 %",如果你傳入的是「李」,那麼你比較的是LIKE N"%李 %"。第一種情況當然可以匹配了,因為「李波」右邊本來就被加了空格,第二種情況顯然不匹配。

在設計資料庫的時候,如果遇到需要存貯字元串類型的column,最好選擇NVARCHAR,而沒有任何理由用NCHAR或者CHAR,這樣會避免很多問題。另外LIKE對於unicode字元和非unicode字元下字元串最右邊的空格的表現也是不一樣的,我們就不討論了,但是只要你不用NCHAR或者CHAR,多數情況下這個問題可以避免。

另外歡迎關注我們的公眾號,不定期推出和資料庫相關的內容。

ABitAboutMyLife


改成nvarchar試試?


推薦閱讀:

女生做web前端還是資料庫運維?
資料庫工程師需要具備什麼樣的能力和素質?
資料庫建表時一定要設置外鍵約束關係嗎?
資料庫中表自連接,如何獲取時間列中小於自身的最大時間呢?
求教SQL面試題目:單張表查詢某欄位排在第二或第n-1問題?

TAG:資料庫 | MicrosoftSQLServer |