摘要：數(shù)據(jù)庫設(shè)計(jì)的三大范式確保每列的原子性如果每列都是不可再分的最小單元信息，則滿足第一范式，比如下圖中，地址是由國家和城市組成的，顯然可以繼續(xù)在拆分成兩個(gè)列，國家和城市，是不滿足第一范式的，需要將地址列差分成國家和城市兩個(gè)列。

這是關(guān)于 MySQL 系列文章的第三篇，在前兩篇文章 《MySQL —— 數(shù)據(jù)庫基礎(chǔ)》 和 《MySQL —— SQL 語句總結(jié)》 中，主要介紹了一些數(shù)據(jù)庫的基礎(chǔ)概念、創(chuàng)建表的方式以及 SQL 語句的使用，本篇在使用的基礎(chǔ)上做一個(gè)小小的升華，來簡單聊一聊數(shù)據(jù)庫的設(shè)計(jì)，還有一句話不得不再次贅述，數(shù)據(jù)庫博大精深，本系列文章內(nèi)容較淺，適合于前端的同學(xué)們對 MySQL 的入門，這也是我的學(xué)習(xí)筆記，希望可以幫助大家。

說到為什么要設(shè)計(jì)數(shù)據(jù)庫，就要說到數(shù)據(jù)的完整性，我們要在設(shè)計(jì)數(shù)據(jù)庫時(shí)保證域的完整性和實(shí)體的完整性，同時(shí)從性能出發(fā)，我們要保證最大限度的節(jié)省存儲空間，比如一張成績表，上面沒必要存儲學(xué)生的姓名、年齡等信息，只需要存儲成績，如果一個(gè)數(shù)據(jù)庫設(shè)計(jì)的合理，最后的結(jié)果就是方便我們對數(shù)據(jù)庫的開發(fā)和擴(kuò)展。

如果是一個(gè) “糟糕” 的數(shù)據(jù)庫設(shè)計(jì)會造成一系列的不良反應(yīng)，比如數(shù)據(jù)冗余，存儲空間浪費(fèi)，內(nèi)存浪費(fèi)，有時(shí)甚至?xí)斐蓴?shù)據(jù)插入和更新的異常，比如學(xué)生表存了學(xué)生信息，而成績表也存了，這樣在修改時(shí)沒有全部修改就會出現(xiàn)錯(cuò)誤。

軟件項(xiàng)目開發(fā)中數(shù)據(jù)庫設(shè)計(jì)的生命周期可大概分為以下幾個(gè)階段：

需求分析階段，分析客戶的業(yè)務(wù)和數(shù)據(jù)處理需求；

概要設(shè)計(jì)階段，設(shè)計(jì)數(shù)據(jù)庫 E-R 模型圖，確認(rèn)需求的正確和完整性；

詳細(xì)設(shè)計(jì)階段，應(yīng)用三大范式審核數(shù)據(jù)庫；

代碼編寫階段，物理實(shí)現(xiàn)數(shù)據(jù)庫，編碼實(shí)現(xiàn)應(yīng)用；

軟件測試階段；

安裝部署階段。

上面數(shù)據(jù)庫的設(shè)計(jì)經(jīng)歷了從 “現(xiàn)實(shí)世界” 到 “信息世界” 到 “數(shù)據(jù)庫模型” 再到 “數(shù)據(jù)庫” 產(chǎn)生的一個(gè)完整過程。

收集信息：收集信息其實(shí)就是與相關(guān)人員進(jìn)行交流、訪談、調(diào)研，充分了解用戶需求，理解整個(gè)項(xiàng)目的完整流程，并理解數(shù)據(jù)庫需要完成的任務(wù)，這部分工作大部分由需求人員完成，并根技術(shù)人員進(jìn)行對接。

標(biāo)識實(shí)體和實(shí)體屬性：開發(fā)人員在明確需求和流程之后，標(biāo)識數(shù)據(jù)庫的實(shí)體，比如學(xué)生信息表，每一條實(shí)體中應(yīng)該由哪些字段組成，成績表中實(shí)體由哪些字段組成等等。

