MySQL floating point type, mysql floating point type

Source: Internet
Author: User
Tags binary to decimal decimal to binary id3 truncated

MySQL floating point type, mysql floating point type

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.


When mysql float data loss precision ,,

Let's talk about my understanding. Please look at it from the perspective of doubt and identification.
1 first understand the number of points
-- Definition:
Indicates that the decimal point is fixed.
-- Storage:
* When stored in a database or computer, the integer and fractional parts are stored in certain bytes (understood as storing two integers respectively ), the decimal point is stored as a storage attribute (for example, when used as a column type, the decimal point is stored in the defined part of the table) without occupying the data storage bytes.
* The number of bytes used to store data depends on the precision specified by the number (precision is the total number of digits). If the total number is less than 2, 1 byte is used, 4 bytes for 5-9 bits and 16 bytes for 19-38 BITs (most databases support a maximum of 38 BITs );
-- Example:
Numeric (9.9), with a precision of 2, uses one byte for storage. The data storage range is [-9.9, 2.3]. For example, if the storage range is 00100011, the decimal position is fixed before the fourth digit, which is specified when the column is defined;
-- Note:
Number of fixed points to store accurate numbers. numeric (9.9) can only store numbers between [-9.9,], instead of an approximate number (because both parts are stored as integers ); when you store 2.33 In numeric (9.3), an implicit conversion occurs, and the actual storage is (note that this is not an approximation );

2. Now let's look at floating point numbers.
-- Definition:
A floating point expression is used to represent data. This expression uses scientific notation to express real numbers, that is, a Mantissa, a Base, and an Exponent) and a positive or negative symbol to express data. For example, 123.45 can be expressed as 1.2345x10 ^ 2 in decimal scientific notation, where 1.2345 is the ending number, 10 is the base number, and 2 is the index. It is called a floating point number because the floating decimal point is achieved by using the index.
-- Storage:
Floating Point Numbers are generally defined by IEEE, that is, for single-precision floating point numbers, 1 bit is used to store the symbol bit (plus or minus sign), 8 bit is used to store the index, and 23 bit is used to store the ending number; in addition, the integer part of the ending number must be 1 (Note: it refers to binary format, such as 1.01001) and is saved using binary, that is, the base number is 2;
When stored in most databases or computers, the single precision is 4 bytes, while the double precision is 8 bytes;
-- Example:
The binary value 1001.101 (corresponding to decimal 9.625) can be expressed as 1.001101 × 2 ^ 3. the symbol bit + storage is 0, the index 3 is stored as 00000011, And the ending number 1.001101 is stored as 0011010000 .. (a total of 23 digits, excluding 1 before the decimal point, which is required by IEEE );
-- Generate an approximation:
Because we use the decimal number, and the computer needs to convert it to the corresponding binary form, because the limited binary digits cannot correspond to the decimal values one by one (in other words, convert decimal places to binary places may become infinite decimal places, resulting in inaccuracy), for example, 2 ^-1 corresponds to 0.5, 2 ^-2 corresponds to 0.25, 2 ^-3 corresponds to 0.125, therefore, 0.4 in decimal format (the last digit of a decimal number is not 5) cannot be precisely stored;
-- Problems caused by approximation:
Create table t (a float, B float); insert into t values (0.11, 0.04), (0.04, 0.11 );
Select * from t; the query is normal, and an approximation (convert decimal to binary) occurs in the underlying storage, while an approximation (convert binary to decimal) occurs in the display );
Select sum (a) from t; the query shows 0.14999999850988388. The reason why it is not 0.15 is self-evident.
This is a special concern for floating point numbers in terms of precision loss, calculation, and comparison;

3. Conclusion
Number of points to store the exact number... the remaining full text>
 
What is "floating point data? Why is it float?

Simply put, the data with decimal places is represented by an integer, that is, only integers can be saved.

In particular, a computer saves floating-point data by keeping a valid number and then an intermediary code, just like scientific notation.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.