MySQL floating point representation

MySQL floating point representation

MySQL floating-point and fixed-point types can be expressed by the type name plus (M, D). M indicates the total length of the value, and D indicates the length after the decimal point, M and D are also called precision and scale. For example, float (999.9999) can be displayed as-999.00009, and MySQL is rounded to 999.0001 when saving the value. If is inserted, the result is. FLOAT and DOUBLE are displayed based on actual precision by default when no precision is specified. When DECIMAL is not specified, the default integer is 10 and the DECIMAL is 0.

Create the following table:
Mysql> create table t2 (id1 FLOAT (5, 2) default null, id2 DOUBLE (5, 2) default null, id3 DECIMAL (5, 2) default null );

Mysql> DESC t2;
+ ------- + -------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + -------------- + ------ + ----- + --------- + ------- +
| Id1 | float (5, 2) | YES | NULL |
| Id2 | double (5, 2) | YES | NULL |
| Id3 | decimal (5, 2) | YES | NULL |
+ ------- + -------------- + ------ + ----- + --------- + ------- +

Insert data 1.23 to the id1, id2, and id3 fields:

Mysql> insert into t2 (id1, id2, id3) VALUES (1.23, 1.23, 1.23 );

Mysql> SELECT * FROM t2;
+ ------ +
| Id1 | id2 | id3 |
+ ------ +
| 1.23 | 1.23 | 1.23 |
+ ------ +

The data is correctly inserted, and 1.234 is inserted into id1, 1.234 is inserted into id2, and 1.23 is still inserted in id3:
Mysql> insert into t2 (id1, id2, id3) VALUES (1.234, 1.234, 1.23 );

Mysql> SELECT * FROM t2;
+ ------ +
| Id1 | id2 | id3 |
+ ------ +
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+ ------ +

All the data is correctly inserted, but id1 and id2 are removed from the last digit due to the scaling limit.

Insert data 1.234 to id1, id2, and id3 at the same time:

Mysql> insert into t2 (id1, id2, id3) VALUES (1.234, 1.234, 1.234 );
Query OK, 1 row affected, 1 warning (0.02 sec)
Mysql> SELECT * FROM t2;
+ ------ +
| Id1 | id2 | id3 |
+ ------ +
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+ ------ +
3 rows in set (0.00 sec)

The data is also inserted successfully, but an error message is displayed,

Mysql> SHOW warnings;
+ ------- + ------ + -------------------------------------------- +
| Level | Code | Message |
+ ------- + ------ + -------------------------------------------- +
| Note | 1265 | Data truncated for column 'id3 'at row 1 |
+ ------- + ------ + -------------------------------------------- +
1 row in set (0.00 sec)

Remove the accuracy and scale of id1, id2, and id3, and then insert 1.234 of the data:

Mysql> alter table t2 MODIFY id1 FLOAT;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> alter table t2 MODIFY id2 DOUBLE;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

Mysql> alter table t2 MODIFY id3 DECIMAL;
Query OK, 4 rows affected, 4 warnings (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 4

Mysql> DESC t2;
+ ------- + --------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------------- + ------ + ----- + --------- + ------- +
| Id1 | float | YES | NULL |
| Id2 | double | YES | NULL |
| Id3 | decimal (10, 0) | YES | NULL |
+ ------- + --------------- + ------ + ----- + --------- + ------- +

Mysql> insert into t2 (id1, id2, id3) VALUES (1.234, 1.234, 1.234 );
Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql> show warnings;
+ ------- + ------ + -------------------------------------------- +
| Level | Code | Message |
+ ------- + ------ + -------------------------------------------- +
| Note | 1265 | Data truncated for column 'id3 'at row 1 |
+ ------- + ------ + -------------------------------------------- +
1 row in set (0.00 sec)

Mysql> SELECT * FROM t2;
+ ------- + ------ +
| Id1 | id2 | id3 |
+ ------- + ------ +
| 1.234 | 1.234 | 1 |
+ ------- + ------ +
1 row in set (0.00 sec)

Id1 and id2 are correctly inserted, while id3 is truncated.

If the floating point does not write precision or scale, it will be displayed according to the actual display. If there is precision and scale, the data will be rounded up and inserted. The system will not report an error. If the precision and scale are not set for the number of points, if the data exceeds the precision and scale value, an error is returned.