Storage of FLOAT and DOUBLE types in MySQL

Storage of FLOAT and DOUBLE types in MySQL

In fact, in the single-precision and double-precision floating-point storage, the storage method is consistent with the C/C ++ standard. They are all floating-point type. The so-called floating-point type is a decimal point location variable, the range that can be expressed is much wider than that of the specified decimal, while the storage space is saved, but it is affected by the precision. Therefore, we try to use the specified decimal mysql decimal (m, d) in strict data) type. Oracle does not have a floating point number, but a number (p, s) decimal point,

Float 4 bytes
1 8 23
Symbol-bit exponential ending number

Double 8 bytes
1 11 52
Symbol-bit exponential ending number
Obviously, their precision depends on the ending number.
The value range is determined by the index.

Float indicates the range:
2 ^ 8 = (-128-127)
-2 ^ 128-2 ^ 127
-3.4E38-3.4E38
Double indicates the range:
2 ^ 11 = (-1024-1023)
-2 ^ 1024-2 ^ 1023
-1.7E308-1.7E308
We can see that this range is actually very wide, but the accuracy is really small.
Float precision:
Float tail number 23 bits, 2 ^ 23 = 8.3E6 6-7 bits
Double ending number 52 bits, 2 ^ 52 = 4.5E15 14-15 bits

If the floating point data is used to save data with a precision greater than its range, it will be truncated by rounding.
MYSQL is as follows:
Mysql> create table dname (id1 float, id2 double, name varchar (20 ));
Query OK, 0 rows affected (0.08 sec)
Mysql> insert into dname values (1234567.123, 1234567.123, 'gaopeng ');
Query OK, 1 row affected (0.00 sec)
Mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Mysql> select * from dname;
+ --------- + ------------- + --------- +
| Id1 | id2 | name |
+ --------- + ------------- + --------- +
| 1234570 | 1234567.123 | gaopeng |
+ --------- + ------------- + --------- +
1 row in set (0.00 sec)
Although rounding, there will be no errors or warnings, which are determined by the standards rather than the database owner.
We can see that 1234567.123 is rounded to 1234570 under FLOAT, and there is no problem with the DOUBLE type.
Extract data directly from the data file.
I still use the self-written tool BCVIEW.
[Root @ Hadoop1 test] # bcview dname. ibd 16 127 40
**************************************** **************************
This Tool Is Uesed For Find The Data In Binary format (Hexadecimal)
Usage:./bcview file blocksize offset cnt-bytes!
File: Is Your File Will To Find Data!
Blocksize: Is N kb Block. Eg: 8 Is 8 Kb Blocksize (Oracle )!
Eg: 16 Is 16 Kb Blocksize (Innodb )!
Offset: Is Every Block Offset Your Want Start!
Cnt-bytes: Is After Offset, How Bytes Your Want Gets!
Edtor QQ: 22389860!
Used gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)
**************************************** **************************
---- Current file size: 0.093750 Mb
---- Current use set blockszie is 16 Kb
Current block: 00000000 -- Offset: 00127 -- cnt bytes: 40 -- data is: 00ffffff00000000000000000000002002600000002002600000000000000000000ffffffffffff
Current block: 00000001 -- Offset: 00127 -- cnt bytes: 40 -- data is: 00000000000000000000000000000000000000000000000000000000000000000000000000000000
Current block: 00000002 -- Offset: 00127 -- cnt bytes: 40 -- data is: bytes
Current block: 00000003 -- Offset: 00127 -- cnt bytes: 40 -- data is: bytes
Current block: 00000004 -- Offset: 00127 -- cnt bytes: 40 -- data is: 00000000000000000000000000000000000000000000000000000000000000000000000000000000
Current block: 00000005 -- Offset: 00127 -- cnt bytes: 40 -- data is: 00000000000000000000000000000000000000000000000000000000000000000000000000000000

The actual data is
000001cc6d09 rowid
Transaction 002d5679ab ID
00000d0c0110 rollback pointer
39b49649 1234570
91ed7c1f87d63241 1234567.123
67616f70656e67 'gaopeng'
For more information about how to obtain data, see my blog.
Http://blog.itpub.net/7728585/viewspace-2071787/
Let's analyze the composition of float, because LINUX is a small end, and the storage will be reversed.
39b49649 is actually 4996b439

49 01001001
96 10010110
B4 10110100
39 00111001

0 10010011 00101101011010000111001
Symbol-bit exponential ending number

10010011 = 147
Here we need to subtract 127
147-127 = 20 is the index

Add a ending number of 00101101011010000111001.
1.00101101011010000111001
In this case, we need
Multiply by the power of 2, and actually move 20 digits to the right
Is
100101101011010000111.001
Integer
100101101011010000111 = 1234567 this is the final data 1234567
In this case, 1234567 is rounded to 1234570.

Let's look at double.

91ed7c1f87d63241
Actually
4132d6871f7ced91

0 sign bit
10000010011 1043 then 1043-1023 = level 20 Index
0010110101101000011100011111011111001110110110010001
Is
1.0010110101101000011100011111011111001110110110010001
100101101011010000111.00011111011111001110110110010001

Integer: 100101101011010000111 = 1234567
Decimal part calculation:
0*2 ^ (0-1) first
0*2 ^ (0-2) second digit
0*2 ^ (0-3) Third digit
1*2 ^ (0-4) = fourth digit of 1/16
1*2 ^ (0-5) = 1/32 fifth digit
1*2 ^ (0-6) = 1/64 sixth Digit
.....
And 0.123 = 0.0001111101111100. The additional part is invalid.

In fact, there is no problem with the data.

This article permanently updates the link address: