快捷導(dǎo)航

一文理解MySQL數(shù)據(jù)庫的約束與表的設(shè)計

更新時間：2022年11月15日 11:39:13 作者：Node_Hao

約束是用來限制表中的數(shù)據(jù)長什么樣子的,即什么樣的數(shù)據(jù)可以插入到表中,什么樣的數(shù)據(jù)插入不到表中,下面這篇文章主要給大家介紹了關(guān)于如何通過一文理解MySQL數(shù)據(jù)庫的約束與表的設(shè)計的相關(guān)資料,需要的朋友可以參考下

1.數(shù)據(jù)庫約束

約束是關(guān)系型數(shù)據(jù)庫提供的一種校驗數(shù)據(jù)合法性的機制.

1.1 約束類型

約束類型	說明	示例
null	使用not null 指定列不為空	name varchar(20) not null;
unique	指定列為唯一的,不重復(fù)的	name varchar(20) unique;
default	指定列為空時的默認(rèn)值	name varchar(20) default "無名氏";
主鍵約束	not null 和unique 的結(jié)合	id int primary key;
外鍵約束	關(guān)聯(lián)其他表的主鍵或唯一鍵	foreign key (字段名) references 主表(列)
check約束	保證列的值符合指定的條件	check(sex='男'or sex='女')

1.2 null 約束

創(chuàng)建表時指定某列不為空.

語法:

create table 表名(字段1 類型 not null,字段2 類型...);

示例:

1.3 unique 唯一的約束

創(chuàng)建表時指定某列為唯一的不重復(fù)的.

語法:

create table 表名(字段1 類型 unique,字段2 類型....);

示例:

1.4 default 默認(rèn)值約束

指定列為空時的默認(rèn)值

語法:

create table 表名(字段1 類型 default 默認(rèn)值,字段2 類型....);

示例:

1.5 primary key 主鍵約束

創(chuàng)建表時指定列有唯一確定的標(biāo)識符

語法:

create table 表名(字段1 類型 parmary key,字段2 類型...);

示例:

學(xué)生id 不為null 且不能重復(fù).

1.6 primary key auto_increment 自增主鍵

對于整數(shù)類型的主鍵, 常搭配自增auto_increment變?yōu)樽栽鲋麈I, 插入字段不給值時, 使用最大值+1.

語法:

create table 表名(字段1 int primary key auto_increment,字段2 類型...);

示例:

分布式系統(tǒng)中唯一id生成算法

對于較小的數(shù)據(jù)量, 所有的數(shù)據(jù)都在一臺MySQL服務(wù)器上存儲,此時自增主鍵可以很好的工作, 但如果涉及的數(shù)據(jù)量較為龐大, 需要將數(shù)據(jù)部署到多臺MySQL服務(wù)器上, 這時再插入數(shù)據(jù)就無法保證自增主鍵的唯一性.因此MySQL內(nèi)部采用分布式系統(tǒng)中唯一id生成算法.

實現(xiàn)公式:

時間戳+主機編號+隨機因子結(jié)合這三部分就可以得到一個全局唯一的id.

1.7 foreign key 外鍵約束

MySQL外鍵是用來在兩個表之間建立連接,可以是一列或多列,一個表可以有多個外鍵.

外鍵是本表的一個字段, 不是本表的主鍵, 但對于另一個表的主鍵.
定義外鍵之后不允許刪除另一個表中具有關(guān)聯(lián)關(guān)系的行.
主表(父表): 相關(guān)聯(lián)字段中主鍵所在的表就是主表.
從表(子表): 相關(guān)聯(lián)字段中外鍵所在的表就是從表.

語法:

添加外鍵約束

(constraint 外鍵名) foreign key (字段名1),(字段名2..) reference 主表名(主鍵列1,主鍵列2..)

修改表時添加外鍵約束

alter table 表名 add (constraint 外鍵名) foreign key (列名) references 主表名(列名);

刪除外鍵約束

alter table 表名 drop forigen key (外鍵名);

示例:

MySQL表中, 沒有默認(rèn)對兩張有關(guān)聯(lián)的表提供檢查機制, 例如:下圖中插入非法班級, 不會報錯. 因此需要引入外鍵約束.

加入外鍵約束之后再插入非法數(shù)據(jù)就會報錯.

非法修改已插入的數(shù)據(jù)也會報錯.

Tips:

父表必須已存在數(shù)據(jù)庫中或是當(dāng)前正在創(chuàng)建的表.
父表中的主鍵不能出現(xiàn)空值, 但外鍵可以.所以通常用primary key來約束主鍵.
外鍵中列是數(shù)目和數(shù)據(jù)類型必須和父表的主鍵一一對應(yīng).
父表限制子表的插入,字表也約束父表的刪除.(互相制約)

父表中元素的刪除:

實際應(yīng)用中刪除父表中數(shù)據(jù)的方式通常是邏輯刪除. 當(dāng)一個商品下架之后, 為了讓客戶可以查看到歷史訂單, 所以不能直接刪除商品信息. 通常需要在建表之初預(yù)留商品下架的字段, 商品下架之后, 給商品下架的字段標(biāo)記賦值達(dá)到偽刪除的效果.

2.表的設(shè)計

基本思路:

先明確實體
再明確實體之間的聯(lián)系
根據(jù)上述內(nèi)容, 嵌套到下面的公式中, 就可做出表.

示例:

粗略設(shè)計一個學(xué)生教務(wù)管理系統(tǒng).

確定實體為: 學(xué)生, 課程, 學(xué)生賬號, 學(xué)生班級
學(xué)生與課程之間存在關(guān)系, 學(xué)生與賬號之間存在關(guān)系, 學(xué)生與班級之間存在關(guān)系.
學(xué)生與課程:多對多, 學(xué)生與賬號:一對一, 學(xué)生與班級:一對多.