如何将 DECIMAL 插入 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6999582/
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
How to insert DECIMAL into MySQL database
提问by Alex
I have a database table with some fields, one of them, cost, is set to the DECIMALdata type. I set the parameters to 4,2, which should allow 4 numbers before the decimal point, and 2 afterwards.
我有一个包含一些字段的数据库表，其中一个cost设置为DECIMAL数据类型。我将参数设置为4,2，这应该允许小数点前有 4 个数字，后有 2 个数字。
(I've been told that the 4here is the total amount, and the 2is the amount after the decimal, could somebody please clear that up on a side note?)
（有人告诉我，4这是总金额，2是小数点后的金额，有人可以在旁注中澄清吗？）
When I insert data via a POST request (the value 3.80for example) the data stored to my database is actually 99.99.
当我通过 POST 请求（3.80例如值）插入数据时，存储到我的数据库中的数据实际上是99.99.
What am I doing wrong to cause this?
我做错了什么导致这个？
Here's the table:
这是表：
CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(256) NOT NULL,
`cost` decimal(4,2) NOT NULL,
PRIMARY KEY (`id`)
)
Here's my add query:
这是我的添加查询：
INSERT INTO mytable (`id`,`title`,`cost`)
VALUES (0, 'test title', '3.80')
Update: It works after I changed 4,2 to 6,2
更新： 它在我将 4,2 更改为 6,2 后有效
回答by JYelton
MySql decimal types are a little bit more complicated than just left-of and right-of the decimal point.
MySql 十进制类型比小数点的左边和右边稍微复杂一点。
The first argument is precision, which is the number of total digits. The second argument is scalewhich is the maximum number of digits to the right of the decimal point.
第一个参数是precision，它是总位数。第二个参数是尺度，其是数字在小数点右侧的最大数目。
Thus, (4,2)can be anything from -99.99to 99.99.
因此，(4,2)可以是从-99.99到 的任何内容99.99。
As for why you're getting 99.99instead of the desired 3.80, the value you're inserting must be interpreted as larger than 99.99, so the max value is used. Maybe you could post the code that you are using to insert or update the table.
至于为什么您得到99.99而不是所需的3.80，您插入的值必须解释为大于99.99，因此使用最大值。也许您可以发布用于插入或更新表的代码。
Edit
编辑
Corrected a misunderstanding of the usage of scale and precision, per http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html.
根据http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html更正了对比例和精度使用的误解。
回答by Marc B
Yes, 4,2 means "4 digits total, 2 of which are after the decimal place". That translates to a number in the format of 00.00. Beyond that, you'll have to show us your SQL query. PHP won't translate 3.80 into 99.99 without good reason. Perhaps you've misaligned your fields/values in the query and are trying to insert a larger number that belongs in another field.
是的，4,2 表示“总共 4 位数字，其中 2 位在小数位后”。这将转换为格式为 的数字00.00。除此之外，您还必须向我们展示您的 SQL 查询。PHP 不会在没有充分理由的情况下将 3.80 转换为 99.99。也许您在查询中未对齐您的字段/值，并试图插入属于另一个字段的更大数字。
回答by Optiq
I noticed something else about your coding.... look
我注意到关于你的编码的其他东西......看
INSERT INTO reports_services (id,title,description,cost) VALUES (0, 'test title', 'test decription ', '3.80')
in your "CREATE TABLE" code you have the id set to "AUTO_INCREMENT" which means it's automatically generating a result for that field.... but in your above code you include it as one of the insertions and in the "VALUES" you have a 0 there... idk if that's your way of telling us you left it blank because it's set to AUTO_INC. or if that's the actual code you have... if it's the code you have not only should you not be trying to send data to a field set to generate it automatically, but the RIGHT WAY to do it WRONG would be
在您的“CREATE TABLE”代码中，您将 id 设置为“AUTO_INCREMENT”，这意味着它会自动为该字段生成结果....那里有一个 0... idk 如果这是你告诉我们你将它留空的方式，因为它被设置为 AUTO_INC。或者，如果这是您拥有的实际代码...
'0',
you put
你把
0,
lol....so that might be causing some of the problem... I also just noticed in the code after "test description" you have a space before the '.... that might be throwing something off too.... idk.. I hope this helps n maybe resolves some other problem you might be pulling your hair out about now.... speaking of which.... I need to figure out my problem before I tear all my hair out..... good luck.. :)
大声笑......所以这可能会导致一些问题......我也刚刚注意到在“测试描述”之后的代码中你在'......之前有一个空格......这可能也会抛出一些东西...... . idk .. 我希望这会有所帮助 n 也许可以解决您现在可能正在拔头发的其他一些问题.... ... 祝你好运.. ：）
UPDATE.....
更新.....
I almost forgot... if you have the 0 there to show that it's blank... you could be entering "test title" as the id and "test description" as the title then "3.whatever cents" for the description leaving "cost" empty...... which could be why it maxed out because if I'm not mistaking you have it set to NOT NULL.... and you left it null... so it forced something... maybe.... lol
我差点忘了……如果你有 0 来表明它是空白的……你可以输入“测试标题”作为 ID，输入“测试描述”作为标题，然后输入“3.whatever cents”作为描述离开“成本”为空......这可能就是它最大化的原因，因为如果我没有误会你将它设置为NOT NULL......你把它留空......所以它强迫了一些......也许……哈哈
