MySQL null和''分析

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 | |      |            0 | NULL |         NULL | | 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 <File Space Header> page offset 00000001, page type <Insert Buffer Bitmap> page offset 00000002, page type <File Segment inode> page offset 00000003, page type <B-tree Node>, page level <0000> page offset 00000000, page type <Freshly Allocated Page> page offset 00000000, page type <Freshly Allocated Page> 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 <B-tree Node>, 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 | | aaaaaaaa | 6161616161616161 | NULL | NULL      | 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 | | aaaaaaaa | 6161616161616161 | NULL | NULL      | 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; +--------------+----------+----------+-------------------------------------------------------------------+ | Table        | Op       | Msg_type | Msg_text                                                          | +--------------+----------+----------+-------------------------------------------------------------------+ | cwdtest.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead | | cwdtest.test | optimize | status   | OK                                                                | +--------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.03 sec) mysql> analyze table test; +--------------+---------+----------+----------+ | Table        | Op      | Msg_type | Msg_text | +--------------+---------+----------+----------+ | cwdtest.test | analyze | status   | OK       | +--------------+---------+----------+----------+ 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  |................|