从底层理解MySQL-字符类型

目录

VARCHAR和CHAR

VARCHAR

CHAR

存储的长度超限

CHAR和VARCHAR的区别：

BLOB和TEXT

MySQL中除了数值类型外，另一个用的比较多的就是字符类型了。字符类型有很多不同种类：VARCHAR,CHAR,BLOB,TEXT

VARCHAR和CHAR

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' |
+---------+---------+

可以看到，设置了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的区别：

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个字节的内容而不是整个内容进行排序。