**前言：**

在我們專案開發中，資料庫及表的設計可以說是非常重要，我遇到過很多庫表設計比較雜亂的專案，像表名、欄位名命名混亂、欄位型別設計混亂等等，此類資料庫後續極難維護與擴充。我一直相信只有優秀的庫表設計才能發揮出MySQL最大的效能，前面有篇文章也分享了資料庫的使用規範，本篇文章主要講幾個庫表設計的小技巧，希望對大家有所啟發。

#### 1.int型別的選用

整型欄位型別包含 `tinyint`、`smallint`、`mediumint`、`int`、`bigint` 五種，佔用空間大小及儲存範圍如下圖所示：

![image.png](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvMTE5NTM3LzE1ODg3MzIzMTM1OTUtYzQ2OWI5ZDgtNGI3NC00MDE5LWJhMTItYWY0N2NjYzMyZjY0LnBuZw?x-oss-process=image/format,png)

儲存位元組越小，佔用空間越小。所以本著最小化儲存的原則，我們要儘量選擇合適的整型，下面給出幾個常見案例及選擇建議。

- 根據儲存範圍選擇合適的型別，比如人的年齡用 unsigned tinyint（範圍 0~255，人的壽命不會超過 255 歲）；海龜就必須是smallint，但如果是太陽的年齡，就必須是int。
- 若儲存的資料為非負數值，建議使用 UNSIGNED 標識，可以擴大正數的儲存範圍。
- 短資料使用 TINYINT 或 SMALLINT，比如：人類年齡，城市程式碼。
- 儲存狀態變數的欄位用 TINYINT ，比如：是否刪除，0代表未刪除 1代表已刪除。
- 主鍵列，無負數，建議使用 INT UNSIGNED 或者 BIGINT UNSIGNED；預估欄位數字取值會超過 42 億，使用 BIGINT 型別。

下面給出建表語句示範：

```sql
CREATE TABLE  `tb_int` (
  `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `stu_age` tinyint unsigned NOT NULL COMMENT '學生年齡',
  `is_deleted` tinyint unsigned DEFAULT '0' COMMENT '0:未刪除 1:刪除',
  `col1` bigint NOT NULL COMMENT 'bigint欄位',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='int測試表';
```

#### 2.時間型別的選用

時間欄位型別可以選用datetime和timestamp，下面用一張表展示下二者的區別：

![image.png](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvMTE5NTM3LzE1ODg3NDkwOTE3OTctYjlmZTkwMGItZmI4ZC00ZGE5LWI1YzgtNTA2YjhjMGVkZDE0LnBuZw?x-oss-process=image/format,png)

timestamp翻譯為漢語即"時間戳"，它是當前時間到 Unix元年(1970 年 1 月 1 日 0 時 0 分 0 秒)的秒數，佔用4個位元組，而且是以UTC的格式儲存，它會自動檢索當前時區並進行轉換。datetime以8個位元組儲存，不會進行時區的檢索。也就是說，對於timestamp來說，如果儲存時的時區和檢索時的時區不一樣，那麼拿出來的資料也不一樣。對於datetime來說，存什麼拿到的就是什麼。下面給出幾個常見案例及選擇建議。

- 根據儲存範圍來選取，比如生產時間，保質期等時間建議選取datetime，因為datetime能儲存的範圍更廣。
- 記錄本行資料的插入時間和修改時間建議使用timestamp。
- 和時區相關的時間欄位選用timestamp。
- 如果只是想表示年、日期、時間的還可以使用 year、 date、 time，它們分別佔據 1、3、3 位元組，而datetime就是它們的集合。

如果timestamp欄位經常用於查詢，我們還可以使用MySQL內建的函式`FROM_UNIXTIME()`、`UNIX_TIMESTAMP()`，將日期和時間戳數字來回轉換，轉換後可以用 INT UNSIGNED 儲存時間，數字是連續的，佔用空間更小，並且可以使用索引提升查詢效能。下面給出示範建表語句及時間戳相關轉換SQL：

```sql
CREATE TABLE `tb_time` (
  `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
	`col1` datetime NOT NULL DEFAULT '2020-10-01 00:00:00' COMMENT '到期時間',
	`unix_createtime` int unsigned NOT NULL COMMENT '建立時間戳',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  PRIMARY KEY (`increment_id`),
  KEY `idx_unix_createtime` (`unix_createtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time測試表';

# 插入資料
insert into tb_time (unix_createtime,create_time) values 
(UNIX_TIMESTAMP(now()),now());

# 時間戳數字與時間相互轉換
select UNIX_TIMESTAMP('2020-05-06 00:00:00')
select FROM_UNIXTIME(1588694400)
```

#### 3.儲存IP值

IP值一般使用char或varchar進行儲存，但是當進行查詢和統計時，字元型別不是很高效。MySQL資料庫內建了兩個IP相關的函式`INET_ATON()`、`INET_NTOA()`，可以實現 IP 地址和整數型別的轉換。轉換後使用可以INT UNSIGNED 來儲存IP，轉換後的數字是連續的，提高了查詢效能，佔用空間更小。

```sql
CREATE TABLE `tb_ip` (
  `increment_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `name` varchar(100) NOT NULL COMMENT '姓名',
	`inet_ip` int(10) unsigned NOT NULL COMMENT 'IP',
  PRIMARY KEY (`increment_id`),
  KEY `idx_inet_ip` (`inet_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ip測試表';

# 插入資料
insert into `tb_ip` (`name`,`inet_ip`) values 
('wang',INET_ATON('192.168.0.1')),('lisi',INET_ATON('192.168.0.2'));

# 相互轉換
select INET_ATON('192.168.0.1');
select INET_NTOA(3232235521);
```

**總結：**

本篇文章分享了幾個庫表設計及欄位型別選取的建議。這些案例都是常常見到的場景，對於int型別及時間型別的選取，本文也根據常見場景給出相關建議，希望大家讀完這篇文章有所收穫。其實庫表設計是件複雜的事情，需要在專案前期多方人員共同規劃討論。還是那句話，只有優秀的庫表設計才能發揮出MySQL最大的效能。

![WeChat](https://img-blog.csdnimg.cn/20200410155158638.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdrdW5fag==,size_16,color_FFFFFF,t_70#pic_center)

MySQL庫表設計小技巧

相關文章