mysql highest bit Whether is 1 or 0 , when column is designed signed int

Question

Why the highest bit is 0, when negative number is stored ?

1. The following is my SQL demo:

create table test
(
    a int not null primary key,
    b varchar(20) not null
);

insert into test values (-4, 'aaa');
insert into test values (5,  'bbb');

2. trans test.ibd to txt via hexdump

[root@xxxxx test2]# hexdump -C -v /usr/local/mysql/data/test2/test.ibd > test.txt

3. /usr/local/mysql/data/test2/test.txt

4. So, why the highest bit is 0, when negative number is stored.

   I confused that it's different to C/C++.

5. Other info

   mysql version: 5.8 engine: innodb

Answer 1

First of all, your create table code doesn't match your data, you need to define the column as signed, not unsigned (otherwise you obviously cannot store negative values).

InnoDB stores positive signed integers by setting the most significant bit: note that in your example, the value 5 is stored as 80 00 00 05, not as 00 00 00 05. Negative values still use the complement representation, but with an unset most significant bit.

So to get from the byte value (interpreted as an unsigned int) to the signed value, you could think of it as substracting the offset 80 00 00 00 from it. E.g. 7f ff ff fc as an unsigned int would be 2 147 483 644, subtracting 2 147 483 648 would be -4. Additionally, if you order them just by bytes, you get 7f ff ff fc (-4) ... 7f ff ff ff (-1) ... 80 00 00 00 (0) ... 80 00 00 05 (5).

Note that unsigned integers are stored as usual (e.g. 5 is stored as 00 00 00 05).