MySQL有哪些約束
- 王林轉載
- 2023-05-26 19:14:101543瀏覽
一、概述
概念： 約束條件是作用於表格中欄位上的規則，用來限制儲存在表格中的數據。
目的： 保證資料庫中資料的正確、有效性和完整性。
分類：
#注意：約束是作用於表中欄位上的，可以在創建表/修改表的時候加入約束。
二、約束示範
上面我們介紹了資料庫中常見的約束，以及約束涉及到的關鍵字，那麼這些約束我們到底如何在建立表格、修改表格的時候來指定呢，接下來我們就透過一個案例，來示範一下。
案例需求： 根據需求，完成表格結構的建立。需求如下：
對應的建表語句為：
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 -》 判斷是否已經有存在的值
（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客戶端呢？
主鍵自增
外鍵： 用來讓兩張表的資料之間建立連接，從而確保資料的一致性和完整性。
我們來看一個例子：
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在插入之前不得不先找到並從磁碟讀取目標頁到內存中，這將導致大量的隨機IO2.因為寫入是亂序的，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中文網其他相關文章！