MYSQL 資料型別儲存-數值型

Steven1981發表於2009-03-11
MySQL 資料型別
探索MYSQL 數值型別的儲存,以及解讀方法.on Engine of myisam[@more@]1. 環境版本:
OS : LINUX AS4
MYSQL: 5.0.51a-log
ENGINE : Myisam DEFAULT CHARSET=latin1

2. 本章研究的數值型別物件:
TINYINT 1個位元組 FIXED
SMALLINT 2個位元組 FIXED
MEDIUMINT 3個位元組 FIXED
INT, INTEGER 4個位元組 FIXED
BIGINT 8個位元組 FIXED
DECIMAL(M,N) >=4位元組 FIXED
3. 數值型別: TINYINT SMALLINT MEDIUMINT INT BIGINT
這幾種資料存取方式都是一樣的: 高位優先儲存,符號位(0正,1負)
drop table if exists heyf ;
create table heyf (id TINYINT ) type myisam DEFAULT CHARSET=latin1;
insert into heyf values (10),(-10) ;
system hexdump /opt/mysql/data/test/heyf.MYD
------------------------------------------
0000000 0afd 0000 0000 fd00 00f6 0000 0000
000000e
------------------------------------------
其中:
ROW1:
--------------------------------
fd : 行header
0a : 值10
---------------------------------
ROW2:
--------------------------------
fd : 行header
f6 : 值-10的補碼
---------------------------------
如果是正數,第1位為"0", 直接讀出來即可;
如果是負數,第1位為"1", 則按常規辦法將值 取反+1.
比如:
原值 原二進位制 取反 加1 十進位制
-------------------------------------------------------
f6 --&gt 1111 0110 --&gt 0000 1001 --&gt 0000 1010 --&gt 10
其他幾個型別請讀者舉一反三.

4. 數值型別: DECIMAL(M,N) 或 DECIMAL(M)
4.1 儲存位計算
最小分配4個位元組空間,比如decimal(4,2),實際用兩個位元組就可以表示.但MYSQL在分配空間時還是用了4個位元組.空閒部分用0填充
DECIMAL(M,N),如果9(這裡為什麼要這樣算,詳見4.2中的例項解釋)

4.2 如何讀取資料.
4.2.1 讀取步驟
按照定義,從磁碟讀出該DECEMAL欄位的所有資料(N位)後:
4.2.1.1 正數,帶小數,DECIMAL(4,2)
0)以1開頭,如果定義為UNSIGNED,則都為1
1)去掉第一位符號位,
2)用小數將剩餘的位數分開, 前面(M位)是整數部分,後面(N位)是小數部分
(在這一步是怎麼分M和N的,我們能根據欄位的定義計算出來)
3)去掉小數點後面(整個位元組)為0的情況,
4)將二進位制轉換成十進位制,即可讀出原值.
(注意,小數的讀取方法與整數的方法一樣,按二進位制向十進位制轉換即可)

4.2.1.2 負數,帶小數 DECIMAL(4,2)
0)以0開頭,
1)去掉第一位符號位,
2)剩餘的數取反+1 ,
3)用小數將剩餘的位數分開, 前面(M位)是整數部分,後面(N位)是小數部分
(在這一步是怎麼分M和N的,我們能根據欄位的定義計算出來)
4)去掉小數點後面(整個位元組)為0的情況,
5)將二進位制轉換成十進位制,即可讀出原值.
(注意,小數的讀取方法與整數的方法一樣,按二進位制向十進位制轉換即可)
4.2.1.3 正數,不帶小數, DECIMAL(N)
0)以1開頭,如果定義為UNSIGNED,則都為1
1)去掉第一位符號位,
2)將剩餘的數位直接按二進位制向十進位制轉換即可
4.2.1.4 負數,不帶小數, DECIMAL(N)
0)以0開頭
1)去掉第一位符號位,
2)將剩餘的數取反+1
3)按二進位制向十進位制轉換即可讀到原值

4.2.1.5 超長數值,如何讀取
當需要表示的數值超過某個限值後,如果你按以上的方法去讀取資料,會發現讀出來的數值是不對的.
資料寫道:
********************************************************************
high byte first, four-byte chunks.
We call the four-byte chunks "*decimal* digits".
Since 2**32 = There is an implied decimal point. Details are in /strings/decimal.c.
Example: a MySQL 5.0 DECIMAL(21,9) column containing 111222333444.555666777
looks like: hexadecimal 80 6f 0d 40 8a 04 21 1e cd 59 -- (flag + '111', '222333444', '555666777').
********************************************************************
但經過測試, 似乎是當數值<=999999999 時,我們還可以用原來的方法去讀取.
一旦數值>999999999,我們就需要按CHUNK(4個位元組)來讀取.
詳見4.2章節中的測試例項.

