INNODB:

CREATE TABLE `test2` (

`col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

`col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

`col3` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL

) ENGINE = INNODB ;

INSERT INTO `test` VALUES (null,1);

INSERT INTO `test2` VALUES ('aaaaaaaa','','ccccccc');

INSERT INTO `test2` VALUES ('aaaaaaaa', NULL,'ccccccc');

mysql> select * from test;

+------+------+

| col1 | col2 |

+------+------+

| | 1 |

| | NULL |

| 1 | 2 |

+------+------+

3 rows in set (0.00 sec)

mysql>

select col1,length(col1),col2,length(col2) from test;

+------+--------------+------+--------------+

| col1 | length(col1) | col2 | length(col2) |

+------+--------------+------+--------------+

| | 0 | 1 | 1 |

| 1 | 1 | 2 | 1 |

+------+--------------+------+--------------+

select col1,hex(col1),col2,hex(col2) from test1;

查看页结构：

python py_innodb_page_info.py -v /data/mysql/cwdtest/test2.ibd

page offset 00000000, page type

page offset 00000001, page type

page offset 00000002, page type

page offset 00000003, page type , page level <0000>

page offset 00000000, page type

Total number of page: 6:

Freshly Allocated Page: 2

Insert Buffer Bitmap: 1

File Space Header: 1

B-tree Node: 1

File Segment inode: 1

对该表数据文件做个hexdump：

hexdump -C -v /data/mysql/cwdtest/test2.ibd >/tmp/udb.txt

page type , page level <0000> 从第四个页开始，从hexdump中可找到相应的位置0x0000c000开始，16k*3=49152=0x0000c000

0000c000 c6 66 c2 4a 00 00 00 03 ff ff ff ff ff ff ff ff |.f.J............|

