MySQL null和''分析

darren__chan發表於2023-03-12
MySqlNull

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部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-2939239/，如需轉載，請註明出處，否則將追究法律責任。

相關文章