davidjhp davidjhp - 3 months ago 29
MySQL Question

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

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

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.

Comments