MySQL decimal参数如何精确控制数值精度与范围？

DECIMAL (或简写为 DEC) 是 MySQL 中用于存储精确数值的数据类型，非常适合存储财务、货币、科学计算等对精度要求极高的数据。

核心参数：DECIMAL(M, D)

DECIMAL 类型的定义主要依赖于两个核心参数，它们必须在创建表时指定。

M (总位数 / Precision)

• 含义：表示数值的总位数，包括整数部分、小数点和小数部分。
• 范围：1 ≤ M ≤ 65，在 MySQL 8.0.17 之前，最大值为 65；从 8.0.17 开始，最大值提升到了 65。
• 作用：定义了数值的“宽度”或“精度”，确保数字不会超过指定的总长度。

D (小数位数 / Scale)

• 含义：表示小数点后可以有多少位数字。
• 范围：0 ≤ D ≤ M D ≤ 30，小数位数 D 不能超过总位数 M，并且有一个上限 30。
• 作用：定义了数值的“小数精度”，确保小数部分的位数是固定的。

语法和示例

基本语法

DECIMAL(M, D)

示例解析

存储空间和计算规则

DECIMAL 的存储方式非常特殊，它不是使用二进制浮点数（如 FLOAT 或 DOUBLE），而是使用二进制字符串来存储，这保证了其精确性。

存储空间计算

存储的字节数取决于 M 和 D 的值。

1. M <= D (即纯小数或小数位数等于总位数):

   
   （图片来源网络，侵删）
   • 需要的字节数 = ceil(M / 9) * 4

2. M > D (即整数部分存在):

   • 整数部分需要的字节数 = ceil((M - D) / 9) * 4
   • 小数部分需要的字节数 = ceil(D / 9) * 2
   • 总字节数 = 整数部分字节数 + 小数部分字节数

示例：

• DECIMAL(19, 4):

  • M - D = 15, ceil(15 / 9) = 2 -> 整数部分需要 2 * 4 = 8 字节。
  • D = 4, ceil(4 / 9) = 1 -> 小数部分需要 1 * 2 = 2 字节。
  • 总计需要 10 字节。

• DECIMAL(18, 9):

  
  （图片来源网络，侵删）
  • M - D = 9, ceil(9 / 9) = 1 -> 整数部分需要 1 * 4 = 4 字节。
  • D = 9, ceil(9 / 9) = 1 -> 小数部分需要 1 * 2 = 2 字节。
  • 总计需要 6 字节。

一个重要的简化规则： 对于最常见的 DECIMAL(19, D) 或 DECIMAL(M, 4) 等情况，可以记住一个经验法则：

• M <= 18，DECIMAL 通常占用 9 字节。
• M 在 19 到 28 之间，DECIMAL 通常占用 13 字节。
• M 在 29 到 38 之间，DECIMAL 通常占用 17 字节。
• 以此类推,每增加 10 位，增加 4 字节。

DECIMAL vs. FLOAT vs. DOUBLE

这是选择数值类型时最常见的问题。

为什么不推荐用 FLOAT 或 DOUBLE 存储钱？ 因为浮点数在二进制中无法精确表示某些十进制小数（如 0.1），在多次计算后，误差会累积，导致最终结果与预期不符，这在财务上是绝对不能接受的。

使用注意事项

1. 必须指定 M 和 D：在 CREATE TABLE 语句中定义 DECIMAL 列时，必须明确指定 (M, D)，否则 MySQL 会报错。

   -- 错误语法
   CREATE TABLE products (
       price DECIMAL -- 必须指定 (M, D)
   );

2. D 的默认值：如果只指定 M 而不指定 D，MySQL 会将 D 默认设置为 0。

   DECIMAL(10)  -- 等同于 DECIMAL(10, 0)

3. 计算和转换：

   • 当 DECIMAL 参与算术运算时，结果的精度和标度会根据 MySQL 的规则进行计算，结果的标度是操作数中标度的最大值。
   • 将 DECIMAL 与其他类型（如 INT 或 FLOAT）进行运算时，MySQL 会尝试将其他类型转换为 DECIMAL 或 DOUBLE，这可能会引入精度损失或类型转换的开销。

4. 性能：DECIMAL 的存储和计算通常比 INT 或 FLOAT 慢一些，因为它不是 CPU 原生支持的数值类型，对于大量数据的计算，性能差异会比较明显，但对于绝大多数应用场景，这种性能影响可以忽略不计。

最佳实践

1. 存储金额：永远使用 DECIMAL。DECIMAL(19, 4) 可以支持万亿级别的金额，并精确到万分位，足以应对绝大多数业务场景。

   CREATE TABLE orders (
       id INT PRIMARY KEY,
       amount DECIMAL(19, 4) NOT NULL COMMENT '订单金额'
   );

2. 存储百分比：根据需求选择，如果需要精确到小数点后多位，使用 DECIMAL，如果只需要近似值，可以使用 DECIMAL(5, 2) 或 FLOAT。

   -- 精确的百分比
   discount_rate DECIMAL(5, 4) COMMENT '折扣率，如 0.1234 表示 12.34%'

3. 权衡精度与空间：不要过度分配 M 和 D，根据业务实际可能的最大值来设定，避免浪费存储空间，如果价格最大是 9999.99，DECIMAL(7, 2) 就足够了，无需使用 DECIMAL(19, 4)。

DECIMAL 的核心是精确，当你的数据不能有任何舍入误差时，它就是你的不二之选。