MYSQL的CHAR和VARCHAR注意事项以及binary和varbinary存储方式是怎样的

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

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