Float and Decimal are two numeric data types that are used for storing decimal values, but they have some key differences. When working with MySQL databases, users often experience a debate regarding which one to choose. Well, the use cases for each of these data types are different, and the choice completely depends on the scenario.
For instance, FLOAT is suitable for situations where an approximate value is acceptable, such as when dealing with scientific or statistical computations. On the other hand, DECIMAL is preferred when precise calculations are required, particularly in economic/financial computations where accuracy is essential.
For better understanding, this post will illustrate the below-listed topics along with suitable examples:
Let’s begin with the float data type.
What is Float in MySQL?
FLOAT is a numeric type in MySQL that consumes 4 bytes of memory to store floating/fractional point numbers. It stores approximate numeric values, including positive as well as negative values with fractional points. Since it is an approximate numeric data type, it may not always represent the exact value you input.
Example: Understanding FLOAT Data Type
Let’s create a new table named “linuxhint_table” with the following columns: “id”, “name”, “age”, and “height”. The height column is created with the FLOAT data type:
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
height FLOAT
);
The “Query OK” message in the following output snippet confirms the table creation:
Now insert the integer, varchar, and floating-point values in the id, name, and “height” columns of the “linuxhint_table”:
('John', 25, 1.75),
('Jane', 30, 1.685),
('Alex', 28, 1.8),
('Sarah', 32, 1.63);
The “4 rows affected” notification in the output snippet proves that the four records have been successfully inserted into the “linuxhint_table”:
Let’s execute the “SELECT *” query with the table name to fetch all the records from the “linuxhint_table”:
It can be observed that the floating-point values have been successfully inserted into the height column of the “linuxhint_table”.
What is Decimal in MySQL?
DECIMAL is a numeric data type in MySQL that stores exact numbers along with decimal precision. It allows us to specify the numeric values with precision and scale of the number. Where precision indicates the total digits to be stored, both before and after the fractional point while the scale indicates the total digits to be stored after the decimal point.
Syntax
Utilize the below-stated syntax to create a column with the DECIMAL data type:
Here in the above snippet:
- The “precision” parameter refers to the total digits that can be stored in a column.
- The “scale” parameter refers to the number/digits to the right side of the fractional point.
Example: Understanding Decimal Data Type
The following example creates a new table named “linuxhint_table” with the following columns: “id”, “name”, “age”, and “height”. The height column is created with the DECIMAL data type:
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
height DECIMAL(10, 2)
);
In this example the DECIMAL type accepts a scale of “2”, meaning that regardless of the number of fractional points a value has, it will be rounded to two decimal places:
Now insert the data of a particular type using the INSERT INTO query as follows:
('John', 25, 1.75),
('Jane', 30, 1.65),
('Alex', 28, 1.81),
('Sarah', 32, 1.630);
To fetch the inserted data, execute the “SELECT *” query with the table name as follows:
Difference Between Float and Decimal in MySQL
The below-given table demonstrates the difference between FLOAT and DECIMAL data types using different parameters:
| Parameter | FLOAT | DECIMAL |
| Syntax | col_name FLOAT; | col_name DECIMAL(precision, scale); |
| Precision | The precision point does not need to be defined. | The precision point must be defined. |
| Scale | No need to define. | It must be defined. |
| Storage Size | It takes 4 bytes to store an approximate value. | Depends on the defined precision and scale. |
| Accuracy | Stores approximate precision. | Stores exact precision. |
| Range | By default, it’s 24 places. | By default, it corresponds to one byte. |
| Floating Point Data Type | 32-bit. | 128-bit. |
That’s all about the difference between the FLOAT and DECIMAL in MySQL.
Conclusion
In MySQL, the FLOAT type is utilized to store approximate numbers, while the DECIMAL type is used for precise and accurate numeric values. Moreover, the FLOAT data type requires a fixed storage size, irrespective of the number being stored. On the other hand, the memory size for the DECIMAL data type depends on the specified precision and scale. There are some other key differences between FLOAT and DECIMAL as discussed in this article.