0000c010 00 00 00 00 00 28 69 70 45 bf 00 00 00 00 00 00 |.....(ipE.......|

0000c020 00 00 00 00 00 1f 00 02 00 cd 80 04 00 00 00 00 |................|

0000c030 00 ab 00 02 00 01 00 02 00 00 00 00 00 00 00 00 |................|

0000c040 00 00 00 00 00 00 00 00 00 31 00 00 00 1f 00 00 |.........1......|

0000c050 00 02 00 f2 00 00 00 1f 00 00 00 02 00 32 01 00 |.............2..|

0000c060 02 00 1e 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00 |...infimum......|

0000c070 73 75 70 72 65 6d 75 6d 07 00 08 00 00 00 10 00 |supremum........|

0000c080 2a 00 00 00 00 02 13 00 00 00 00 0b 4c bd 00 00 |*...........L...|

0000c090 01 37 01 10 61 61 61 61 61 61 61 61 63 63 63 63 |.7..aaaaaaaacccc|

0000c0a0 63 63 63 07 08 01 00 00 18 ff c5 00 00 00 00 02 |ccc.............|

0000c0b0 14 00 00 00 00 0b 4d be 00 00 01 39 01 10 61 61 |......M....9..aa|

0000c0c0 61 61 61 61 61 61 63 63 63 63 63 63 63 00 00 00 |aaaaaaccccccc...|

0000c0d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c0e0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c100 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

mysql> select col1,hex(col1),col2,hex(col2),col3,hex(col3) from test2;

+----------+------------------+------+-----------+---------+----------------+

| col1 | hex(col1) | col2 | hex(col2) | col3 | hex(col3) |

+----------+------------------+------+-----------+---------+----------------+

| aaaaaaaa | 6161616161616161 | | | ccccccc | 63636363636363 |

+----------+------------------+------+-----------+---------+----------------+

61 61 61 61 61 61 61 61 63 63 63 63 63 63 63

从上面col,col2,col3字段内容aaaaaaaaccccccc可以看出，在INNODB数据文件中，不管存放的是null还是'',col2字段并没有占用任何字符。

MYISAM:

CREATE TABLE `test3` (

`col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

`col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

`col3` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL

) ENGINE = MYISAM ;

INSERT INTO `test3` VALUES ('aaaaaaaa','','ccccccc');

INSERT INTO `test3` VALUES ('aaaaaaaa', NULL,'ccccccc');

INSERT INTO `test3` VALUES ('aaaaaaaa', NULL,'');

select col1,hex(col1),col2,hex(col2),col3,hex(col3) from test3;

+----------+------------------+------+-----------+---------+----------------+

| col1 | hex(col1) | col2 | hex(col2) | col3 | hex(col3) |

+----------+------------------+------+-----------+---------+----------------+

| aaaaaaaa | 6161616161616161 | | | ccccccc | 63636363636363 |

+----------+------------------+------+-----------+---------+----------------+

2 rows in set (0.00 sec)

[root@cwdtest2 innodb]# hexdump -C -v /data/mysql/cwdtest/test3.MYD

00000000 03 00 13 01 fc 08 61 61 61 61 61 61 61 61 00 07 |......aaaaaaaa..|

00000010 63 63 63 63 63 63 63 00 03 00 13 01 fd 08 61 61 |ccccccc.......aa|

00000020 61 61 61 61 61 61 00 07 63 63 63 63 63 63 63 00 |aaaaaa..ccccccc.|

00000030

而MYISAM文件中是aaaaaaaa..ccccccc，相对应的16进制码是61 61 61 61 61 61 61 61 00 07 63 63 63 63 63 63 63

可以看到在col1和col3之间存在 00 07的字符

CREATE TABLE `test4` (

`col1` CHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

`col2` CHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

`col3` CHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL

) ENGINE = INNODB ;

INSERT INTO `test4` VALUES ('aaaaaaaa','','ccccccc');

INSERT INTO `test4` VALUES ('aaaaaaaa', NULL,'ccccccc');

0000bfd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000bfe0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000bff0 00 00 00 00 00 00 00 00 19 45 b3 a5 00 28 85 c1 |.........E...(..|

0000c000 c9 db 1e 30 00 00 00 03 ff ff ff ff ff ff ff ff |...0............|

0000c010 00 00 00 00 00 28 96 f2 45 bf 00 00 00 00 00 00 |.....(..E.......|

0000c020 00 00 00 00 00 20 00 02 00 e1 80 04 00 00 00 00 |..... ..........|

0000c030 00 ba 00 02 00 01 00 02 00 00 00 00 00 00 00 00 |................|

0000c040 00 00 00 00 00 00 00 00 00 32 00 00 00 20 00 00 |.........2... ..|

0000c050 00 02 00 f2 00 00 00 20 00 00 00 02 00 32 01 00 |....... .....2..|

0000c060 02 00 1e 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00 |...infimum......|

0000c070 73 75 70 72 65 6d 75 6d 0a 0a 0a 00 00 00 10 00 |supremum........|

0000c080 39 00 00 00 00 02 15 00 00 00 00 0b 56 c5 00 00 |9...........V...|

0000c090 01 50 01 10 61 61 61 61 61 61 61 61 20 20 20 20 |.P..aaaaaaaa |

0000c0a0 20 20 20 20 20 20 20 20 63 63 63 63 63 63 63 20 | ccccccc |

0000c0b0 20 20 0a 0a 01 00 00 18 ff b6 00 00 00 00 02 16 | ..............|

0000c0c0 00 00 00 00 0b 57 c6 00 00 01 51 01 10 61 61 61 |.....W....Q..aaa|

0000c0d0 61 61 61 61 61 20 20 63 63 63 63 63 63 63 20 20 |aaaaa ccccccc |

0000c0e0 20 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | ...............|

0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c000 8c 69 ae 6a 00 00 00 03 ff ff ff ff ff ff ff ff |.i.j............|

0000c010 00 00 00 00 00 29 6a a5 45 bf 00 00 00 00 00 00 |.....)j.E.......|

0000c020 00 00 00 00 00 21 00 02 00 e7 80 05 00 00 00 00 |.....!..........|

0000c030 00 c4 00 02 00 00 00 03 00 00 00 00 00 00 00 00 |................|

0000c040 00 00 00 00 00 00 00 00 00 33 00 00 00 21 00 00 |.........3...!..|

0000c050 00 02 00 f2 00 00 00 21 00 00 00 02 00 32 01 00 |.......!.....2..|

0000c060 02 00 1d 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|

0000c070 73 75 70 72 65 6d 75 6d 08 00 00 00 00 10 00 22 |supremum......."|

0000c080 00 00 00 00 02 0d 00 00 00 00 0b 36 ab 00 00 01 |...........6....|

0000c090 22 01 10 61 61 61 61 61 61 61 61 07 01 00 00 18 |"..aaaaaaaa.....|

0000c0a0 00 22 00 00 00 00 02 0e 00 00 00 00 0b 37 ac 00 |."...........7..|

0000c0b0 00 01 23 01 10 63 63 63 63 63 63 63 08 08 00 20 |..#..ccccccc... |

0000c0c0 00 20 ff ac 00 00 00 00 02 0f 00 00 00 00 0b a1 |. ..............|

0000c0d0 76 00 00 01 72 01 10 68 68 68 68 68 68 68 68 78 |v...r..hhhhhhhhx|

0000c0e0 78 78 78 78 78 78 78 00 00 00 00 00 00 00 00 00 |xxxxxxx.........|

0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c100 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c110 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

mysql> optimize table test;

+--------------+----------+----------+-------------------------------------------------------------------+

+--------------+----------+----------+-------------------------------------------------------------------+

+--------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (0.03 sec)

mysql> analyze table test;

+--------------+---------+----------+----------+

+--------------+---------+----------+----------+

+--------------+---------+----------+----------+

0000c000 f0 d3 26 ea 00 00 00 03 ff ff ff ff ff ff ff ff |..&.............|

0000c010 00 00 00 00 00 29 7b ce 45 bf 00 00 00 00 00 00 |.....){.E.......|

0000c020 00 00 00 00 00 24 00 02 00 bc 80 04 00 00 00 00 |.....$..........|

0000c030 00 a2 00 02 00 00 00 02 00 00 00 00 00 00 00 00 |................|

0000c040 00 00 00 00 00 00 00 00 00 36 00 00 00 24 00 00 |.........6...$..|

0000c050 00 02 00 f2 00 00 00 24 00 00 00 02 00 32 01 00 |.......$.....2..|

0000c060 02 00 1d 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00 |...infimum......|

0000c070 73 75 70 72 65 6d 75 6d 08 00 00 00 00 10 00 22 |supremum......."|

0000c080 00 00 00 00 02 0d 00 00 00 00 0b 36 ab 00 00 01 |...........6....|

0000c090 22 01 10 61 61 61 61 61 61 61 61 07 01 00 00 18 |"..aaaaaaaa.....|

0000c0a0 ff ce 00 00 00 00 02 0e 00 00 00 00 0b 37 ac 00 |.............7..|

0000c0b0 00 01 23 01 10 63 63 63 63 63 63 63 00 00 00 00 |..#..ccccccc....|

0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c0d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c0e0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

0000c100 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

MySQL null和''分析