本文源碼：GitHub·點(diǎn)這里 || GitEE·點(diǎn)這里

創(chuàng)新互聯(lián)長(zhǎng)期為上1000家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為烏魯木齊企業(yè)提供專業(yè)的成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作，烏魯木齊網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

一、數(shù)據(jù)場(chǎng)景

1、表結(jié)構(gòu)簡(jiǎn)介

任何工具類的東西都是為了解決某個(gè)場(chǎng)景下的問(wèn)題，比如redis緩存系統(tǒng)熱點(diǎn)數(shù)據(jù)，ClickHouse解決海量數(shù)據(jù)的實(shí)時(shí)分析，MySQL關(guān)系型數(shù)據(jù)庫(kù)存儲(chǔ)結(jié)構(gòu)化數(shù)據(jù)。數(shù)據(jù)的存儲(chǔ)則需要設(shè)計(jì)對(duì)應(yīng)的表結(jié)構(gòu)，清楚的表結(jié)構(gòu)，有助于快速開(kāi)發(fā)業(yè)務(wù)，和理解系統(tǒng)。表結(jié)構(gòu)的設(shè)計(jì)通常從下面幾個(gè)方面考慮：業(yè)務(wù)場(chǎng)景、設(shè)計(jì)規(guī)范、表結(jié)構(gòu)、字段屬性、數(shù)據(jù)管理。

2、用戶場(chǎng)景

例如存儲(chǔ)用戶基礎(chǔ)信息數(shù)據(jù)，通常都會(huì)下面幾個(gè)相關(guān)表結(jié)構(gòu)：用戶信息表、單點(diǎn)登錄表、狀態(tài)管理表、支付賬戶表等。

• 用戶信息表

存儲(chǔ)用戶三要素相關(guān)信息：姓名，手機(jī)號(hào)，身份證，登錄密碼，郵箱等。

CREATE TABLE `ms_user_center` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶ID',
  `user_name` varchar(20) NOT NULL COMMENT '用戶名',
  `real_name` varchar(20) DEFAULT NULL COMMENT '真實(shí)姓名',
  `pass_word` varchar(32) NOT NULL COMMENT '密碼',
  `phone` varchar(20) NOT NULL COMMENT '手機(jī)號(hào)',
  `email` varchar(32) DEFAULT NULL COMMENT '郵箱',
  `head_url` varchar(100) DEFAULT NULL COMMENT '用戶頭像URL',
  `card_id` varchar(32) DEFAULT NULL COMMENT '身份證號(hào)',
  `user_sex` int(1) DEFAULT '1' COMMENT '用戶性別:0-女,1-男',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶表';

• 單點(diǎn)登錄表

用意是在多個(gè)業(yè)務(wù)系統(tǒng)中，用戶登錄一次就可以訪問(wèn)所有相互信任的業(yè)務(wù)子系統(tǒng)，是聚合業(yè)務(wù)平臺(tái)常用的解決方案。

