Home > Net >  length of varchar store in mysql
length of varchar store in mysql

Time:07-01

Sorry, English isn't my first language... :(

I want to learn the actual length of varchar how to store in mysql .ibd file. So I deside to make a test like this:

CREATE TABLE record_format_demo (
         c1 VARCHAR(10),
        c2 VARCHAR(10) NOT NULL,
         c3 CHAR(10),
         c4 VARCHAR(10)
     ) CHARSET=ascii ROW_FORMAT=COMPACT;

INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd');

SELECT * FROM record_format_demo;
 ------ ----- ------ ------ 
| c1   | c2  | c3   | c4   |
 ------ ----- ------ ------ 
| aaaa | bbb | cc   | d    |
 ------ ----- ------ ------ 

and I open ibd file by ultraEdit, the varchar length in hex look like this: enter image description here

  • HEX 01 is c4's length, decimal is 1
  • HEX 03 is c2's length, decimal is 3
  • HEX 04 is c1's length , decimal is 4

but these column's value is too short, just need one byte can count, so I do another test like this :

CREATE TABLE record_format_demo2 (
         c1 VARCHAR(10),
        c2 VARCHAR(256) NOT NULL,
         c3 CHAR(10),
         c4 VARCHAR(357)
     ) CHARSET=ascii ROW_FORMAT=COMPACT;
         
         
INSERT INTO record_format_demo2(c1, c2, c3, c4) VALUES('aaaa', REPEAT('a',127), 'cc', REPEAT('a', 356));

and it's ibd file like this :

enter image description here

  • HEX 04 is c1's length ,because 'aaaa' only have 4 char
  • HEX 7F is c2's length, decimal is 127, because c2 = REPEAT('a',127)

But hex of C4's length is 64 81 ,it's decimal is 25729 . More than 356. I try to plus two HEX ,But 64 81 = E5 which decimal is 229, not equal 356.

It make me confuse , So I want to know why hex of c4 show like this., and how can I figure out 356

CodePudding user response:

Perhaps this:

  • x81 is 01 with the high bit on
  • The high bit says "this number is continued"
  • The continuation is x64
  • Putting the pieces together gives x0164 = decimal 356

Have you found where/how the NULL flags show up?

  • Related