<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
概念: 約束是作用於表中欄位上的規則,用於限制儲存在表中的資料。
目的: 保證資料庫中資料的正確、有效性和完整性。
分類:
注意:約束是作用於表中欄位上的,可以在建立表/修改表的時候新增約束。
上面我們介紹了資料庫中常見的約束,以及約束涉及到的關鍵字,那這些約束我們到底如何在建立表、修改表的時候來指定呢,接下來我們就通過一個案例,來演示一下。
案例需求: 根據需求,完成表結構的建立。需求如下:
對應的建表語句為:
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
外來鍵: 用來讓兩張表的資料之間建立連線,從而保證資料的一致性和完整性。
我們來看一個例子:
左側的emp表是員工表,裡面儲存員工的基本資訊,包含員工的ID、姓名、年齡、職位、薪資、入職日期、上級主管ID、部門ID,在員工的資訊中儲存的是部門的ID dept_id,而這個部門的ID是關聯的部門表dept的主鍵id,那emp表的dept_id就是外來鍵,關聯的是另一張表的主鍵。
通過上面的範例,我們分別來演示 新增外來鍵 和不新增外來鍵的區別,首先來看不新增 外來鍵 對資料有什麼影響:
準備資料:
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去檢視一下有沒有對應的員工表,如果有則刪除失敗,沒有則刪除成功。
可以在建立表的時候直接新增外來鍵,也可以對現已存在的表新增外來鍵。
(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;
新增了外來鍵之後,在刪除父表資料時產生的約束行為,我們就稱為刪除/更新行為。具體的刪除/更新行為有以下幾種:
在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
(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這種刪除/更新行為的效果。
在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其它相關文章!
相關文章
<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
综合看Anker超能充系列的性价比很高,并且与不仅和iPhone12/苹果<em>Mac</em>Book很配,而且适合多设备充电需求的日常使用或差旅场景,不管是安卓还是Switch同样也能用得上它,希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
除了L4WUDU与吴亦凡已经多次共事,成为了明面上的厂牌成员,吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节,这也是20XXCLUB首次全员合照,王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
目前应用IPFS的机构:1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆,历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
开拓者的车机是兼容苹果和<em>安卓</em>,虽然我不怎么用,但确实兼顾了我家人的很多需求:副驾的门板还配有解锁开关,有的时候老婆开车,下车的时候偶尔会忘记解锁,我在副驾驶可以自己开门:第二排设计很好,不仅配置了一个很大的
2021-06-01 09:30:48
不仅是<em>安卓</em>手机,苹果手机的降价力度也是前所未有了,iPhone12也“跳水价”了,发布价是6799元,如今已经跌至5308元,降价幅度超过1400元,最新定价确认了。iPhone12是苹果首款5G手机,同时也是全球首款5nm芯片的智能机,它
2021-06-01 09:30:45