4.2.2 例項驗證
4.2.2.1 帶符號位的DECIMAL(M,N).
Drop table if exists heyf ;
create table heyf (id DECIMAL(4,2) ) type myisam ;
insert into heyf values (65),(-65),(23.34),(-23.34);
system hexdump /opt/mysql/data/test/heyf.MYD
----------------------------------------------
0000000 c1fd 0000 0000 fd00 ff3e 0000 0000 97fd
0000010 0022 0000 fd00 dd68 0000 0000
----------------------------------------------
ROW1: c1 00 00 00 : 1 1000001 00000000 00000000 00000000
ROW2: 3e ff 00 00 : 0 0111100 ffffffff 00000000 00000000
ROW3: 97 22 00 00 : 1 0010111 00100010 00000000 00000000
ROW4: 68 dd 00 00 : 0 1101000 11011101 00000000 00000000
------------------------------------------------------------
磁碟資料 符號 整數 小數 空閒 空閒
我們來看上面的方法進行讀取:
ROW1:
符號位 : 1,正數.
整數部分: 1000001 = 65
小數部分: 0
原值 : 65
ROW2:
符號位 : 0,負數
取反+1 : 1000100 00000000
整數部分: 1000001 = 65
小數部分: 0
原值 : -65
ROW3:
符號位 : 1,正數
整數部分: 0010111 = 23
小數部分: 00100010 = 34
原值 : 23.34
ROW4:
符號位 : 0,負數
取反+1 : 0010111 00100010
整數部分: 0010111 = 23
小數部分: 00100010 = 34
原值 : -23,34

4.2.2.2 不帶符號位的,DECIMAL(M,N)UNSIGNED.

Drop table if exists heyf ;
create table heyf (id DECIMAL(4,2) UNSIGNED ) type myisam ;
insert into heyf values (65),(23.34);
system hexdump /opt/mysql/data/test/heyf.MYD
----------------------------------------------
0000000 c1fd 0000 0000 fd00 2297 0000 0000
000000e
----------------------------------------------
ROW1: c1 00 00 00 : 1 1000001 00000000 00000000 00000000
ROW3: 97 22 00 00 : 1 0010111 00100010 00000000 00000000
------------------------------------------------------------
磁碟資料 符號 整數 小數 空閒 空閒
注意:符號位都為"1".
整數部分和小數部分,該例項與4.2.2.1中例項的取值一樣,在這裡不再贅述.


4.2.2.3 帶符號位的, DECIMAL(M)

Drop table if exists heyf ;
create table heyf (id DECIMAL(10)) type myisam ;
insert into heyf values (65),(-65 );
system hexdump /opt/mysql/data/test/heyf.MYD
----------------------------------------------
0000000 80fd 0000 4100 fd00 ff7f ffff 00be
----------------------------------------------
ROW1: 80 00 00 00 41 --&gt 10000000 00000000 00000000 00000000 01000001
ROW2: 7f ff ff ff be --&gt 01111111 11111111 11111111 11111111 10111110
到這裡,如果你認真地讀完了4.2.2.1和4.2.2.2小節,那麼下面的轉換對你來說將不再是難事了.
正數,去符號位後直接轉換成十進位制;
負數,去符號位,剩餘取反+1後,轉換成十進位制;

4.2.2.4 不帶符號位的DECIMAL(M) UNSIGNED .

Drop table if exists heyf ;
create table heyf (id DECIMAL(10) UNSIGNED ) type myisam ;
insert into heyf values (65),(200000);
system hexdump /opt/mysql/data/test/heyf.MYD
----------------------------------------------
0000000 80fd 0000 4100 fd00 0080 0d03 0040
----------------------------------------------

ROW1: 80 00 00 00 41
ROW2: 80 00 03 0d 40
在這裡用了五個位元組來表示DECIMAL(10).關於原數值,我想大家應該都能看出來了.
0X41 --&gt 65
0X30d40 --&gt 20000
4.2.2.5 超長數值的讀取(>999999999)
Drop table if exists heyf ;
create table heyf (id DECIMAL(10) UNSIGNED ) type myisam ;
insert into heyf values (999999999),(1000000000),(2147483648);
system hexdump /opt/mysql/data/test/heyf.MYD
----------------------------------------------
0000000 80fd 9a3b ffc9 fd00 0081 0000 0000 82fd
0000010 ca08 006c
----------------------------------------------
ROW1: 80 3b 9a c9 ff --&gt 10000000 00111011 10011010 11001001 11111111
ROW2: 81 00 00 00 00 --&gt 10000001 00000000 00000000 00000000 00000000
ROW2: 82 08 ca 6c 00 --&gt 10000010 00001000 11001010 01101100 00000000

試著用原來的方法將資料進行轉換:
ROW1: select conv(000000000111011100110101100100111111111,2,10) ;
--&gt 999999999 正確
ROW2: select conv(000000100000000000000000000000000000000,2,10) ;
--&gt 4294967296 與原值不符
ROW3: select conv(000001000001000110010100110110000000000,2,10) ;
--&gt 8737418240 與原值不符
其實,正如上面所說的,如果數值超過999999999,那麼需要按CHUNK(4個位元組)來讀取,並在最後將數拼起來.
比如我們讀第三行資料:
從右到左讀:
1)先讀4個位元組:00001000 11001010 01101100 00000000 --&gt 147483648
2)再讀剩餘的1個位元組:0000010 --&gt 2
把以上兩個結果拼起來: "2" || "147483648" = "2147483648" 這裡才與原值相符

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/703656/viewspace-1018472/，如需轉載，請註明出處，否則將追究法律責任。

相關文章