開發(fā)的時(shí)候第一步就是建表，在創(chuàng)建表的時(shí)候，我們需要定義表的字段，每個(gè)字段都有一些屬性，比如說(shuō)是否為空，是否允許有默認(rèn)值，是不是逐漸等。

這些約束字段的屬性，可以讓字段的值更符合我們的預(yù)期，也會(huì)為以后的數(shù)據(jù)查詢和更新提供便利。

比如說(shuō)，我們?cè)诙x字段的時(shí)候添加了默認(rèn)值，那在插入數(shù)據(jù)的時(shí)候，如果我們沒(méi)有主動(dòng)指定這個(gè)字段的值（比如 Java 程序中），數(shù)據(jù)庫(kù)就會(huì)使用默認(rèn)值幫我們自動(dòng)填充。

像在技術(shù)派項(xiàng)目中的文章詳情表，我們?yōu)?id 字段設(shè)置了 NOT NULL、AUTO_INCREMENT、COMMENT 等屬性。

那接下來(lái)，就來(lái)一起看看 MySQL 字段的常用屬性都有哪些吧。

默認(rèn)值

默認(rèn)值（DEFAULT）是指在插入數(shù)據(jù)的時(shí)候，如果沒(méi)有指定這個(gè)字段的值，那就會(huì)使用默認(rèn)值。

我們創(chuàng)建這樣一張表，包含了 varchar、int、datetime 等字段類型，每個(gè)字段都設(shè)置了默認(rèn)值。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '張三',
  `age` int(11) DEFAULT 18,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在插入數(shù)據(jù)的時(shí)候，如果沒(méi)有指定 name、age、create_time 字段的值，那就會(huì)使用默認(rèn)值。

INSERT INTO `user` (`id`) VALUES (1);

可以看到，插入數(shù)據(jù)的時(shí)候，我們只指定了 id 字段的值，其他字段都省略了，但 MySQL 自動(dòng)幫我們填充了默認(rèn)值。

• DEFAULT '張三'：指定了 name 字段的默認(rèn)值為“張三”。
• DEFAULT 18：指定了 age 字段的默認(rèn)值為 18。
• DEFAULT CURRENT_TIMESTAMP：指定了 create_time 字段的默認(rèn)值為當(dāng)前時(shí)間。

那假如我們沒(méi)有指定默認(rèn)值，又沒(méi)有主動(dòng)插入數(shù)據(jù)，那這個(gè)字段的值會(huì)是什么呢？

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `age` int(11),
  `create_time` datetime,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在插入數(shù)據(jù)的時(shí)候，我們沒(méi)有指定 name、age、create_time 字段的值，也沒(méi)有設(shè)置默認(rèn)值。

INSERT INTO `user` (`id`) VALUES (1);

可以看到，此時(shí)，MySQL 幫我們填充的值是 NULL。

這就是為什么阿里巴巴開發(fā)規(guī)約要求我們，在POJO中，要使用包裝類型，而不是基本數(shù)據(jù)類型，因?yàn)閿?shù)據(jù)庫(kù)的查詢結(jié)構(gòu)可能是 null，如果使用基本數(shù)據(jù)類型的畫，因?yàn)橐詣?dòng)拆箱，會(huì)拋出NPE異常。

當(dāng)然了，DEFAULT 也不能亂用，要根據(jù)業(yè)務(wù)需求來(lái)設(shè)置默認(rèn)值，比如說(shuō)，我們?cè)趧?chuàng)建用戶表的時(shí)候，就不應(yīng)該為 name 字段設(shè)置默認(rèn)值，因?yàn)檫@樣的話，如果用戶沒(méi)有填寫名字，MySQL 就會(huì)默認(rèn)填充“張三”，這顯然是不合理的。

我們要盡早提示用戶填寫名字，而不是用默認(rèn)值填充。

但對(duì)于 create_time 字段，我們就可以設(shè)置默認(rèn)值為 CURRENT_TIMESTAMP，這樣的話，MySQL 就會(huì)自動(dòng)幫我們填充當(dāng)前時(shí)間，Java 程序就不需要在插入數(shù)據(jù)的時(shí)候，手動(dòng)填充時(shí)間了。

