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