腳本之家服務器常用軟件

快捷導航

MySQL字段定義時的屬性設置方式

更新時間：2024年04月10日 10:42:45 作者：嘩嘩的世界

這篇文章主要介紹了MySQL字段定義時的屬性設置方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

默認值

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

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

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 字段的值，也沒有設置默認值。

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è)務需求來設置默認值，比如說，我們在創(chuàng)建用戶表的時候，就不應該為 name 字段設置默認值，因為這樣的話，如果用戶沒有填寫名字，MySQL 就會默認填充“張三”，這顯然是不合理的。

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

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

是否允許為空

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

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

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

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

主鍵

主鍵（PRIMARY KEY）是用來唯一標識一條記錄的，一個表中只能有一個主鍵，主鍵的值不能重復，也不能為 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;

第二種方式在復合主鍵（由兩個或更多的字段組合而成）的時候會更加方便，比如說，我們要為學生課程表設置復合主鍵，就可以這樣定義。

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)。

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

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

所以在實際開發(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ù)庫層面就對字段的值進行了唯一性約束，雖然如果一個字段不允許重復的話，在 Java 程序中也會先進行校驗。

當然，也可以對過個字段進行唯一性約束，語法和復合主鍵類似，用 UNIQUE 關(guān)鍵字后面跟上括號內(nèi)的多個字段名來實現(xiàn)。比如說，我們要為學生課程表設置復合唯一性約束，就可以這樣定義。

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 字段設置唯一性約束，就可以這樣定義。

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ù)檢索的效率，因為它會為對應的列創(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ù)庫設計時使用 UNIQUE 約束時，這個約束確保了所有的非 NULL 值在該列中是唯一的，但是對于 NULL 值的處理則有所不同，因為 NULL 與任何其他值（包括另一個 NULL）都不相等。

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

外鍵

外鍵（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 會自動為該字段的值填充前導零，直到達到該字段定義的寬度。這個屬性常常與數(shù)值類型的字段一起使用，以確保顯示的數(shù)值具有固定的寬度，這對于報表和數(shù)據(jù)展示的格式化非常有用。

特別注意：

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

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