Decimal(3,2) values in MySQL are always 9.99

Question

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?

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.

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.

Any ideas? Thanks.

Answer 1

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.

The first argument is the total number of digits of precision, and the second argument is the number of digits after the decimal point.

Answer 2

In older versions MySQL DECIMAL(3,2) meant 3 integers to the left of the DP and 2 to the right.

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.

It's a little confusing. Basically, for DECIMAL fields, whatever number of integers you want before the DP needs to be added to whatever number of integers you want after the DP and set as your first option.

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.