耳根,国际完美世界下载

主頁 > 知識(shí)庫 > MySQL CHAR和VARCHAR存儲(chǔ)、讀取時(shí)的差別

MySQL CHAR和VARCHAR存儲(chǔ)、讀取時(shí)的差別

導(dǎo)讀

你真的知道CHAR和VARCHAR類型在存儲(chǔ)和讀取時(shí)的區(qū)別嗎？

還是先拋幾條結(jié)論吧：

1、存儲(chǔ)的時(shí)候，CHAR總是會(huì)補(bǔ)足空格后再存儲(chǔ)，不管用戶插入數(shù)據(jù)時(shí)尾部有沒有包含空格。

2、存儲(chǔ)的時(shí)候，VARCHAR不會(huì)先補(bǔ)足空格后再存儲(chǔ)，但如果是用戶在插入時(shí)特地加了空格那就會(huì)如實(shí)存儲(chǔ)，而不會(huì)給刪除。

3、讀取數(shù)據(jù)時(shí)，CHAR總是會(huì)刪除尾部空格（哪怕是寫入時(shí)包含空格）。

4、讀取數(shù)據(jù)時(shí)，VARCHAR總是如實(shí)取出之前存入的值（如果存儲(chǔ)時(shí)尾部包含空格，就會(huì)繼續(xù)保留著，不會(huì)像CHAR那樣刪除尾部空格）。

下面是測試驗(yàn)證過程。

1、測試CHAR類型

表結(jié)構(gòu)：

CREATE TABLE `tchar` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `c1` char(20) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入幾條記錄：

insert into tchar values (1, concat('a', repeat(' ',19)));
insert into tchar values (2, concat(' ', repeat('a',19)));
insert into tchar values (3, 'a');
insert into tchar values (4, ' ');
insert into tchar values (5, '');

查看存儲(chǔ)結(jié)構(gòu)：

(1) INFIMUM record offset:99 heapno:0 ...
(2) SUPREMUM record offset:112 heapno:1 ...
(3) normal record offset:126 heapno:2 ... - id=1
(4) normal record offset:169 heapno:3 ... - id=2
(5) normal record offset:212 heapno:4 ... - id=3
(6) normal record offset:255 heapno:5 ... - id=4
(7) normal record offset:298 heapno:6 ... - id=5

看到這坨東西有點(diǎn)懵是不是，還記得我給你們安利過的一個(gè)工具不，看這里：innblock | InnoDB page觀察利器。

可以看到，無論我們存儲(chǔ)多長的字符串進(jìn)去，每條記錄實(shí)際都是占用43（169-126=43）字節(jié)。由此結(jié)論1成立。
簡單說下，43字節(jié)的由來：
DB_TRX_ID, 6字節(jié)。
DB_ROLL_PTR, 7字節(jié)。
id, int, 4字節(jié)。
c1, char(20), 20字節(jié)；因?yàn)槭荂HAR類型，還需要額外1字節(jié)。
每條記錄總是需要額外5字節(jié)頭信息（row header）。
這樣總的加起來就是43字節(jié)了。

再看下讀取tchar表的結(jié)果：

select id,concat('000',c1,'$$$'),length(c1) from tchar ;
+----+----------------------------+------------+
| id | concat('000',c1,'$$$')  | length(c1) |
+----+----------------------------+------------+
| 1 | 000a$$$     |   1 | - 刪除尾部空格
| 2 | 000 aaaaaaaaaaaaaaaaaaa$$$ |   20 |
| 3 | 000a$$$     |   1 |
| 4 | 000$$$      |   0 | - 刪除尾部空格，結(jié)果和id=5一樣
| 5 | 000$$$      |   0 |
+----+----------------------------+------------+

2、測試VARCHAR類型

表結(jié)構(gòu)：

CREATE TABLE `tvarchar` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `c1` varchar(20) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入幾條記錄：

insert into tvarchar values (1, concat('a', repeat(' ',19)));
insert into tvarchar values (2, concat(' ', repeat('a',19)));
insert into tvarchar values (3, 'a');
insert into tvarchar values (4, ' ');
insert into tvarchar values (5, '');
insert into tvarchar values (6, '');

查看存儲(chǔ)結(jié)構(gòu)：

