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'
可以看到重複的行