MYSQL的CHAR和VARCHAR注意事項以及binary和varbinary存儲方式是怎樣的

139次閱讀

共計 7037 個字符，預計需要花費 18 分鐘才能閱讀完成。

行業資訊
數據庫
MySQL 數據庫
MYSQL 的 CHAR 和 VARCHAR 注意事項以及 binary 和 varbinary 存儲方式是怎樣的

MYSQL 的 CHAR 和 VARCHAR 注意事項以及 binary 和 varbinary 存儲方式是怎樣的，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

版本使用 5.7 存儲引擎 INNODB 行格式 Dynamic
從概念上講他們用于存放字符型數據，其允許的范圍：
1、char 0-255 bytes，固定長度，不夠的長度用 ASCII 0X20 進行補足。
2、varchar 0-65535(2^8-1)bytes, 注意是整個表所有的 varchar 字段的長度，所謂可變長度，就是按需分配空間。

下面就幾個問題進行討論：

第一個問題：varchar 的可變長度
那么這里引出了第一個問題，可變長度，在 INNODB(Dynamic 行格式)，在行頭使用，1- 2 個字節
來存儲這個可變長度及：
variable field lengths (1-2 bytes* var)
(具體參考 http://blog.itpub.net/7728585/viewspace-2071787/)
2 個字節也剛好是 65535 的長度，這是 INNODB 對 MYSQL 的一個實現方法，同時如果使用 5.7 INNODB
online DDL 進行 modify varchar column 的長度，在 1 -255 和 256-65535 之間都可以迅速完成，但是
如果跨越比如改變一個 varchar 字段的長度從 250 到 300 注意是字節，就會出現需要使用
inpace 或者 copy 等方法，那就非常慢了，原因也在這里因為涉及到行頭的擴張了，以前是一
個字節來存儲長度，而改變后需要二個字節，當然也就需要重新組織表，而如果不跨越就不
會改變表的組織方式，也就值需要修改數據字典和 frm 文件而已，當然瞬間完成，下面來做
一個測試。對于 UTF8 字符集，它的這個點是 255/3=85。
注意使用版本 5.7 引擎為 innodb 行格式為 Dynamic，并且這一列不能有索引，如果有索引
索引會帶入而外的操作，也是比較慢的
mysql select count(*) from testshared3;
+———-+
| count(*) |
+———-+
| 1048576 |
+———-+
1 row in set (0.35 sec)

mysql show create table testshared3;
+————-+———————————————————————————————————————————-+
| Table | Create Table |
+————-+———————————————————————————————————————————-+
| testshared3 | CREATE TABLE `testshared3` (
`id` int(11) DEFAULT NULL,
`name` varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+————-+———————————————————————————————————————————-+
1 row in set (0.01 sec)

mysql alter table testshared3 change name name varchar(85) ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

可以看到雖然有 1048576 行的數據但是 modify 還是瞬間完成了。但是如果從 85 改到 86 如何呢？
mysql alter table testshared3 ALGORITHM=INPLACE, change name name varchar(86) ;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql alter table testshared3 change name name varchar(86) ;
Query OK, 1048576 rows affected (15.68 sec)
Records: 1048576 Duplicates: 0 Warnings: 0
可以看到使用了 15 秒多，而且 ALGORITHM=COPY。

第二個問題：關于 char 和 varchar 左空格存儲以及顯示的不同

mysql create table testvc(name1 varchar(5),name2 char(5));
Query OK, 0 rows affected (0.08 sec)

mysql insert into testvc values(gao , gao
Query OK, 1 row affected (0.01 sec)
mysql select concat(name1,) ),concat(name2,) ) from testvc;
+——————-+——————-+
| concat(name1,) ) | concat(name2,) ) |
+——————-+——————-+
| gao ) | gao) |
+——————-+——————-+
1 row in set (0.06 sec)
可以看到 varchar 可以正常顯示 gao 后面的空格，而 char 卻不行，那么他們內部到底是如何存儲的，我們需要
用二進制方式查看一下：
(下面是我解析好的，具體的方法參考 http://blog.itpub.net/7728585/viewspace-2071787/ 需要用到我自己
寫的幾個小工具)
04 –varchar 長度
00 –NULL 位圖
00
0010
0024
00000089a25
40000002e0c1bd9
000001640110
67616f20 –varchar(5) gao
67616f2020 –char(5) gao

這里我們可以明顯看到 varchar 的長度為 4，當存儲 varchar 的 gao 的時候存儲的是 0X67616f20 也就是 ASCII 的 gao , 當存儲 char 類型的 gao
的時候為 0X67616f2020，可以看到他后面有兩個 0X20，也就是 ASCII 的空格那么我們可以知道 char(5)會對不夠的字節全部補上 ASCII 0X20，這也就是
為什么輸出的時候空格不在了，因為了 char 字段中存儲的時候尾部的 0X20 作為了補足的字符，而 VARCHAR 中卻不是這樣 0X20 作為了實際的字符，也就
是空格那么輸出就有了。

