據(jù)類(lèi)型避坑指南：實(shí)戰(zhàn)經(jīng)驗(yàn)分享)
1. 為什么SQL Server數(shù)據(jù)類(lèi)型這么容易踩坑我剛?cè)胄心菚?huì)兒最常被前輩罵的就是你這字段類(lèi)型怎么選的。SQL Server的數(shù)據(jù)類(lèi)型看似簡(jiǎn)單但實(shí)際用起來(lái)處處是雷。先說(shuō)幾個(gè)真實(shí)案例去年我們團(tuán)隊(duì)有個(gè)報(bào)表系統(tǒng)突然崩潰排查發(fā)現(xiàn)是有人用varchar(50)存了身份證號(hào)。看起來(lái)沒(méi)問(wèn)題對(duì)吧直到某天業(yè)務(wù)部門(mén)要求支持港澳臺(tái)身份證那些18位的號(hào)碼直接截?cái)嘞到y(tǒng)直接崩了。更慘的是等我們發(fā)現(xiàn)時(shí)已經(jīng)有上千條數(shù)據(jù)被損壞。另一個(gè)經(jīng)典案例是財(cái)務(wù)系統(tǒng)的金額字段。新人用float存金額結(jié)果季度報(bào)表對(duì)不上賬差了幾毛錢(qián)。財(cái)務(wù)總監(jiān)直接沖到技術(shù)部拍桌子——你猜最后怎么著是因?yàn)閒loat的精度問(wèn)題導(dǎo)致0.10.2≠0.3這種基礎(chǔ)問(wèn)題。這些坑我都親自踩過(guò)所以今天要跟你聊的不是教科書(shū)式的數(shù)據(jù)類(lèi)型列表而是實(shí)戰(zhàn)中那些容易忽略的細(xì)節(jié)。我會(huì)重點(diǎn)講5個(gè)最要命的陷阱以及對(duì)應(yīng)的避坑方案。2. 字符串類(lèi)型你以為的varchar并不是你以為的2.1 varchar vs nvarchar字符編碼的生死抉擇先說(shuō)結(jié)論在中文環(huán)境下99%的情況你應(yīng)該用nvarchar。為什么來(lái)看這個(gè)實(shí)驗(yàn)CREATE TABLE TestChars ( regular_col VARCHAR(100), unicode_col NVARCHAR(100) ); INSERT INTO TestChars VALUES (你好世界, N你好世界);執(zhí)行后你會(huì)發(fā)現(xiàn)varchar列存儲(chǔ)的中文變成了亂碼。這是因?yàn)関archar按字節(jié)存儲(chǔ)每個(gè)中文字符占2-3字節(jié)而nvarchar按字符存儲(chǔ)每個(gè)字符固定2字節(jié)。關(guān)鍵經(jīng)驗(yàn)只要涉及多語(yǔ)言或特殊字符無(wú)腦用nvarchar。雖然會(huì)占用更多空間但比起數(shù)據(jù)損壞的代價(jià)這點(diǎn)存儲(chǔ)成本微不足道。2.2 長(zhǎng)度陷阱為什么你的字段總是不夠用我見(jiàn)過(guò)太多這樣的設(shè)計(jì)CREATE TABLE Users ( username VARCHAR(20), email VARCHAR(50) );看起來(lái)合理等業(yè)務(wù)說(shuō)要支持企業(yè)郵箱時(shí)那些超長(zhǎng)的公司郵箱地址直接GG。我的經(jīng)驗(yàn)法則是用戶名/昵稱最少nvarchar(64)郵箱最少nvarchar(256)地址最少nvarchar(512)備注/描述直接nvarchar(MAX)記住現(xiàn)代存儲(chǔ)很便宜但數(shù)據(jù)截?cái)嗟男迯?fù)成本極高。特別要注意的是SQL Server的varchar(MAX)和nvarchar(MAX)其實(shí)性能并不差不要被MAX嚇到。3. 數(shù)字類(lèi)型精度問(wèn)題能讓你加班到天亮3.1 永遠(yuǎn)不要用float/real存金額這是我見(jiàn)過(guò)最貴的錯(cuò)誤之一。來(lái)看這個(gè)驚悚的例子DECLARE a FLOAT 0.1, b FLOAT 0.2; IF a b 0.3 PRINT 相等 ELSE PRINT 不相等; -- 這里會(huì)輸出不相等解決方案很簡(jiǎn)單金額用decimal(19,4) —— 足夠存萬(wàn)億級(jí)別的金額精確到分百分比用decimal(5,2) —— 范圍0-999.99%數(shù)量根據(jù)業(yè)務(wù)需求選int或bigint3.2 自增ID的坑int真的夠用嗎很多表這樣設(shè)計(jì)CREATE TABLE Orders ( OrderID INT IDENTITY(1,1), -- 其他字段 );等業(yè)務(wù)跑幾年后OrderID突破21億時(shí)整個(gè)系統(tǒng)直接崩掉。我的建議是高頻增長(zhǎng)的表直接用bigint數(shù)據(jù)量小的表可以用int但要定期監(jiān)控ID增長(zhǎng)情況4. 日期時(shí)間類(lèi)型時(shí)區(qū)問(wèn)題能讓你懷疑人生4.1 datetime vs datetime2精度與范圍的權(quán)衡老系統(tǒng)常用datetime但它有幾個(gè)致命缺陷精度只有3.33毫秒范圍只有1753-9999年沒(méi)錯(cuò)1753年之前的日期存不了存儲(chǔ)要8字節(jié)而datetime2精度可達(dá)100納秒范圍0001-9999年存儲(chǔ)6-8字節(jié)根據(jù)精度調(diào)整所以現(xiàn)代開(kāi)發(fā)應(yīng)該優(yōu)先用datetime2。示例CREATE TABLE Events ( EventTime DATETIME2(7) -- 7表示納秒精度 );4.2 時(shí)區(qū)陷阱你的時(shí)間不是我的時(shí)間最坑的是跨時(shí)區(qū)系統(tǒng)。假設(shè)你在北京時(shí)間2023-08-01 08:00:00存了條數(shù)據(jù)美國(guó)用戶查出來(lái)顯示什么取決于你用的是什么類(lèi)型datetime不帶時(shí)區(qū)信息直接按服務(wù)器時(shí)區(qū)顯示datetimeoffset包含時(shí)區(qū)信息會(huì)自動(dòng)轉(zhuǎn)換解決方案CREATE TABLE GlobalEvents ( EventTime DATETIMEOFFSET -- 會(huì)存儲(chǔ)時(shí)區(qū)信息 );插入時(shí)INSERT INTO GlobalEvents VALUES (2023-08-01T08:00:0008:00);這樣查詢時(shí)會(huì)自動(dòng)按客戶端時(shí)區(qū)轉(zhuǎn)換顯示。5. 二進(jìn)制與特殊類(lèi)型這些坑你可能還沒(méi)遇到5.1 文件存儲(chǔ)varbinary(MAX)的替代方案很多人喜歡把文件直接存數(shù)據(jù)庫(kù)CREATE TABLE Documents ( Content VARBINARY(MAX) );這會(huì)導(dǎo)致數(shù)據(jù)庫(kù)膨脹。更好的方案是小文件256KB可以存varbinary大文件存文件路徑文件放專用存儲(chǔ)5.2 XML和JSON現(xiàn)代SQL Server的新選擇SQL Server 2016原生支持JSONCREATE TABLE Products ( Attributes NVARCHAR(MAX) CHECK (ISJSON(Attributes)1) -- 確保是合法JSON );查詢時(shí)可以用JSON函數(shù)SELECT ProductID, JSON_VALUE(Attributes, $.color) AS Color FROM Products比以前用字符串自己拼JSON方便多了。6. 數(shù)據(jù)類(lèi)型選擇速查表最后送大家一張我整理的決策表數(shù)據(jù)場(chǎng)景推薦類(lèi)型替代方案禁忌中文文本nvarchar(XX)nvarchar(MAX)varchar金額/財(cái)務(wù)decimal(19,4)moneyfloat/real自增IDbigintintsmallint精確時(shí)間datetime2(7)datetimeoffsetdatetime布爾值bittinyintvarchar(5)存true枚舉值tinyint 約束nvarchar(20)直接用varchar無(wú)約束文件內(nèi)容varbinary(MAX)文件路徑text/ntext復(fù)雜結(jié)構(gòu)JSON(nvarchar)XML自己拼字符串這張表是我用五年時(shí)間、踩過(guò)無(wú)數(shù)坑總結(jié)出來(lái)的。貼在你工位上下次設(shè)計(jì)表時(shí)先看一眼至少能避開(kāi)80%的坑。 SQL Server數(shù)據(jù)類(lèi)型避坑指南：實(shí)戰(zhàn)經(jīng)驗(yàn)分享
1. 為什么SQL Server數(shù)據(jù)類(lèi)型這么容易踩坑我剛?cè)胄心菚?huì)兒最常被前輩罵的就是你這字段類(lèi)型怎么選的。SQL Server的數(shù)據(jù)類(lèi)型看似簡(jiǎn)單但實(shí)際用起來(lái)處處是雷。先說(shuō)幾個(gè)真實(shí)案例去年我們團(tuán)隊(duì)有個(gè)報(bào)表系統(tǒng)突然崩潰排查發(fā)現(xiàn)是有人用varchar(50)存了身份證號(hào)。看起來(lái)沒(méi)問(wèn)題對(duì)吧直到某天業(yè)務(wù)部門(mén)要求支持港澳臺(tái)身份證那些18位的號(hào)碼直接截?cái)嘞到y(tǒng)直接崩了。更慘的是等我們發(fā)現(xiàn)時(shí)已經(jīng)有上千條數(shù)據(jù)被損壞。另一個(gè)經(jīng)典案例是財(cái)務(wù)系統(tǒng)的金額字段。新人用float存金額結(jié)果季度報(bào)表對(duì)不上賬差了幾毛錢(qián)。財(cái)務(wù)總監(jiān)直接沖到技術(shù)部拍桌子——你猜最后怎么著是因?yàn)閒loat的精度問(wèn)題導(dǎo)致0.10.2≠0.3這種基礎(chǔ)問(wèn)題。這些坑我都親自踩過(guò)所以今天要跟你聊的不是教科書(shū)式的數(shù)據(jù)類(lèi)型列表而是實(shí)戰(zhàn)中那些容易忽略的細(xì)節(jié)。我會(huì)重點(diǎn)講5個(gè)最要命的陷阱以及對(duì)應(yīng)的避坑方案。2. 字符串類(lèi)型你以為的varchar并不是你以為的2.1 varchar vs nvarchar字符編碼的生死抉擇先說(shuō)結(jié)論在中文環(huán)境下99%的情況你應(yīng)該用nvarchar。為什么來(lái)看這個(gè)實(shí)驗(yàn)CREATE TABLE TestChars ( regular_col VARCHAR(100), unicode_col NVARCHAR(100) ); INSERT INTO TestChars VALUES (你好世界, N你好世界);執(zhí)行后你會(huì)發(fā)現(xiàn)varchar列存儲(chǔ)的中文變成了亂碼。這是因?yàn)関archar按字節(jié)存儲(chǔ)每個(gè)中文字符占2-3字節(jié)而nvarchar按字符存儲(chǔ)每個(gè)字符固定2字節(jié)。關(guān)鍵經(jīng)驗(yàn)只要涉及多語(yǔ)言或特殊字符無(wú)腦用nvarchar。雖然會(huì)占用更多空間但比起數(shù)據(jù)損壞的代價(jià)這點(diǎn)存儲(chǔ)成本微不足道。2.2 長(zhǎng)度陷阱為什么你的字段總是不夠用我見(jiàn)過(guò)太多這樣的設(shè)計(jì)CREATE TABLE Users ( username VARCHAR(20), email VARCHAR(50) );看起來(lái)合理等業(yè)務(wù)說(shuō)要支持企業(yè)郵箱時(shí)那些超長(zhǎng)的公司郵箱地址直接GG。我的經(jīng)驗(yàn)法則是用戶名/昵稱最少nvarchar(64)郵箱最少nvarchar(256)地址最少nvarchar(512)備注/描述直接nvarchar(MAX)記住現(xiàn)代存儲(chǔ)很便宜但數(shù)據(jù)截?cái)嗟男迯?fù)成本極高。特別要注意的是SQL Server的varchar(MAX)和nvarchar(MAX)其實(shí)性能并不差不要被MAX嚇到。3. 數(shù)字類(lèi)型精度問(wèn)題能讓你加班到天亮3.1 永遠(yuǎn)不要用float/real存金額這是我見(jiàn)過(guò)最貴的錯(cuò)誤之一。來(lái)看這個(gè)驚悚的例子DECLARE a FLOAT 0.1, b FLOAT 0.2; IF a b 0.3 PRINT 相等 ELSE PRINT 不相等; -- 這里會(huì)輸出不相等解決方案很簡(jiǎn)單金額用decimal(19,4) —— 足夠存萬(wàn)億級(jí)別的金額精確到分百分比用decimal(5,2) —— 范圍0-999.99%數(shù)量根據(jù)業(yè)務(wù)需求選int或bigint3.2 自增ID的坑int真的夠用嗎很多表這樣設(shè)計(jì)CREATE TABLE Orders ( OrderID INT IDENTITY(1,1), -- 其他字段 );等業(yè)務(wù)跑幾年后OrderID突破21億時(shí)整個(gè)系統(tǒng)直接崩掉。我的建議是高頻增長(zhǎng)的表直接用bigint數(shù)據(jù)量小的表可以用int但要定期監(jiān)控ID增長(zhǎng)情況4. 日期時(shí)間類(lèi)型時(shí)區(qū)問(wèn)題能讓你懷疑人生4.1 datetime vs datetime2精度與范圍的權(quán)衡老系統(tǒng)常用datetime但它有幾個(gè)致命缺陷精度只有3.33毫秒范圍只有1753-9999年沒(méi)錯(cuò)1753年之前的日期存不了存儲(chǔ)要8字節(jié)而datetime2精度可達(dá)100納秒范圍0001-9999年存儲(chǔ)6-8字節(jié)根據(jù)精度調(diào)整所以現(xiàn)代開(kāi)發(fā)應(yīng)該優(yōu)先用datetime2。示例CREATE TABLE Events ( EventTime DATETIME2(7) -- 7表示納秒精度 );4.2 時(shí)區(qū)陷阱你的時(shí)間不是我的時(shí)間最坑的是跨時(shí)區(qū)系統(tǒng)。假設(shè)你在北京時(shí)間2023-08-01 08:00:00存了條數(shù)據(jù)美國(guó)用戶查出來(lái)顯示什么取決于你用的是什么類(lèi)型datetime不帶時(shí)區(qū)信息直接按服務(wù)器時(shí)區(qū)顯示datetimeoffset包含時(shí)區(qū)信息會(huì)自動(dòng)轉(zhuǎn)換解決方案CREATE TABLE GlobalEvents ( EventTime DATETIMEOFFSET -- 會(huì)存儲(chǔ)時(shí)區(qū)信息 );插入時(shí)INSERT INTO GlobalEvents VALUES (2023-08-01T08:00:0008:00);這樣查詢時(shí)會(huì)自動(dòng)按客戶端時(shí)區(qū)轉(zhuǎn)換顯示。5. 二進(jìn)制與特殊類(lèi)型這些坑你可能還沒(méi)遇到5.1 文件存儲(chǔ)varbinary(MAX)的替代方案很多人喜歡把文件直接存數(shù)據(jù)庫(kù)CREATE TABLE Documents ( Content VARBINARY(MAX) );這會(huì)導(dǎo)致數(shù)據(jù)庫(kù)膨脹。更好的方案是小文件256KB可以存varbinary大文件存文件路徑文件放專用存儲(chǔ)5.2 XML和JSON現(xiàn)代SQL Server的新選擇SQL Server 2016原生支持JSONCREATE TABLE Products ( Attributes NVARCHAR(MAX) CHECK (ISJSON(Attributes)1) -- 確保是合法JSON );查詢時(shí)可以用JSON函數(shù)SELECT ProductID, JSON_VALUE(Attributes, $.color) AS Color FROM Products比以前用字符串自己拼JSON方便多了。6. 數(shù)據(jù)類(lèi)型選擇速查表最后送大家一張我整理的決策表數(shù)據(jù)場(chǎng)景推薦類(lèi)型替代方案禁忌中文文本nvarchar(XX)nvarchar(MAX)varchar金額/財(cái)務(wù)decimal(19,4)moneyfloat/real自增IDbigintintsmallint精確時(shí)間datetime2(7)datetimeoffsetdatetime布爾值bittinyintvarchar(5)存true枚舉值tinyint 約束nvarchar(20)直接用varchar無(wú)約束文件內(nèi)容varbinary(MAX)文件路徑text/ntext復(fù)雜結(jié)構(gòu)JSON(nvarchar)XML自己拼字符串這張表是我用五年時(shí)間、踩過(guò)無(wú)數(shù)坑總結(jié)出來(lái)的。貼在你工位上下次設(shè)計(jì)表時(shí)先看一眼至少能避開(kāi)80%的坑。