davidjhp - 1 year ago 146

MySQL Question

MySQL

`create table tran (`

id int,

amount float(9,3)

);

insert into tran (id, amount) values (1, -1000000)

;

Error: Out of range value for column 'amount' at row 1

The amount of -1000000 is 7 digits, so wouldnt the 9 in the float be more than enough?

The manual says

"(M,D) means than values can be stored with up to M digits in total"

http://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html

http://sqlfiddle.com/#!9/aa6cc

Answer Source

`FLOAT(9,3)`

means:

- 9
*total*units of precision - with 3 units past the decimal place, hence
- only 6 units
*before*the decimal place

If you want 9 digits of non-decimal precision then use `FLOAT(12, 3)`

.

Have a look at the MySQL **documentation**.