做MySQL時間久了你會發(fā)現(xiàn)線上事故翻來覆去就那么幾個根源其中一半是索引沒用對另一半幾乎都藏在數(shù)據(jù)類型亂選和約束設計草率里。今天就把「MySQL數(shù)據(jù)類型與完整性約束」這個老生常談的話題掰開揉碎講清楚。很多人建表全憑一時手感手機號用int存、金額用float存、時間用varchar存等數(shù)據(jù)量跑起來才發(fā)現(xiàn)查詢慢、對不上賬、擴展不得那時候再回頭改表結(jié)構(gòu)代價往往比當初多花十分鐘想清楚要大得多。這篇內(nèi)容適合剛接觸MySQL的新手也適合那些想系統(tǒng)性梳理建表規(guī)范、避免重復踩坑的開發(fā)者。我會把每個類型選型背后的理由、約束真正的執(zhí)行機制、以及生產(chǎn)環(huán)境里我親身踩過和排查過的坑都攤開聊盡量讓每一處選擇都有依據(jù)而不是“大家都這么用所以我也這么用”。1. 整體設計思路為什么數(shù)據(jù)類型與約束必須放在一起聊1.1 數(shù)據(jù)類型選錯的真實代價很多人覺得數(shù)據(jù)類型就是個“存儲格式”影響不大實際上它是整個SQL優(yōu)化的地基。我早期維護過一個訂單系統(tǒng)用戶表里有個手機號字段當初圖省事建成了bigint。理由也很簡單就是一串數(shù)字int存不下就上bigint反正數(shù)字還能比varchar省空間。結(jié)果業(yè)務后面要支持座機號帶區(qū)號、要做手機號前幾位模糊搜索bigint直接沒法走常規(guī)前綴匹配索引查詢只能全表掃。后來改成varchar(20)同樣的查詢從800ms降到60ms級別。這個例子特別典型它說明數(shù)據(jù)類型的選擇不僅關(guān)系到存儲占用更直接決定了索引策略、查詢性能、以及未來業(yè)務的擴展空間。再往深一層說MySQL里每個數(shù)據(jù)類型都有自己的存儲結(jié)構(gòu)、比較規(guī)則和索引行為。你在建表時選定某個類型實際上是在告訴優(yōu)化器這個列應該怎么存儲、怎么排序、怎么建索引。選錯了就等于告訴優(yōu)化器一個錯誤的信息后面再怎么調(diào)SQL也難有起色。這也是為什么我一直建議建表階段多花5分鐘思考字段類型比上線后花5個小時改表結(jié)構(gòu)值太多。1.2 完整性約束管理的到底是什么完整性約束這個詞初學者容易理解為“不讓填空”“不讓重復”其實這只是一部分。完整性約束的本質(zhì)是數(shù)據(jù)庫在每次寫入時自動執(zhí)行的一套校驗規(guī)則它把數(shù)據(jù)質(zhì)量控制的關(guān)卡從應用層下沉到數(shù)據(jù)庫這一層。你想想應用層你能保證每個接口都嚴格校驗參數(shù)嗎團隊擴容之后能保證每個新來的開發(fā)都記得給字段做校驗嗎現(xiàn)實是做不到的。數(shù)據(jù)庫層的約束就是最后一道兜底防線哪怕應用層漏了數(shù)據(jù)也進不來。用一個生活化的類比表就像一棟小區(qū)約束就是門禁系統(tǒng)和物業(yè)管理規(guī)則。沒門禁的時候誰都能進垃圾亂扔、寵物亂跑看著自由住久了問題全暴露。裝上約束之后訪客要登記、外賣要驗證看起來麻煩了點但該進的人都能正常進小區(qū)秩序好得不是一點半點。約束當然有代價比如寫入性能會略降、外鍵有額外維護開銷但這點成本換來的數(shù)據(jù)一致性絕對值得。從設計思路看正確姿勢是“先業(yè)務后類型、先規(guī)則后建表”。也就是建表之前先梳理清楚每個字段到底存什么取值范圍多大有沒有不可為空的約束是否要唯一是否要和別的表關(guān)聯(lián)把這些理清楚再動手寫CREATE TABLE出來的表結(jié)構(gòu)才是有依據(jù)的而不是拍腦袋拼出來的。2. 數(shù)值類型與字符串類型的選型實踐2.1 整數(shù)類型的隱藏細節(jié)MySQL提供了TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT五種整數(shù)類型分別占用1、2、3、4、8字節(jié)。這誰都知道但容易忽略的是它們的取值范圍具體是多少。TINYINT是-128到127SMALLINT是-32768到32767MEDIUMINT約正負838萬INT約正負21億BIGINT則大到天文數(shù)字。實際業(yè)務中狀態(tài)碼用TINYINT就夠訂單序號用INT分布式ID雪花這種必須上BIGINT選大了浪費存儲選小了可能溢出。有個非常經(jīng)典的坑INT后面的顯示寬度。比如INT(11)里的11很多人誤以為它能存11位數(shù)而INT(5)只能存5位數(shù)這是完全錯誤的。這里的數(shù)字只是“顯示寬度”只有在配合ZEROFILL時才有補零展示的效果對存儲范圍和字段本身沒有任何約束力。INT(5)和INT(11)能存的最大值完全一樣。搞清楚這點建表時就不會被各種帶數(shù)字的寫法弄迷糊了。再說說UNSIGNED。如果業(yè)務明確不會出現(xiàn)負數(shù)比如年齡、庫存量、點擊次數(shù)用UNSIGNED可以把正數(shù)上限翻倍。INT UNSIGNED能存到42億左右對某些場景來說剛好夠用。但要注意兩個UNSIGNED字段相減時如果結(jié)果是負數(shù)MySQL會報錯或產(chǎn)生一個很大的正數(shù)這在寫SQL時要格外小心。另外TINYINT(1)經(jīng)常被當作布爾類型使用它實際還是能存-128到127或0到255只是業(yè)務上按0/1理解建議加注釋說明避免后來的人誤用。2.2 浮點數(shù)與定點數(shù)存錢的坑一定要避開FLOAT和DOUBLE是浮點類型DOUBLE比FLOAT精度高但它們本質(zhì)上都是二進制浮點數(shù)運算時會出現(xiàn)精度丟失。我最想講的坑就是金額字段用FLOAT或DOUBLE。之前接手過一個對賬項目月度對賬總有幾分錢的差異查了一個通宵最后定位到金額字段用的FLOAT浮點運算的誤差會在累加過程中被放大。改成DECIMAL(10,2)之后問題徹底消失。DECIMAL是定點數(shù)按十進制存儲不會出現(xiàn)二進制浮點誤差適合金額、稅率、精確數(shù)量等場景。寫法是DECIMAL(M,D)M是總位數(shù)D是小數(shù)位數(shù)M最大65D最大30。比如DECIMAL(10,2)表示8位整數(shù)加2位小數(shù)最大金額能到99999999.99絕大多數(shù)業(yè)務夠用了。定DECIMAL精度時一定要把未來幾年數(shù)據(jù)增長考慮進去比如某些積分類業(yè)務數(shù)值可能到百萬級就要預留足夠位數(shù)。需要提醒的是DECIMAL的運算速度比FLOAT/DOUBLE要慢因為它底層是按字符串存儲和運算。所以在不要求精確計算的場景比如評分均值、百分比展示用DOUBLE問題不大。但凡是涉及錢、庫存扣減、加減乘除的必須用DECIMAL這條原則沒有任何例外。2.3 字符集與字符串類型怎么選才不會出亂子字符串類型最常用的是CHAR和VARCHAR。CHAR(N)是定長不足部分用空格填充讀取時會去掉尾部空格VARCHAR(N)是變長額外用1到2字節(jié)記錄實際長度。選型的核心看數(shù)據(jù)長度的波動性。身份證號、MD5值、固定長度的狀態(tài)碼這類長度幾乎不變用CHAR更好因為定長行在InnoDB里更新時不容易觸發(fā)頁分裂。用戶名、地址、備注這種長度變化大的必須用VARCHAR否則空間浪費驚人。字符集這塊就一句忠告除非有特殊要求否則一律用utf8mb4。MySQL里那個叫utf8的字符集實際上是utf8mb3最多只有3字節(jié)存不了emoji表情、也存不了部分生僻字寫入時會直接報錯。utf8mb4才是完整的UTF-8編碼4字節(jié)以內(nèi)都能存。現(xiàn)在新版本MySQL默認已經(jīng)是utf8mb4但老的庫、老的連接配置可能還在用utf8mb3遷移或者建新表時一定要檢查。排序規(guī)則collation也值得留意utf8mb4_general_ci和utf8mb4_unicode_ci是常用的兩個_ci結(jié)尾表示大小寫不敏感。一般業(yè)務用general_ci夠用追求更精確的Unicode排序規(guī)則可以選unicode_ci核心是前后端庫表連接配置要統(tǒng)一否則會出現(xiàn)亂碼和索引失效。3. 日期時間類型與常用場景3.1 五種日期類型到底有什么區(qū)別MySQL里的日期時間類型一共有五種YEAR、DATE、TIME、DATETIME、TIMESTAMP。YEAR占1字節(jié)范圍1901到2155通常只用來存年份比如“注冊年份”這種統(tǒng)計維度。DATE占3字節(jié)存年月日格式是YYYY-MM-DD適合生日、節(jié)日、合同起始日期。TIME占3字節(jié)存時分秒格式是HH:MM:SS適合營業(yè)時間、跑步耗時這類不關(guān)心日期的場景。DATETIME占8字節(jié)存完整的日期加時間范圍從1000年到9999年是絕大多數(shù)業(yè)務的首選。TIMESTAMP也存日期加時間但占4字節(jié)范圍只有1970年到2038年底層存的是UTC時間戳。這里要特別注意2038年問題。如果你的表會存到2038年之后的日期TIMESTAMP就扛不住了建表時用DATETIME更保險。很多老系統(tǒng)已經(jīng)在為這個問題做改造新項目沒必要給自己埋這個雷。3.2 DATETIME與TIMESTAMP業(yè)務里怎么選DATETIME和TIMESTAMP最直觀的區(qū)別是存儲范圍和占用空間但真正的本質(zhì)區(qū)別在于時區(qū)處理邏輯。TIMESTAMP存入時按當前時區(qū)轉(zhuǎn)成UTC讀取時再按會話時區(qū)轉(zhuǎn)換回來所以它是“帶時區(qū)語義”的。DATETIME不關(guān)心時區(qū)存入什么就取出什么是“純字面量”的日期時間。如果你的應用是國際化產(chǎn)品用戶分布在多個時區(qū)比如記錄用戶最近登錄時間、創(chuàng)建時間用TIMESTAMP有天然優(yōu)勢因為底層存的UTC展示時按客戶端時區(qū)動態(tài)轉(zhuǎn)換。如果只是記錄業(yè)務發(fā)生時刻且公司內(nèi)所有系統(tǒng)統(tǒng)一用北京時間用DATETIME反而省心避免時區(qū)轉(zhuǎn)換造成混亂。還要提一個非常實用的機制TIMESTAMP和DATETIME在較新版本MySQL里都可以設置DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。前者表示插入時自動寫當前時間后者表示這行數(shù)據(jù)被更新時自動刷新該字段。這意味著建表時只要寫上這兩句完全不需要應用層手動維護“創(chuàng)建時間”和“更新時間”少寫很多重復代碼也避免漏更新的問題。4. 完整性約束的落地與執(zhí)行邏輯4.1 NOT NULL與DEFAULT的配合關(guān)系NOT NULL的語義是這一列不允許為NULL但很多人沒細想它和DEFAULT是兩件事。一個字段聲明NOT NULL之后插入時如果不給值MySQL會去看有沒有DEFAULT有就用默認值沒有就會報錯。所以建表時這兩個約束通常會一起出現(xiàn)字段名 類型 NOT NULL DEFAULT 值。我個人的建表習慣是幾乎所有業(yè)務字段都加NOT NULL和DEFAULT哪怕是“備注”“描述”這種看起來可以空的字段也建議DEFAULT 。這么做的好處很明顯查詢里不用到處寫IS NULL或IFNULL索引字段為NULL時優(yōu)化器的處理也更繞。實踐中大量慢查詢就是字段允許NULL導致的NULL值在InnoDB索引里需要額外標記且不能用常規(guī)的等值查詢?nèi)テヅ?。能用空串表示“沒有”就盡量別用NULL。4.2 主鍵約束與唯一約束的使用邊界主鍵約束和唯一約束都保證列的唯一性但它們在InnoDB里的地位完全不同。InnoDB是索引組織表主鍵就是聚簇索引的入口主鍵值的順序決定了數(shù)據(jù)在物理文件里的存放順序。這就是為什么“主鍵設計”是建表時最重要的事情之一。最常見的兩種主鍵方案是自增ID和UUID或雪花ID。自增ID插入時永遠追加到末尾寫入性能好、占空間小適合大多數(shù)業(yè)務。但數(shù)據(jù)量極大時自增ID會有單調(diào)遞增被預測的問題分庫分表時也麻煩。UUID作為主鍵可以避免分庫分表沖突但它占空間大而且因為是隨機值每次插入都要重新排列B樹節(jié)點容易出現(xiàn)頁分裂寫入性能明顯下降。如果一定要用非自增主鍵建議用雪花ID這類有序的分布式ID兼顧全局唯一和相對有序。主鍵設計沒有絕對好壞關(guān)鍵要匹配業(yè)務的寫入和擴展方式。唯一約束的典型場景是手機號、身份證號、郵箱、訂單號。業(yè)務上不允許重復的字段除了業(yè)務代碼做校驗數(shù)據(jù)庫唯一約束都建議加上。代碼校驗總有并發(fā)穿透的時候唯一約束是數(shù)據(jù)庫層面最后一道保險。要區(qū)分“唯一的業(yè)務字段”和“邏輯主鍵”比如一個表的主鍵是自增id但身份證號也要求唯一那身份證號單獨加個UNIQUE KEY即可不必為了它重造主鍵。4.3 外鍵約束什么時候該用什么時候該避外鍵約束的作用是維護表與表之間的參照完整性。比如訂單表里的用戶ID如果在用戶表里不存在插入訂單時數(shù)據(jù)庫直接拒絕。這在傳統(tǒng)ERP、后臺管理類系統(tǒng)中特別好用數(shù)據(jù)一致性有兜底連錯誤提示都省了。但互聯(lián)網(wǎng)高并發(fā)場景對外鍵的態(tài)度要謹慎。外鍵在插入、更新、刪除時都會觸發(fā)對父表的額外檢查遇到熱點行還可能產(chǎn)生鎖競爭在寫入壓力大的表上會放大性能問題。而且一旦分庫分表外鍵基本就廢了因為跨庫無法保證約束。很多團隊的做法是數(shù)據(jù)庫層面不建外鍵依賴應用層保證關(guān)聯(lián)關(guān)系把“刪除用戶時同時刪掉訂單”這類邏輯放到業(yè)務代碼里。這個取舍沒有絕對對錯核心原則是數(shù)據(jù)一致性要求極高且寫入量可控用外鍵省心高并發(fā)、表量大、將來可能分片的就別用外鍵給自己添堵。如果真的用了外鍵需要記住幾個限制外鍵列必須和參照列類型嚴格一致參照列必須是索引InnoDB引擎才支持外鍵MyISAM不支持。這些細節(jié)在建模工具里經(jīng)常校驗不嚴踩坑之后才后悔。4.4 CHECK約束的現(xiàn)狀與正確用法MySQL對CHECK約束的支持歷史比較曲折。5.7及更早的版本雖然語法上接受CHECK但實際上直接忽略不產(chǎn)生任何校驗效果。從8.0.16開始MySQL才真正強制執(zhí)行CHECK約束。這意味著很多老代碼里寫了CHECK到新版本升級時突然開始報錯或者拒絕寫入這也是我見過的一個真實翻車場景。CHECK約束適合用來做簡單的范圍校驗比如年齡字段CHECK (age 0 AND age 150)狀態(tài)字段CHECK (status IN (0,1,2))。它能減輕應用層校驗壓力但別指望它做復雜的跨字段邏輯比如“結(jié)束時間必須大于開始時間”這種寫起來別扭排查起來也繞。我的建議是能用CHECK做簡單值域判斷尤其8.0以上環(huán)境放心用復雜業(yè)務校驗還是放應用層數(shù)據(jù)庫層保持簡單高效。5. 常見問題與排查技巧實錄5.1 類型隱式轉(zhuǎn)換導致索引失效分享一個高頻排查現(xiàn)場某查詢明明字段上有索引但EXPLAIN里type是ALL走了全表掃描。最常見原因之一就是字段類型和查詢條件的類型不一致觸發(fā)了隱式轉(zhuǎn)換。比如字段是varchar類型查詢條件寫的是WHERE phone 13812345678數(shù)字沒有加引號MySQL會把字段轉(zhuǎn)成數(shù)字再比較索引直接失效因為不能對字段使用函數(shù)轉(zhuǎn)換后再走索引。反過來字段是int類型查詢條件是WHERE id 123字符串轉(zhuǎn)數(shù)字不影響索引用因為轉(zhuǎn)換發(fā)生在參數(shù)一側(cè)。排查這類問題的標準動作是看EXPLAIN尤其關(guān)注type列是不是從ref變成了ALL或index再看Extra列有沒有出現(xiàn)Using where配合字段類型和查詢條件的對照基本幾秒鐘就能定位。這類問題在接口升級、前后端數(shù)據(jù)類型傳輸出偏差的時候尤其容易出現(xiàn)值得養(yǎng)成寫SQL帶正確類型的習慣。5.2 utf8mb4與排序規(guī)則不一致的癥狀亂碼和數(shù)據(jù)查不到有時不是數(shù)據(jù)本身的問題而是字符集或collation不統(tǒng)一。比如表的字符集是utf8mb4連接字符串里指定的是latin1寫入時數(shù)據(jù)可能已經(jīng)被截斷或變成問號。再比如兩表關(guān)聯(lián)時一個字段是utf8mb4_general_ci另一個是utf8mb4_bin關(guān)聯(lián)查詢時MySQL可能拒絕使用索引或者報“Illegal mix of collations”的錯誤。排查方法很簡單用SHOW CREATE TABLE查看各表的字符集和collation用SHOW VARIABLES LIKE character_set%查看連接層的設置。原則是庫、表、字段、連接四層字符集盡量統(tǒng)一統(tǒng)一成utf8mb4。一旦出現(xiàn)“Illegal mix of collations”要么給字段統(tǒng)一加COLLATE要么改表結(jié)構(gòu)不建議用CONVERT函數(shù)硬轉(zhuǎn)那只是治標。5.3 約束沖突排查速查表實際操作中約束報錯是最常見的新手攔路虎這里整理一個速查表錯誤信息特征可能原因排查方向Column cannot be null插入了NULL值而字段是NOT NULL檢查代碼中該字段是否沒賦值或DEFAULT沒配Duplicate entry for key違反唯一約束或主鍵約束查重復值來源確認是并發(fā)插入還是數(shù)據(jù)本身重復Foreign key constraint fails外鍵參照的值在父表不存在確認子表插入前父表記錄是否已存在或外鍵類型是否一致Check constraint violated觸發(fā)了CHECK約束的校驗失敗對照CHECK條件檢查插入值是否越界Data truncation / Out of range value插入值超出類型的取值范圍檢查字段類型是否過小比如TINYINT存了128Incorrect date value日期字符串格式不合法檢查字符串格式是否為YYYY-MM-DD或?qū)愋透袷脚挪榧s束沖突不要只看報錯信息要結(jié)合SHOW CREATE TABLE看約束定義再用SELECT去確認表中現(xiàn)有數(shù)據(jù)是不是已經(jīng)臟了。有時候約束不存在但數(shù)據(jù)一致說明應用層校驗到位有時候約束存在但報錯頻繁說明應用層校驗缺失得雙管齊下。寫在最后的一點經(jīng)驗建表看起來門檻低但其實是最容易欠技術(shù)債的地方。我個人的體會是一個字段在定類型和約束時多花一分鐘思考后續(xù)可能省下整個團隊的很多個深夜。建議每張表在建表之前把字段清單、類型、約束、索引先寫進設計文檔過一遍“這個字段會不會變長、會不會有負數(shù)、會不會超范圍、業(yè)務上允不允許重復、能不能為空”這五個問題再落到CREATE TABLE語句里。數(shù)據(jù)是資產(chǎn)表結(jié)構(gòu)是裝資產(chǎn)的容器容器選錯了后面換容器永遠比一開始選對容器痛苦。希望這篇內(nèi)容能幫你少走點彎路讓建表的每個選擇都更有底氣。