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 --> 65
0X30d40 --> 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 --> 10000000 00111011 10011010 11001001 11111111
ROW2: 81 00 00 00 00 --> 10000001 00000000 00000000 00000000 00000000
ROW2: 82 08 ca 6c 00 --> 10000010 00001000 11001010 01101100 00000000
试着用原来的方法将数据进行转换:
ROW1: select conv(000000000111011100110101100100111111111,2,10) ;
--> 999999999 正确
ROW2: select conv(000000100000000000000000000000000000000,2,10) ;
--> 4294967296 与原值不符
ROW3: select conv(000001000001000110010100110110000000000,2,10) ;
--> 8737418240 与原值不符
其实,正如上面所说的,如果数值超过999999999,那么需要按CHUNK(4个字节)来读取,并在最后将数拼起来.
比如我们读第三行数据:
从右到左读:
1)先读4个字节:00001000 11001010 01101100 00000000 --> 147483648
2)再读剩余的1个字节:0000010 --> 2
把以上两个结果拼起来: "2" || "147483648" = "2147483648" 这里才与原值相符
