Usage of Float,Double and Decimal Data Types in MySql

When we start developing a database, we have to use the most appropriate data types for all the columns to store data values of the respective tables in it. For dealing with the floating type data values, most of the times, we get confused about which data type to go for, as we have Float, Decimal and Double as well.

I, myself, as a new DBA, got confused about the usage of the same. So, i have re-searched through some websites over internet and have concluded some facts about these data types' usage. i want to share my experience with all of you.

• FLOAT : When we use this data type, it considers only 4 digits which are entered after decimal and ignores the other digits afterwards.

for example,

create table myTable(marks float);

insert into myTable values(25.54675);

When we will execute these queries, a table will be created "myTable" with a column "marks" having a value "25.5467".

• Double : When we use this data type, it considers only 14 digits which are entered after decimal and ignores the other digits afterwards.

for example,

create table myTable(marks double);

insert into myTable values(21.97653876235671234);

When we will execute these queries, a table will be created "myTable" with a column "marks" having a value "21.97653876235671”.

• Decimal : When we use this data type, it considers only 1 digit entered after decimal and ignores the other digits afterwards.

for example,

create table myTable(marks decimal);

insert into myTable2 values(23.274);

When we will execute these queries, a table will be created "myTable" with a column "marks" having a value "23”.

This is the case in which the digits after decimal point is less than 5.

Same ways, if we perform the below query,

create table myTable(marks decimal);

insert into myTable2 values(23.518);

We can notice that, if the digits after the decimal point are greater than or equal to 5, then, the digit before decimal point is incremented.

Happy Reading

Gunjan Singh - Facebook