今天這篇文章，我和你聊一聊在 MySQL 中如何存儲(chǔ)貨幣的方法。你可能已經(jīng)知道，在 SQL Server 數(shù)據(jù)庫(kù)中是有專用的 money 數(shù)據(jù)類型的，還支持貨幣符號(hào)，非常好用。但是在 MySQL 中沒(méi)有這樣的實(shí)現(xiàn)，所以我們就要另辟蹊徑去解決它。
1. 高精度的數(shù)據(jù)類型都有哪些？
如果不考慮數(shù)據(jù)的精確度和使用的便利程度、性能、存儲(chǔ)等條件，在 MySQL 中可以存儲(chǔ)數(shù)值的數(shù)據(jù)類型其實(shí)有很多，例如整形數(shù)據(jù)類型 INT、BIGINT，定點(diǎn)數(shù) DECIMAL，浮點(diǎn)數(shù) FLOAT、DOUBLE，甚至 VARCHAR 都能拿來(lái)存儲(chǔ)數(shù)值。
那哪種數(shù)據(jù)類型適合存儲(chǔ)貨幣呢？
首先 FLOAT 和 DOUBLE 這兩個(gè)數(shù)據(jù)類型是一定要排除的。
由于這兩個(gè)類型都是浮點(diǎn)數(shù)據(jù)類型，不夠精確，在運(yùn)算時(shí)無(wú)法保證結(jié)果的正確性。
例如 M*G/G 的結(jié)果不一定等于 M。
DECIMAL 是一種高精度的定點(diǎn)數(shù)據(jù)類型，在 MySQL 中它還有 1 個(gè)同義詞：NUMERIC，這兩個(gè)數(shù)據(jù)類型在 MySQL 中被視為相同的類型。
目前業(yè)內(nèi)大多都是采用這種數(shù)據(jù)類型來(lái)保存財(cái)務(wù)數(shù)據(jù)等對(duì)精度要求非常高的數(shù)據(jù)。
不過(guò)凡事無(wú)絕對(duì)，你也可以把財(cái)務(wù)數(shù)據(jù)使用 BIGINT 的數(shù)據(jù)類型做存儲(chǔ)。假設(shè)存儲(chǔ)的數(shù)據(jù)要精確到小數(shù)點(diǎn)后兩位，則把金額乘以 100 之后存儲(chǔ)即可，取出時(shí)再除以 100。
再說(shuō)下 INT 數(shù)據(jù)類型，由于這個(gè)數(shù)據(jù)類型的存儲(chǔ)空間只有 4 個(gè)字節(jié)，數(shù)值范圍不一定能滿足財(cái)務(wù)數(shù)據(jù)的需求，因此 INT 數(shù)據(jù)類型比較適合存儲(chǔ)游戲金幣、網(wǎng)站代幣等要求高精度但數(shù)值范圍不大的數(shù)據(jù)。
那 VARCHAR 這個(gè)數(shù)據(jù)類型是否能存儲(chǔ)貨幣呢？
我個(gè)人是不太建議使用 VARCHAR 這個(gè)數(shù)據(jù)類型存儲(chǔ)貨幣值。
一是缺少約束，除了數(shù)字以外其他亂七八糟的字符都可以存進(jìn)去；二是使用了 VARCHAR 就不能使用 MySQL 內(nèi)置的一些數(shù)字函數(shù)（MIN, MAX, SUM 等），會(huì)有一些意料之外的情況出現(xiàn)。
下面我們就做個(gè)實(shí)驗(yàn)來(lái)看一下，在 VARCHAR 數(shù)據(jù)類型中使用數(shù)字函數(shù)的問(wèn)題。
先創(chuàng)建一個(gè)包含有 VARCHAR 數(shù)據(jù)類型的表，在表中插入幾行字符型的數(shù)值數(shù)據(jù)：
mysql> create table varchar_test (col_1 varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into varchar_test values('0'),('1'),('2'),('9'),('10');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select max(col_1) from varchar_test;
+------------+
| max(col_1) |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)
可以看到 MAX 函數(shù)給出的結(jié)果是不正確的，其他的數(shù)字函數(shù)你可以自行測(cè)試一下。
因此不建議使用 VARCHAR 數(shù)據(jù)類型存儲(chǔ)貨幣值。
2. DECIMAL 數(shù)據(jù)類型的使用
DECIMAL 和 NUMRIC 在 MySQL 內(nèi)部被實(shí)現(xiàn)為同樣的類型，用來(lái)保存高精度的數(shù)值。
在對(duì)列進(jìn)行定義的時(shí)候，可以指定它的精度和標(biāo)度：
DECIMAL(M,D)
-
M 是精度，也就是數(shù)據(jù)的總長(zhǎng)度（這不包括小數(shù)點(diǎn)和負(fù)號(hào)）
-
D 是標(biāo)度，也就是小數(shù)點(diǎn)后的保留長(zhǎng)度
DECIMAL 數(shù)據(jù)類型支持的 M 的最大值為 65，D 是 30，并且 D 的長(zhǎng)度只能小于或等于 M。
如果你在創(chuàng)建數(shù)據(jù)列的時(shí)候沒(méi)有指定精度和標(biāo)度，默認(rèn)按照 DECIMAL (10,0) 處理。
3. DECIMAL 數(shù)據(jù)類型的使用限制與注意事項(xiàng)
第一，MySQL 的 DECIMAL 數(shù)據(jù)類型不能存儲(chǔ)貨幣的符號(hào)（也就是 $、￥等貨幣符號(hào)）。
因此如果你的財(cái)務(wù)數(shù)據(jù)包含多幣種的話，需要另加一個(gè)字段存儲(chǔ)幣種。
第二，如果插入的值的精度高于實(shí)際定義的精度，系統(tǒng)會(huì)自動(dòng)四舍五入處理，使插入的值符合我們的定義。
在操作 DECIMAL 數(shù)據(jù)類型的字段時(shí)，MySQL 會(huì)四舍五入把超出范圍的數(shù)據(jù)截?cái)唷?/p>
因此設(shè)置標(biāo)度的時(shí)候要足夠的長(zhǎng)，留足小數(shù)點(diǎn)后的空間，免得四舍五入造成財(cái)務(wù)數(shù)據(jù)不準(zhǔn)確。
因?yàn)樨?cái)務(wù)妹子對(duì)賬的要求可是一分錢都不能錯(cuò)的，你自掏腰包把錢補(bǔ)給她都不行，就是非常的嚴(yán)格。
下面我們就做個(gè)實(shí)驗(yàn)看一下 DECIMAL 在標(biāo)度不夠時(shí)的表現(xiàn)。
先建一張包含 decimal (5,1) 的表，插入 3 行不同長(zhǎng)度的數(shù)據(jù)：
mysql> create table decimal_test(col_1 int, col2 decimal(5,1));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into decimal_test values(1,10.1),(2,10.11),(3,10.16);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 2
mysql> select * from decimal_test;
+-------+------+
| col_1 | col2 |
+-------+------+
| 1 | 10.1 |
| 2 | 10.1 |
| 3 | 10.2 |
+-------+------+
3 rows in set (0.00 sec)
在這個(gè)實(shí)驗(yàn)中，你可以觀察到由于操作的數(shù)值超出了標(biāo)度的范圍，后面的兩行數(shù)據(jù)都四舍五入了。
因此在設(shè)計(jì)貨幣相關(guān)的字段時(shí)請(qǐng)務(wù)必留出足夠的空間。
4. DECIMAL 數(shù)據(jù)類型性能相關(guān)
在早期的 MySQL 版本中，就支持了 DECIMAL 的數(shù)據(jù)類型。
但是由于 CPU 并不支持對(duì) DECIMAL 的直接計(jì)算，當(dāng)時(shí)的 MySQL 數(shù)據(jù)庫(kù)使用了浮點(diǎn)運(yùn)算來(lái)實(shí)現(xiàn)了這個(gè)數(shù)據(jù)類型，因此并不精確。
直到 5.0 版本開(kāi)始，MySQL 才真正實(shí)現(xiàn)了 DECIMAL 數(shù)據(jù)類型的精確計(jì)算，真正能用在財(cái)務(wù)系統(tǒng)中。
也正是因?yàn)?strong>和 DECIMAL 相關(guān)的計(jì)算都是由 MySQL 來(lái)處理的，因此在對(duì) DECIMAL 相關(guān)的列進(jìn)行計(jì)算時(shí)，性能不如 CPU 直接支持的浮點(diǎn)數(shù)和整數(shù)計(jì)算的快。
因此在合適的情況下，也可以考慮選用 BIGINT 的數(shù)據(jù)類型，它能同時(shí)避免浮點(diǎn)數(shù)計(jì)算不精確和 DECIMAL 計(jì)算代價(jià)高的問(wèn)題，不過(guò)你也需要同時(shí)處理和小數(shù)點(diǎn)相關(guān)的問(wèn)題。
5. 小結(jié)
今天，我給你介紹了如何在 MySQL 中存儲(chǔ)財(cái)務(wù)數(shù)據(jù)等要求高精度數(shù)據(jù)類型的場(chǎng)景，并介紹了兩種可行的方案：BIGINT 和 DECIMAL。
在存儲(chǔ)財(cái)務(wù)數(shù)據(jù)時(shí)，DECIMAL 是這幾種數(shù)據(jù)類型中使用起來(lái)最方便的，它直接支持高精度小數(shù)的存儲(chǔ)。
但是由于 CPU 不能直接對(duì) DECIMAL 進(jìn)行計(jì)算，因此理論上計(jì)算效率要稍低一些。
BIGINT 數(shù)據(jù)類型是一個(gè)不錯(cuò)的平衡，它能直接利用 CPU 進(jìn)行計(jì)算，也能存儲(chǔ)足夠大的數(shù)值。
但是由于 BIGINT 是整數(shù)類型，在存取的時(shí)候如果有小數(shù)點(diǎn)，要處理小數(shù)點(diǎn)相關(guān)的問(wèn)題。所以在使用時(shí)要在公司內(nèi)部做好上下游的溝通，建立代碼規(guī)范，避免上游或下游消費(fèi)數(shù)據(jù)時(shí)出錯(cuò)。
綜合來(lái)說(shuō)，建議首選 DECIMAL 這個(gè)數(shù)據(jù)類型，其次是 BIGINT。
參考文獻(xiàn)：
- MySQL 官方文檔：decimal 數(shù)據(jù)類型，https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html
- 《MySQL 技術(shù)內(nèi)幕：SQL 編程》，姜承堯著