MySQL六種約束的示例詳解(全網最全)
一、概述
概念： 約束是作用於表中字段上的規則，用於限制存儲在表中的數據。
目的： 保證數據庫中數據的正確、有效性和完整性。
分類：
註意：約束是作用於表中字段上的，可以在創建表/修改表的時候添加約束。
二、約束演示
上面我們介紹瞭數據庫中常見的約束，以及約束涉及到的關鍵字，那這些約束我們到底如何在創建表、修改表的時候來指定呢，接下來我們就通過一個案例，來演示一下。
案例需求： 根據需求，完成表結構的創建。需求如下：
對應的建表語句為：
CREATE TABLE tb_user ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一標識', NAME VARCHAR ( 10 ) NOT NULL UNIQUE COMMENT '姓名', age INT CHECK ( age > 0 && age <= 120 ) COMMENT '年齡', STATUS CHAR ( 1 ) DEFAULT '1' COMMENT '狀態', gender CHAR ( 1 ) COMMENT '性別' );
在為字段添加約束時，我們隻需要在字段之後加上約束的關鍵字即可，需要關註其語法。
我們執行上面的SQL把表結構創建完成，然後接下來，就可以通過一組數據進行測試，從而驗證一下，約束是否可以生效。
（1）先是新增瞭三條數據
insert into tb_user(name,age,status,gender) values ('Tom1',19,'1','男'),('Tom2',25,'0','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
新增三條數據，竟然用瞭21秒，這是什麼情況？
本來我還以為是新增這些約束導致新增數據慢的，其實不是，因為我這個是阿裡的linux服務器，然後我在linux中通過客戶端連接mysql執行新增，也就0.01秒，說明這是navicat連接遠程主機耗時的。
就算新增瞭這些約束，會導致新增數據慢，那也是批量的時候才能明顯察覺出來，單條數據基本上看不出來的。
（2）測試name NOT NULL
insert into tb_user(name,age,status,gender) values (null,19,'1','男');
（3）測試name UNIQUE（唯一）
上面新增的數據已經有Tom3瞭，再次新增直接報錯。
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
雖然報錯瞭，但是我們這時候再新增一條數據會發現一個現象。
insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');
明明是自增id，但是卻沒有4，原因就是UNIQUE（唯一）是在申請完自增id後，準備入庫瞭，然後這時候會先去看看庫裡面是否有存在相同name的值，如果有則新增失敗，雖然新增失敗瞭，但是自增id已經申請過瞭！
相反我們剛剛測試的null的name的時候他並沒有去申請id，因為他在剛開始就已經判斷他為空瞭，還沒走到申請id這一步。
判斷是否為空 -》 申請自增id -》 判斷是否已經有存在的值
總結：當新增的name不為空的時候，但是和之前存在的數據有相同的，這時候新增會失敗，但是他會申請主鍵id。
（4）測試CHECK
我們設置的是age必須大於0小於等於120，否則保存失敗！
age int check (age > 0 && age <= 120) COMMENT '年齡' ,
insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男');
（5）測試 DEFAULT ‘1’ 默認值
STATUS CHAR ( 1 ) DEFAULT '1' COMMENT '狀態',
insert into tb_user(name,age,gender) values ('Tom5',120,'男');
（6）上面，我們是通過編寫SQL語句的形式來完成約束的指定，那假如我們是Navicat客戶端呢？
主鍵自增
name唯一約束
status默認為1
三、外鍵約束
1、 什麼是外鍵約束
外鍵： 用來讓兩張表的數據之間建立連接，從而保證數據的一致性和完整性。
我們來看一個例子：
左側的emp表是員工表，裡面存儲員工的基本信息，包含員工的ID、姓名、年齡、職位、薪資、入職日期、上級主管ID、部門ID，在員工的信息中存儲的是部門的ID dept_id，而這個部門的ID是關聯的部門表dept的主鍵id，那emp表的dept_id就是外鍵,關聯的是另一張表的主鍵。
2、 不使用外鍵有什麼影響
通過上面的示例，我們分別來演示 添加外鍵 和不添加外鍵的區別，首先來看不添加 外鍵 對數據有什麼影響：
準備數據：
CREATE TABLE dept ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '部門名稱' ) COMMENT '部門表'; INSERT INTO dept (id, name) VALUES (1, '研發部'), (2, '市場部'),(3, '財務部'), (4, '銷售部'), (5, '總經辦'); CREATE TABLE emp ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名', age INT COMMENT '年齡', job VARCHAR ( 20 ) COMMENT '職位', salary INT COMMENT '薪資', entrydate date COMMENT '入職時間', managerid INT COMMENT '直屬領導ID', dept_id INT COMMENT '部門ID' ) COMMENT '員工表'; INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES (1, '金庸', 66, '總裁',20000, '2000-01-01', null,5), (2, '張無忌', 20, '項目經理',12500, '2005-12-05', 1,1), (3, '楊逍', 33, '開發', 8400,'2000-11-03', 2,1), (4, '韋一笑', 48, '開 發',11000, '2002-02-05', 2,1), (5, '常遇春', 43, '開發',10500, '2004-09-07', 3,1), (6, '小昭', 19, '程 序員鼓勵師',6600, '2004-10-12', 2,1);
接下來，我們可以做一個測試，刪除id為1的部門信息。
結果，我們看到刪除成功，而刪除成功之後，部門表不存在id為1的部門，而在emp表中還有很多的員工，關聯的為id為1的部門，此時就出現瞭數據的不完整性。 而要想解決這個問題就得通過數據庫的外鍵約束。
正常開發當中有時候會通過業務代碼來控制數據的不完整性，例如刪除部門的時候會先根據部門id去查看一下有沒有對應的員工表，如果有則刪除失敗，沒有則刪除成功。
3、 添加外鍵的語法
可以在創建表的時候直接添加外鍵，也可以對現已存在的表添加外鍵。
（1）方式一
CREATE TABLE 表名( 字段名 數據類型, ... [CONSTRAINT] [外鍵名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表 (主表列名) );
使用示例：
CREATE TABLE emp ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名', age INT COMMENT '年齡', job VARCHAR ( 20 ) COMMENT '職位', salary INT COMMENT '薪資', entrydate date COMMENT '入職時間', managerid INT COMMENT '直屬領導ID', dept_id INT COMMENT '部門ID', CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept (id) ) COMMENT '員工表';
也可以省略掉CONSTRAINT fk_emp_dept_id 這樣mysql就會自動給我們起外鍵名稱。
方式二：對現存在的表添加外鍵
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名) REFERENCES 主表 (主表列名) ;
使用示例：
alter table emp add constraint fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);
方式三：Navicat添加外鍵
刪除外鍵：
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;
使用示例：
alter table emp drop foreign key fk_emp_dept_id;
4、 刪除/更新行為
添加瞭外鍵之後，在刪除父表數據時產生的約束行為，我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種:
在mysql8.0.27版本當中，RESTRICT是默認的刪除更新行為！不同的版本可能也會有所差距！
具體語法為:
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
就是比原先添加外鍵後面多瞭這些ON UPDATE CASCADE ON DELETE CASCADE，代表的是更新時采用CASCADE ，刪除時也采用CASCADE
5、 演示刪除/更新行為
（1）演示RESTRICT
當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則不允許刪除/更新。 (與 NO ACTION 一致) 默認行為
首先要添加外鍵，默認是RESTRICT行為！
alter table emp add constraint fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);
當我要刪除父表當中id為5的記錄的時候會報錯，原因就是emp表的dept_id存在5。假如要更新id也同樣會報錯的！
（2）演示CASCADE
當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外鍵，如果有，則
也刪除/更新外鍵在子表中的記錄。
刪除外鍵的語法：
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵約束名;
刪除外鍵的示例：
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;
指定外鍵的刪除更新行為為cascade
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;
修改父表id為1的記錄，將id修改為6
我們發現，原來在子表中dept_id值為1的記錄，現在也變為6瞭，這就是cascade級聯的效果。
在一般的業務系統中，不會修改一張表的主鍵值。
刪除父表id為6的記錄
我們發現，父表的數據刪除成功瞭，但是子表中關聯的記錄也被級聯刪除瞭。
（3）演示SET NULL
當在父表中刪除對應記錄時，首先檢查該記錄是否有對應外鍵，如果有則設置子表中該外鍵值為null（這就要求該外鍵允許取null）。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;
在進行測試之前，我們先需要刪除上面建立的外鍵 fk_emp_dept_id。然後再通過數據腳本，將emp、dept表的數據恢復瞭。
接下來，我們刪除id為1的數據，看看會發生什麼樣的現象。
我們發現父表的記錄是可以正常的刪除的，父表的數據刪除之後，再打開子表 emp，我們發現子表emp的dept_id字段，原來dept_id為1的數據，現在都被置為NULL瞭。
這就是SET NULL這種刪除/更新行為的效果。
四、主鍵id到底用自增好還是uuid好
在mysql中設計表的時候，mysql官方推薦不要使用uuid或者不連續不重復的雪花id(long形且唯一)，而是推薦連續自增的主鍵id，官方的推薦是auto_increment，那麼為什麼不建議采用uuid，使用uuid究竟有什麼壞處？
1、測試uuid和自增id還有隨機數插入效率
首先來建立三張表，user_auto_key代表的是自增表，user_uuid代表的是id存儲的uuid，random_key代表的是表id是雪花id。然後通過連接jdbc批量插入數據測試測試結果如下：
在已有數據量為130W的時候：我們再來測試一下插入10w數據，看看會有什麼結果：
可以看出在數據量100W左右的時候,uuid的插入效率墊底，並且在後序增加瞭130W的數據，uudi的時間又直線下降。時間占用量總體可以打出的效率排名為：auto_key>random_key>uuid，uuid的效率最低
2、使用自增id的缺點
1.別人一旦爬取你的數據庫,就可以根據數據庫的自增id獲取到你的業務增長信息，很容易分析出你的經營情況
2.對於高並發的負載，innodb在按主鍵進行插入的時候會造成明顯的鎖爭用，主鍵的上界會成為爭搶的熱點，因為所有的插入都發生在這裡，並發插入會導致間隙鎖競爭
3.Auto_Increment鎖機制會造成自增鎖的搶奪,有一定的性能損失
4.自增id涉及到數據遷移的話是相當麻煩的！
5.而且一旦涉及到分庫分表自增id也是相當麻煩的！
3、使用uuid的缺點
因為uuid相對順序的自增id來說是毫無規律可言的，新行的值不一定要比之前的主鍵的值要大，所以innodb無法做到總是把新行插入到索引的最後，而是需要為新行尋找新的合適的位置從而來分配新的空間。這個過程需要做很多額外的操作，數據的毫無順序會導致數據分佈散亂，將會導致以下的問題：
1.寫入的目標頁很可能已經刷新到磁盤上並且從緩存上移除，或者還沒有被加載到緩存中，innodb在插入之前不得不先找到並從磁盤讀取目標頁到內存中，這將導致大量的隨機IO
2.因為寫入是亂序的，innodb不得不頻繁的做頁分裂操作，以便為新的行分配空間，頁分裂導致移動大量的數據，一次插入最少需要修改三個頁以上
3.由於頻繁的頁分裂，頁會變得稀疏並被不規則的填充，最終會導致數據會有碎片
頁分裂和碎片問題，uuid確實會引起這個問題，但雪花可以解決這個問題，雪花算法天然具有順序性新插入的ID一定是最大的，所以我認為用雪花算法是一個很不錯的選擇！
五、實際開發盡量少用外鍵
主鍵和索引是不可少的，不僅可以優化數據檢索速度，開發人員還省不其它的工作。
矛盾焦點：數據庫設計是否需要外鍵。這裡有兩個問題：
一個是如何保證數據庫數據的完整性和一致性；
二是第一條對性能的影響。
這裡分為瞭正方和反方兩個觀點，供參考！
1、正方觀點
1.由數據庫自身保證數據一致性，完整性，更可靠，因為程序很難100％保證數據的完整性，而用外鍵即使在數據庫服務器當機或者出現其他問題的時候，也能夠最大限度的保證數據的一致性和完整性。
2.有主外鍵的數據庫設計可以增加ER圖的可讀性，這點在數據庫設計時非常重要。
3.外鍵在一定程度上說明的業務邏輯，會使設計周到具體全面。
數據庫和應用是一對多的關系，Ａ應用會維護他那部分數據的完整性，系統一變大時，增加瞭Ｂ應用，Ａ和Ｂ兩個應用也許是不同的開發團隊來做的。他們如何協調保證數據的完整性，而且一年以後如果又增加瞭C應用呢？
2、反方觀點
1.可以用觸發器或應用程序保證數據的完整性
2.過分強調或者說使用主鍵／外鍵會平添開發難度，導致表過多等問題
3.不用外鍵時數據管理簡單，操作方便，性能高（導入導出等操作，在insert, update, delete 數據的時候更快）
在海量的數據庫中想都不要去想外鍵，試想，一個程序每天要insert數百萬條記錄，當存在外鍵約束的時候，每次要去掃描此記錄是否合格，一般還不 止一個字段有外鍵，這樣掃描的數量是成級數的增長！我的一個程序入庫在3個小時做完，如果加上外鍵，需要28個小時！
3、結論
1.在大型系統中（性能要求不高，安全要求高），使用外鍵；在大型系統中（性能要求高，安全自己控制），不用外鍵；小系統隨便，最好用外鍵。
2.用外鍵要適當，不能過分追求
3.不用外鍵而用程序控制數據一致性和完整性時，應該寫一層來保證，然後個個應用通過這個層來訪問數據庫。
需要註意的是：
MySQL允許使用外鍵，但是為瞭完整性檢驗的目的，在除瞭InnoDB表類型之外的所有表類型中都忽略瞭這個功能。這可能有些怪異，實際上卻非常正常：對於數據庫的所有外鍵的每次插入、更新和刪除後，進行完整性檢查是一個耗費時間和資源的過程，它可能影響性能，特別是當處理復雜的或者是纏繞的連接數時。因而，用戶可以在表的基礎上，選擇適合於特定需求的。
所以，如果需要更好的性能，並且不需要完整性檢查，可以選擇使用MyISAM表類型，如果想要在MySQL中根據參照完整性來建立表並且希望在此基礎上保持良好的性能，最好選擇表結構為innoDB類型
以上就是MySQL六種約束的示例詳解(全網最全)的詳細內容，更多關於MySQL約束的資料請關註WalkonNet其它相關文章！