MySQL 中的 Decimal(3,2) 值始终为 9.99
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1523173/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Decimal(3,2) values in MySQL are always 9.99
提问by labratmatt
I have a field, justsomenum, of type decimal(3,2) in MySQL that seems to always have values of 9.99 when I insert something like 78.3. Why?
我在 MySQL 中有一个类型为 decimal(3,2) 的字段 justsomenum，当我插入类似 78.3 的内容时，该字段的值似乎始终为 9.99。为什么？
This is what my table looks like:
这是我的表的样子：
mysql> describe testtable;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| firstname | varchar(20) | YES | | NULL | |
| lastname | varchar(20) | YES | | NULL | |
| justsomenum | decimal(3,2) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
When I insert something like this and the select:
当我插入这样的东西并选择时：
mysql> insert into testtable (firstname, lastname, justsomenum) values ("Lloyd", "Christmas", 83.5);
I get 9.99 when I select.
当我选择时，我得到 9.99。
mysql> select * from testtable;
+----+-----------+-----------+---------------+
| id | firstname | lastname | justsomenum |
+----+-----------+-----------+---------------+
| 1 | Shooter | McGavin | 9.99 |
| 2 | Lloyd | Christmas | 9.99 |
| 3 | Lloyd | Christmas | 9.99 |
| 4 | Lloyd | Christmas | 9.99 |
+----+-----------+-----------+---------------+
4 rows in set (0.00 sec)
This is MySQL 5.0.86 on Mac OS X 10.5.8.
这是 Mac OS X 10.5.8 上的 MySQL 5.0.86。
Any ideas? Thanks.
有任何想法吗？谢谢。
回答by recursive
The maximum value for decimal(3, 2)is 9.99, so when you try to insert something larger than that, it is capped to 9.99. Try decimal(5, 2)or something else if you want to store larger numbers.
的最大值为decimal(3, 2)9.99，因此当您尝试插入大于该值的内容时，其上限为 9.99。decimal(5, 2)如果您想存储更大的数字，请尝试或其他方法。
The first argument is the total number of digits of precision, and the second argument is the number of digits after the decimal point.
第一个参数是精度的总位数，第二个参数是小数点后的位数。
回答by simonhamp
In older versions MySQL DECIMAL(3,2) meant 3 integers to the left of the DP and 2 to the right.
在旧版本中，MySQL DECIMAL(3,2) 表示 DP 左侧有 3 个整数，右侧有 2 个整数。
The MySQL devs have since changed it so the first property (in this case '3') is the complete number of integers in the decimal (9.99 being three numbers), and the second property (in this case '2') stays the same — the number of decimal places.
MySQL 开发人员已经更改了它，因此第一个属性（在本例中为“3”）是十进制整数的完整数量（9.99 是三个数字），第二个属性（在本例中为“2”）保持不变——小数位数。
It's a little confusing. Basically, for DECIMAL fields, whatever number of integers you want before the DP needs to be addedto whatever number of integers you want after the DP and set as your first option.
这有点令人困惑。基本上，对于 DECIMAL 字段，在 DP 之前需要的任何整数数量都需要添加到 DP 之后您想要的任何整数数量，并设置为您的第一个选项。
Then as has already been said, if you try to enter a number greater than the maximum value for the field, MySQL trims it for you. The problem here is your MySQL configuration. I go into more detail about this on my blog.
然后正如已经说过的，如果您尝试输入一个大于该字段最大值的数字，MySQL 会为您修剪它。这里的问题是您的 MySQL 配置。我在我的博客上更详细地介绍了这一点。
