一、概述

概念： 約束是作用于表中字段上的規(guī)則，用于限制存儲在表中的數(shù)據(jù)。

目的： 保證數(shù)據(jù)庫中數(shù)據(jù)的正確、有效性和完整性。

分類：

注意：約束是作用于表中字段上的，可以在創(chuàng)建表/修改表的時候添加約束。

二、約束演示

上面我們介紹了數(shù)據(jù)庫中常見的約束，以及約束涉及到的關(guān)鍵字，那這些約束我們到底如何在創(chuàng)建表、修改表的時候來指定呢，接下來我們就通過一個案例，來演示一下。

案例需求： 根據(jù)需求，完成表結(jié)構(gòu)的創(chuàng)建。需求如下：

對應(yīng)的建表語句為：

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 '狀態(tài)',
	gender CHAR ( 1 ) COMMENT '性別' 
);

在為字段添加約束時，我們只需要在字段之后加上約束的關(guān)鍵字即可，需要關(guān)注其語法。

我們執(zhí)行上面的SQL把表結(jié)構(gòu)創(chuàng)建完成，然后接下來，就可以通過一組數(shù)據(jù)進行測試，從而驗證一下，約束是否可以生效。

（1）先是新增了三條數(shù)據(jù)

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','男');

新增三條數(shù)據(jù)，竟然用了21秒，這是什么情況？

本來我還以為是新增這些約束導(dǎo)致新增數(shù)據(jù)慢的，其實不是，因為我這個是阿里的linux服務(wù)器，然后我在linux中通過客戶端連接mysql執(zhí)行新增，也就0.01秒，說明這是navicat連接遠程主機耗時的。

就算新增了這些約束，會導(dǎo)致新增數(shù)據(jù)慢，那也是批量的時候才能明顯察覺出來，單條數(shù)據(jù)基本上看不出來的。

（2）測試name NOT NULL

insert into tb_user(name,age,status,gender) values (null,19,'1','男'); 

（3）測試name UNIQUE（唯一）

上面新增的數(shù)據(jù)已經(jīng)有Tom3了，再次新增直接報錯。

insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');

雖然報錯了，但是我們這時候再新增一條數(shù)據(jù)會發(fā)現(xiàn)一個現(xiàn)象。

insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');

明明是自增id，但是卻沒有4，原因就是UNIQUE（唯一）是在申請完自增id后，準備入庫了，然后這時候會先去看看庫里面是否有存在相同name的值，如果有則新增失敗，雖然新增失敗了，但是自增id已經(jīng)申請過了！

相反我們剛剛測試的null的name的時候他并沒有去申請id，因為他在剛開始就已經(jīng)判斷他為空了，還沒走到申請id這一步。

判斷是否為空 -》 申請自增id -》 判斷是否已經(jīng)有存在的值

總結(jié)：當新增的name不為空的時候，但是和之前存在的數(shù)據(jù)有相同的，這時候新增會失敗，但是他會申請主鍵id。

（4）測試CHECK

我們設(shè)置的是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 '狀態(tài)',

insert into tb_user(name,age,gender) values ('Tom5',120,'男');

（6）上面，我們是通過編寫SQL語句的形式來完成約束的指定，那假如我們是Navicat客戶端呢？

主鍵自增

name唯一約束

status默認為1

三、外鍵約束

1、 什么是外鍵約束

外鍵： 用來讓兩張表的數(shù)據(jù)之間建立連接，從而保證數(shù)據(jù)的一致性和完整性。

我們來看一個例子：

左側(cè)的emp表是員工表，里面存儲員工的基本信息，包含員工的ID、姓名、年齡、職位、薪資、入職日期、上級主管ID、部門ID，在員工的信息中存儲的是部門的ID dept_id，而這個部門的ID是關(guān)聯(lián)的部門表dept的主鍵id，那emp表的dept_id就是外鍵,關(guān)聯(lián)的是另一張表的主鍵。

2、 不使用外鍵有什么影響

通過上面的示例，我們分別來演示 添加外鍵 和不添加外鍵的區(qū)別，首先來看不添加 外鍵 對數(shù)據(jù)有什么影響：

