The difference between Decimal type and Float Double in MySQL (detailed explanation)

MySQL has non-standard data types such as float and double, as well as standard data types such as decimal.

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

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

Data Definition

float(M,S) M is the total length and S is the length after the decimal point. There are many inaccurate examples on the Internet, copy them as follows:

mysql> create table t1(c1 float(10,2), c3 decimal(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 --- There are at most 2 decimal places, so it can be saved, and the data will be automatically rounded off, but waning will be reported

12.34 --- OK

1234.5 --- Because the decimal part is less than 2 digits, it needs to be padded with 0. So it should be saved as 1234.50. So the total number of digits exceeds 5, and an error is reported when saving.

1.2 --- Fill the decimal part with 0. Save as per 1.20.

Default state comparison

If the longitude and scale are not written for floating-point numbers, they will be saved according to the actual precision value. If the precision and scale are written, 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 article on the difference between Decimal type and Float Double in MySQL (detailed explanation) is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.