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)

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> desc testbin3;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name1 | varbinary(10) | YES  |     | NULL    |       |
| name2 | binary(10)    | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 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存儲方式是怎樣的問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。