CODEWITHSUNDEEP
phpmysqlsql
Kenmore
Kenmore

Reputation: 1595

How do I stop a MySQL decimal field from being rounded?

I have a table in MySQL with a field that is decimal, length 9, unsigned. I'm using it for prices.

After I insert the data, and query for it, it has all been rounded and the decimal has been removed.

I'm confused as to why.

Troubleshooting tips?

Host: web.com

phpMyAdmin version: 2.11.10.1

MySQL client version: 5.0.95

Upvotes: 9

Views: 29792

Answers (5)

Will Buffington
Will Buffington

Reputation: 1670

In PHP/MySQLAdmin go to the Structure of the table, hit change and then set the length (which defaults to 10,0) to 10,2 for financial data (dollars).

Upvotes: 0

Dhaval Chheda
Dhaval Chheda

Reputation: 924

I had the same problem. Turns out PHPMyAdmin ignores the decimal place if you enter and assumes it to be 0. Use alter table query to change it. ALTER TABLE tablename CHANGE rate rate DECIMAL(30,3) UNSIGNED NOT NULL;

Upvotes: 1

Levon Barker
Levon Barker

Reputation: 1

The rounding is happening because you need to declare the precision in the type declaration

create table test01 (field01 decimal(9,2));

Upvotes: 0

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

THe rounding is done because may be you have set its datatype as int.

Change its datatype to double OR Float.

This will help you out.

EDIT

If you are having datatype decimal give its size like this edit.

create table numbers (a decimal(10,2));

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230346

Decimal type in MySQL has two tuning knobs: precision and scale. You omitted the scale, so it defaults to 0.

Documentation (link)

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.1 are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL permitted a range of 1 to 254.)

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

Example

mysql> create table test01 (field01 decimal(9));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test01 (field01) values (123.456);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test01;
+---------+
| field01 |
+---------+
|     123 |
+---------+
1 row in set (0.00 sec)

mysql> create table test02 (field01 decimal(9, 4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test02 (field01) values (123.456);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test02;
+----------+
| field01  |
+----------+
| 123.4560 |
+----------+
1 row in set (0.00 sec)

Upvotes: 19

Related Questions