本文原始碼：GitHub·點這里 || GitEE·點這里
一、資料場景
1、表結構簡介
任何工具類的東西都是為了解決某個場景下的問題，比如Redis快取系統熱點資料，ClickHouse解決海量資料的實時分析，MySQL關系型資料庫存盤結構化資料，資料的存盤則需要設計對應的表結構，清楚的表結構，有助于快速開發業務，和理解系統，表結構的設計通常從下面幾個方面考慮：業務場景、設計規范、表結構、欄位屬性、資料管理，
2、用戶場景
例如存盤用戶基礎資訊資料，通常都會下面幾個相關表結構：用戶資訊表、單點登錄表、狀態管理表、支付賬戶表等，
- 用戶資訊表
存盤用戶三要素相關資訊：姓名，手機號，身份證，登錄密碼，郵箱等，
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 '真實姓名',
`pass_word` varchar(32) NOT NULL COMMENT '密碼',
`phone` varchar(20) NOT NULL COMMENT '手機號',
`email` varchar(32) DEFAULT NULL COMMENT '郵箱',
`head_url` varchar(100) DEFAULT NULL COMMENT '用戶頭像URL',
`card_id` varchar(32) DEFAULT NULL COMMENT '身份證號',
`user_sex` int(1) DEFAULT '1' COMMENT '用戶性別:0-女,1-男',
`create_time` datetime DEFAULT NULL COMMENT '創建時間',
`update_time` datetime DEFAULT NULL COMMENT '更新時間',
`state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶表';
- 單點登錄表
用意是在多個業務系統中，用戶登錄一次就可以訪問所有相互信任的業務子系統，是聚合業務平臺常用的解決方案，
CREATE TABLE `ms_user_sso` (
`user_id` int(11) NOT NULL COMMENT '用戶ID',
`sso_id` varchar(32) NOT NULL COMMENT '單點資訊編號ID',
`sso_code` varchar(32) NOT NULL COMMENT '單點登錄碼,唯一核心標識',
`log_ip` varchar(32) DEFAULT NULL COMMENT '登錄IP地址',
`create_time` datetime DEFAULT NULL COMMENT '創建時間',
`update_time` datetime DEFAULT NULL COMMENT '更新時間',
`state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶單點登錄表';
- 狀態管理表
系統用戶在使用時候可能出現多個狀態，例如賬戶凍結、密碼鎖定等，把狀態聚合到一起，可以更加方便的管理和驗證，
CREATE TABLE `ms_user_status` (
`user_id` int(11) NOT NULL COMMENT '用戶ID',
`account_status` int(1) DEFAULT '1' COMMENT '賬戶狀態：0-凍結,1-未凍結',
`real_name_status` int(1) DEFAULT '0' COMMENT '實名認證狀態：0-未實名,1-已實名',
`pay_pass_status` int(1) DEFAULT '0' COMMENT '支付密碼是否設定：0-未設定,1-設定',
`wallet_pass_status` int(1) DEFAULT '0' COMMENT '錢包密碼是否設定：0-未設定,1-設定',
`wallet_status` int(1) DEFAULT '1' COMMENT '錢包是否凍結:0-凍結,1-未凍結',
`email_status` int(1) DEFAULT '0' COMMENT '郵箱狀態:0-未激活,1-激活',
`message_status` int(1) DEFAULT '1' COMMENT '短信提醒開啟：0-未開啟,1-開啟',
`letter_status` int(1) DEFAULT '1' COMMENT '站內信提醒開啟：0-未開啟,1-開啟',
`emailmsg_status` int(1) DEFAULT '0' COMMENT '郵件提醒開啟：0-未開啟,1-開啟',
`create_time` datetime DEFAULT NULL COMMENT '創建時間',
`update_time` datetime DEFAULT NULL COMMENT '更新時間',
`state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶狀態表';
- 支付賬戶表
用戶交易的核心表，存盤用戶相關的賬戶資金資訊，
CREATE TABLE `ms_user_wallet` (
`wallet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '錢包ID',
`user_id` int(11) NOT NULL COMMENT '用戶ID',
`wallet_pwd` varchar(32) DEFAULT NULL COMMENT '錢包密碼',
`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 '凍結金額',
`freeze_time` datetime DEFAULT NULL COMMENT '凍結時間',
`thaw_time` datetime DEFAULT NULL COMMENT '解凍時間',
`create_time` datetime DEFAULT NULL COMMENT '創建時間',
`update_time` datetime DEFAULT NULL COMMENT '更新時間',
`state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
PRIMARY KEY (`wallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶錢包';
二、設計規范
1、涉及模塊
通過上面幾個表設計的案例，可以看到表設計關聯到資料庫的各個方面知識：資料型別，索引，編碼，存盤引擎等，表設計是一個很大的命題，不過也遵循一個基本規范：三范式，
2、三范式
- 基礎概念
一范式
表的列的具有原子性,不可再分解，即列的資訊，不能分解,關系型資料庫MySQL、Oracle等自動的滿足，
二范式
每個事實的資料記錄只會出現一次, 不會冗余, 通常設計一個主鍵來實作，
三范式
要求一個表中不包含已經存在于其它表的非主鍵資訊，例如部門和員工的資訊，員工表包含部門表的主鍵ID，則可以關聯獲取相關資訊，沒必要在員工表保存相關資訊，
- 優缺點對比
范式化設計
范式化結構設計通常更新快，因為冗余資料較少，表結構輕巧，也更好的寫入記憶體中，但是查詢起來涉及到關聯，代價非常高，非常損耗查詢性能，
反范式化設計
所有的資料都在一張表中，避免關聯查詢，索引的有效性更高，但是資料的冗余性極高，
- 建議結論
上述的兩種設計方式在實際開發中都是不存在的，在實際開發中都是混合使用，比如匯總統計，快取資料，都會基于反范式化的設計，
三、欄位屬性
合適的欄位型別對于高性能來說非常重要，基本原則如下：簡單的型別占用資源更少；在可以正確存盤資料的情況下，選最小的資料型別，
1、資料型別選擇
- 整數型別
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，根據資料型別范圍合理選擇即可，
- 實數型別
FLOAT、DOUBLE、DECIMAL，建議資金貨幣相關型別使用高精度DECIMAL存盤，或者把資料成倍擴大為整數，采用BIGINT存盤，不過處理相對麻煩，
- 字符型別
CHAR、VARCHAR，長度不確定建議采用VARCHAR存盤，不過VARCHAR型別需要額外開銷記錄字串長度，CHAR適合存盤短字符，或者定長字串，例如MD5的加密結構，
- 時間型別
DATETIME、TIMESTAMP，DATETIME保存大范圍的值，精度秒，TIMESTAMP以時間戳的格式，范圍相對較小，效率也相對較高，所以通常情況建議使用，
MySQL的欄位型別有很多種，可以根據數據特性選擇合適的，這里只描述常見的幾種型別，
2、基礎用法操作
- 資料型別
修改欄位型別
ALTER TABLE ms_user_sso MODIFY state CHAR(1) DEFAULT '0' ;
ALTER TABLE ms_user_sso
MODIFY state INT(1) DEFAULT '1' COMMENT '狀態：0不可用,1可用';
修改名稱位置
ALTER TABLE ms_user_sso
CHANGE log_ip login_ip VARCHAR(32) AFTER update_time ;
- 索引使用
索引型別：主鍵索引，普通索引，唯一索引，組合索引，全文索引，這里演示普通索引的操作，MySQL的核心模塊，后續詳說，
添加索引
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 ;
修改索引
不具有真正意義上的修改，可以把原有的索引洗掉之后，再次添加索引，
- 外鍵關聯
用處：外鍵關聯的作用保證多個資料表的資料一致性和完整性，建表時先有主表，后有從表；洗掉資料表，需要先刪從表，再刪主表，復雜場景不建議使用，實際開發中用的也不多，
添加外鍵
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 ;
四、表結構管理
1、查看結構
DESC ms_user_status ;
SHOW CREATE TABLE ms_user_status ;
2、欄位結構
- 添加欄位
ALTER TABLE ms_user_status
ADD `delete_time` datetime DEFAULT NULL COMMENT '洗掉時間' ;
- 洗掉欄位
ALTER TABLE ms_user_status DROP COLUMN delete_time ;
3、修改表名
ALTER TABLE ms_user_center RENAME ms_user_info ;
4、存盤引擎
- 存盤引擎
SELECT VERSION() ; SHOW ENGINES ;
MySQL 5.6 支持的存盤引擎有InnoDB、MyISAM、Memory、Archive、CSV、BLACKHOLE等，一般默認使用InnoDB，支持事務管理，該模塊MySQL核心，后續詳解，
- 修改引擎
資料量大的場景下，存盤引擎修改是一個難度極大的操作，容易會導致表的特性變動，引起各種后續反應，后續會詳說，
ALTER TABLE ms_user_sso ENGINE = MyISAM ;
5、修改編碼
表字符集默認使用utf8，通用，無亂碼風險，漢字3位元組，英文1位元組，utf8mb4是utf8的超集，有存盤4位元組例如表情符號時使用，
- 查看編碼
SHOW VARIABLES LIKE 'character%';
- 修改編碼
ALTER TABLE ms_user_sso DEFAULT CHARACTER SET utf8mb4;
五、資料管理
1、增刪改查
添加資料
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'
);
更新資料
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';
查詢資料
一般情況下都是禁止使用 select* 操作，
SELECT user_id,sso_id,sso_code,create_time,update_time,login_ip,state
FROM ms_user_sso WHERE user_id = '1';
洗掉資料
DELETE FROM ms_user_sso WHERE user_id = '2' ;
不帶where條件，就是洗掉全部資料，原則上不允許該操作，優化篇會詳解，TRUNCATE TABLE也是清空表資料，但是占用的資源相對較少，
2、資料安全
- 不可逆加密
這類加密演算法，多用來做資料驗證操作，比如常見的密碼驗證，
SELECT MD5('cicada')='94454b1241ad2cfbd0c44efda1b6b6ba' ;
SELECT SHA('cicada')='0501746a2e4fd34e1d14015fc4d58309585edc7d';
SELECT PASSWORD('smile')='*B4FB95D86DCFC3F33A3852714DC742C77504479D' ;
- 可逆加密
安全性要求高的系統，需要做三級等保，對資料的安全性極高，資料在存盤時必須加密入庫，取出時候需要解密，這些就需要可逆加密，
SELECT DECODE(ENCODE('123456','key_salt'),'key_salt') ;
SELECT AES_DECRYPT(AES_ENCRYPT('cicada','salt123'),'salt123');
上述資料安全的管理，也可以基于應用系統的服務(代碼)層進行處理，相對專業的流程是從資料生成源頭處理，規避資料傳遞程序泄露，造成不必要的風險，
六、源代碼地址
GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/90878.html
標籤：MySQL
上一篇：資料庫中間件DBLE學習(二) 學習配置schema.xml
下一篇：Mysql基礎04-查詢