一、概述

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

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

分類：

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

二、約束演示

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

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

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

CREATE TABLE tb_user (
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一標(biāo)識(shí)',
	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 '性別' 
);

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

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

（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ù)慢的，其實(shí)不是，因?yàn)槲疫@個(gè)是阿里的linux服務(wù)器，然后我在linux中通過客戶端連接mysql執(zhí)行新增，也就0.01秒，說明這是navicat連接遠(yuǎn)程主機(jī)耗時(shí)的。

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

（2）測(cè)試name NOT NULL

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

（3）測(cè)試name UNIQUE（唯一）

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

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

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

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

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

相反我們剛剛測(cè)試的null的name的時(shí)候他并沒有去申請(qǐng)id，因?yàn)樗趧傞_始就已經(jīng)判斷他為空了，還沒走到申請(qǐng)id這一步。

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

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

（4）測(cè)試CHECK

我們?cè)O(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）測(cè)試 DEFAULT ‘1’ 默認(rèn)值

STATUS CHAR ( 1 ) DEFAULT '1' COMMENT '狀態(tài)',

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

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

主鍵自增

name唯一約束

status默認(rèn)為1

三、外鍵約束

1、 什么是外鍵約束

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

我們來看一個(gè)例子：

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

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

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

準(zhǔn)備數(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, '市場(chǎng)部'),(3, '財(cái)務(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 '入職時(shí)間',
	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, '項(xiàng)目經(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, '程 序員鼓勵(lì)師',6600, '2004-10-12', 2,1);

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

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

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

3、 添加外鍵的語法

可以在創(chuàng)建表的時(shí)候直接添加外鍵，也可以對(duì)現(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 '入職時(shí)間',
	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就會(huì)自動(dòng)給我們起外鍵名稱。

方式二：對(duì)現(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ù)時(shí)產(chǎn)生的約束行為，我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種:

在mysql8.0.27版本當(dāng)中，RESTRICT是默認(rèn)的刪除更新行為！不同的版本可能也會(huì)有所差距！

具體語法為:

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

就是比原先添加外鍵后面多了這些ON UPDATE CASCADE ON DELETE CASCADE，代表的是更新時(shí)采用CASCADE ，刪除時(shí)也采用CASCADE

5、 演示刪除/更新行為

（1）演示RESTRICT

當(dāng)在父表中刪除/更新對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(yīng)外鍵，如果有則不允許刪除/更新。 (與 NO ACTION 一致) 默認(rèn)行為

首先要添加外鍵，默認(rèn)是RESTRICT行為！

alter table emp add constraint fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

當(dāng)我要?jiǎng)h除父表當(dāng)中id為5的記錄的時(shí)候會(huì)報(bào)錯(cuò)，原因就是emp表的dept_id存在5。假如要更新id也同樣會(huì)報(bào)錯(cuò)的！

（2）演示CASCADE

當(dāng)在父表中刪除/更新對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(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級(jí)聯(lián)的效果。

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

刪除父表id為6的記錄

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

（3）演示SET NULL

當(dāng)在父表中刪除對(duì)應(yīng)記錄時(shí)，首先檢查該記錄是否有對(duì)應(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 ;

在進(jìn)行測(cè)試之前，我們先需要?jiǎng)h除上面建立的外鍵 fk_emp_dept_id。然后再通過數(shù)據(jù)腳本，將emp、dept表的數(shù)據(jù)恢復(fù)了。

接下來，我們刪除id為1的數(shù)據(jù)，看看會(huì)發(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è)計(jì)表的時(shí)候，mysql官方推薦不要使用uuid或者不連續(xù)不重復(fù)的雪花id(long形且唯一)，而是推薦連續(xù)自增的主鍵id，官方的推薦是auto_increment，那么為什么不建議采用uuid，使用uuid究竟有什么壞處？

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

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

在已有數(shù)據(jù)量為130W的時(shí)候：我們?cè)賮頊y(cè)試一下插入10w數(shù)據(jù)，看看會(huì)有什么結(jié)果：

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

2、使用自增id的缺點(diǎn)

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

2.對(duì)于高并發(fā)的負(fù)載，innodb在按主鍵進(jìn)行插入的時(shí)候會(huì)造成明顯的鎖爭(zhēng)用，主鍵的上界會(huì)成為爭(zhēng)搶的熱點(diǎn)，因?yàn)樗械牟迦攵及l(fā)生在這里，并發(fā)插入會(huì)導(dǎo)致間隙鎖競(jìng)爭(zhēng)

3.Auto_Increment鎖機(jī)制會(huì)造成自增鎖的搶奪,有一定的性能損失

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

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

3、使用uuid的缺點(diǎn)

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

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

2.因?yàn)閷懭胧莵y序的，innodb不得不頻繁的做頁分裂操作，以便為新的行分配空間，頁分裂導(dǎo)致移動(dòng)大量的數(shù)據(jù)，一次插入最少需要修改三個(gè)頁以上

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

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

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

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

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

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

二是第一條對(duì)性能的影響。

這里分為了正方和反方兩個(gè)觀點(diǎn)，供參考！

1、正方觀點(diǎn)

1.由數(shù)據(jù)庫自身保證數(shù)據(jù)一致性，完整性，更可靠，因?yàn)槌绦蚝茈y100％保證數(shù)據(jù)的完整性，而用外鍵即使在數(shù)據(jù)庫服務(wù)器當(dāng)機(jī)或者出現(xiàn)其他問題的時(shí)候，也能夠最大限度的保證數(shù)據(jù)的一致性和完整性。

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

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

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

2、反方觀點(diǎn)

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

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

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

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

3、結(jié)論

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

2.用外鍵要適當(dāng)，不能過分追求

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

需要注意的是：

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

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

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

最新評(píng)論