首頁 > 軟體

MySQL六種約束的範例詳解(全網最全)

2022-07-26 14:03:37

一、概述

概念: 約束是作用於表中欄位上的規則,用於限制儲存在表中的資料。

目的: 保證資料庫中資料的正確、有效性和完整性。

分類:

注意:約束是作用於表中欄位上的,可以在建立表/修改表的時候新增約束。

二、約束演示

上面我們介紹了資料庫中常見的約束,以及約束涉及到的關鍵字,那這些約束我們到底如何在建立表、修改表的時候來指定呢,接下來我們就通過一個案例,來演示一下。

案例需求: 根據需求,完成表結構的建立。需求如下:

對應的建表語句為:

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.外來鍵在一定程度上說明的業務邏輯,會使設計周到具體全面。

資料庫和應用是一對多的關係,A應用會維護他那部分資料的完整性,系統一變大時,增加了B應用,A和B兩個應用也許是不同的開發團隊來做的。他們如何協調保證資料的完整性,而且一年以後如果又增加了C應用呢?

2、反方觀點

1.可以用觸發器或應用程式保證資料的完整性

2.過分強調或者說使用主鍵/外來鍵會平添開發難度,導致表過多等問題

3.不用外來鍵時資料管理簡單,操作方便,效能高(匯入匯出等操作,在insert, update, delete 資料的時候更快)

在海量的資料庫中想都不要去想外來鍵,試想,一個程式每天要insert數百萬條記錄,當存在外來鍵約束的時候,每次要去掃描此記錄是否合格,一般還不 止一個欄位有外來鍵,這樣掃描的數量是成級數的增長!我的一個程式入庫在3個小時做完,如果加上外來鍵,需要28個小時!

3、結論

1.在大型系統中(效能要求不高,安全要求高),使用外來鍵;在大型系統中(效能要求高,安全自己控制),不用外來鍵;小系統隨便,最好用外來鍵。

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

3.不用外來鍵而用程式控制資料一致性和完整性時,應該寫一層來保證,然後個個應用通過這個層來存取資料庫。

需要注意的是:

MySQL允許使用外來鍵,但是為了完整性檢驗的目的,在除了InnoDB表型別之外的所有表型別中都忽略了這個功能。這可能有些怪異,實際上卻非常正常:對於資料庫的所有外來鍵的每次插入、更新和刪除後,進行完整性檢查是一個耗費時間和資源的過程,它可能影響效能,特別是當處理複雜的或者是纏繞的連線數時。因而,使用者可以在表的基礎上,選擇適合於特定需求的。

所以,如果需要更好的效能,並且不需要完整性檢查,可以選擇使用MyISAM表型別,如果想要在MySQL中根據參照完整性來建立表並且希望在此基礎上保持良好的效能,最好選擇表結構為innoDB型別

以上就是MySQL六種約束的範例詳解(全網最全)的詳細內容,更多關於MySQL約束的資料請關注it145.com其它相關文章!


IT145.com E-mail:sddin#qq.com