MySQL中除了数值类型外，另一个用的比较多的就是字符类型了。字符类型有很多不同种类：VARCHAR,CHAR,BLOB,TEXT
VARCHAR
VARCHAR是变长的字符类型，实际的存储格式如下图所示：
img1.png
VARCHAR类型由prefix和data组成，其中prefix由一个或者两个字节构成，作用是指定data的长度。如果data的长度小于等于255，则prefix只需要一个字节，因为一个字节可表示的最大的无符号数就是255，如果data的长度大于255，则prefix需要用两个字节表示，而两个字节可表示的最大的无符号数是65535，由此可知VARCHAR最大可以存储65535个字符。
CHAR
CHAR是定长的字符类型，实际的存储格式如下图所示：
img2.png
和VARCHAR不同的是，CHAR中只存储实际的内容，没有用来指定长度的prefix，并且CHAR最大可存储的字符数是255。假设一个CHAR(5)的字段，存储的内容只有4个字符，那么实际存储时会在最后加上1个空格来补齐至5个字符。而在查询时，返回的结果中末尾的空格将被移除，除非开启了PAD_CHAR_TO_FULL_LENGTH模式。
一个CHAR(5)的字段，分配了5个字符的空间，如下图所示：
img3.png
当插入了abcd之后，实际存储的内容如下图所示：
img4.png
当实际存储的字符长度不足时，MySQL会在末尾用空格补足。
而查询时，实际返回的内容中，末尾的空格又是被移除的，如下图所示：
img5.png
用一个实际的例子演示一下：
- 1、准备一张表
create table `test_c` (
a char(5) not null,
b varchar(5) not null
) engine=InnoDB;
- 2、插入几条测试数据
insert into test_c (a,b)
values
('abcd','abcd'),
('abcd ','abcd '),
(' abcd',' abcd');
- 3、查询结果
select concat("'",a,"'") as a,concat("'",b,"'") as b from test_c;
- 4、结果如下
+---------+---------+
| a | b |
+---------+---------+
| 'abcd' | 'abcd' |
| 'abcd' | 'abcd ' |
| ' abcd' | ' abcd' |
+---------+---------+
可以发现查询时，返回的结果中末尾的空格都被移除了，不管末尾的空格是不是我们实际存储的值。那这种情况下就会出现问题，实际我们是需要末尾的空格的，但查询的结果末尾的空格被移除了。解决的方法是，开启PAD_CHAR_TO_FULL_LENGTH的SQL Mode。
- 1、先查一下当前的SQL Mode：
select @@sql_mode;
结果如下：
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
- 2、设置SQL Mode：
SET sql_mode=`PAD_CHAR_TO_FULL_LENGTH`;
- 3、再查询结果：
select concat("'",a,"'") as a,concat("'",b,"'") as b from test_c;
- 4、结果如下：
+---------+---------+
| a | b |
+---------+---------+
| 'abcd ' | 'abcd' |
| 'abcd ' | 'abcd ' |
| ' abcd' | ' abcd' |
+---------+---------+
可以看到，设置了PAD_CHAR_TO_FULL_LENGTH的SQL Mode之后，CHAR类型的字段末尾的空格没有被移除。
存储的长度超限
当存储的字符长度超过了CHAR和VARCHAR设置的长度，在不同的情况下会有不同的效果。在严格模式下，超过长度的字符是不能保存成功的，系统会报错。在非严格模式下，能保存成功，CHAR类型的会截断超过长度的部分，并且不会有任何反映，VARCHAR类型的也会截断超过长度的部分，但是会抛出警告。
PS：
需要注意的是，CHAR和VARCHAR中存储的内容的长度是指的字符长度，不是字节长度，而字符长度跟选择的字符集有关，比如ASCII字符集可以存储英文和数字等，不能存储中文，因为一个中文字符占3个字节，UTF8字符集就可以存储中文了，但是UTF8不能存储emoji表情，因为emoji表情占4个字节，而UTF8MB4就可以存储emoji表情。
CHAR和VARCHAR的区别：
| 比较维度 | CHAR | VARCHAR |
|---|---|---|
| 组成结构 | data | prefix+data |
| 最低存储需求 | 0字节 | 1字节 |
| 最大长度 | 255 | 65535 |
| 存储时长度不足 | 末尾用空格补齐 | 不补齐 |
| 存储时长度超限(非严格模式) | 截断并且静默 | 截断并且警告 |
| 存储时长度超限(严格模式) | 报错并且不能保存 | 报错并且不能保存 |
| 查询时(无PAD_CHAR_TO_FULL_LENGTH) | 移除末尾空格 | 不移除末尾空格 |
| 查询时(有PAD_CHAR_TO_FULL_LENGTH) | 不移除末尾空格 | 不移除末尾空格 |
BLOB和TEXT
BLOB和TEXT主要是用来存储数据长度非常大的字符类型的，BLOB主要是存储二进制字符类型，TEXT则是存储字符串。
其中BLOB又包括了TINYBLOB,SMALLBLOB,MEDIUNBLOB,LONGBLOB,TEXT包括了TINYTEXT,SMALLTEXT,MEDIUNTEXT,LONGTEXTB。
当BLOB和TEXT的值太大时，InnoDB会使用专门的外部存储区域来存储，这时就会在字段中用一个1~4个字节的指针，纪录下外部实际存储的区域。
MySQL对BLOB和TEXT类型的字段进行排序时，只会对字段的前max_sort_length个字节的内容而不是整个内容进行排序。
我是逅弈，如果文章对您有帮助，欢迎您点赞加关注，并欢迎您关注我的公众号：
欢迎关注微信公众号
网友评论