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

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

比如說，我們在定義字段的時候添加了默認值，那在插入數(shù)據(jù)的時候，如果我們沒有主動指定這個字段的值（比如 Java 程序中），數(shù)據(jù)庫就會使用默認值幫我們自動填充。

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

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

默認值

默認值（DEFAULT）是指在插入數(shù)據(jù)的時候，如果沒有指定這個字段的值，那就會使用默認值。

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

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ù)的時候，如果沒有指定 name、age、create_time 字段的值，那就會使用默認值。

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

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

• DEFAULT '張三'：指定了 name 字段的默認值為“張三”。
• DEFAULT 18：指定了 age 字段的默認值為 18。
• DEFAULT CURRENT_TIMESTAMP：指定了 create_time 字段的默認值為當前時間。

那假如我們沒有指定默認值，又沒有主動插入數(shù)據(jù)，那這個字段的值會是什么呢？

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ù)的時候，我們沒有指定 name、age、create_time 字段的值，也沒有設(shè)置默認值。

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

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

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

當然了，DEFAULT 也不能亂用，要根據(jù)業(yè)務(wù)需求來設(shè)置默認值，比如說，我們在創(chuàng)建用戶表的時候，就不應(yīng)該為 name 字段設(shè)置默認值，因為這樣的話，如果用戶沒有填寫名字，MySQL 就會默認填充“張三”，這顯然是不合理的。

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

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

是否允許為空

有時候，我們會希望某個字段的值不能為空，比如說，用戶名、手機號、郵箱等，這些字段的值都是必填的。

那我們在創(chuàng)建表的時候，就會明確指定這些字段是 NOT NULL 的。

這樣在插入數(shù)據(jù)的時候，如果我們沒有指定 name、mobile、email 字段的值，那 MySQL 就會報錯。

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

主鍵

主鍵（PRIMARY KEY）是用來唯一標識一條記錄的，一個表中只能有一個主鍵，主鍵的值不能重復(fù)，也不能為 NULL。

主鍵的指定方式有兩種，一種是在字段定義的時候直接跟上 PRIMARY KEY，另一種是在所有字段定義完成后，再通過 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ù)合主鍵（由兩個或更多的字段組合而成）的時候會更加方便，比如說，我們要為學(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;

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

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

的時候，因為需要處理復(fù)合主鍵的多個字段，也會使 SQL 查詢語句變得復(fù)雜。

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

自增

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

一般用于類型為整型的主鍵字段，比如說 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;

我們來插入幾條數(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ù)的時候，id 都會在以前的最大值上加 1。

注釋

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

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

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)建時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

這樣的話，我們在查看表結(jié)構(gòu)的時候，就可以看到每個字段的注釋了。

SHOW FULL COLUMNS FROM `user`;

注釋的作用是讓其他人更容易理解這個字段的含義，沒啥好說的。

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;

這樣的話，我們在插入數(shù)據(jù)的時候，如果 mobile 的值已經(jīng)存在，MySQL 就會報錯。

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

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

當然，也可以對過個字段進行唯一性約束，語法和復(fù)合主鍵類似，用 UNIQUE 關(guān)鍵字后面跟上括號內(nèi)的多個字段名來實現(xiàn)。比如說，我們要為學(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 約束指定別名，比如說，我們 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īng)的列創(chuàng)建一個唯一索引。

可以通過 SHOW INDEX FROM 表名 來查看索引信息。查看 Non_unique 列的值，可以確認是否為唯一索引（0 表示唯一，1 表示非唯一）

SHOW INDEX FROM `user` \G

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

不過，與 PRIMARY KEY 不同，UNIQUE 約束允許有 NULL 值。我們來測試一下：

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

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

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

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

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

外鍵

外鍵（FOREIGN KEY）是用來建立兩個表之間的關(guān)聯(lián)關(guā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");

這樣的話，我們在插入訂單的時候，如果 user_id 指向的用戶不存在，MySQL 就會報錯。

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

可以看到，user_id 為 2 的訂單插入失敗了，因為 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ù)的完整性，比如說，我們在刪除用戶的時候，如果用戶有訂單，就不允許刪除。

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

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

ZEROFILL

ZEROFILL 是指在插入數(shù)據(jù)的時候，如果字段的值小于字段的長度，MySQL 就會在字段的值前面填充 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;

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

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

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

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

特別注意：

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

OK，我們通過 show columns from user like 'money'; 來查看一下字段的屬性。

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

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

總結(jié)

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

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

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

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

最新評論