数值是否超出十进制数据类型的范围?

I have a column called closing_price which is DECIMAL(6,2). My understanding is that this means it can take 6 digits and 2 decimal places, so the largest number that could fit in this column would be 999999.99.

但是,我现在收到此错误:

Numeric value out of range: 1264 Out of range value for column 'closing_price' at row 1 (SQL: insert into `stock_history` (`date`, `closing_price`, `stock_id`, `updated_at`, `created_at`) values (2000-02-01, 51900.000000, 214, 2020-02-07 09:51:03, 2020-02-07 09:51:03))

I don't understand how this could fall out of range of DECIMAL(6,2). Please correct me if my understanding is wrong, or help me troubleshoot this if I am right.

我不知道为什么数字51900在6个小数位处显示零。我从API获取这些值,其中许多结尾最多有6个零,但这是在插入超过一百万次后抛出的第一个错误。

评论
  • 顏小朵o
    顏小朵o 回复

    Your understanding is slightly off. DECIMAL(6,2) means that there are 6 total places of precision, with two of those places occurring after the decimal point. So, to store a value as large as 999999.99, you would actually need DECIMAL(8,2).

    If you want a generally good type for storing currency values, you might follow the advice of this canonical SO answer, which suggests using DECIMAL(15,2).