是否允許為空

有時(shí)候，我們會(huì)希望某個(gè)字段的值不能為空，比如說(shuō)，用戶名、手機(jī)號(hào)、郵箱等，這些字段的值都是必填的。

那我們?cè)趧?chuàng)建表的時(shí)候，就會(huì)明確指定這些字段是 NOT NULL 的。

這樣在插入數(shù)據(jù)的時(shí)候，如果我們沒(méi)有指定 name、mobile、email 字段的值，那 MySQL 就會(huì)報(bào)錯(cuò)。

雖然我們也會(huì)在 Java 程序中對(duì)這些字段進(jìn)行校驗(yàn)，但在數(shù)據(jù)庫(kù)層面，也要對(duì)字段的值進(jìn)行約束，這樣可以更好地保證數(shù)據(jù)的完整性。

主鍵

主鍵（PRIMARY KEY）是用來(lái)唯一標(biāo)識(shí)一條記錄的，一個(gè)表中只能有一個(gè)主鍵，主鍵的值不能重復(fù)，也不能為 NULL。

主鍵的指定方式有兩種，一種是在字段定義的時(shí)候直接跟上 PRIMARY KEY，另一種是在所有字段定義完成后，再通過(guò) PRIMARY KEY（字段）這種方式指定。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

或者

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第二種方式在復(fù)合主鍵（由兩個(gè)或更多的字段組合而成）的時(shí)候會(huì)更加方便，比如說(shuō)，我們要為學(xué)生課程表設(shè)置復(fù)合主鍵，就可以這樣定義。

CREATE TABLE `student_course` (
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  created_time datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通過(guò) PRIMARY KEY 關(guān)鍵字后面跟上括號(hào)內(nèi)的多個(gè)字段名來(lái)實(shí)現(xiàn)。

不過(guò)，復(fù)合主鍵會(huì)創(chuàng)建更復(fù)雜的索引，可能會(huì)對(duì)插入、更新、刪除等操作的性能產(chǎn)生影響，另外，在執(zhí)行聯(lián)合查詢

的時(shí)候，因?yàn)樾枰幚韽?fù)合主鍵的多個(gè)字段，也會(huì)使 SQL 查詢語(yǔ)句變得復(fù)雜。

所以在實(shí)際開發(fā)中，復(fù)合主鍵的使用頻率并不高。

自增

自增（AUTO_INCREMENT）是指在插入數(shù)據(jù)的時(shí)候，如果沒(méi)有指定這個(gè)字段的值，那 MySQL 就會(huì)自動(dòng)幫我們填充一個(gè)遞增的值。

一般用于類型為整型的主鍵字段，比如說(shuō) int 或 bigint。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我們來(lái)插入幾條數(shù)據(jù)，看看 id 字段的值是怎么填充的。

INSERT INTO `user` (`name`) VALUES ('張三');
INSERT INTO `user` (`name`) VALUES ('張四');
INSERT INTO `user` (`name`) VALUES ('張五');

再刪除一條數(shù)據(jù)后插入：

DELETE FROM `user` WHERE `id` = 2;
INSERT INTO `user` (`name`) VALUES ('張六');

可以看到，每次插入數(shù)據(jù)的時(shí)候，id 都會(huì)在以前的最大值上加 1。

注釋

注釋（COMMENT）是指在字段定義的時(shí)候，可以添加一些描述性的文字，和 Java 中中的雙斜杠注釋類似。

語(yǔ)法也非常簡(jiǎn)單，就是在字段定義的后面跟上 COMMENT '注釋內(nèi)容'（建議單引號(hào)）。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年齡',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

這樣的話，我們?cè)诓榭幢斫Y(jié)構(gòu)的時(shí)候，就可以看到每個(gè)字段的注釋了。

SHOW FULL COLUMNS FROM `user`;

注釋的作用是讓其他人更容易理解這個(gè)字段的含義，沒(méi)啥好說(shuō)的。

UNIQUE

UNIQUE可以確保一列或幾列的組合值在整張表中是唯一的。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `mobile` varchar(11) UNIQUE,
  `email` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

