SourceURL:file:///home/cheng/bb/artical/BB系列文章002_PostgreSQL深度探秘_整數(shù).docx
BB系列文章002
PostgreSQL深度探秘：整數(shù)
SourceURL:file:///home/cheng/bb/artical/BB系列文章002_PostgreSQL深度探秘_整數(shù).docx
注：本文中使用的導(dǎo)出PostgreSQL表數(shù)據(jù)的工具pgexp和查看導(dǎo)出結(jié)果的工具bb可以在https://gitee.com/cheng719/bb.git下載， 包含了在linux下運(yùn)行的這些工具和說(shuō)明文檔者吁，如果需要其它平臺(tái)（例如AIX复凳、HPUX、Solaris等）(未支持這些平臺(tái)的原因手頭沒(méi)有編譯環(huán)境）育八，請(qǐng)聯(lián)系作者abusoft@sina.com。
如果要實(shí)現(xiàn)捕獲PostgreSQL數(shù)據(jù)庫(kù)的變化數(shù)據(jù)（CDC）以便用來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)的容災(zāi)掀宋、備份劲妙、復(fù)用等儒喊，需要掌握數(shù)據(jù)庫(kù)各種數(shù)據(jù)類型在磁盤(pán)里面實(shí)際存儲(chǔ)的內(nèi)容。
[if !supportLists]1.?[endif]PostgreSQL整數(shù)包含如下幾種類型：
整數(shù)類型存儲(chǔ)長(zhǎng)度描述范圍
smallint2小整數(shù)-32768~+32767
integer4整數(shù)-2147483648 to +2147483647
bigint8長(zhǎng)整數(shù)-9223372036854775808 to +9223372036854775807
smallserial2小自增整數(shù)1 to 32767
serial4自增整數(shù)1 to 2147483647
bigserial8長(zhǎng)自增整數(shù)1 to 9223372036854775807
[if !supportLists]2.?[endif]建立測(cè)試表侨颈，并且插入數(shù)據(jù)：
create table test_int (
f_smallint smallint,
f_int integer,
f_big bigint,
f_smallserial smallserial,
f_serial serial,
f_bigserial bigserial );
insert into test_int values ( 100,101, 102, 103, 104,105 );
insert into test_int values ( 1000,1010, 1020, 1030, 1040,1050 );
insert into test_int values ( 10000,10100, 10200, 10300, 10400,10500 );
[if !supportLists]3.?[endif]使用工具pgexp導(dǎo)出表的數(shù)據(jù)：
$ pgexp -o /tmp/11 -t public.test_int
1118221628[5] Export: bank.public.test_int
1118221628[5] OUT: writedSize=??143??rows=??3?dataSize=??143
Export: rows=3, Length=552
導(dǎo)出了3條記錄哈垢，結(jié)果存放在文件 /tmp/11耘分，文件長(zhǎng)度552绑警。
[if !supportLists]4.?[endif]使用工具bb查看導(dǎo)出的表的數(shù)據(jù)：
$ bb -3 /tmp/11
create table bank.public.test_int (
????f_smallint???int2
?,?f_int????????int4
?,?f_big????????int8
?,?f_smallserial smallserial not null
?,?f_serial?????serial not null
?,?f_bigserial??bigserial not null
?);
insert into bank.public.test_int?(f_smallint,f_int,f_big,f_smallserial,f_serial,f_bigserial) values?(100,101,102,103,104,105),
(1000,1010,1020,1030,1040,1050),
(10000,10100,10200,10300,10400,10500);
[if !supportLists]5.?[endif]使用工具bb查看導(dǎo)出的表的數(shù)據(jù)的內(nèi)部表示方法：
$bb -2 /tmp/11
???R000 CC [????1]: 06
??????c0_0 [????2]: 0064???????????????????????????????????.d????????????????
??????c0_1 [????4]: 0000 0065??????????????????????????????...e??????????????
??????c0_2 [????8]: 0000 0000 0000 0066????????????????????.......f??????????
??????c0_3 [????2]: 0067???????????????????????????????????.g????????????????
??????c0_4 [????4]: 0000 0068??????????????????????????????...h??????????????
??????c0_5 [????8]: 0000 0000 0000 0069????????????????????.......i??????????
???R001 CC [????1]: 06
??????c1_0 [????2]: 03e8???????????????????????????????????..????????????????
??????c1_1 [????4]: 0000 03f2??????????????????????????????....??????????????
??????c1_2 [????8]: 0000 0000 0000 03fc????????????????????........??????????
??????c1_3 [????2]: 0406???????????????????????????????????..????????????????
??????c1_4 [????4]: 0000 0410??????????????????????????????....??????????????
??????c1_5 [????8]: 0000 0000 0000 041a????????????????????........??????????
???R002 CC [????1]: 06
??????c2_0 [????2]: 2710???????????????????????????????????'.????????????????
??????c2_1 [????4]: 0000 2774??????????????????????????????..'t??????????????
??????c2_2 [????8]: 0000 0000 0000 27d8????????????????????......'.??????????
??????c2_3 [????2]: 283c???????????????????????????????????(<????????????????
??????c2_4 [????4]: 0000 28a0??????????????????????????????..(.??????????????
??????c2_5 [????8]: 0000 0000 0000 2904????????????????????......).????
[if !supportLists]6.?[endif]數(shù)據(jù)類型為?smallint渴频、smallserial 的內(nèi)部表示：
數(shù)值數(shù)據(jù)庫(kù)文件內(nèi)部存儲(chǔ)
1000x0064
1030x0067
10000x03e8
10300x0406
100000x2710
103000x283c
即Postgresql存儲(chǔ)smallint卜朗、smallserial的方法就是按照大頭優(yōu)先( big endian)存儲(chǔ)2字節(jié)的小整數(shù)內(nèi)存變量到數(shù)據(jù)庫(kù)磁盤(pán)文件中聊替。
[if !supportLists]7.?[endif]數(shù)據(jù)類型為?integer培廓、serial?的內(nèi)部表示:
數(shù)值數(shù)據(jù)庫(kù)文件內(nèi)部存儲(chǔ)
1010x00000065
1040x00000068
10100x000003f2
10400x00000410
101000x00002774
104000x000028a0
即Postgresql存儲(chǔ)integer、serial的方法就是按照大頭優(yōu)先( big endian)存儲(chǔ)4字節(jié)的整數(shù)內(nèi)存變量到數(shù)據(jù)庫(kù)磁盤(pán)文件中泣港。
[if !supportLists]8.?[endif]數(shù)據(jù)類型為?bigint、bigserial?的內(nèi)部表示:
數(shù)值數(shù)據(jù)庫(kù)文件內(nèi)部存儲(chǔ)
1020x0000000000000066
1050x0000000000000069
10200x00000000000003fc
10500x000000000000041a
102000x00000000000027d8
105000x0000000000002904
即Postgresql存儲(chǔ)bigint呛每、bigserial的方法就是按照大頭優(yōu)先( big endian)存儲(chǔ)8字節(jié)的整數(shù)內(nèi)存變量到數(shù)據(jù)庫(kù)磁盤(pán)文件中坡氯。