準備數(shù)據(jù)：

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, '研發(fā)部'), (2, '市場部'),(3, '財務(wù)部'), (4, '銷售部'), (5, '總經(jīng)辦');

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 '直屬領(lǐng)導(dǎo)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, '項目經(jīng)理',12500, '2005-12-05', 1,1), 
(3, '楊逍', 33, '開發(fā)', 8400,'2000-11-03', 2,1),
(4, '韋一笑', 48, '開 發(fā)',11000, '2002-02-05', 2,1), 
(5, '常遇春', 43, '開發(fā)',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程 序員鼓勵師',6600, '2004-10-12', 2,1);

接下來，我們可以做一個測試，刪除id為1的部門信息。

結(jié)果，我們看到刪除成功，而刪除成功之后，部門表不存在id為1的部門，而在emp表中還有很多的員工，關(guān)聯(lián)的為id為1的部門，此時就出現(xiàn)了數(shù)據(jù)的不完整性。 而要想解決這個問題就得通過數(shù)據(jù)庫的外鍵約束。

正常開發(fā)當中有時候會通過業(yè)務(wù)代碼來控制數(shù)據(jù)的不完整性，例如刪除部門的時候會先根據(jù)部門id去查看一下有沒有對應(yīng)的員工表，如果有則刪除失敗，沒有則刪除成功。

3、 添加外鍵的語法

可以在創(chuàng)建表的時候直接添加外鍵，也可以對現(xiàn)已存在的表添加外鍵。

（1）方式一

CREATE TABLE 表名( 
	字段名 數(shù)據(jù)類型, 
	... 
	[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 '直屬領(lǐng)導(dǎo)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就會自動給我們起外鍵名稱。

方式二：對現(xiàn)存在的表添加外鍵

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、 刪除/更新行為

添加了外鍵之后，在刪除父表數(shù)據(jù)時產(chǎn)生的約束行為，我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種:

在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

當在父表中刪除/更新對應(yīng)記錄時，首先檢查該記錄是否有對應(yīng)外鍵，如果有則不允許刪除/更新。 (與 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

當在父表中刪除/更新對應(yīng)記錄時，首先檢查該記錄是否有對應(yīng)外鍵，如果有，則

也刪除/更新外鍵在子表中的記錄。

刪除外鍵的語法：

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

我們發(fā)現(xiàn)，原來在子表中dept_id值為1的記錄，現(xiàn)在也變?yōu)?了，這就是cascade級聯(lián)的效果。

在一般的業(yè)務(wù)系統(tǒng)中，不會修改一張表的主鍵值。

刪除父表id為6的記錄

我們發(fā)現(xiàn)，父表的數(shù)據(jù)刪除成功了，但是子表中關(guān)聯(lián)的記錄也被級聯(lián)刪除了。

（3）演示SET NULL

當在父表中刪除對應(yīng)記錄時，首先檢查該記錄是否有對應(yīng)外鍵，如果有則設(shè)置子表中該外鍵值為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。然后再通過數(shù)據(jù)腳本，將emp、dept表的數(shù)據(jù)恢復(fù)了。

接下來，我們刪除id為1的數(shù)據(jù)，看看會發(fā)生什么樣的現(xiàn)象。

我們發(fā)現(xiàn)父表的記錄是可以正常的刪除的，父表的數(shù)據(jù)刪除之后，再打開子表 emp，我們發(fā)現(xiàn)子表emp的dept_id字段，原來dept_id為1的數(shù)據(jù)，現(xiàn)在都被置為NULL了。

這就是SET NULL這種刪除/更新行為的效果。

四、主鍵id到底用自增好還是uuid好

在mysql中設(shè)計表的時候，mysql官方推薦不要使用uuid或者不連續(xù)不重復(fù)的雪花id(long形且唯一)，而是推薦連續(xù)自增的主鍵id，官方的推薦是auto_increment，那么為什么不建議采用uuid，使用uuid究竟有什么壞處？

1、測試uuid和自增id還有隨機數(shù)插入效率

首先來建立三張表，user_auto_key代表的是自增表，user_uuid代表的是id存儲的uuid，random_key代表的是表id是雪花id。然后通過連接jdbc批量插入數(shù)據(jù)測試測試結(jié)果如下：

在已有數(shù)據(jù)量為130W的時候：我們再來測試一下插入10w數(shù)據(jù)，看看會有什么結(jié)果：

可以看出在數(shù)據(jù)量100W左右的時候,uuid的插入效率墊底，并且在后序增加了130W的數(shù)據(jù)，uudi的時間又直線下降。時間占用量總體可以打出的效率排名為：auto_key>random_key>uuid，uuid的效率最低

2、使用自增id的缺點

1.別人一旦爬取你的數(shù)據(jù)庫,就可以根據(jù)數(shù)據(jù)庫的自增id獲取到你的業(yè)務(wù)增長信息，很容易分析出你的經(jīng)營情況

2.對于高并發(fā)的負載，innodb在按主鍵進行插入的時候會造成明顯的鎖爭用，主鍵的上界會成為爭搶的熱點，因為所有的插入都發(fā)生在這里，并發(fā)插入會導(dǎo)致間隙鎖競爭

3.Auto_Increment鎖機制會造成自增鎖的搶奪,有一定的性能損失

4.自增id涉及到數(shù)據(jù)遷移的話是相當麻煩的！

5.而且一旦涉及到分庫分表自增id也是相當麻煩的！

3、使用uuid的缺點

因為uuid相對順序的自增id來說是毫無規(guī)律可言的，新行的值不一定要比之前的主鍵的值要大，所以innodb無法做到總是把新行插入到索引的最后，而是需要為新行尋找新的合適的位置從而來分配新的空間。這個過程需要做很多額外的操作，數(shù)據(jù)的毫無順序會導(dǎo)致數(shù)據(jù)分布散亂，將會導(dǎo)致以下的問題：

1.寫入的目標頁很可能已經(jīng)刷新到磁盤上并且從緩存上移除，或者還沒有被加載到緩存中，innodb在插入之前不得不先找到并從磁盤讀取目標頁到內(nèi)存中，這將導(dǎo)致大量的隨機IO

2.因為寫入是亂序的，innodb不得不頻繁的做頁分裂操作，以便為新的行分配空間，頁分裂導(dǎo)致移動大量的數(shù)據(jù)，一次插入最少需要修改三個頁以上

3.由于頻繁的頁分裂，頁會變得稀疏并被不規(guī)則的填充，最終會導(dǎo)致數(shù)據(jù)會有碎片

頁分裂和碎片問題，uuid確實會引起這個問題，但雪花可以解決這個問題，雪花算法天然具有順序性新插入的ID一定是最大的，所以我認為用雪花算法是一個很不錯的選擇！

五、實際開發(fā)盡量少用外鍵

主鍵和索引是不可少的，不僅可以優(yōu)化數(shù)據(jù)檢索速度，開發(fā)人員還省不其它的工作。

矛盾焦點：數(shù)據(jù)庫設(shè)計是否需要外鍵。這里有兩個問題：

一個是如何保證數(shù)據(jù)庫數(shù)據(jù)的完整性和一致性；

二是第一條對性能的影響。

這里分為了正方和反方兩個觀點，供參考！

1、正方觀點

1.由數(shù)據(jù)庫自身保證數(shù)據(jù)一致性，完整性，更可靠，因為程序很難100％保證數(shù)據(jù)的完整性，而用外鍵即使在數(shù)據(jù)庫服務(wù)器當機或者出現(xiàn)其他問題的時候，也能夠最大限度的保證數(shù)據(jù)的一致性和完整性。

2.有主外鍵的數(shù)據(jù)庫設(shè)計可以增加ER圖的可讀性，這點在數(shù)據(jù)庫設(shè)計時非常重要。

3.外鍵在一定程度上說明的業(yè)務(wù)邏輯，會使設(shè)計周到具體全面。

數(shù)據(jù)庫和應(yīng)用是一對多的關(guān)系，Ａ應(yīng)用會維護他那部分數(shù)據(jù)的完整性，系統(tǒng)一變大時，增加了Ｂ應(yīng)用，Ａ和Ｂ兩個應(yīng)用也許是不同的開發(fā)團隊來做的。他們?nèi)绾螀f(xié)調(diào)保證數(shù)據(jù)的完整性，而且一年以后如果又增加了C應(yīng)用呢？

2、反方觀點

1.可以用觸發(fā)器或應(yīng)用程序保證數(shù)據(jù)的完整性

2.過分強調(diào)或者說使用主鍵／外鍵會平添開發(fā)難度，導(dǎo)致表過多等問題

3.不用外鍵時數(shù)據(jù)管理簡單，操作方便，性能高（導(dǎo)入導(dǎo)出等操作，在insert, update, delete 數(shù)據(jù)的時候更快）

在海量的數(shù)據(jù)庫中想都不要去想外鍵，試想，一個程序每天要insert數(shù)百萬條記錄，當存在外鍵約束的時候，每次要去掃描此記錄是否合格，一般還不 止一個字段有外鍵，這樣掃描的數(shù)量是成級數(shù)的增長！我的一個程序入庫在3個小時做完，如果加上外鍵，需要28個小時！

3、結(jié)論

1.在大型系統(tǒng)中（性能要求不高，安全要求高），使用外鍵；在大型系統(tǒng)中（性能要求高，安全自己控制），不用外鍵；小系統(tǒng)隨便，最好用外鍵。

2.用外鍵要適當，不能過分追求

3.不用外鍵而用程序控制數(shù)據(jù)一致性和完整性時，應(yīng)該寫一層來保證，然后個個應(yīng)用通過這個層來訪問數(shù)據(jù)庫。

需要注意的是：

MySQL允許使用外鍵，但是為了完整性檢驗的目的，在除了InnoDB表類型之外的所有表類型中都忽略了這個功能。這可能有些怪異，實際上卻非常正常：對于數(shù)據(jù)庫的所有外鍵的每次插入、更新和刪除后，進行完整性檢查是一個耗費時間和資源的過程，它可能影響性能，特別是當處理復(fù)雜的或者是纏繞的連接數(shù)時。因而，用戶可以在表的基礎(chǔ)上，選擇適合于特定需求的。

所以，如果需要更好的性能，并且不需要完整性檢查，可以選擇使用MyISAM表類型，如果想要在MySQL中根據(jù)參照完整性來建立表并且希望在此基礎(chǔ)上保持良好的性能，最好選擇表結(jié)構(gòu)為innoDB類型

以上就是MySQL六種約束的示例詳解(全網(wǎng)最全)的詳細內(nèi)容，更多關(guān)于MySQL約束的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論