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