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 &#39;年龄&#39;,
	STATUS CHAR ( 1 ) DEFAULT &#39;1&#39; COMMENT &#39;状态&#39;,
	gender CHAR ( 1 ) COMMENT &#39;性别&#39; 
);

在為欄位新增約束時，我們只需要在欄位之後加上約束的關鍵字即可，需要注意其語法。

我們執行上面的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 &#39;年龄&#39; ,

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、 删除/更新行为

我们将在父表数据删除时发生的限制行为称为删除/更新行为，此行为是在添加外键之后发生的。具体的删除/更新行为有以下几种:

默认的MySQL 8.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.用外鍵要適當，不能過度追求

為了保證資料的一致性和完整性，可以不使用外鍵而透過程式控制。這時應編寫一層來實現資料保護，再透過這個層來存取資料庫的各個應用程式。

要注意的是：

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

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

以上是MySQL有哪些約束的詳細內容。更多資訊請關注PHP中文網其他相關文章！