標(biāo)識實(shí)體之間的關(guān)系：其實(shí)就是通過表之間的某字段對表進(jìn)行關(guān)聯(lián)，對表的實(shí)體之間建立對應(yīng)關(guān)系，如學(xué)生表的 id 字段會關(guān)聯(lián)成績表的 student_id 字段，用來查找某個(gè)學(xué)生的成績。

E-R 圖也叫做實(shí)體關(guān)系圖，是指用實(shí)體、關(guān)系、屬性三個(gè)基本概念概括數(shù)據(jù)的基本結(jié)構(gòu)，從而描述靜態(tài)數(shù)據(jù)的概念模型。

E-R 圖的實(shí)體：即數(shù)據(jù)模型中的數(shù)據(jù)對象，每一張表就是一個(gè) E-R 圖的實(shí)體。

E-R 圖的屬性：即數(shù)據(jù)對象中所具有的屬性，例如學(xué)生表的學(xué)生、姓名、年齡等，屬性又分為唯一屬性和非唯一屬性，唯一屬性如經(jīng)過唯一約束和主鍵約束的屬性，不可重復(fù)，其他的都是非唯一屬性。

E-R 圖的關(guān)系：用來表示每一個(gè)數(shù)據(jù)對象與數(shù)據(jù)對象之間的聯(lián)系，即每一個(gè)實(shí)體之間的聯(lián)系，例如學(xué)生表和成績表之間的聯(lián)系，因?yàn)槊總€(gè)學(xué)生都有自己的成績。

1 對 1 關(guān)系是指對于實(shí)體集 A 和 實(shí)體集 B，A 中的每一個(gè)實(shí)體最多與 B 中的一個(gè)實(shí)體有關(guān)系，反之在實(shí)體集 B 中的每一個(gè)實(shí)體之多與實(shí)體集 A 中的一個(gè)實(shí)體有關(guān)系。

1 對多關(guān)系是指實(shí)體集 A 與實(shí)體集 B 中至少有 N (N > 0) 個(gè)實(shí)體有關(guān)系，并且實(shí)體集 B 中最多與實(shí)體集 A 中的一個(gè)實(shí)體有關(guān)系。

多對多關(guān)系是指實(shí)體集 A 中的每一個(gè)實(shí)體與實(shí)體集 B 中至少有 M (M > 0) 個(gè)實(shí)體有關(guān)系，并且實(shí)體集 B 中的每一個(gè)實(shí)體與實(shí)體集 A 中至少有 N (N > 0) 個(gè)實(shí)體有關(guān)系。

如果每列都是不可再分的最小單元信息，則滿足第一范式，比如下圖中，地址是由國家和城市組成的，顯然可以繼續(xù)在拆分成兩個(gè)列，國家和城市，是不滿足第一范式的，需要將地址列差分成國家和城市兩個(gè)列。

舉一個(gè)簡單的例子，我們平時(shí)在淘寶購物的時(shí)候需要添加地址，在填寫新地址時(shí)，都是讓我們選擇國家、省、城市、區(qū)、街道、小區(qū)這樣的方式，而不是讓我們自己將這些地址寫在一起，其原因就是因?yàn)樘詫毜臄?shù)據(jù)庫設(shè)計(jì)嚴(yán)格遵循每列的原子性，這樣的提交可以方便后端獲取每一個(gè)列的信息在數(shù)據(jù)庫中進(jìn)行存儲。

如下圖中所示，在左側(cè)的表中，描述了學(xué)生信息和課程信息，這明顯是兩件事情，假設(shè)再有一張成績表，也要描述學(xué)生信息，課程信息和成績等多件事情，就會造成數(shù)據(jù)的重復(fù)、冗余，也可能會導(dǎo)致更新、插入、刪除數(shù)據(jù)異常的現(xiàn)象。