(1) INFIMUM record offset:99 heapno:0 ...
(2) SUPREMUM record offset:112 heapno:1 ...
(3) normal record offset:126 heapno:2 ... - id=1
(4) normal record offset:169 heapno:3 ... - id=2
(5) normal record offset:212 heapno:4 ... - id=3
(6) normal record offset:236 heapno:5 ... - id=4
(7) normal record offset:260 heapno:6 ... - id=5
(8) normal record offset:283 heapno:7 ... - id=6

可以看到，幾條記錄的字節(jié)數(shù)分別是：43、43、24、24、23、23（最后一條記錄和id=5那條記錄一樣）。
對上面這個(gè)結(jié)果有點(diǎn)詫異是不是，尤其是id=1的記錄（插入的是'a…后面19個(gè)空格'），居然也要消耗43字節(jié)，這就佐證了上面的結(jié)論2。
同樣的，id=3和id=4這兩條記錄都是占用24字節(jié)，而id=5和id=6這兩條記錄都是占用23字節(jié)（沒有額外存儲(chǔ)字符串的字節(jié)數(shù)，只有id列4個(gè)字節(jié)）。

再看下讀取tvarchar表的結(jié)果：

select id,concat('000',c1,'$$$'),length(c1) from tvarchar;
+----+----------------------------+------------+
| id | concat('000',c1,'$$$')  | length(c1) |
+----+----------------------------+------------+
| 1 | 000a     $$$ |   20 | - 讀取結(jié)果中沒有刪除尾部的空格
| 2 | 000 aaaaaaaaaaaaaaaaaaa$$$ |   20 |
| 3 | 000a$$$     |   1 |
| 4 | 000 $$$     |   1 | - 讀取結(jié)果中沒有刪除此空格
| 5 | 000$$$      |   0 |
| 6 | 000$$$      |   0 |
+----+----------------------------+------------+

總的來說，可以總結(jié)成兩條結(jié)論：
1、從讀取的結(jié)果來看，CHAR類型列看起來像是在存儲(chǔ)時(shí)把空格給吃了，但實(shí)際上只是在讀取時(shí)才給吃了（顯示層面上把空格刪除了）。
2、從讀取的結(jié)果來看，VARCHAR類型列看起來像是反倒保留了多余的空格，實(shí)際上也是只在讀取時(shí)才恢復(fù)這些空格（但實(shí)際物理存儲(chǔ)時(shí)還是會(huì)刪掉這些空格）。

最后，來看下文檔里怎么說的：

When CHAR values are stored, they are right-padded with spaces to the
specified length. 簡言之，CHAR列在存儲(chǔ)時(shí)尾部加空格補(bǔ)齊長度。

When CHAR values are retrieved, trailing spaces are removed unless the
PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
簡言之，CHAR列在讀取時(shí)會(huì)去掉尾部空格，除非設(shè)置sql_mode值PAD_CHAR_TO_FULL_LENGTH=1。

VARCHAR values are not padded when they are stored.
簡言之，存VARCHAR時(shí)尾部不加空格。

Trailing spaces are retained when values are stored and retrieved, in
conformance with standard SQL. 簡言之，讀取VARCHAR時(shí)會(huì)顯示空格。

以上測試使用的版本及環(huán)境：

mysql> select version()\G
...
version(): 8.0.15

mysql> select @@sql_mode\G
...
@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

參考文檔

11.4.1 The CHAR and VARCHAR Types，https://dev.mysql.com/doc/refman/5.7/en/char.html

以上就是MySQL CHAR和VARCHAR存儲(chǔ)的差別的詳細(xì)內(nèi)容，更多關(guān)于MySQL CHAR和VARCHAR的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:

MySQL CHAR和VARCHAR該如何選擇
Mysql中varchar類型一些需要注意的地方
MYSQL中 char 和 varchar的區(qū)別
MySQL中字段類型char、varchar和text的區(qū)別
mysql varchar類型求和實(shí)例操作
對比MySQL中int、char以及varchar的性能
MySQL動(dòng)態(tài)修改varchar長度的方法
Mysql中varchar長度設(shè)置方法
Mysql數(shù)據(jù)庫中把varchar類型轉(zhuǎn)化為int類型的方法
mysql如何處理varchar與nvarchar類型中的特殊字符
當(dāng)面試官問mysql中char與varchar的區(qū)別

標(biāo)簽：阿里合肥沈陽呼和浩特天津牡丹江公主嶺惠州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL CHAR和VARCHAR存儲(chǔ)、讀取時(shí)的差別》，本文關(guān)鍵詞 MySQL,CHAR,和,VARCHAR,存儲(chǔ),；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。