在進行表結(jié)構(gòu)設計時，數(shù)字類型是最為常見的類型之一，但要用好數(shù)字類型并不如想象得那么簡單，比如：
以上全錯！ 數(shù)字類型看似簡單，但在表結(jié)構(gòu)架構(gòu)設計中很容易出現(xiàn)上述“設計上思考不全面”的問題（特別是在海量并發(fā)的互聯(lián)網(wǎng)場景下）。所以我將從業(yè)務架構(gòu)設計的角度帶你深入了解數(shù)字類型的使用，期待你學完后，能真正用好 MySQL 的數(shù)字類型（整型類型、浮點類型和高精度型）。 數(shù)字類型整型類型MySQL 數(shù)據(jù)庫支持 SQL 標準支持的整型類型：INT、SMALLINT。此外，MySQL 數(shù)據(jù)庫也支持諸如 TINYINT、MEDIUMINT 和 BIGINT 整型類型（表 1 顯示了各種整型所占用的存儲空間及取值范圍）：
各 INT 類型的取值范圍 在整型類型中，有 signed 和 unsigned 屬性，其表示的是整型的取值范圍，默認為 signed。在設計時，我不建議你刻意去用 unsigned 屬性，因為在做一些數(shù)據(jù)分析時，SQL 可能返回的結(jié)果并不是想要得到的結(jié)果。 來看一個“銷售表 sale”的例子，其表結(jié)構(gòu)和數(shù)據(jù)如下。這里要特別注意，列 sale_count 用到的是 unsigned 屬性（即設計時希望列存儲的數(shù)值大于等于 0）： mysql> SHOW CREATE TABLE sale\G *************************** 1. row *************************** Table: sale Create Table: CREATE TABLE `sale` ( `sale_date` date NOT NULL, `sale_count` int unsigned DEFAULT NULL, PRIMARY KEY (`sale_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec) mysql> SELECT * FROM sale; +------------+------------+ | sale_date | sale_count | +------------+------------+ | 2020-01-01 | 10000 | | 2020-02-01 | 8000 | | 2020-03-01 | 12000 | | 2020-04-01 | 9000 | | 2020-05-01 | 10000 | | 2020-06-01 | 18000 | +------------+------------+ 6 rows in set (0.00 sec) 其中，sale_date 表示銷售的日期，sale_count 表示每月的銷售數(shù)量。現(xiàn)在有一個需求，老板想要統(tǒng)計每個月銷售數(shù)量的變化，以此做商業(yè)決策。這條 SQL 語句需要應用到非等值連接，但也并不是太難寫： SELECT s1.sale_date, s2.sale_count - s1.sale_count AS diff FROM sale s1 LEFT JOIN sale s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date ORDER BY sale_date; 然而，在執(zhí)行的過程中，由于列 sale_count 用到了 unsigned 屬性，會拋出這樣的結(jié)果： ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`s2`.`sale_count` - `test`.`s1`.`sale_count`)' 可以看到，MySQL 提示用戶計算的結(jié)果超出了范圍。其實，這里 MySQL 要求 unsigned 數(shù)值相減之后依然為 unsigned，否則就會報錯。 為了避免這個錯誤，需要對數(shù)據(jù)庫參數(shù) sql_mode 設置為 NO_UNSIGNED_SUBTRACTION，允許相減的結(jié)果為 signed，這樣才能得到最終想要的結(jié)果： mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION'; Query OK, 0 rows affected (0.00 sec) SELECT s1.sale_date, IFNULL(s2.sale_count - s1.sale_count,'') AS diff FROM sale s1 LEFT JOIN sale s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date ORDER BY sale_date; +------------+-------+ | sale_date | diff | +------------+-------+ | 2020-01-01 | | | 2020-02-01 | 2000 | | 2020-03-01 | -4000 | | 2020-04-01 | 3000 | | 2020-05-01 | -1000 | | 2020-06-01 | -8000 | +------------+-------+ 6 rows in set (0.00 sec) 浮點類型和高精度型除了整型類型，數(shù)字類型常用的還有浮點和高精度類型。 MySQL 之前的版本中存在浮點類型 Float 和 Double，但這些類型因為不是高精度，也不是 SQL 標準的類型，所以在真實的生產(chǎn)環(huán)境中不推薦使用，否則在計算時，由于精度類型問題，會導致最終的計算結(jié)果出錯。 更重要的是，從 MySQL 8.0.17 版本開始，當創(chuàng)建表用到類型 Float 或 Double 時，會拋出下面的警告：MySQL 提醒用戶不該用上述浮點類型，甚至提醒將在之后版本中廢棄浮點類型。 Specifying number of digits for floating point data types is deprecated and will be removed in a future release 而數(shù)字類型中的高精度 DECIMAL 類型可以使用，當聲明該類型列時，可以（并且通常必須要）指定精度和標度，例如： salary DECIMAL(8,2) 其中，8 是精度（精度表示保存值的主要位數(shù)），2 是標度（標度表示小數(shù)點后面保存的位數(shù)）。通常在表結(jié)構(gòu)設計中，類型 DECIMAL 可以用來表示用戶的工資、賬戶的余額等精確到小數(shù)點后 2 位的業(yè)務。 然而，在海量并發(fā)的互聯(lián)網(wǎng)業(yè)務中使用，金額字段的設計并不推薦使用 DECIMAL 類型，而更推薦使用 INT 整型類型（下文就會分析原因）。 業(yè)務表結(jié)構(gòu)設計實戰(zhàn)整型類型與自增設計在真實業(yè)務場景中，整型類型最常見的就是在業(yè)務中用來表示某件物品的數(shù)量。例如上述表的銷售數(shù)量，或電商中的庫存數(shù)量、購買次數(shù)等。在業(yè)務中，整型類型的另一個常見且重要的使用用法是作為表的主鍵，即用來唯一標識一行數(shù)據(jù)。 整型結(jié)合屬性 auto_increment，可以實現(xiàn)自增功能，但在表結(jié)構(gòu)設計時用自增做主鍵，希望你特別要注意以下兩點，若不注意，可能會對業(yè)務造成災難性的打擊：
從表 1 可以發(fā)現(xiàn)，INT 的范圍最大在 42 億的級別，在真實的互聯(lián)網(wǎng)業(yè)務場景的應用中，很容易達到最大值。例如一些流水表、日志表，每天 1000W 數(shù)據(jù)量，420 天后，INT 類型的上限即可達到。 因此，（敲黑板 1）用自增整型做主鍵，一律使用 BIGINT，而不是 INT。不要為了節(jié)省 4 個字節(jié)使用 INT，當達到上限時，再進行表結(jié)構(gòu)的變更，將是巨大的負擔與痛苦。 那這里又引申出一個有意思的問題：如果達到了 INT 類型的上限，數(shù)據(jù)庫的表現(xiàn)又將如何呢？是會重新變?yōu)?1？我們可以通過下面的 SQL 語句驗證一下： mysql> CREATE TABLE t ( -> a INT AUTO_INCREMENT PRIMARY KEY -> ); mysql> INSERT INTO t VALUES (2147483647); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t VALUES (NULL); ERROR 1062 (23000): Duplicate entry '2147483647' for key 't.PRIMARY' 可以看到，當達到 INT 上限后，再次進行自增插入時，會報重復錯誤，MySQL 數(shù)據(jù)庫并不會自動將其重置為 1。 第二個特別要注意的問題是，（敲黑板 2）MySQL 8.0 版本前，自增不持久化，自增值可能會存在回溯問題！ mysql> SELECT * FROM t; +---+ | a | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.01 sec) mysql> DELETE FROM t WHERE a = 3; Query OK, 1 row affected (0.02 sec) mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `a` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec 可以看到，在刪除自增為 3 的這條記錄后，下一個自增值依然為 4（AUTO_INCREMENT=4），這里并沒有錯誤，自增并不會進行回溯。但若這時數(shù)據(jù)庫發(fā)生重啟，那數(shù)據(jù)庫啟動后，表 t 的自增起始值將再次變?yōu)?3，即自增值發(fā)生回溯。具體如下所示： mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `a` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 s 若要徹底解決這個問題，有以下 2 種方法：
其實，在海量互聯(lián)網(wǎng)架構(gòu)設計過程中，為了之后更好的分布式架構(gòu)擴展性，不建議使用整型類型做主鍵，更為推薦的是字符串類型（這部分內(nèi)容將在 05 節(jié)中詳細介紹）。 資金字段設計在用戶余額、基金賬戶余額、數(shù)字錢包、零錢等的業(yè)務設計中，由于字段都是資金字段，通常程序員習慣使用 DECIMAL 類型作為字段的選型，因為這樣可以精確到分，如：DECIMAL(8,2)。 CREATE TABLE User ( userId BIGINT AUTO_INCREMENT, money DECIMAL(8,2) NOT NULL, ...... ) （敲黑板3）在海量互聯(lián)網(wǎng)業(yè)務的設計標準中，并不推薦用 DECIMAL 類型，而是更推薦將 DECIMAL 轉(zhuǎn)化為 整型類型。也就是說，資金類型更推薦使用用分單位存儲，而不是用元單位存儲。如1元在數(shù)據(jù)庫中用整型類型 100 存儲。 金額字段的取值范圍如果用 DECIMAL 表示的，如何定義長度呢？因為類型 DECIMAL 是個變長字段，若要定義金額字段，則定義為 DECIMAL(8,2) 是遠遠不夠的。這樣只能表示存儲最大值為 999999.99，百萬級的資金存儲。 用戶的金額至少要存儲百億的字段，而統(tǒng)計局的 GDP 金額字段則可能達到數(shù)十萬億級別。用類型 DECIMAL 定義，不好統(tǒng)一。 另外重要的是，類型 DECIMAL 是通過二進制實現(xiàn)的一種編碼方式，計算效率遠不如整型來的高效。因此，推薦使用 BIG INT 來存儲金額相關的字段。 字段存儲時采用分存儲，即便這樣 BIG INT 也能存儲千兆級別的金額。這里，1兆 = 1萬億。 這樣的好處是，所有金額相關字段都是定長字段，占用 8 個字節(jié)，存儲高效。另一點，直接通過整型計算，效率更高。 注意，在數(shù)據(jù)庫設計中，我們非常強調(diào)定長存儲，因為定長存儲的性能更好。 我們來看在數(shù)據(jù)庫中記錄的存儲方式，大致如下：
若發(fā)生更新，記錄 1 原先的空間無法容納更新后記錄 1 的存儲空間，因此，這時數(shù)據(jù)庫會將記錄 1 標記為刪除，尋找新的空間給記錄1使用，如：
上圖中*記錄 1 表示的就是原先記錄 1 占用的空間，而這個空間后續(xù)將變成碎片空間，無法繼續(xù)使用，除非人為地進行表空間的碎片整理。 那么，當使用 BIG INT 存儲金額字段的時候，如何表示小數(shù)點中的數(shù)據(jù)呢？其實，這部分完全可以交由前端進行處理并展示。作為數(shù)據(jù)庫本身，只要按分進行存儲即可。 總結(jié)今天，我?guī)闵钊肓私饬?MySQL 數(shù)字類型在表結(jié)構(gòu)設計中的實戰(zhàn)。我總結(jié)一下今天的重點：
|
|
|
來自： 一本正經(jīng)地胡鬧 > 《數(shù)據(jù)庫》