CREATE TABLE `ms_user_sso` (
  `user_id` int(11) NOT NULL COMMENT '用戶ID',
  `sso_id` varchar(32) NOT NULL COMMENT '單點(diǎn)信息編號(hào)ID',
  `sso_code` varchar(32) NOT NULL COMMENT '單點(diǎn)登錄碼,唯一核心標(biāo)識(shí)',
  `log_ip` varchar(32) DEFAULT NULL COMMENT '登錄IP地址',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶單點(diǎn)登錄表';

• 狀態(tài)管理表

系統(tǒng)用戶在使用時(shí)候可能出現(xiàn)多個(gè)狀態(tài)，例如賬戶凍結(jié)、密碼鎖定等，把狀態(tài)聚合到一起，可以更加方便的管理和驗(yàn)證。

CREATE TABLE `ms_user_status` (
  `user_id` int(11) NOT NULL COMMENT '用戶ID',
  `account_status` int(1) DEFAULT '1' COMMENT '賬戶狀態(tài)：0-凍結(jié),1-未凍結(jié)',
  `real_name_status` int(1) DEFAULT '0' COMMENT '實(shí)名認(rèn)證狀態(tài)：0-未實(shí)名,1-已實(shí)名',
  `pay_pass_status` int(1) DEFAULT '0' COMMENT '支付密碼是否設(shè)置：0-未設(shè)置,1-設(shè)置',
  `wallet_pass_status` int(1) DEFAULT '0' COMMENT '錢(qián)包密碼是否設(shè)置：0-未設(shè)置,1-設(shè)置',
  `wallet_status` int(1) DEFAULT '1' COMMENT '錢(qián)包是否凍結(jié):0-凍結(jié),1-未凍結(jié)',
  `email_status` int(1) DEFAULT '0' COMMENT '郵箱狀態(tài):0-未激活,1-激活',
  `message_status` int(1) DEFAULT '1' COMMENT '短信提醒開(kāi)啟：0-未開(kāi)啟,1-開(kāi)啟',
  `letter_status` int(1) DEFAULT '1' COMMENT '站內(nèi)信提醒開(kāi)啟：0-未開(kāi)啟,1-開(kāi)啟',
  `emailmsg_status` int(1) DEFAULT '0' COMMENT '郵件提醒開(kāi)啟：0-未開(kāi)啟,1-開(kāi)啟',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶狀態(tài)表';

• 支付賬戶表

用戶交易的核心表，存儲(chǔ)用戶相關(guān)的賬戶資金信息。

CREATE TABLE `ms_user_wallet` (
  `wallet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '錢(qián)包ID',
  `user_id` int(11) NOT NULL COMMENT '用戶ID',
  `wallet_pwd` varchar(32) DEFAULT NULL COMMENT '錢(qián)包密碼',
  `total_account` decimal(20,2) DEFAULT '0.00' COMMENT '賬戶總額',
  `usable_money` decimal(20,2) DEFAULT '0.00' COMMENT '可用余額',
  `freeze_money` decimal(20,2) DEFAULT '0.00' COMMENT '凍結(jié)金額',
  `freeze_time` datetime DEFAULT NULL COMMENT '凍結(jié)時(shí)間',
  `thaw_time` datetime DEFAULT NULL COMMENT '解凍時(shí)間',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`wallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶錢(qián)包';

二、設(shè)計(jì)規(guī)范

1、涉及模塊

通過(guò)上面幾個(gè)表設(shè)計(jì)的案例，可以看到表設(shè)計(jì)關(guān)聯(lián)到數(shù)據(jù)庫(kù)的各個(gè)方面知識(shí)：數(shù)據(jù)類型，索引，編碼，存儲(chǔ)引擎等。表設(shè)計(jì)是一個(gè)很大的命題，不過(guò)也遵循一個(gè)基本規(guī)范：三范式。

2、三范式

• 基礎(chǔ)概念

一范式

表的列的具有原子性,不可再分解，即列的信息，不能分解,關(guān)系型數(shù)據(jù)庫(kù)MySQL、Oracle等自動(dòng)的滿足。

二范式

每個(gè)事實(shí)的數(shù)據(jù)記錄只會(huì)出現(xiàn)一次, 不會(huì)冗余, 通常設(shè)計(jì)一個(gè)主鍵來(lái)實(shí)現(xiàn)。

三范式

要求一個(gè)表中不包含已經(jīng)存在于其它表的非主鍵信息，例如部門(mén)和員工的信息，員工表包含部門(mén)表的主鍵ID，則可以關(guān)聯(lián)獲取相關(guān)信息，沒(méi)必要在員工表保存相關(guān)信息。

• 優(yōu)缺點(diǎn)對(duì)比

范式化設(shè)計(jì)

范式化結(jié)構(gòu)設(shè)計(jì)通常更新快，因?yàn)槿哂鄶?shù)據(jù)較少，表結(jié)構(gòu)輕巧，也更好的寫(xiě)入內(nèi)存中。但是查詢起來(lái)涉及到關(guān)聯(lián)，代價(jià)非常高，非常損耗查詢性能。

反范式化設(shè)計(jì)

所有的數(shù)據(jù)都在一張表中，避免關(guān)聯(lián)查詢，索引的有效性更高，但是數(shù)據(jù)的冗余性極高。

• 建議結(jié)論

上述的兩種設(shè)計(jì)方式在實(shí)際開(kāi)發(fā)中都是不存在的，在實(shí)際開(kāi)發(fā)中都是混合使用。比如匯總統(tǒng)計(jì)，緩存數(shù)據(jù)，都會(huì)基于反范式化的設(shè)計(jì)。

三、字段屬性

合適的字段類型對(duì)于高性能來(lái)說(shuō)非常重要，基本原則如下：簡(jiǎn)單的類型占用資源更少；在可以正確存儲(chǔ)數(shù)據(jù)的情況下，選最小的數(shù)據(jù)類型。

1、數(shù)據(jù)類型選擇

• 整數(shù)類型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，根據(jù)數(shù)據(jù)類型范圍合理選擇即可。

• 實(shí)數(shù)類型

FLOAT、DOUBLE、DECIMAL，建議資金貨幣相關(guān)類型使用高精度DECIMAL存儲(chǔ)，或者把數(shù)據(jù)成倍擴(kuò)大為整數(shù)，采用BIGINT存儲(chǔ)，不過(guò)處理相對(duì)麻煩。

• 字符類型

CHAR、VARCHAR，長(zhǎng)度不確定建議采用VARCHAR存儲(chǔ)，不過(guò)VARCHAR類型需要額外開(kāi)銷記錄字符串長(zhǎng)度。CHAR適合存儲(chǔ)短字符，或者定長(zhǎng)字符串，例如MD5的加密結(jié)構(gòu)。

• 時(shí)間類型

DATETIME、TIMESTAMP，DATETIME保存大范圍的值，精度秒。TIMESTAMP以時(shí)間戳的格式，范圍相對(duì)較小，效率也相對(duì)較高，所以通常情況建議使用。

MySQL的字段類型有很多種，可以根據(jù)數(shù)據(jù)特性選擇合適的，這里只描述常見(jiàn)的幾種類型。

2、基礎(chǔ)用法操作

• 數(shù)據(jù)類型

修改字段類型

ALTER TABLE ms_user_sso MODIFY state CHAR(1) DEFAULT '0' ;

ALTER TABLE ms_user_sso 
MODIFY state INT(1) DEFAULT '1' COMMENT '狀態(tài)：0不可用,1可用';

修改名稱位置

ALTER TABLE ms_user_sso 
CHANGE log_ip login_ip VARCHAR(32) AFTER update_time ;

• 索引使用

索引類型：主鍵索引，普通索引，唯一索引，組合索引，全文索引。這里演示普通索引的操作。MySQL的核心模塊，后續(xù)詳說(shuō)。

添加索引

ALTER TABLE ms_user_wallet ADD INDEX user_id_index(user_id) ;
CREATE INDEX state_index ON ms_user_wallet(state) ;

查看索引

SHOW INDEX FROM ms_user_wallet;

刪除索引

DROP INDEX state_index ON ms_user_wallet ;

修改索引

不具有真正意義上的修改，可以把原有的索引刪除之后，再次添加索引。

• 外鍵關(guān)聯(lián)

用處：外鍵關(guān)聯(lián)的作用保證多個(gè)數(shù)據(jù)表的數(shù)據(jù)一致性和完整性，建表時(shí)先有主表，后有從表；刪除數(shù)據(jù)表，需要先刪從表，再刪主表。復(fù)雜場(chǎng)景不建議使用，實(shí)際開(kāi)發(fā)中用的也不多。

添加外鍵

ALTER TABLE ms_user_wallet 
ADD CONSTRAINT user_id_out_key FOREIGN KEY(user_id) REFERENCES ms_user_center(id) ;

刪除外鍵

ALTER TABLE ms_user_wallet DROP FOREIGN KEY user_id_out_key ;

四、表結(jié)構(gòu)管理

1、查看結(jié)構(gòu)

DESC ms_user_status ;
SHOW CREATE TABLE ms_user_status ;

2、字段結(jié)構(gòu)

• 添加字段

ALTER TABLE ms_user_status 
ADD `delete_time` datetime DEFAULT NULL COMMENT '刪除時(shí)間' ;

• 刪除字段

ALTER TABLE ms_user_status DROP COLUMN delete_time ;

3、修改表名

ALTER TABLE ms_user_center RENAME ms_user_info ;

4、存儲(chǔ)引擎

• 存儲(chǔ)引擎

SELECT VERSION() ; SHOW ENGINES ;

MySQL 5.6 支持的存儲(chǔ)引擎有InnoDB、MyISAM、Memory、Archive、CSV、BLACKHOLE等。一般默認(rèn)使用InnoDB，支持事務(wù)管理。該模塊MySQL核心，后續(xù)詳解。

• 修改引擎

數(shù)據(jù)量大的場(chǎng)景下，存儲(chǔ)引擎修改是一個(gè)難度極大的操作，容易會(huì)導(dǎo)致表的特性變動(dòng)，引起各種后續(xù)反應(yīng)，后續(xù)會(huì)詳說(shuō)。

ALTER TABLE ms_user_sso ENGINE = MyISAM ;

5、修改編碼

表字符集默認(rèn)使用utf8，通用，無(wú)亂碼風(fēng)險(xiǎn)，漢字3字節(jié)，英文1字節(jié)，utf8mb4是utf8的超集，有存儲(chǔ)4字節(jié)例如表情符號(hào)時(shí)使用。

• 查看編碼

SHOW VARIABLES LIKE 'character%';

• 修改編碼

ALTER TABLE ms_user_sso DEFAULT CHARACTER SET utf8mb4; 

五、數(shù)據(jù)管理

1、增刪改查

添加數(shù)據(jù)

INSERT INTO ms_user_sso (
    user_id,sso_id,sso_code,create_time,update_time,login_ip,state
)
VALUES
    (
        '1','SSO7637267','SSO78631273612',
        '2019-12-24 11:56:57','2019-12-24 11:57:01','127.0.0.1','1'
    );

更新數(shù)據(jù)

UPDATE ms_user_sso SET 
 user_id = '1',sso_id = 'SSO20191224',sso_code = 'SSO20191224',
 create_time = '2019-11-24 11:56:57',update_time = '2019-11-24 11:57:01',
 login_ip = '127.0.0.1',state = '1'
WHERE user_id = '1';

查詢數(shù)據(jù)

一般情況下都是禁止使用 select* 操作。

SELECT user_id,sso_id,sso_code,create_time,update_time,login_ip,state 
FROM ms_user_sso WHERE user_id = '1';

刪除數(shù)據(jù)

DELETE FROM ms_user_sso WHERE user_id = '2' ;

不帶where條件，就是刪除全部數(shù)據(jù)。原則上不允許該操作，優(yōu)化篇會(huì)詳解。TRUNCATE TABLE也是清空表數(shù)據(jù)，但是占用的資源相對(duì)較少。

2、數(shù)據(jù)安全

• 不可逆加密

這類加密算法，多用來(lái)做數(shù)據(jù)驗(yàn)證操作，比如常見(jiàn)的密碼驗(yàn)證。

SELECT MD5('cicada')='94454b1241ad2cfbd0c44efda1b6b6ba' ;
SELECT SHA('cicada')='0501746a2e4fd34e1d14015fc4d58309585edc7d';
SELECT PASSWORD('smile')='*B4FB95D86DCFC3F33A3852714DC742C77504479D' ;

• 可逆加密

安全性要求高的系統(tǒng)，需要做三級(jí)等保，對(duì)數(shù)據(jù)的安全性極高，數(shù)據(jù)在存儲(chǔ)時(shí)必須加密入庫(kù)，取出時(shí)候需要解密，這些就需要可逆加密。

SELECT DECODE(ENCODE('123456','key_salt'),'key_salt') ;
SELECT AES_DECRYPT(AES_ENCRYPT('cicada','salt123'),'salt123');

上述數(shù)據(jù)安全的管理，也可以基于應(yīng)用系統(tǒng)的服務(wù)(代碼)層進(jìn)行處理，相對(duì)專業(yè)的流程是從數(shù)據(jù)生成源頭處理，規(guī)避數(shù)據(jù)傳遞過(guò)程泄露，造成不必要的風(fēng)險(xiǎn)。

六、源代碼地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base

當(dāng)前題目：MySQL基礎(chǔ)篇(02)：從五個(gè)維度出發(fā)，審視表結(jié)構(gòu)設(shè)計(jì)
文章源于：http://www.rwnh.cn/article20/gpoejo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供虛擬主機(jī)、網(wǎng)頁(yè)設(shè)計(jì)公司、ChatGPT、App設(shè)計(jì)、網(wǎng)站制作、

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)