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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 版本使用5.7 存储引擎INNODB 行格式Dynamic 从概念上讲他们用于存放字符型数据,其允许的范围: 1、char 0-255 bytes,固定长度,不够的长度用ASCII 0X20进行补足。
版本使用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'
可以看到重复的行

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
4月前
|
存储 关系型数据库 MySQL
【免费动手教程上线】阿里云RDS MySQL推出大容量高性能存储:高性能本地盘(最高16TB存储空间)、高性能云盘(最高64TB存储空间)
阿里云RDS MySQL提供高性能本地盘与高性能云盘等存储方案,满足用户大容量、低延迟需求。高性能本地盘单盘最大16TB,IO延时微秒级;高性能云盘兼容ESSD特性,支持IO性能突发、BPE及16K原子写等能力。此外,阿里云还提供免费动手体验教程,帮助用户直观感受云数据库 RDS 存储性能表现。
|
6月前
|
存储 SQL 关系型数据库
【YashanDB知识库】MySQL迁移至崖山char类型数据自动补空格问题
**简介**:在MySQL迁移到崖山环境时,若字段类型为char(2),而应用存储的数据仅为'0'或'1',查询时崖山会自动补空格。原因是mysql的sql_mode可能启用了PAD_CHAR_TO_FULL_LENGTH模式,导致保留CHAR类型尾随空格。解决方法是与应用确认数据需求,可将崖山环境中的char类型改为varchar类型以规避补空格问题,适用于所有版本。
|
7月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(09)数据磁盘存储模型 | 一行数据怎么存?
文中详细介绍了MySQL数据库中一行数据在磁盘上的存储机制,包括表空间、段、区、页和行的具体结构,以及如何设计和优化行数据存储以提高性能。
|
7月前
|
存储 SQL 关系型数据库
【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
问题分类】功能使用 【关键字】char,char(1) 【问题描述】MySQL 迁移至崖山环境,字段类型源端和目标端都为 char(2),但应用存储的数据为'0'、'1',此时崖山查询该表字段时会自动补充空格 【问题原因分析】mysql 有 sql_mode 控制,检查是否启用了 PAD_CHAR_TO_FULL_LENGTH SQL 模式。如果启用了这个模式,MySQL 才会保留 CHAR 类型字段的尾随空格,默认没有启动。 #查看sql_mode mysql> SHOW VARIABLES LIKE 'sql_mode'; 【解决/规避方法】与应用确认存储的数据,正确定义数据
|
10月前
|
存储 关系型数据库 MySQL
MySQL 字段类型探究:深入理解 Varchar(50) 与 Varchar(500)
在MySQL数据库中,`VARCHAR`类型是一种常用的字符串存储类型,它允许定义一个可变长度的字符串。然而,`VARCHAR(50)`和`VARCHAR(500)`之间的差异不仅仅是长度的不同,它们在存储和性能方面也有显著的区别。本文将深入探讨这两种字段类型的区别,以及它们在实际应用中的选择。
406 3
|
10月前
|
存储 关系型数据库 MySQL
MySQL 字段类型深度解析:VARCHAR(50) 与 VARCHAR(500) 的差异
在MySQL数据库中,`VARCHAR`类型是一种非常灵活的字符串存储类型,它允许存储可变长度的字符串。然而,`VARCHAR(50)`和`VARCHAR(500)`之间的差异不仅仅是长度的不同,它们在存储效率、性能和使用场景上也有所不同。本文将深入探讨这两种字段类型的区别及其对数据库设计的影响。
353 2
|
11月前
|
存储 关系型数据库 MySQL
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
268 2
|
11月前
|
存储 SQL 关系型数据库
MySQL 存储函数及调用
MySQL 存储函数及调用
626 3
|
11月前
|
存储 关系型数据库 MySQL
Key_Value 形式 存储_5级省市城乡划分代码 (mysql 8.0 实例)
本文介绍了如何使用MySQL8.0数据库中的Key_Value形式存储全国统计用区划代码和城乡划分代码(5级),包括导入数据、通过数学函数提取省市区信息,以及查询5级行政区划的详细数据。
133 0

推荐镜像

更多