Lesson Learnt about MySQL Datatype

The Content is derived for Rails audiences but it also useful for all those guys who ever had used/currently using  framework supported by a Database Mapper encapsulated in them. 

The post describe how naively we ignore the database ideology and lay all our worries to ActiveRecord and one of this worry is defining Data Type in ActiveRecord . 

All Rails guys would be quite familiar with the following command .

rails g migration add_column_to_some_table some_column:float
rake db:migrate 

Now this create a datatype FLOAT in database (MySQL) .

Now all look fine up till now but there is problem (if your using MySQL database preferably) .

Problem is how MySQL understand FLOAT DataType .

According to  MySQL documentation . MySQL accept the FLOAT datatype definition to look like this .

alter table some_tables change some_column some_column float (7,4) 

I mean float(L,P) .

L => Length of the number 
P => Number of Precision Value 

So from above "7" is length of the number and "4" is max precision so the max number that specify (7,4) criteria is 999.9999 .

Now as usual it not mentioned anywhere in the MySQL documentation what is default format of FLOAT is?
Well, if not specified (because that's what the above migration do,it defines a FLOAT without any precision. A common practice that developer do these days)

So what is the FLOAT format when not specified(e.g above) .

The answer  => Default Precision (6,0) .

Conversion and Rounding Issue.

Real World                    MySQL
511111.1221                   511111
51111.1221                    51111.1
5111.1221                     5111.12
 ... ...                      ... ...
 ... ...                      ... ...

Real World                  MySQL
5111111.12121              5111110
51111111.12121             51111100
511111111.12121            511111100

So friend always, watch out rounding issues.

A Lesson well Learnt .

Thanks