上周處理了一個線上事故模擬訂單表里同一筆訂單號出現(xiàn)了三次金額三成是 NULL狀態(tài)字段五六個寫法并存。排查之后發(fā)現(xiàn)建表語句里除了一個自增主鍵其他所有字段全靠應(yīng)用層自覺。那一刻我特別想感慨MySQL 的表約束不是教科書考點而是生產(chǎn)環(huán)境里數(shù)據(jù)質(zhì)量的最后一道閘門。這篇文章我想把 MySQL 的表約束徹底講透?，F(xiàn)有六種約束——NOT NULL非空、DEFAULT默認值、UNIQUE唯一、PRIMARY KEY主鍵、CHECK檢查、FOREIGN KEY外鍵——分別解決什么問題、怎么寫、有什么版本差異和隱藏坑以及表已經(jīng)上線之后補約束該怎么操作都會覆蓋到。適合剛把增刪改查跑順、準備設(shè)計真實表結(jié)構(gòu)的開發(fā)者也適合被老系統(tǒng)的臟數(shù)據(jù)折磨過一遍又一遍的運維和開發(fā)。1. 約束不是附加功能它是數(shù)據(jù)的守門員1.1 一段沒有約束的臟數(shù)據(jù)現(xiàn)場先還原一個我見過很多次的場景。某項目的訂單表order_no 允許為 NULL于是有人直接插入了一條沒有訂單號的記錄。起初沒什么影響等到對賬、導(dǎo)出、和支付系統(tǒng)做回調(diào)匹配的時候這條 NULL 記錄讓所有 join 和 count 全對不上前端顯示undefined客服后臺一片問號。這不是程序 bug而是表結(jié)構(gòu)打一開始就沒攔住非法輸入。再看另一個經(jīng)典場景訂單表里的 user_id 沒有外鍵某天運營在后臺誤刪了一個測試用戶這個用戶名下幾十條訂單全部變成了孤兒數(shù)據(jù)。業(yè)務(wù)查詢用戶訂單時自然少了一截但你在數(shù)據(jù)庫里怎么數(shù)都數(shù)不出問題因為訂單還在只是關(guān)聯(lián)斷了。數(shù)據(jù)不一致的這種臟比字段為 NULL 更隱蔽、更難排查。還有狀態(tài)字段。沒有 CHECK 約束的時候status 今天寫 paid明天寫 PAID后天寫 已支付統(tǒng)計報表里 WHERE status paid 永遠比實際少幾行還得寫一堆 CASE WHEN 來兜底。這類問題不是在某個時間點突然爆雷而是持續(xù)性地污染統(tǒng)計口徑和分析結(jié)論。這些例子說明一個道理約束不是給開發(fā)添麻煩。數(shù)據(jù)庫是數(shù)據(jù)落地的最后執(zhí)行者應(yīng)用層校驗再嚴也架不住漏網(wǎng)之魚、臨時腳本、運營手工改庫。把規(guī)則寫在表結(jié)構(gòu)里等于讓數(shù)據(jù)庫替每個人把關(guān)。1.2 MySQL 的六種約束速覽MySQL 的約束可以分成兩類一類管單個字段的值合不合法另一類管字段之間、表與表之間的關(guān)系對不對。先看一張速覽表約束類型作用關(guān)鍵字聲明位置非空約束字段值不允許為 NULLNOT NULL列級默認值約束未顯式賦值時填充預(yù)置值DEFAULT列級唯一約束字段或字段組合的值不重復(fù)UNIQUE列級/表級主鍵約束行的唯一標識隱含非空加唯一PRIMARY KEY列級/表級檢查約束字段值必須滿足給定表達式CHECK列級/表級外鍵約束保證子表與父表引用關(guān)系完整FOREIGN KEY表級NOT NULL、DEFAULT、UNIQUE 這三板斧管的是這個字段能不能為空、默認變成什么、會不會重復(fù)。PRIMARY KEY 決定了一行數(shù)據(jù)在整個表里的身份。CHECK 管的是字段取值白名單或范圍。FOREIGN KEY 管的是表和表之間的引用關(guān)系。后面幾節(jié)我逐個拆解重點放在為什么和踩坑上。2. 最基礎(chǔ)的三道防線NOT NULL、DEFAULT 與 UNIQUE2.1 NOT NULL 和 DEFAULT 是黃金搭檔別拆開用先看一個常見誤解字段加上 NOT NULL 就一定不會出現(xiàn)臟數(shù)據(jù)了只說對了一半。如果一個字段聲明為 NOT NULL 但沒有 DEFAULT插入數(shù)據(jù)時不顯式給值在 MySQL 嚴格的 sql_mode 下會直接報 1364 錯誤Field doesnt have a default value但在非嚴格模式下MySQL 會悄悄按照字段類型的隱式默認值填充字符串填空串、數(shù)字填 0。很多老項目跑著跑著出現(xiàn)一堆 0 和空串原因就在這。所以 NOT NULL 幾乎總是要和 DEFAULT 一起設(shè)計。比如時間字段created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPcreated_at 靠 DEFAULT 在插入時自動帶上當前時間updated_at 靠 ON UPDATE 在行更新時自動刷新。這套組合是建表的基本功也是我審查別人表結(jié)構(gòu)時第一個看的地方。這里有一個經(jīng)驗要強調(diào)DEFAULT 的取值必須語義明確。業(yè)務(wù)上沒有填寫年齡應(yīng)該用 NULL 表示未知那就別寫成 DEFAULT 0如果業(yè)務(wù)要求必須有值再考慮 0 或某個哨兵值。很多系統(tǒng)后來查不出到底是真 0 還是沒填就是因為當年為了省事把 DEFAULT 寫成了 0。另外提醒一句DATETIME 類型從某個版本開始才支持直接用 CURRENT_TIMESTAMP 做默認值老項目里那種靠應(yīng)用層手動拼時間的寫法在設(shè)計新表時完全可以退休了。2.2 UNIQUE 約束的 NULL 行為是個經(jīng)典陷阱UNIQUE 約束保證字段或字段組合的值不重復(fù)但它有一個經(jīng)常被忽略的特性MySQL 允許在 UNIQUE 列里插入多行 NULL。原因很簡單在 MySQL 看來 NULL 表示未知NULL 不等于 NULL所以每一行 NULL 都是新的。這個特性對業(yè)務(wù)影響很大。假設(shè) user 表的 email 字段加了 UNIQUE 約束注冊時用戶沒有填郵箱應(yīng)用層往庫里寫 NULL。第一個用戶能注冊第二個也能因為 NULL 不算重復(fù)。如果運營用 SELECT COUNT(DISTINCT email) 去判斷郵箱重復(fù)結(jié)果會和直覺完全不同。從 SQL Server 轉(zhuǎn)過來的同事最容易在這里踩坑——SQL Server 的唯一約束默認只允許一個 NULL。應(yīng)對辦法也簡單如果業(yè)務(wù)上要求要么必填要么只有一個 NULL就把可空字段的 NULL 在應(yīng)用層統(tǒng)一轉(zhuǎn)成空字符串處理如果一個都不能重復(fù)那這個字段就不該允許 NULL。2.3 UNIQUE 和普通索引的分工別搞混UNIQUE 約束在 MySQL 里落地時會自動創(chuàng)建一個唯一索引既做約束又加速查詢。普通索引INDEX只加速查找不限制重復(fù)。很多剛?cè)腴T的同學(xué)會有疑問那我在登錄賬號上建普通索引不也能加快查詢嗎能但這意味著數(shù)據(jù)庫默許了賬號重復(fù)等于把去重的責(zé)任全部推給應(yīng)用層。我的建議很直接凡是業(yè)務(wù)上不允許重復(fù)的字段——賬號、郵箱、手機號、訂單號——直接建 UNIQUE 約束只是需要加速查詢、允許重復(fù)的字段比如訂單表的用戶 ID、文章表的狀態(tài)建普通索引就夠了。判斷標準就一句話重復(fù)會不會造成業(yè)務(wù)事故。會就上 UNIQUE不會普通索引即可。3. 主鍵InnoDB 表的身份證到底怎么選3.1 自增主鍵為什么是默認選擇主鍵約束合并了 NOT NULL 和 UNIQUE一行一個主鍵值不能為空也不能重復(fù)。在 InnoDB 里主鍵還有一個隱藏身份它是聚簇索引也就是說表數(shù)據(jù)的物理存儲順序就是主鍵順序。這意味著主鍵生成方式直接影響寫入性能。自增主鍵是單調(diào)遞增的新行的主鍵值總是比已有的大寫入時追加在索引末尾頁分裂少、隨機 IO 少。而如果把 UUID 字符串當主鍵值完全隨機每次插入都可能落在索引中間的某個位置觸發(fā)頁分裂和碎片整理。同樣是十萬行數(shù)據(jù)UUID 主鍵的表寫入耗時可能是自增主鍵的好幾倍索引體積也更大。我見過不少項目選擇 UUID 主鍵是為了防枚舉或分布式環(huán)境不依賴自增這個理由存在但和性能的取舍要講清楚。如果確實需要分布式唯一 ID優(yōu)先考慮帶時間序的生成算法類似雪花 ID、UUIDv7 這類既保持分布式的天然優(yōu)勢又把索引寫入的隨機性問題壓到最低。3.2 業(yè)務(wù)主鍵和數(shù)據(jù)庫主鍵是兩回事很多表設(shè)計里會把訂單號直接設(shè)為 PRIMARY KEY我覺得這通常是設(shè)計錯誤。訂單號是業(yè)務(wù)上暴露給外部的編號要面向用戶、要進日志、要參與對外查詢它未來可能因為業(yè)務(wù)規(guī)則變化而需要調(diào)整格式或長度。而數(shù)據(jù)庫主鍵只是行的內(nèi)部標識最好是一個和業(yè)務(wù)完全無關(guān)、穩(wěn)定不變的數(shù)字。讓訂單號做 UNIQUE 約束來保證業(yè)務(wù)唯一性讓自增 id 做主鍵來管物理存儲各司其職將來改訂單號規(guī)則時你才會感謝當初的設(shè)計。復(fù)合主鍵則要想清楚查詢模式再決定。比如選課表 PRIMARY KEY (class_id, student_no)如果業(yè)務(wù)主要按 student_no 反查學(xué)生選了哪些課這個復(fù)合主鍵幫不上忙還得額外建索引。復(fù)合主鍵遵循最左前綴原則最左邊的列一定要是高區(qū)分度的列否則索引效率很低。我見過把 status 放在復(fù)合主鍵第一列的查詢性能差到離譜后來還是拆了單列主鍵加普通索引。4. CHECK 與外鍵強約束的正確打開方式4.1 CHECK 約束在 8.0.16 前后是兩個世界很多老資料會說 MySQL 不支持 CHECK 約束這個說法已經(jīng)過時了。MySQL 8.0.16 開始CHECK 約束被真正強制執(zhí)行在此之前MySQL 雖然在建表語法里接受 CHECK 子句但只是解析后直接忽略并不會攔截非法數(shù)據(jù)。所以如果你還在用 5.7 或更早的版本不要在表結(jié)構(gòu)上指望 CHECK老老實實靠應(yīng)用層校驗。如果你已經(jīng)用 8.0 及以上CHECK 是控制字段取值白名單的好工具。舉兩個例子status VARCHAR(16) NOT NULL DEFAULT pending, CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, refunded, closed)), CONSTRAINT chk_orders_amount CHECK (amount 0)加了這兩個約束之后任何一條把 status 寫成 PAID 或者把 amount 寫成負數(shù)的 INSERT 和 UPDATE都會被數(shù)據(jù)庫直接拒絕報 3819 錯誤Check constraint violated。這對防止應(yīng)用層漏校驗和運營手工改庫時手滑很有價值。一個細節(jié)CHECK 約束只攔截約束添加之后的新寫入和更新不會去回溯清理表里已經(jīng)存在的臟數(shù)據(jù)。換句話說在 8.0 上給一張滿是臟數(shù)據(jù)的表加 CHECK加約束本身通常不會報錯但后續(xù)對臟數(shù)據(jù)行的任何 UPDATE 都可能突然報 3819。上線前先把歷史數(shù)據(jù)洗干凈是動手前的必修課。4.2 FOREIGN KEY 的級聯(lián)策略CASCADE、SET NULL、RESTRICT 怎么選外鍵約束只在 InnoDB 引擎下生效它保證子表引用父表的每一行都真實存在于父表。刪除父表行時外鍵的級聯(lián)策略決定了子表怎么反應(yīng)常見的有三種RESTRICT / NO ACTION默認行為父表有子表引用時禁止刪除或更新直接報 1451 錯誤CASCADE刪除或更新父表行時子表引用行跟著刪除或更新SET NULL父表行刪除時子表外鍵列被置為 NULL前提是該列允許為空。級聯(lián)策略沒有標準答案取決于業(yè)務(wù)語義。還是拿訂單舉例訂單是核心財務(wù)數(shù)據(jù)用戶不能在有訂單的情況下被刪除所以 user_id 外鍵應(yīng)該用 ON DELETE RESTRICT。而評論、站內(nèi)信這類附屬數(shù)據(jù)主內(nèi)容被刪了留著沒意義用 CASCADE 反而省事或者出于審計需求保留記錄那就把刪除改成邏輯刪除字段而不是物理刪行。SET NULL 要特別注意這個策略要求外鍵列本身允許 NULL。我在代碼評審里見過不少把外鍵列寫成 NOT NULL 卻配置 ON DELETE SET NULL 的表最后刪除父行時直接報錯。設(shè)計階段就要想清楚這個關(guān)系是不是可斷開的斷了之后子表記錄還有沒有存在意義。外鍵也不是免費的。每次在子表寫入、更新或刪除InnoDB 都要去父表檢查引用是否存在會引入額外的鎖和 IO。高并發(fā)寫入的核心鏈路外鍵的檢查開銷和鎖競爭會被放大這也是很多團隊在核心表上刻意不用外鍵的原因。4.3 分庫分表之后外鍵的限度要重新評估外鍵是數(shù)據(jù)庫層面的完整性機制前提是子表和父表在同一個數(shù)據(jù)庫實例里。一旦做了分庫分表訂單表在訂單庫、用戶表在用戶庫外鍵就徹底失效跨庫跨節(jié)點的引用完整性只能由應(yīng)用層通過事務(wù)、消息和補償機制來保證。所以外鍵用不用不是一個好不好的道德問題而是部署架構(gòu)決定的工程問題。單體應(yīng)用、數(shù)據(jù)量可控的中小系統(tǒng)外鍵是性價比極高的數(shù)據(jù)質(zhì)量保障分布式架構(gòu)、核心鏈路高并發(fā)外鍵反而可能成為性能和擴展的瓶頸。我見過一些團隊為了避免日后分庫分表從第一天起就在所有表上禁用外鍵這有點矯枉過正——等到真的需要分庫分表時再清除外鍵成本完全可控但在此之前外鍵替應(yīng)用層擋住了大量臟數(shù)據(jù)。5. 從零設(shè)計一張帶約束的訂單表完整實操5.1 把業(yè)務(wù)需求翻譯成約束清單設(shè)計規(guī)范的表結(jié)構(gòu)本質(zhì)上是一道翻譯題把業(yè)務(wù)規(guī)則翻譯成數(shù)據(jù)庫能執(zhí)行的約束。就以一個模擬訂單場景為例需求是這樣的每一筆訂單必須有唯一的業(yè)務(wù)訂單號訂單必須屬于某個真實存在的用戶訂單狀態(tài)只能是 pending、paid、refunded、closed 四種訂單金額不允許為負數(shù)有訂單的用戶不允許被直接刪除。翻譯成約束清單就是order_no 加 UNIQUE 約束user_id 加 NOT NULL 并建外鍵引用用戶表級聯(lián)策略選 RESTRICTstatus 加 CHECKamount 加 CHECK。這四條需求每一條都對應(yīng)一個明確的約束設(shè)計時對照檢查就不會出現(xiàn)字段裸奔的情況。5.2 完整建表 SQL 逐行解讀最終建表語句如下CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, status VARCHAR(16) NOT NULL DEFAULT pending, amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT, CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, refunded, closed)), CONSTRAINT chk_orders_amount CHECK (amount 0) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4;逐行看id 是自增主鍵純粹的行標識不參與任何業(yè)務(wù)邏輯order_no 是業(yè)務(wù)訂單號用 uk_order_no 這個唯一約束保證不重復(fù)user_id 上的外鍵 fk_orders_user 同時保證了用戶必須存在和有訂單的用戶刪不掉status 和 amount 兩個 CHECK 約束把取值范圍釘死created_at 和 updated_at 交給數(shù)據(jù)庫自動維護。外鍵這里有個細節(jié)值得留意InnoDB 要求外鍵的引用列子表和被引用列父表都存在合適的索引。子表 user_id 上如果事先沒有索引MySQL 會自動給它補一個父表 users.id 因為有主鍵天然滿足條件。但如果你引用的是父表的非主鍵列一定要確認那個列有索引否則建外鍵會直接報 1215 錯誤。5.3 約束命名規(guī)范給自己留后路約束命名看起來是小事實際上非常影響后續(xù)維護。MySQL 里刪除外鍵約束、檢查約束都必須知道約束的名字如果建表時不顯式命名系統(tǒng)會自動生成一堆類似 orders_ibfk_1 的名字數(shù)量一多根本分不清誰是誰。我習(xí)慣的命名規(guī)則是主鍵就叫 PRIMARY唯一約束用 uk_ 前綴加字段名比如 uk_order_no外鍵用 fk_ 前綴加子表_父表比如 fk_orders_userCHECK 用 chk_ 前綴加表名和含義比如 chk_orders_status普通索引用 idx_ 前綴。這套規(guī)則簡單、自解釋幾個月后回頭維護表結(jié)構(gòu)看名字就能知道每個約束在管什么。6. 上線之后想改約束ALTER TABLE 操作與報錯排查6.1 增刪約束的標準語法表已經(jīng)上線了數(shù)據(jù)也跑了一兩個月這時候想補約束靠的就是 ALTER TABLE。語法其實不復(fù)雜-- 添加 CHECK ALTER TABLE orders ADD CONSTRAINT chk_orders_status CHECK (status IN (pending, paid, refunded, closed)); -- 刪除 CHECK ALTER TABLE orders DROP CHECK chk_orders_status; -- 添加唯一約束 ALTER TABLE employees ADD UNIQUE KEY uk_emp_email (email); -- 刪除唯一約束通過 DROP INDEX 刪除因為 UNIQUE 約束底層就是唯一索引 ALTER TABLE employees DROP INDEX uk_emp_email; -- 添加外鍵 ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id); -- 刪除外鍵 ALTER TABLE orders DROP FOREIGN KEY fk_orders_user; -- 給字段加非空 ALTER TABLE orders MODIFY COLUMN user_id BIGINT UNSIGNED NOT NULL;初學(xué)者最容易搞混的是刪除語法刪除普通索引用 DROP INDEX刪除唯一約束也是 DROP INDEX刪除外鍵是 DROP FOREIGN KEY刪除 CHECK 是 DROP CHECK刪除主鍵是 DROP PRIMARY KEY。為什么唯一約束走 DROP INDEX因為 MySQL 里 UNIQUE 約束和唯一索引本質(zhì)上是同一個東西沒有單獨的 DROP UNIQUE 語法。6.2 加約束失敗的典型報錯與其解法給線上表加約束最大概率遇到的坑是表里已經(jīng)有臟數(shù)據(jù)。約束是對未來的承諾但對過去的數(shù)據(jù)同樣要負責(zé)MySQL 發(fā)現(xiàn)存量數(shù)據(jù)違反約束時加約束操作會失敗。常見的報錯和根因如下報錯含義典型處理ERROR 1062 (23000): Duplicate entry已有重復(fù)值UNIQUE/主鍵加不上先查重復(fù)數(shù)據(jù)清洗后再加ERROR 1138 (22004): Invalid use of NULL value已有 NULLNOT NULL 加不上先 UPDATE 填充默認值再 MODIFYERROR 1452 (23000): Cannot add or update a child row已有孤兒數(shù)據(jù)外鍵加不上先處理不存在的引用再建外鍵ERROR 3819: Check constraint violated已有值不滿足 CHECK 表達式先定位非法值并修正再加 CHECK以最常見的 1062 為例排查動作其實三行 SQL 就夠SELECT order_no, COUNT(*) AS cnt FROM orders GROUP BY order_no HAVING cnt 1; -- 根據(jù)業(yè)務(wù)決定保留哪條多余的刪除或改號 DELETE FROM orders WHERE id IN (...); ALTER TABLE orders ADD UNIQUE KEY uk_order_no (order_no);我的原則是任何約束操作之前先把對應(yīng)字段的 SELECT 查一遍。加非空之前查 NULL加唯一之前查重復(fù)加外鍵之前查孤兒加 CHECK 之前查范圍之外的值。這一步十分鐘的檢查能省下半夜處理報警的時間。6.3 外鍵報錯的完整排查鏈路外鍵相關(guān)的報錯是 ALTER 操作里最繞的我拆一個完整鏈路。假設(shè)線上執(zhí)行刪除用戶操作時報錯ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails (shop.orders, CONSTRAINTfk_orders_userFOREIGN KEY (user_id) REFERENCESusers(id))第一步看報錯里給出的約束名和表名能立刻定位是 orders 表的 fk_orders_user 約束擋住了刪除。第二步確認級聯(lián)策略SHOW CREATE TABLE orders 查看外鍵定義發(fā)現(xiàn)是 ON DELETE RESTRICT說明設(shè)計上就是不允許刪有訂單的用戶。第三步查詢引用數(shù)據(jù)量SELECT COUNT(*) FROM orders WHERE user_id 目標ID。第四步?jīng)Q定方案要么給用戶做邏輯刪除而不是物理刪除要么先把訂單歸檔或遷移要么修改外鍵為 ON DELETE CASCADE——最后這個方案不推薦風(fēng)險太大。再看加外鍵失敗的場景ALTER TABLE 報 ERROR 1215: Cannot add foreign key constraint。這種情況的根因排查順序非常重要通常按下面三步走先對比數(shù)據(jù)類型和字符集子表 user_id 如果是 INT父表 id 是 BIGINT或兩邊字符集、排序規(guī)則不一致都會被拒絕再檢查父表被引用列是否有索引父表被引用列不是索引InnoDB 直接拒絕建外鍵最后檢查子表已有數(shù)據(jù)子表里有父表不存在的 user_id加外鍵會被 1452 拒絕。這套鏈路走一遍95% 的外鍵問題都能在十分鐘內(nèi)定位完。剩下那 5%多半是字符集混用或者約束名沖突這類環(huán)境問題用 SHOW CREATE TABLE 把兩個表結(jié)構(gòu)擺在一起對比很快也能看到端倪。最后補一句個人經(jīng)驗。如果你接手了一張跑了兩三年的老表第一反應(yīng)千萬別是把所有約束一次性都加上。先挑唯一性和非空這兩類約束直接決定業(yè)務(wù)數(shù)據(jù)能不能信跑穩(wěn)定了再加外鍵和 CHECK。每加一個約束之前先跑一條 SELECT 把違反規(guī)則的行撈出來和業(yè)務(wù)方確認清洗方案再執(zhí)行 ALTER。數(shù)據(jù)庫約束這東西是在事情還沒發(fā)生時就建好的防御工事等數(shù)據(jù)爛了再補成本和勇氣都不是平常能想象的。