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

Question

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

Answer 1

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)