mysql - Decimal(2,1) 的存储要求是什么？

Question

它在 MySQL 5.6 manual 中说那:

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table:

+----------------------------------+
|Leftover Digits | Number of Bytes |
+----------------------------------+
|       0        |        0        |
+----------------------------------+
|       1        |        1        |
+----------------------------------+
|       2        |        1        |
+----------------------------------+
|       3        |        2        |
+----------------------------------+
|       4        |        2        |
+----------------------------------+
|       5        |        3        |
+----------------------------------+
|       6        |        3        |
+----------------------------------+
|       7        |        4        |
+----------------------------------+
|       8        |        4        |
+----------------------------------+

我的问题是，如果小数点两边的数字都小于九，它是否仍会用完 4 个字节，还是会被视为“剩数”？

Answer 1

我使用 MyISAM 表进行了一些测量，在将一些列添加到六行数据后比较表 dec.MYD 的文件大小来获得答案。这是我得到的:

Original:
-rw-rw----. 1 mysql mysql   54 Dec 17 18:49 dec.MYD

Add one column of DECIMAL(2,1):
-rw-rw----. 1 mysql mysql   66 Dec 17 18:51 dec.MYD

Further add one column of DECIMAL(4,1):
-rw-rw----. 1 mysql mysql   84 Dec 17 18:51 dec.MYD

结论:

DECIMAL(2,1) requires (66-54)/6 = 2 bytes in total
DECIMAL(4,1) requires (84-66)/6 = 3 bytes in total

请注意，测试并不是那么简单，因为第一列似乎总是 7 个字节，无论它是 DECIMAL(2,1) 还是 INT。您需要添加更多列才能重现上述结果。