MySQL庫表設計的技巧有哪些

155次閱讀

沒有評論

共計 2792 個字符，預計需要花費 7 分鐘才能閱讀完成。

MySQL 庫表設計的技巧有哪些，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

1.int 類型的選用

整型字段類型包含
tinyint、smallint、mediumint、int、bigint 五種，占用空間大小及存儲范圍如下圖所示：

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

根據存儲范圍選擇合適的類型，比如人的年齡用 unsigned tinyint（范圍 0~255，人的壽命不會超過 255 歲）；海龜就必須是 smallint，但如果是太陽的年齡，就必須是 int。

若存儲的數據為非負數值，建議使用 UNSIGNED 標識，可以擴大正數的存儲范圍。

短數據使用 TINYINT 或 SMALLINT，比如：人類年齡，城市代碼。

存儲狀態變量的字段用 TINYINT，比如：是否刪除，0 代表未刪除 1 代表已刪除。

主鍵列，無負數，建議使用 INT UNSIGNED 或者 BIGINT UNSIGNED；預估字段數字取值會超過 42 億，使用 BIGINT 類型。

下面給出建表語句示范：

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，下面用一張表展示下二者的區別：

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：

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，轉換后的數字是連續的，提高了查詢性能，占用空間更小。

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

看完上述內容，你們掌握 MySQL 庫表設計的技巧有哪些的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

正文完