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類型,不管你傳入的字元串到底有多長,都會在最右邊補空格,一直補到你定義的時候指定的那麼長。
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;
可以得到這樣的結果:
上面DBCC命令的結果清楚地表明了SQL Server是怎樣存儲數據的,其中我標識出來的數據非別是:
1. 這行數據的狀態信息2. "這行中有幾個column"的這個信息在哪裡什麼位置保存著呢?1e也就是10進位的30,表示在偏移量30個位元組的位置,數一數也就是在上圖中5標識出的位置,佔兩個位元組3. 這是我們的int column的值,這裡保存的是數字14. 這是我們的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 |