到底怎樣才能創(chuàng)建一張規(guī)范的MySQL表?

為什么必定需要有無(wú)業(yè)務(wù)意義的主鍵,并且還需要自增?

應(yīng)該在哪些字段添加索引?

或許有些人會(huì)有以上類(lèi)似的疑問(wèn).這幾天通過(guò)本身的整理加上好友的指導(dǎo),整理了一份稍微規(guī)范一點(diǎn)的建表語(yǔ)句.

如下：

• 規(guī)范表示例

MySQL 5.5

CREATE TABLE student_info (

`id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',

`stu_name` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '姓名',

`stu_class` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '班級(jí)',

`stu_num` INT (11) NOT NULL DEFAULT '0' COMMENT '學(xué)號(hào)',

`stu_score` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '總分',

`tuition` DECIMAL (5, 2) NOT NULL DEFAULT '0' COMMENT '學(xué)費(fèi)',

`phone_number` VARCHAR (20) NOT NULL DEFAULT '0' COMMENT '電話(huà)號(hào)碼',

`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時(shí)間',

`update_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '記錄更新時(shí)間',

`status` TINYINT NOT NULL DEFAULT '1' COMMENT '1代表記錄有效,0代表記錄無(wú)效',

PRIMARY KEY (`id`),

UNIQUE KEY uniq_stu_num (`stu_num`),

KEY idx_stu_score (`stu_score`),

KEY idx_update_time_tuition (`update_time`, `tuition`)

) ENGINE = INNODB charset = utf8mb4 COMMENT '學(xué)生信息表';

MySQL 5.6及以上版本

CREATE TABLE student_info (

`id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',

`stu_name` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '姓名',

`stu_class` VARCHAR (10) NOT NULL DEFAULT '' COMMENT '班級(jí)',

`stu_num` INT (11) NOT NULL DEFAULT '0' COMMENT '學(xué)號(hào)',

`stu_score` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '總分',

`tuition` DECIMAL (5, 2) NOT NULL DEFAULT '0' COMMENT '學(xué)費(fèi)',

`phone_number` VARCHAR (20) NOT NULL DEFAULT '0' COMMENT '電話(huà)號(hào)碼',

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時(shí)間',

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄更新時(shí)間',

`status` TINYINT NOT NULL DEFAULT '1' COMMENT '1代表記錄有效,0代表記錄無(wú)效',

PRIMARY KEY (`id`),

UNIQUE KEY uniq_stu_num (`stu_num`),

KEY idx_stu_score (`stu_score`),

KEY idx_update_time_tuition (`update_time`, `tuition`)

) ENGINE = INNODB charset = utf8mb4 COMMENT '學(xué)生信息表';

• 解釋

1、表的命名：使用有意義的英文詞匯,詞匯中間以下劃線(xiàn)分割,全部采用小寫(xiě)；如示例表表名student_info

2、表必需有無(wú)符號(hào)int型自增主鍵,對(duì)應(yīng)示例表中id字段.

必需得有主鍵的原因：

采用RBR模式復(fù)制,無(wú)主鍵的表刪除,會(huì)導(dǎo)致備庫(kù)夯住

使用自增的原因：

數(shù)據(jù)寫(xiě)入可以提高插入性能,避免page分裂,減少表碎片.

3、必需把字段定義為NOT NULL并且提供默認(rèn)值

原因：

a.null的列使索引、統(tǒng)計(jì)都更加復(fù)雜,使優(yōu)化更加困難

b.NULL并不是空值,也會(huì)占用空間,所以在MySQL進(jìn)行比擬時(shí),NULL會(huì)參與字段比擬,所以對(duì)效率有一部分影響

4、所有表、字段都應(yīng)該有 comment ,來(lái)描述表、字段所代表的含義,便利同事查看.

5、能用SMALLINT或者tinyint的情況就不用int,如字段 stu_score就使用的是SMALLINT

原因：使用SMALLINT或者tinyint能節(jié)約存儲(chǔ)空間

6、涉及到錢(qián)的字段建議使用DECIMAL,如示例表字段tuition

7、電話(huà)號(hào)碼建議使用varchar(20),如示例表字段phone_number

原因：

a.涉及到區(qū)號(hào)或者國(guó)家代號(hào),可能出現(xiàn)+-()

b.不會(huì)有誰(shuí)用手機(jī)號(hào)做運(yùn)算吧

c.varchar可以支持模糊查詢(xún)

8、表建議增加create_time和update_time,以記錄某條數(shù)據(jù)的創(chuàng)建時(shí)間和修改時(shí)間.

注意：這里5.5和5.6有區(qū)別,5.5使用的是TIMESTAMP,并且5.5不支持多個(gè)CURRENT_TIMESTAMP 默認(rèn)值,因此如上示例設(shè)計(jì)；5.6版本使用了datetime,因?yàn)閐atetime支持的范圍更廣(范圍為：'1000-01-01 00:00:00'到'9999-12-31 23:59:59'),并且create_time和update_time兩個(gè)字段都設(shè)置了CURRENT_TIMESTAMP(從5.6.5開(kāi)始支持多個(gè)字段默認(rèn)值設(shè)置為CURRENT_TIMESTAMP)

原因：增加這兩個(gè)字段便利統(tǒng)計(jì)和歸檔.

9、表建議包括一個(gè)狀態(tài)標(biāo)記字段,來(lái)標(biāo)識(shí)數(shù)據(jù)是否被刪除,而不使用物理刪除；比如示例表字段status.

10、不建議使用ENUM,使用TINYINT來(lái)代替；如示例表字段status使用的是TINYINT類(lèi)型.

原因：增加新的ENUM值要做DDL操作

11、使用唯一索引約束字段值唯一的數(shù)據(jù),唯一索引以u(píng)niq_字段名方式命名；如示例表中的uniq_stu_num

12、在經(jīng)常作為查詢(xún)條件的字段上添加索引,普通索引以idx_字段名方式命名；如示例表中的idx_stu_score

13、經(jīng)常同時(shí)出現(xiàn)在where條件中的幾個(gè)字段可以放在聯(lián)合索引中；如idx_update_time_tuition；必要注意的是應(yīng)該把選擇性更大的列放在聯(lián)合索引的最左邊.

14、盡量不使用TEXT、BLOB類(lèi)型

原因：會(huì)浪費(fèi)更多的磁盤(pán)和內(nèi)存空間,非需要的大量的大字段查詢(xún)會(huì)淘汰掉熱數(shù)據(jù),導(dǎo)致內(nèi)存命中率急劇降低,影響數(shù)據(jù)庫(kù)性能

15、建議使用innodb存儲(chǔ)引擎

原因：innodb支持事務(wù),是行級(jí)鎖,并發(fā)性能更好、CPU及內(nèi)存緩存頁(yè)優(yōu)化使得資源利用率更高.

16、建議使用utf8mb4字符集

原因：萬(wàn)國(guó)碼,無(wú)亂碼風(fēng)險(xiǎn)；與utf8編碼相比,能支持Emoji臉色.