DBA MySQL字符类型

字符类型

基本介绍

MySQL中提供了多种关于字符存储的类型，大多数情况下我们只使用CHAR与VARCHAR即可：

类型	大小	用途
CHAR	0 - 255字符	定长字符串
VARCHAR	0 - 65535字符	变长字符串
TINYBLOB	0 - 255字符	不超过 255 个字符的二进制字符串
TINYTEXT	0 - 255字符	短文本字符串
BLOB	0 - 65 535字符	二进制形式的长文本数据
TEXT	0 - 65 535字符	长文本数据
MEDIUMBLOB	0 - 16 777 215字符	二进制形式的中等长度文本数据
MEDIUMTEXT	0 - 16 777 215字符	中等长度文本数据
LONGBLOB	0 - 4 294 967 295字符	二进制形式的极大文本数据
LONGTEXT	0 - 4 294 967 295字符	极大文本数据

基本定义

与数值类型不同，对于字符类型而言，其指定宽度均为存入宽度而非显示宽度，且以字符为单位：

CREATE TABLE temp(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "编号",
    ch CHAR(10) NOT NULL COMMENT "定长字符串",
    vc VARCHAR(10) NOT NULL DEFAULT COMMENT "变长字符串"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

定长变长

区别差异

CHAR为定长字符串，存入宽度不够指定宽度时，将会使用空字符进行填充。

VARCHAR为变长字符串，存入宽度不够指定宽度时，不会使用空字符进行填充。

通常情况下来讲，CHAR类型存取速度要比VARCHAR高约50%，但是更加浪费磁盘空间

在InnoDB存储引擎中，不存在这种差异

建议在同一张数据表中统一使用VARCHAR或CHAR类型，这里更推荐使用CHAR类型。

差异详解：

# char类型：定长，简单粗暴，浪费空间，存取速度快
    字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节，utf8mb4编码的4个字节）
    存储：
        存储char类型的值时，会往右填充空格来满足长度
        例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储

检索：
        在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）

# varchar类型：变长，精准，节省空间，存取速度慢
    字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
    存储：
        varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来
        强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）

检索：
        尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

插入值	CHAR(4)存入值	CHAR(4)占据空间	VARCHAR(4)存入值	VARCHAR(4)占据空间
''	' '	4 bytes	' '	1 bytes
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

由于VARCHAR是变长存储，所以需要一个头部标识来表示真实内容究竟占据多少字符，这个头部标识通常占据1-2个字节的存储空间，可通过执行计划查看到。

存入abcd，VARCHAR(4)需要算上头部标识，即最小占用5字节，最大占用6字节。而CHAR(4)则仅占用4字节。

差异对比

如果要想进定长与变长的差异对比试验，需要用到以下两个函数，除此之外还需要用到执行计划。

函数	描述
LENGTH()	查看字节数
CHAR_LENGTH()	查看字符数

我们将从2个方面入手，查看两者区别。

第1个方面是查看其内容本身占据的字节与字符数差异。

第2个方面是作为索引列查看其索引长度差异。

示例如下：

# 必须修改模式，使其查看定长字符类型时不会将填充空格移除
M > SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

# 创建表
M > CREATE TABLE temp(
         id INT PRIMARY KEY AUTO_INCREMENT COMMENT "编号",
         ch CHAR(4) NOT NULL DEFAULT "unk" COMMENT "定长字符串",
         vc VARCHAR(4) NOT NULL DEFAULT "unk" COMMENT "变长字符串"
     ) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 为定长类型与变长类型存入相同数据
M > INSERT INTO
         temp(ch, vc)
     VALUES
         ("", ""),
         ("ab", "ab"),
         ("abcd", "abcd"),
         ("abcdefg", "abcdefg");

# 查看数据本身占用字节数
M > SELECT LENGTH(ch) as "占用字节数CHAR", LENGTH(vc) as "占用字节数VARCHAR" FROM temp L;
+---------------------+------------------------+
| 占用字节数CHAR        | 占用字节数VARCHAR       |
+---------------------+------------------------+
|                   4 |                      0 |
|                   4 |                      2 |
|                   4 |                      4 |
|                   4 |                      4 |
+---------------------+------------------------+

# 查看数据本身占用字符数
M > SELECT CHAR_LENGTH(ch) as "占用字符数CHAR", CHAR_LENGTH(vc) as "占用字符 数VARCHAR" FROM temp;
+---------------------+-------------------------+
| 占用字符数CHAR        | 占用字符 数VARCHAR       |
+---------------------+-------------------------+
|                   4 |                       0 |
|                   4 |                       2 |
|                   4 |                       4 |
|                   4 |                       4 |
+---------------------+-------------------------+

# 为两个列添加普通索引，查看索引长度获得区别
M > ALTER TABLE temp ADD INDEX cidx(ch);

M > ALTER TABLE temp ADD INDEX vidx(vc);

# 使用执行计划可以看见ch索引长度key_len是16个字节。utf8mb4下4*4=16
M > DESC SELECT ch FROM temp WHERE ch="";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | temp  | NULL       | ref  | cidx          | cidx | 16      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

# 使用执行计划可以看见vc的key_len是18个字节。这是因为除了utf8mb4下4*4=16之外，还需要加上开始和结束位置，占2字节
M > DESC SELECT vc FROM temp WHERE vc="";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | temp  | NULL       | ref  | vidx          | vidx | 18      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

常用函数

大小写转换

UPPER()函数功能是小写转大写。

LOWER()函数功能是大写转小写。

M > SELECT UPPER("mysql"), LOWER("MYSQL");
+----------------+----------------+
| UPPER("mysql") | LOWER("MYSQL") |
+----------------+----------------+
| MYSQL          | mysql          |
+----------------+----------------+

指定量取

LEFT()函数是从左往右取指定数量的字符。

RIGHT()函数是从右往左取指定数量的字符。

M > SELECT LEFT("ABCDE",3), RIGHT("ABCDE",3);
+-----------------+------------------+
| LEFT("ABCDE",3) | RIGHT("ABCDE",3) |
+-----------------+------------------+
| ABC             | CDE              |
+-----------------+------------------+

中间字符

MID()函数从中间取字符。

参数2为指定起始位置，默认从1开始。

参数3为指定取出的字符数量，默认一直取完。

M > SELECT MID("ABCDE",2,2);
+------------------+
| MID("ABCDE",2,2) |
+------------------+
| BC               |
+------------------+

截取子串

SUBSTRING()函数从指定起始位置开始取出像右的指定字符。

参数2为指定起始位置，默认从1开始。

参数3为指定取出的字符数量，默认一直取完。

感觉和MID()函数没啥大区别：

M > SELECT SUBSTRING("ABCDE",2,2);
+------------------------+
| SUBSTRING("ABCDE",2,2) |
+------------------------+
| BC                     |
+------------------------+

字符长度

CHAR_LENGTH()函数可获取字符长度，如果想要获取CHAR类型的字符长度且包括空白填充字符时需要修改SQL模式：

# 修改SQL模式
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

函数示例演示：

M > SELECT CHAR_LENGTH("ABCDE");
+----------------------+
| CHAR_LENGTH("ABCDE") |
+----------------------+
|                    5 |
+----------------------+

字节长度

LENGTH()函数可获取字节长度，如果想要获取CHAR类型的字节长度且包括空白填充字符时需要修改SQL模式：

# 修改SQL模式
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';