MySQL知多少--字符类型

2018-03-25 本文已影响16人逅弈

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

我是逅弈，如果文章对您有帮助，欢迎您点赞加关注，并欢迎您关注我的公众号：

欢迎关注微信公众号

MySQL知多少--字符类型

VARCHAR

CHAR

BLOB和TEXT

猜你喜欢

热点阅读