MySQL中除了数值类型外，另一个用的比较多的就是字符类型了。字符类型有很多不同种类： VARCHAR
, CHAR
, BLOB
, TEXT
VARCHAR
VARCHAR是变长的字符类型，实际的存储格式如下图所示：
VARCHAR类型由 prefix
和 data
组成，其中 prefix
由一个或者两个字节构成，作用是指定 data
的长度。如果 data
的长度小于等于255，则 prefix
只需要一个字节，因为一个字节可表示的最大的无符号数就是255，如果 data
的长度大于255，则 prefix
需要用两个字节表示，而两个字节可表示的最大的无符号数是65535，由此可知VARCHAR最大可以存储65535个字符。
CHAR
CHAR是定长的字符类型，实际的存储格式如下图所示：
和VARCHAR不同的是，CHAR中只存储实际的内容，没有用来指定长度的 prefix
，并且CHAR最大可存储的字符数是255。假设一个CHAR(5)的字段，存储的内容只有4个字符，那么实际存储时会在最后加上1个空格来补齐至5个字符。而在查询时，返回的结果中末尾的空格将被移除，除非开启了 PAD_CHAR_TO_FULL_LENGTH
模式。
一个CHAR(5)的字段，分配了5个字符的空间，如下图所示：
当插入了 abcd
之后，实际存储的内容如下图所示：
当实际存储的字符长度不足时，MySQL会在末尾用空格补足。
而查询时，实际返回的内容中，末尾的空格又是被移除的，如下图所示：
用一个实际的例子演示一下：
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' |
+---------+---------+
可以看到，设置了PADCHARTOFULLLENGTH的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 |
| 存储时长度不足 | 末尾用空格补齐 | 不补齐 |
| 存储时长度超限(非严格模式) | 截断并且静默 | 截断并且警告 |
| 存储时长度超限(严格模式) | 报错并且不能保存 | 报错并且不能保存 |
| 查询时(无PADCHARTOFULLLENGTH) | 移除末尾空格 | 不移除末尾空格 |
| 查询时(有PADCHARTOFULLLENGTH) | 不移除末尾空格 | 不移除末尾空格 |
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
个字节的内容而不是整个内容进行排序。