所以正確的做法是應(yīng)該將左側(cè)表差分成兩張表分別為學(xué)生表和課程表，并使用學(xué)生編號與課程編號進(jìn)行關(guān)聯(lián)。

其他列都不傳遞依賴于主鍵列的意思是表中各列必須都與主鍵直接相關(guān)，不能簡介相關(guān)，從下圖左表可以看出，學(xué)生編號為主鍵，年級 ID 也應(yīng)該為主鍵，正常應(yīng)該通過學(xué)生編號找到年級 ID，再找到年級名稱，這樣年級名稱與學(xué)生編號之間就形成了一個(gè)傳遞并且依賴于主鍵年級 ID，即年級 ID 做為主鍵在中間隔了一層，這樣就使年級名稱與主鍵學(xué)生編號間接相關(guān)，如果在同一張表中，所有的字段都是應(yīng)該直接依賴于主鍵，而不是再通過其他的主鍵傳遞。

如果一個(gè)表中表述了多件事情并有多個(gè)作為主鍵的列，與上一條的處理方式相同，應(yīng)該拆成多張表，并且每張表只有一個(gè)主鍵列。

RBAC（Role-Based Access Control）基于角色的訪問控制，就是用戶通過角色與權(quán)限進(jìn)行關(guān)聯(lián)，簡單的說，一個(gè)用戶擁若干個(gè)角色，每個(gè)角色擁有若干個(gè)權(quán)限，這樣就構(gòu)造成了 “用戶 → 角色 → 權(quán)限 → 資源” 的授權(quán)模型，在這個(gè)模型中，用戶與角色之間，角色與權(quán)限之間，權(quán)限與資源之間，一般都是多對多的關(guān)系，在 RBAC 中最重要的概念主要有四部分，就是用戶（User）、角色（Role）、權(quán)限（Permission）和資源（Resource）。

最小權(quán)限原則：最小權(quán)限原則之所以被 RBAC 所支持，是因?yàn)?RBAC 可以將其角色配置成完成任務(wù)所需要的最小的權(quán)限集；

責(zé)任分離原則：可以通過調(diào)用相互獨(dú)立互斥的角色來共同完成敏感的任務(wù)而體現(xiàn)，比如要求一個(gè)計(jì)帳員和財(cái)務(wù)管理員共參與同一個(gè)帳目；

數(shù)據(jù)抽象原則：數(shù)據(jù)抽象可以通過權(quán)限的抽象來體現(xiàn)，如財(cái)務(wù)操作用借款、存款等抽象權(quán)限，而不用操作系統(tǒng)提供的典型的讀、寫、執(zhí)行權(quán)限。

之前說 RBAC 最重要的概念由四部分，其實(shí)體現(xiàn)在數(shù)據(jù)庫的表中有主要三部分，因?yàn)榻巧陀脩羰侵丿B的，那么主要有三張表分別為用戶表、權(quán)限表和資源表，其中用戶表與權(quán)限表之間有一張關(guān)聯(lián)表，權(quán)限表與資源表之間有一張關(guān)聯(lián)表，E-R 圖如下。

在生活中我們經(jīng)常使用銀行轉(zhuǎn)賬或者支付寶和微信支付，這種操作每一次至少影響兩個(gè)用戶的數(shù)據(jù)信息，比如一方給另一方轉(zhuǎn)錢，如果成功則轉(zhuǎn)錢方余額減去轉(zhuǎn)出金額，而收錢方余額增加收到的金額，這應(yīng)該是一個(gè)請求操作了數(shù)據(jù)表中的倆個(gè)實(shí)體，如果在兩個(gè)操作數(shù)據(jù)的環(huán)節(jié)任意一個(gè)失敗了，都會影響兩個(gè)人數(shù)據(jù)的正確性，這種時(shí)候需要兩個(gè)操作同時(shí)失敗或同時(shí)成功，就是說有一個(gè)操作出現(xiàn)失敗的情況，即使另一個(gè)成功了也需要進(jìn)行回滾操作，這就是事務(wù)的由來。

