MySQL庫表設計的技巧有哪些

庫表設計的技巧有哪些，相信很多沒(méi)有經(jīng)驗的人對此束手無(wú)策，為此本文總結了問(wèn)題出現的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

1.int類(lèi)型的選用

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

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

• 根據存儲范圍選擇合適的類(lèi)型，比如人的年齡用 unsigned tinyint（范圍 0~255，人的壽命不會(huì )超過(guò) 255 歲）；海龜就必須是smallint，但如果是太陽(yáng)的年齡，就必須是int。

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

• 短數據使用 TINYINT 或 SMALLINT，比如：人類(lèi)年齡，城市代碼。

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

• 主鍵列，無(wú)負數，建議使用 INT UNSIGNED 或者 BIGINT UNSIGNED；預估字段數字取值會(huì )超過(guò) 42 億，使用 BIGINT 類(lèi)型。

下面給出建表語(yǔ)句示范：

CREATE TABLE  `tb_int` (
  `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `stu_age` tinyint unsigned NOT NULL COMMENT '學(xué)生年齡',
  `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.時(shí)間類(lèi)型的選用

時(shí)間字段類(lèi)型可以選用datetime和timestamp，下面用一張表展示下二者的區別：

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

• 根據存儲范圍來(lái)選取，比如生產(chǎn)時(shí)間，保質(zhì)期等時(shí)間建議選取datetime，因為datetime能存儲的范圍更廣。

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

• 和時(shí)區相關(guān)的時(shí)間字段選用timestamp。

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

如果timestamp字段經(jīng)常用于查詢(xún)，我們還可以使用MySQL內置的函數FROM_UNIXTIME()、UNIX_TIMESTAMP()，將日期和時(shí)間戳數字來(lái)回轉換，轉換后可以用 INT UNSIGNED 存儲時(shí)間，數字是連續的，占用空間更小，并且可以使用索引提升查詢(xún)性能。下面給出示范建表語(yǔ)句及時(shí)間戳相關(guān)轉換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 '到期時(shí)間',
    `unix_createtime` int unsigned NOT NULL COMMENT '創(chuàng  )建時(shí)間戳',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng  )建時(shí)間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
  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());
# 時(shí)間戳數字與時(shí)間相互轉換
select UNIX_TIMESTAMP('2020-05-06 00:00:00')
select FROM_UNIXTIME(1588694400)

3.存儲IP值

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

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