MySQL有哪些約束

本篇內容介紹了“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有哪些約束”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！