事務(wù)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作，多個(gè)操作作為一個(gè)整體向系統(tǒng)提交，要么都執(zhí)行，要么都不執(zhí)行，是一個(gè)不可分割的工作邏輯單元。

轉(zhuǎn)賬過程就是一個(gè)整體，它需要兩條 UPDATE 語句，如果任何一個(gè)出錯(cuò)，則整個(gè)轉(zhuǎn)賬業(yè)務(wù)取消，兩個(gè)賬戶的余額都恢復(fù)到原來的數(shù)據(jù)（回滾），確保總余額不變。

這里再舉一個(gè)例子，有一個(gè)上傳文件的功能，后端接收到文件流時(shí)是需要先寫入的，當(dāng)寫入成功后，會將上傳成功的結(jié)果返回給客戶端，如果文件很大，寫入的時(shí)間就會長，如果在此期間突然寫入失敗，則會刪除之前寫入的內(nèi)容，將整個(gè)操作回滾到寫入之前，這里面主要兩步操作，創(chuàng)建一個(gè)新文件并寫入，寫入成功刪除舊文件，如果寫入失敗，兩個(gè)操作將會同時(shí)失敗，即不會刪除舊文件，這也是一個(gè)事務(wù)的例子，只是沒有轉(zhuǎn)賬那么明顯。

事務(wù)具有以下特性，被簡稱為 ACID：

原子性（Atomicity）：事務(wù)是一個(gè)完整的操作，事務(wù)各個(gè)部分是不可分的，要么都執(zhí)行，要么都不執(zhí)行；

一致性（Consistency）：當(dāng)事務(wù)完成后，數(shù)據(jù)必須處理完整的狀態(tài)；

隔離性（Isolation）：并發(fā)事務(wù)彼此隔離、獨(dú)立，它不應(yīng)該以任何方式依賴于其它事務(wù)；

持久性（Durability）：事務(wù)完成后，它對數(shù)據(jù)庫的修改被永久保持。

創(chuàng)建表 account，id 列為主鍵列，name 列為姓名，balance 為余額。

CREATE TABLE `account` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) NOT NULL,
    `balance` INT(11) DEFAULT 0
    PRIMARY KEY (`id`)
);

將表 account 添加兩條數(shù)據(jù)，分別為 “張三” 和 “李四”，余額都為 100。

INSERT INTO `student` (`name`, `balance`) VALUES ("張三", 100);
INSERT INTO `student` (`name`, `balance`) VALUES ("李四", 100);

const mysql = require("mysql");

// 創(chuàng)建數(shù)據(jù)庫連接
const connection = mysql.createConnection({
    host: "localhost", // 主機(jī)名
    port: "3306", // 數(shù)據(jù)庫服務(wù)端口號
    username: "root", // 數(shù)據(jù)庫名稱
    pwd: "123456", // 數(shù)據(jù)庫密碼
    database: "school"  // 連接的數(shù)據(jù)庫名稱
});

connection.connect();

// 開啟事務(wù)
connection.beginTransaction(err => {
    // 回調(diào)參數(shù)為錯(cuò)誤對象，返回結(jié)果，返回字段描述
    connection.query("UPDATE account SET balance - 50 WHERE id = 1", (err, result, fields) => {
        if (err) {
            connection.rollback(); // 如果失敗直接回歸
        } else {
            connection.query("UPDATE account SET balance - 50 WHERE id = 1", (err, result, fields) => {
                if (err) {
                    connection.rollback(); // 如果失敗直接回歸
                } else {
                    connection.commit(); // 如果兩個(gè)都成功了則提交事務(wù)
                }
            });
        }
    });
});

到此關(guān)于 MySQL 的系列文章就告一段落了，希望前端的同學(xué)們在看了這幾篇文章后對你們?nèi)腴T MySQL 有一些幫助，那這幾篇的文章就達(dá)到目的了，也歡迎后端的小伙伴來指出文章中的錯(cuò)誤和不足。