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 |................|
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29863023/viewspace-2939239/ ，如需转载，请注明出处，否则将追究法律责任。
