Difference between the Decimal type and Float Double type in MySQL (detailed description), mysqldecimal

Difference between the Decimal type and Float Double type in MySQL (detailed description), mysqldecimal

MySQL has non-standard data types such as float and double, and 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 string form.
Float and double types can be stored as floating-point numbers (I .e. decimal numbers), but float has a disadvantage. When the given data is an integer, it is processed as an integer. In this way, we naturally encounter problems when accessing the currency value. My default value is 0.00, and the actual storage is 0. Similarly, my access currency is 12.00, and the actual storage is 12.

Fortunately, mysql provides two data types: decimal, which can easily solve the problem above: the decimal type is implemented by MySQL with the same type, which is allowed in the SQL92 standard. They are used to store values that have important requirements for accuracy, such as data related to money.

Data Definition

Float (M, S) M is the full length, and S is the length after the decimal point. There are many inaccurate examples on the network. 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)

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 --- a maximum of two digits after the decimal point, so you can save it. The data is automatically rounded up, but the waning message is returned.

12.34 --- OK

1234.5 --- because the decimal part is less than 2 digits, add 0. Therefore, save 1234.50. Therefore, if the number of digits exceeds 5, an error is returned.

1.2 --- add 0 to the fractional part. Save as 1.20.

Default status comparison

If the floating point does not write the longitude and scale, it will be saved according to the actual precision value. If there is precision and scale, it will automatically Insert the result after rounding, and the system will not report an error; if no precision or scale is specified, decimal (10, 0) is performed by default. If the data exceeds the precision and scale value, the system reports an error.

The difference between the Decimal type and Float Double type in MySQL (detailed description) is all the content that I have shared with you in this article. I hope you can give us a reference and also hope you can provide more support for the customer's house.