第三個問題：比較和 varchar 以及 char 尾部的空格。
在 MYSQL 文檔中描述：
This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces
“Comparison”in this context does not include the LIKEpattern-matching operator
For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has
an index that requires unique values, inserting into the column values that differ only in number of trailing
pad characters will result in a duplicate-key error.

也就是除了 LIKE 的比較操作，都會忽略尾部空格不管是 VARCHAR CHAR 還是 TEXT，并且如果字段是唯一鍵，唯一性判斷的時候
也會忽略空格。

還是剛才的表我們在 varchar 的 name1 上加上唯一索引。
mysql alter table testvc add unique key(name1);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

我們剛才插入的數據為
insert into testvc values(gao , gao

mysql select * from testvc where name1= gao
+——-+——-+
| name1 | name2 |
+——-+——-+
| gao | gao |
+——-+——-+
1 row in set (0.00 sec)

可以看到不管存儲有沒有 0X20 空格，也不管條件 = 后面是否有 0X20 空格
都會查詢出來，我們來測試一下插入

mysql insert into testvc values(gao , gao10
ERROR 1062 (23000): Duplicate entry gao for key name1
mysql insert into testvc values(gao , gao10
ERROR 1062 (23000): Duplicate entry gao for key name1

不管我插入的是 gao 還是 gao 都是重復的值，證明的文檔的說法，另外
這個問題在 ORACLE 中是不存在，MYSQL 也比較奇怪。很多 ORACLE 的概念在 MYSQL
中需要打一個問號。
ORACLE:
SQL create table testui1(name varchar2(20));
Table created
SQL create unique index testuiq_IDX on testui1(name);
Index created
SQL insert into testui1 values(gao
1 row inserted
SQL insert into testui1 values(gao
1 row inserted
SQL insert into testui1 values(gao
1 row inserted
SQL commit;
Commit complete

接下來看看 LIKE：
varchar：
mysql select * from testvc where name1 like gao %
+——-+——-+
| name1 | name2 |
+——-+——-+
| gao | gao |
+——-+——-+
1 row in set (0.00 sec)

mysql select * from testvc where name1= gao
+——-+——-+
| name1 | name2 |
+——-+——-+
| gao | gao |
+——-+——-+
1 row in set (0.00 sec)

char：
mysql select * from testvc where name2 like gao %
Empty set (0.00 sec)

mysql select * from testvc where name2= gao
+——-+——-+
| name1 | name2 |
+——-+——-+
| gao | gao |
+——-+——-+
1 row in set (0.00 sec)

這里 char name2 like gao % 沒有出來數據，而 varchar name1 like gao %
出來了數據也正是證明了我們對存儲格式的剖析，因為 char 對尾部的 0X20 空格在
存儲的時候已經去掉了，但是 VARCHAR 沒有，只要 LIKE 是嚴格匹配就會出來這樣的
效果。

最后來看看 MYSQL 的 binary 和 varbinary 格式，這種格式就是說明其存儲和比較都使用二進制格式，也就是按照一個
字節一個字節的比較 ASCII 值，官方描述如下：
they contain byte strings rather than character strings. This
means that they have no character set, and sorting and comparison are based on the numeric values of
the bytes in the values.
他們的意義和 char 和 varchar 相似，但是有一點，其比較方法和存儲方法不太一樣
binary 使用 0X00 也就是 \0 補足不夠的字節，而其比較也是嚴格和存儲中的格式進行
匹配不存在 char 和 varchar 那樣對空格的處理

mysql insert into testbin3 values(a , a
Query OK, 1 row affected (0.03 sec)

mysql select * from testbin3 where name1= a
+——-+————+
| name1 | name2 |
+——-+————+
| a | a |
+——-+————+
1 row in set (0.00 sec)

mysql select * from testbin3 where name2= a
Empty set (0.00 sec)

mysql select * from testbin3 where name2= a \0\0\0\0\0\0\0\0
+——-+————+
| name1 | name2 |
+——-+————+
| a | a |
+——-+————+
1 row in set (0.00 sec)

可以看到 varbinary 使用 a 可以查詢到記錄但是 binary 使用 a 不能查到，為什么呢？
我們看看他的內部存儲
00000089a25f
0000002e0c66bc
0000012a0110

6120 –binary a
612000000000000000 –varbinary a

可以看到 varbinary 使用 8 個 0X00 進行補足，既然他嚴格按照而進行進行匹配那么我們這樣可以
查出數據：

mysql select * from testbin3 where name2= a \0\0\0\0\0\0\0\0
+——-+————+
| name1 | name2 |
+——-+————+
| a | a |
+——-+————+
1 row in set (0.00 sec)
當然 unique 也是嚴格按照而進行進行比較
增加一個 unique key 在 binary 上
mysql alter table testbin3 add unique key(name2);
mysql insert into testbin3 values(a , a \0\0\0\0\0\0\0\0
ERROR 1062 (23000): Duplicate entry a for key name2
可以看到重復的行

關于 MYSQL 的 CHAR 和 VARCHAR 注意事項以及 binary 和 varbinary 存儲方式是怎樣的問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。

正文完