Detailed introduction to the difference between Decimal type and Float Double in MySQL

The following editor will bring you an article on the difference between the Decimal type in MySQL and the Float Double (detailed explanation). The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

There are non-standard data types such as float and double in MySQL, and there are also standard data types such as decimal.

The difference is that non-standard types such as float and double store approximate values ​​in DB, while Decimal stores values ​​in the form of strings.
float, the double type can store floating point numbers (that is, decimal type), but float has a disadvantage. When the data you give is an integer, it will process it for you as an integer. In this way, we naturally encounter problems when depositing and withdrawing currency values. My default value is: 0.00 and the actual storage is 0. Similarly, when I deposit and withdraw currency, it is 12.00, and the actual storage is 12.

Fortunately, mysql provides two data types: decimal. This data type can easily solve the above problems: the decimal type is implemented by MySQL as the same type, which is allowed in the SQL92 standard. They are used to store values ​​where accuracy is important, such as money-related data.

Data definition

float(M,S) M is the full length, S is the length after the decimal point. There are many inaccurate examples on the Internet, Copy is as follows:

mysql> create table t1(c1 float(10,2), c3decimal(10,2));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(9876543.21, 9876543.12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----------------+-----------------+
| c1 | c3 |
+----------------+-----------------+
| 9876543.00 | 9876543.12 |
+----------------+------------------+

2 rows in set (0.00 sec)

Another example: DECIMAL (5,2)

mysql> create table t1(id1 float(5,2) default null,id2 double(5,2) default null,id3 decimal(5,2) default null );
mysql> insert into t1 values(1.2345,1.2345,1.2345);Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

1.2345 --- Maximum 2 digits after the decimal point, so it can be saved. Data will be automatically rounded and truncated, but waning

12.34 --- OK

1234.5 --- Because the decimal part is less than 2 digits, 0 must be added. Therefore, the value should be saved as 1234.50. Therefore, the entire number of digits exceeds 5, and an error is reported when saving.

1.2 --- Fill in the decimal part with 0s. Save according to 1.20.

Default state comparison

If the floating point number does not write longitude and scale, it will be saved according to the actual precision value. If there is precision and scale, the rounded result will be automatically inserted, and the system will not report an error; if the precision and scale are not written for fixed-point numbers, the default value of decimal(10,0) will be used. If the data exceeds the precision and scale value, the system will report an error.

The above is the detailed content of Detailed introduction to the difference between Decimal type and Float Double in MySQL. For more information, please follow other related articles on the PHP Chinese website!