MySQL 数据库的字符串数据类型说明

[TOC]

##  字符串类型
### 1. 字符串类型介绍
下表中列出了 MySQL 中的字符串数据类型，括号中的M表示可以为其指定长度

|类型|说明|格式|字节|取值范围|存储空间方式|
|--|--|--|--|--|--|
|char |定义表结构时已经确定了最终数据的存储长度 |char(M) |M |1 <= M <= 255 |定长字符串|
|varchar |分配空间时，按最大的空间分配，但实际上是根据数据来确定占用空间 |varchar(M)|L+1 | L+1 < M， 1 <= M <= 65535 |`变长字符串`|
|text |文本字符串，存储文字格式 __缺点：__`无法建立索引和占用更多的空间`|text|| 0 至 232-1|`变长字符串`|
|blob |二进制大对象数据类型，存储二进制数据 __缺点：__`不适合建立索引和排序并且占用更多的空间` __备注：__不建议使用，文件直接存储路径即可 |blob || 0 至 232-1|`变长字符串`|
|binary |二进制数据类型，可设置固定的存储长度，常被使用存储哈希值、加密密钥等这类等长的数据 __缺点：__`无法建立索引和占用更多的空间`|binary(M) |M |1 <= M <= 255|定长字符串|
|varbinary |二进制数据类型 __备注：__数据在存储时不会进行字符集转换 |varbinary(M)|M |1 <= M <= 65536|`变长字符串`|
|[enum](#h4-1-3-) |枚举字符串，事先将所有可能出现的结果都设计好，存储的数据必须是规定好的数据中的一个， __备注：__枚举实际存储的是数值而非字符串 |enum(可选字符集合)|M |1 <= M <= 65536||
|[set](#h4-1-4-) |集合字符串，和枚举类似事先将所有可能出现的结果都设计好，存储的数据必须是规定好的数据中的一个或多个，元素数量最多可以定义64个，存储是多个时使用逗号分隔 __备注：__集合实际存储的是数值而非字符串 |set(可选字符集合)|M | 1 <= M <= 255| &nbsp;|

```SQL
create table test_string (name varchar(20), phone char(11)) charset utf8mb4;
```

#### 1.1. text类型的四种变体
变体之间的区别是存储最大文本的长度

#### 1.2. blob类型的四种变体
变体之间的区别是存储最大文本的长度

#### 1.3. 枚举字符串类型
枚举在进行数据规范（定义）的时候，系统会自动建一个数字与枚举元素的对应关系（关系记录在日志中），然后在进行数据插入的时候，系统自动将字符串转换成对应的数值存储，然后再进行数据提取的时候，系统又自动将数值转换成对应的字符串显示。因为存在转换的过程所以效率会受到影响。

-- 1. 插入一条无枚举类型的数据
insert into test_string(name,phone) values('曹操','18136369897'); -- Query OK
-- 2. 插入一条正常符合枚举类型可选值的记录
insert into test_string values('曹植','18136369897','男'); -- Query OK
-- 3. 插入一条不是枚举类型规定的可选值记录
insert into test_string values('曹操','18136369897','男女');
-- 插入失败提示{ERROR 1265 (01000): Data truncated for column 'enumSex' at row 1}
-- 4. 枚举类型插入数值也是可以的，因为枚举实际存储的是数值，所以可以直接插入数值
insert into test_string values('曹节','13436369897','2'); -- Query OK

select * from test_string;
+------+-------------+---------+
| name | phone       | enumSex |
+------+-------------+---------+
| 曹操 | 18136369897 | 男      |
| 曹植 | 18136369897 | 男      |
| 曹节 | 13436369897 | 女      |
+------+-------------+---------+
```

__作用：__1、规范数据格式，数据只能是可选的值。2、节省存储空间，枚举实际存储的是数值而不是字符串格式，枚举存储数值的方式是按照创建时可选项的顺序，从1开始编号的，示例中enumSex字段存储的值就是1和2，男-1，女-2

> __附加知识《字符串格式转换》：__在MySQL中，系统也是自动转换数据格式的，下面示例代码展示了字符串转成数字的效果，如果字符串前面有数值则取数值，如果是纯字符串则转换为0。
```SQL
-- 字符串前面有数值则取数值
select 1 + '2hello' num ;  --运算结果为3
+-----+
| num |
+-----+
|   3 |
+-----+
-- 纯字符串则转换为0
select 1 + 'hello' num ;  --运算结果为1
+-----+
| num |
+-----+
|   1 |
+-----+
```
> __附加知识《验证枚举类型存储的是数值》：__使用枚举示例中的表进行验证，从下面的查询指令中看出枚举类型存储的确实是数值，如果存储的是字符串，字符串（转换为数值后为0）与0相加结果应该还是0，结果为1说明枚举存储的就是数值
```SQL
select enumSex + 0 验证结果  from test_string;
+----------+
| 验证结果 |
+----------+
|        1 |
|        1 |
+----------+
```

#### 1.4. 集合字符串类型
集合中每个元素都是对应一个二进制位，被选中的位1，没有选择则为0，最后左右反过来后再存入数据库。因为存在转换的过程所以效率会受到影响，如果存储是集合中的多个时，使用逗号分隔，选项的顺序可以任意，存储时都会根据字段定义的顺序重新排序

-- 1. 为集合类型添加多个选项
update test_string set likeFruit = '苹果,桔子,梨子' where name = '曹操'; -- Query OK

-- 2. 使用数值为集合类型添加选项，这里插入的数值3但并不是可选项中对应的‘梨子’
update test_string set likeFruit = 3 where name = '曹植'; -- Query OK

select * from test_string;
+------+-------------+---------+---------------+
| name | phone       | enumSex | likeFruit     |
+------+-------------+---------+---------------+
| 曹操 | 18136369897 | 男      | 苹果,桔子,梨子  |
| 曹植 | 18136369897 | 男      | 苹果,桔子       |
| 曹节 | 13436369897 | 女      | NULL           |
+------+-------------+---------+---------------+
```

> __附加知识《验证集合类型存储的是二进制转换的数值》：__使用合集示例中的表进行验证
>```SQL
select likeFruit + 0 验证结果  from test_string;
+----------+
| 验证结果  |
+----------+
|        7 |
|        3 |
+----------+
-- 修改的第一条记录值为【苹果,桔子,梨子】转换为数值是7，它转换的方式是如下：
+------+------+------+------+------+
|苹果  |桔子   |梨子   |香蕉  |西瓜  |
|1    |1      |1      |0     |0    |
|0    |0      |1      |1     |1    | 转换为 0+0+4+2+1 = 7
+------+------+------+------+------+
-- 修改的第二条记录值是一个数值3转换后就是【苹果,桔子】，它转换的方式是如下：
+------+------+------+------+------+
|苹果  |桔子   |梨子   |香蕉  |西瓜  |
|0    |0      |0      |1     |1    | 转换为 0+0+0+2+1 = 3
|1    |1      |0      |0     |0    | 选中的是【苹果,桔子】
+------+------+------+------+------+
```

__作用：__规范数据和节省空间，但同样存在数据转换的消耗，影响效率

### 2. 字符串类型储存空间说明
__varchar(M)：__知识点说明
+ varchar(M)的实际长度是数据实际长度+1个字节，例如varchar(10)储存了10个汉字，在utf8mb3的编码环境下转换为字节实际需要占用10\*3+1个字节。如果储存了3个汉字，在utf8mb3的编码环境下转换为字节实际需要占用3\*3+1个字节
+ 如果varchar(M)存储的数据长度超过了255，这时既不用定长也不用变长字符串，而是使用文本字符串text

__定长和变长的存储空间对比：__下面的对比都是基于utf8mb3编码

|存储数据|char(4)|varchar(4)|char占用字节|varchar占用字节|
|--|--|--|--|--|
|ABCD|ABCD|ABCD|4 \* 3 = 12|4 \* 3 + 1= 12|
|A|A|A|4 \* 3 = 12|1 \* 3 + 1= 4|
|ABCDE|❌|❌|数据超出长度提交会失败|数据超出长度提交会失败|

> __附加说明：__字符串类型是以字符为单位，如果要换算为字节，需要根据编码环境来换算，例如M个字符在utf8mb3编码下换算成字节是M\*3个，在uft8mb4则是M\*4个

__定长和变长的优缺点：__
+ 定长字符串：磁盘空间比较浪费，但是效率高，数据长度固定时使用，如身份证，电话号码，手机号码
+ 变长字符串：磁盘空间比较节省，变长类型需要使用额外的空间来存储每个数据的长度信息，因此它的效率比定长类型低，数据长度不固定时使用

### 3. MySQL记录长度
__MySQL中规定：__任何一条记录最长不能超过65535个字节，varchar永远不可能达到理论值的65535，因为整条记录需要2个字节来存储数据长度信息，这里所说的整条记录指的是表所有的列相加不能超过65535并非指一个字段。

__计算varchar的实际存储长度：__下面通过报错的形式来得到varchar的实际最大长度，varchar的实际最大长度还受到字符集影响。

使用理论的最大值65535创建表

```SQL
create table test_maxvarchar_utf(name varchar(65535)) charset utf8mb4;
-- ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead
-- 上述错误中已经给出了varchar在utf8mb4字符集下最大长度是16383
create table test_maxvarchar_gbk(name varchar(65535)) charset gbk;
-- ERROR 1074 (42000): Column length too big for column 'name' (max = 32767); use BLOB or TEXT instead
-- 上述错误中已经给出了varchar在utf8mb4字符集下最大长度是32767
```

使用上面示例中得到最大值`utf8mb4:16383`,`gbk:32767`创建表

```SQL
create table test_maxvarchar_utf(name varchar(16383)) charset utf8mb4;
-- Query OK, 0 rows affected (0.33 sec)
-- 使用utf8mb4字符集创建成功了，这是因为 【16383 * 4 + 2 = 65534 < 65535】 （utf8mb4字符集下1个字符4个字节）
create table test_maxvarchar_gbk(name varchar(32767)) charset gbk;
-- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
-- 使用gbk字符集创建失败，这是因为【32767 * 2 + 2 = 65536 > 65535】。所以gbk下varchar的最大值应该是32766 （gbk字符集下1个字符2个字节）
```

通过以上的示例得出了下面的结果

+ uft8mb4下varchar的实际最大长度是16383个字节
+ GBK下varchar的实际最大长度是32766个字节

__MySQL记录中：__任何一个字段允许为空，系统都会自动从整个记录中保留一个字节来存储NULL，若想释放NULL所占用的自己，必须保证所有字段都不允许为空

使用示例来证明，在上面的gbk字符集下varchar最大长度为32766，这样比65535刚好少了一个字节，如果想要这条记录刚好达到最大限度，可以给表添加一个tinyint类型的字段，这样长度就刚好满65535了

```SQL
create table test_maxvarchar_gbk1(id tinyint, name varchar(32766)) charset gbk;
-- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
-- 上面示例创建失败，这是因为字段有NULL的情况，NULL也会占用字节，如果想要上面的语句能创建成功，则需要将字段定义为非空列
create table test_maxvarchar_gbk1(id tinyint not null, name varchar(32766) not null) charset gbk;
-- Query OK, 0 rows affected
-- 创建成功了，这就是释放了NULL值，确保了不需要字节来保存NULL的信息
```

__MySQL中text文本类型不占用记录长度：__text类型存储的数据不算在65535之内，是额外存储的，但是text文本字符串也属于记录的一部分，会占据记录长度中的部分长度，10个字节，它存储的是text这个字段的信息，例如数据的地址，数据的长度，下面是一个证明text占用10个字节记录长度的示例

```SQL
create table test_text_gbk(name varchar(32763),content text) charset gbk;
-- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
-- 上述错误提示记录长度又超出了，但这里的长度32763*2 + 2 = 65528，它是小于65535的，所以说text也是占用了记录长度的
create table test_text_gbk(name varchar(32761),content text) charset gbk;
-- Query OK
-- 创建成功，这里text占用的长度= 65535 - (32761*2 + 2) = 11，这是因为字段没有全部设置为NOT NULL的原因，有1个字节正好用来储存NULL了，因此text是占用了10个字节的记录长度
```