MySQL floating-point representation

MySQL floating-point and fixed-point types can be represented by the type name plus (m,d), m represents the total length of the value, D represents the length after the decimal point, and M and D are also known as precision and scale, such as float (7,4) Can be displayed as -999.9999,mysql is rounded when the value is saved, and if 999.00009 is inserted, the result is 999.0001. float and double do not specify precision, the default is the actual precision to display, and decimal when the precision is not specified, the default integer is 10, the decimal number 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 into the three fields of ID1,ID2,ID3:

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 inserted correctly and then inserted into the ID1 1.234,id2 insert 1.234,ID3 still inserts 1.23:
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 |
+------+------+------+

The data were all correctly inserted, but ID1 and Id2 were given the last one because of the scale limitations.

Also insert data 1.234 into the ID1,ID2,ID3:

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 there is an error message,

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 the ID1,ID2,ID3, then insert the data 1.234:

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 data are inserted correctly, while ID3 is truncated.

Floating-point number if not write precision and scale, will follow the actual display, if there is precision and scale, then the data will be rounded and inserted, the system does not error, fixed-point if not set precision and scale, just follow the default (10,0) operation, if the data exceeds the accuracy and scale values, will be an error.

MySQL floating-point representation