這樣的話，我們?cè)诓迦霐?shù)據(jù)的時(shí)候，如果 mobile 的值已經(jīng)存在，MySQL 就會(huì)報(bào)錯(cuò)。

INSERT INTO `user` (`name`, `mobile`, `email`) VALUES ('張三', '12345678901', '982323232@qq.com');
INSERT INTO `user` (`name`, `mobile`, `email`) VALUES ('張四', '12345678901', 'www.huahua@169.com');

等于說(shuō)在數(shù)據(jù)庫(kù)層面就對(duì)字段的值進(jìn)行了唯一性約束，雖然如果一個(gè)字段不允許重復(fù)的話，在 Java 程序中也會(huì)先進(jìn)行校驗(yàn)。

當(dāng)然，也可以對(duì)過(guò)個(gè)字段進(jìn)行唯一性約束，語(yǔ)法和復(fù)合主鍵類似，用 UNIQUE 關(guān)鍵字后面跟上括號(hào)內(nèi)的多個(gè)字段名來(lái)實(shí)現(xiàn)。比如說(shuō)，我們要為學(xué)生課程表設(shè)置復(fù)合唯一性約束，就可以這樣定義。

CREATE TABLE `student_course` (
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  created_time datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (`student_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

也可以為 UNIQUE 約束指定別名，比如說(shuō)，我們 mobile 字段設(shè)置唯一性約束，就可以這樣定義。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `mobile` varchar(11),
  `email` varchar(255),
  PRIMARY KEY (`id`),
  UNIQUE `mobile_unique` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

UNIQUE 約束既可以保證數(shù)據(jù)的唯一性，也可以提高數(shù)據(jù)檢索的效率，因?yàn)樗鼤?huì)為對(duì)應(yīng)的列創(chuàng)建一個(gè)唯一索引。

可以通過(guò) SHOW INDEX FROM 表名 來(lái)查看索引信息。查看 Non_unique 列的值，可以確認(rèn)是否為唯一索引（0 表示唯一，1 表示非唯一）

SHOW INDEX FROM `user` \G

的確，我們可以看到 mobile 字段的 Non_unique 是 0，也就是唯一索引，索引類型是 BTREE，這是一種高效的索引結(jié)構(gòu)。

不過(guò)，與 PRIMARY KEY 不同，UNIQUE 約束允許有 NULL 值。我們來(lái)測(cè)試一下：

INSERT INTO `user` (`name`,`email`) VALUES ('張三', '234536076@qq.com');
INSERT INTO `user` (`name`,`email`) VALUES ('張三', '234536076@qq.com');

我們來(lái)看一下結(jié)果，果然允許 NULL 值。

既然 UNIQUE 約束是用來(lái)保證數(shù)據(jù)的唯一性的，為什么允許有多個(gè) NULL 值呢？

主要與 NULL 值在 SQL 中的特殊含義有關(guān)。在 SQL 中，NULL 代表一個(gè)未知值或不存在的值。當(dāng)我們對(duì)數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)使用 UNIQUE 約束時(shí)，這個(gè)約束確保了所有的非 NULL 值在該列中是唯一的，但是對(duì)于 NULL 值的處理則有所不同，因?yàn)?NULL 與任何其他值（包括另一個(gè) NULL）都不相等。

在技術(shù)派中的文章詳情表，我們?yōu)?article_id 和 version 字段設(shè)置了唯一性約束，這樣的話，就可以保證每篇文章的每個(gè)版本都是唯一的。

外鍵

外鍵（FOREIGN KEY）是用來(lái)建立兩個(gè)表之間的關(guān)聯(lián)關(guān)系的，它指向另一張表的主鍵。

下面是一個(gè)簡(jiǎn)單的例子，我們創(chuàng)建了兩張表，一張是用戶表，一張是訂單表，用戶表的 id 字段是主鍵，訂單表的 user_id 字段是外鍵，指向用戶表的 id 字段。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `mobile` varchar(11) UNIQUE,
  `email` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
CREATE TABLE `order1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_no` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我們先在 user 中插入一條數(shù)據(jù)：

 insert into `user` (`name` , `mobile` ,`email`) values ("張三" , "19203004404" ,"1213232@qq.com");

這樣的話，我們?cè)诓迦胗唵蔚臅r(shí)候，如果 user_id 指向的用戶不存在，MySQL 就會(huì)報(bào)錯(cuò)。

INSERT INTO `order1` (`user_id`, `order_no`) VALUES (1, '2024020801');
INSERT INTO `order1` (`user_id`, `order_no`) VALUES (2, '2024020802');

可以看到，user_id 為 2 的訂單插入失敗了，因?yàn)?user_id 為 2 的用戶不存在。

Cannot add or update a child row: a foreign key constraint fails (itwanger.order, CONSTRAINT order_ibfk_1 FOREIGN KEY (user_id) REFERENCES user (id))

外鍵約束可以確保數(shù)據(jù)的完整性，比如說(shuō)，我們?cè)趧h除用戶的時(shí)候，如果用戶有訂單，就不允許刪除。

DELETE FROM `user` WHERE `id` = 1;

外鍵是 MySQL 中不可或缺的一部分，它通過(guò)確保表之間的數(shù)據(jù)引用完整性，幫助構(gòu)建結(jié)構(gòu)化和組織良好的數(shù)據(jù)庫(kù)模式。正確使用外鍵不僅可以保證數(shù)據(jù)的一致性和準(zhǔn)確性，還可以提高數(shù)據(jù)庫(kù)操作的效率。

ZEROFILL

ZEROFILL 是指在插入數(shù)據(jù)的時(shí)候，如果字段的值小于字段的長(zhǎng)度，MySQL 就會(huì)在字段的值前面填充 0。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `money` int(11) ZEROFILL,
  `father_money` int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我們來(lái)插入一條數(shù)據(jù)，看看 id 字段的值是怎么填充的。

INSERT INTO `user` (`money`, `father_money`) VALUES (1, 1);

可以看到，money 字段的值是 00000000001，而 father_money 字段的值是 1。

當(dāng)一個(gè)字段被定義為 ZEROFILL 時(shí)，MySQL 會(huì)自動(dòng)為該字段的值填充前導(dǎo)零，直到達(dá)到該字段定義的寬度。這個(gè)屬性常常與數(shù)值類型的字段一起使用，以確保顯示的數(shù)值具有固定的寬度，這對(duì)于報(bào)表和數(shù)據(jù)展示的格式化非常有用。

特別注意：

ZEROFILL 屬性僅影響數(shù)值的顯示方式，并不改變存儲(chǔ)在數(shù)據(jù)庫(kù)中的實(shí)際值。例如，無(wú)論是否使用 ZEROFILL，數(shù)值 123 都存儲(chǔ)為 123，只是顯示時(shí)可能會(huì)不同。ZEROFILL 填充的零只是為了達(dá)到字段定義的顯示寬度，它并不影響字段的存儲(chǔ)范圍或存儲(chǔ)大小。當(dāng)字段被定義為 ZEROFILL 時(shí)，MySQL 也會(huì)自動(dòng)將其標(biāo)記為 UNSIGNED。這是因?yàn)榍皩?dǎo)零填充通常只對(duì)正數(shù)有意義。

OK，我們通過(guò) show columns from user like 'money'; 來(lái)查看一下字段的屬性。

可以看到，money 字段的屬性中，有 ZEROFILL 和 UNSIGNED。

除了通過(guò)這種方式，也可以通過(guò) desc table_name 來(lái)查看表的結(jié)構(gòu)。

總結(jié)

字段的屬性設(shè)置是 MySQL 表設(shè)計(jì)中的重要一環(huán)，掌握它們是非常有必要的。這次我們依次講了默認(rèn)值、是否允許為空、主鍵、自增、注釋、唯一性約束、外鍵、ZEROFILL 等屬性。

這里溫馨提示一點(diǎn)，盡量不要使用 MySQL 的關(guān)鍵字，盡管我們可以通過(guò)反引號(hào)（`）來(lái)避免關(guān)鍵字沖突，但這樣會(huì)使 SQL 語(yǔ)句變得復(fù)雜，不利于維護(hù)。

尤其是一些關(guān)鍵字和 Java 當(dāng)中的關(guān)鍵字重合時(shí)，很容易出現(xiàn)意料之外的錯(cuò)誤。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論