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部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-2939239/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL中is not null和!=null和<>null的區別MySqlNull
- IS NULL和IS NOT NULLNull
- mysql探究之null與not nullMySqlNull
- MySQL中的NULL和空串比較MySqlNull
- MySQL案例-TIMESTAMP NOT NULL與NULLMySqlNull
- 故障分析 | MySQL 遷移後 timestamp 列 cannot be nullMySqlNull
- MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！MySqlNull索引
- mysql分割槽nullMySqlNull
- NULL在oracle和mysql索引上的區別NullOracleMySql索引
- mysql 空值（null）和空字元（''）的區別MySqlNull字元
- MySQL null值儲存，null效能影響MySqlNull
- mysql 查詢欄位為null或者非nullMySqlNull
- undefined和nullUndefinedNull
- MYSQL timestamp NOT NULL插入NULL的報錯問題MySqlNull
- 關於ORACLE MYSQL NOT IN和NOT exists需要注意的 NULL值OracleMySqlNull
- 2>/dev/null和>/dev/null 2>&1和2>&1>/dev/null的區別devNull
- MySQL NOT NULL列用 WHERE IS NULL 也能查到資料的原因MySqlNull
- 為什麼索引無法使用is null和is not null索引Null
- mysql 關於exists 和in分析MySql
- not null constraint和check constriant的問題及分析NullAI
- undefined 和 null 區別？UndefinedNull
- null和undefined區別NullUndefined
- ((NULL) null).printNULL()；((NULL) null).printnull();Null
- mysql中null與“空值”的坑MySqlNull
- MySQL為何不建議使用null列MySqlNull
- mysql遇到Variable can’t be set to the value of ‘NULL’MySqlNull
- MySQL裡null與空值的辨析MySqlNull
- 你還不知道mysql中空值和null值的區別嗎？MySqlNull
- ORACLE 索引和MYSQL INNODB 輔助索引對NULL的處理區別Oracle索引MySqlNull
- Dart Sound Null Safety 深入分析DartNull
- SQL中的Null深入研究分析SQLNull
- MySQL優化表和分析表MySql優化
- TypeScript Null和Undefined 型別TypeScriptNullUndefined型別
- null 和 undefined 的區別NullUndefined
- null 和 undefined 的區別！NullUndefined
- NULL和0的區別Null
- index fast full scan 和 nullIndexASTNull
- 別名的使用和nullNull