如何将DECIMAL插入MySQL数据库 [英] How to insert DECIMAL into MySQL database

问题描述

我有一个包含一些字段的数据库表，其中一个 cost 设置为 DECIMAL 数据类型。我将参数设置为 4,2 ，这应该允许小数点前的4个数字，以及后面的2个。

（我被告知， 4 这里是总金额， 2

当我通过POST请求插入数据时（值 3.80 例如）存储到我的数据库的数据实际上是 99.99 。

  CREATE TABLE IF NOT EXISTS`mytable`（
`id` int（11）NOT NULL AUTO_INCREMENT，
`title` varchar（256）NOT NULL，
` ，
 PRIMARY KEY（`id`）
）
  

：

  INSERT INTO mytable（`id`，`title`，`cost'）
 VALUES（0，测试标题'，'3.80'）
  

更新：
改变4,2到6,2

解决方案

MySql十进制类型比起左 -

第一个参数是 precision ，即总位数。第二个参数是 scale ，这是小数点右边的最大位数。

因此， （4,2）可以是 -99.99 到 99.99 的任何东西。

至于为什么得到 99.99 而不是所需的 3.80 ，则您插入的值必须解释为大于 99.99 ，因此使用最大值。也许您可以发布您用于插入或更新表格的代码。

编辑

更正了对缩放和精度的使用的误解，每 http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html 。

I have a database table with some fields, one of them, cost, is set to the DECIMAL data type. I set the parameters to 4,2, which should allow 4 numbers before the decimal point, and 2 afterwards.

(I've been told that the 4 here is the total amount, and the 2 is the amount after the decimal, could somebody please clear that up on a side note?)

When I insert data via a POST request (the value 3.80 for example) the data stored to my database is actually 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

解决方案

MySql decimal types are a little bit more complicated than just left-of and right-of the decimal point.

The first argument is precision, which is the number of total digits. The second argument is scale which is the maximum number of digits to the right of the decimal point.

Thus, (4,2) can be anything from -99.99 to 99.99.

As for why you're getting 99.99 instead 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.

Edit

Corrected a misunderstanding of the usage of scale and precision, per http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html.

这篇关于如何将DECIMAL插入MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！