Shulou(Shulou.com)06/01 Report--

About the storage of FLOAT and DOUBLE types in MYSQL

In fact, in single-precision and double-precision floating-point storage, their storage methods and the IEEE standard are both floating-point type. The so-called floating-point type is the variable position of the decimal point, which can be expressed in a much wider range than the fixed-point decimal point, and the storage space is saved, but it is affected by the accuracy, so try to use the fixed-point decimal mysql decimal type in strict data. ORACLE does not have a floating-point number type at all, but a fixed-point decimal number of number (pforce).

Float 4 byt

1 8 23

Symbolic bit index bit Mantissa

Double 8 byt

1 11 52

Symbolic bit index bit Mantissa

So it's obvious that their accuracy depends on Mantissa.

The range of representation depends on the index.

Float represents a range:

2 ^ 8 = (- 128-127)

-2 ^ 128-2 ^ 127

About-3.4E38-3.4E38

Double represents a range:

2 ^ 11 = (- 1024-1023)

-2 ^ 1024-2 ^ 1023

About-1.7E308-1.7E308

You can see that this range is actually very wide, but the accuracy is very small.

Float precision:

Float Mantissa 23 bits, 2 ^ 23 = 8.3E6 6-7 digits

Double Mantissa 52 bits, 2 ^ 52 = 4.5E15 14-15 digits

Then if you use floating-point data to save data with 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)

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 it is rounded, there will be no errors or warnings, which is determined by its standards, not the database itself.

You can see that 1234567.123 is rounded to 1234570 under FLOAT, and there is no problem with the DOUBLE type, so we

Extract data directly from the data file.

I still used the gadget BCVIEW that I wrote.

[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 is: 0.093750 Mb

-Current use set blockszie is 16 Kb

Current block:00000000--Offset:00127--cnt bytes:40--data is:00ffffffff0000000000010000000200260000000200260000000000000000ffffffff0000ffffff

Current block:00000001--Offset:00127--cnt bytes:40--data is:00000000000000000000000000000000000000000000000000000000000000000000000000000000

Current block:00000002--Offset:00127--cnt bytes:40--data is:ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

Current block:00000003--Offset:00127--cnt bytes:40--data is:000001cc6d090000002d5679ab00000d0c011039b4964991ed7c1f87d6324167616f70656e670000

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

0000002d5679ab things ID

00000d0c0110 rollback pointer

39b49649 1234570

91ed7c1f87d63241 1234567.123

67616f70656e67 'gaopeng'

You can refer to my blog post on how to get the data.

Http://blog.itpub.net/7728585/viewspace-2071787/

Let's analyze the composition of float, because LINUX belongs to the small end, and the storage will be reversed.

39b49649 is actually 4996b439.

49 01001001

96 10010110

B4 10110100

39 00111001

0 10010011 00101101011010000111001

Symbolic bit index bit Mantissa

100100110147

We need to subtract 127 here.

147-12720 is the index.

The Mantissa 0010110101010000111001 needs to add a 1.

The following is 1.0010110101010000111001

So we need 1.00101101011010000111001

Multiplying 2 to the power of 20 is actually moving 20 digits to the right.

For

100101101011010000111.001

Integer part

10010110101010000111 "1234567 here is the final data 1234567.

When it is displayed, 1234567 is rounded to 1234570.

Let's take a look at double.

91ed7c1f87d63241

It is actually

4132d6871f7ced91

0 symbol bit

10000010011 1043 and then 1043-1023 level 20 index bit

0010110101101000011100011111011111001110110110010001

For

1.0010110101101000011100011111011111001110110110010001

100101101011010000111.00011111011111001110110110010001

The integer part is 1001011010100001111234567

About the calculation of the decimal part:

0 * 2 ^ (0-1) first

0 * 2 ^ (0-2) second place

0 * 2 ^ (0-3) the third place

1 * 2 ^ (0-4) = 1gam16 fourth place

1 * 2 ^ (0-5) = the fifth position of 1gam32

1 * 2 ^ (0-6) = the sixth bit of 1DB 64

.

And 0.123, 0.0001111101111100, the extra parts are invalid numbers.

In fact, there is no problem with the data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.