前言：

在我們項目開發(fā)中，數(shù)據(jù)庫及表的設計可以說是非常重要，我遇到過很多庫表設計比較雜亂的項目，像表名、字段名命名混亂、字段類型設計混亂等等，此類數(shù)據(jù)庫后續(xù)極難維護與拓展。我一直相信只有優(yōu)秀的庫表設計才能發(fā)揮出MySQL最大的性能，前面有篇文章也分享了數(shù)據(jù)庫的使用規(guī)范，本篇文章主要講幾個庫表設計的小技巧，希望對大家有所啟發(fā)。

1.int類型的選用

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

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

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

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

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

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

主鍵列，無負數(shù)，建議使用 INT UNSIGNED 或者 BIGINT UNSIGNED；預估字段數(shù)字取值會超過 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，下面用一張表展示下二者的區(qū)別：

timestamp翻譯為漢語即"時間戳"，它是當前時間到 Unix元年(1970 年 1 月 1 日 0 時 0 分 0 秒)的秒數(shù)，占用4個字節(jié)，而且是以UTC的格式儲存，它會自動檢索當前時區(qū)并進行轉換。datetime以8個字節(jié)儲存，不會進行時區(qū)的檢索。也就是說，對于timestamp來說，如果儲存時的時區(qū)和檢索時的時區(qū)不一樣，那么拿出來的數(shù)據(jù)也不一樣。對于datetime來說，存什么拿到的就是什么。下面給出幾個常見案例及選擇建議。

根據(jù)存儲范圍來選取，比如生產(chǎn)時間，保質期等時間建議選取datetime，因為datetime能存儲的范圍更廣。

記錄本行數(shù)據(jù)的插入時間和修改時間建議使用timestamp。

和時區(qū)相關的時間字段選用timestamp。

如果只是想表示年、日期、時間的還可以使用 year、 date、 time，它們分別占據(jù) 1、3、3 字節(jié)，而datetime就是它們的集合。

如果timestamp字段經(jīng)常用于查詢，我們還可以使用MySQL內置的函數(shù)FROM_UNIXTIME()、UNIX_TIMESTAMP()，將日期和時間戳數(shù)字來回轉換，轉換后可以用 INT UNSIGNED 存儲時間，數(shù)字是連續(xù)的，占用空間更小，并且可以使用索引提升查詢性能。下面給出示范建表語句及時間戳相關轉換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 '創(chuàng)建時間戳',

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',

`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測試表';

# 插入數(shù)據(jù)

insert into tb_time (unix_createtime,create_time) values

(UNIX_TIMESTAMP(now()),now());

# 時間戳數(shù)字與時間相互轉換

select UNIX_TIMESTAMP('2020-05-06 00:00:00')

select FROM_UNIXTIME(1588694400)

3.存儲IP值

IP值一般使用char或varchar進行存儲，但是當進行查找和統(tǒng)計時，字符類型不是很高效。MySQL數(shù)據(jù)庫內置了兩個IP相關的函數(shù)INET_ATON()、INET_NTOA()，可以實現(xiàn) IP 地址和整數(shù)類型的轉換。轉換后使用可以INT UNSIGNED 來存儲IP，轉換后的數(shù)字是連續(xù)的，提高了查詢性能，占用空間更小。

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測試表';

# 插入數(shù)據(jù)

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類型及時間類型的選取，本文也根據(jù)常見場景給出相關建議，希望大家讀完這篇文章有所收獲。其實庫表設計是件復雜的事情，需要在項目前期多方人員共同規(guī)劃討論。還是那句話，只有優(yōu)秀的庫表設計才能發(fā)揮出MySQL最大的性能。

總結

以上是生活随笔為你收集整理的mysql设计技巧_MySQL库表设计小技巧的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。