首页 > 编程笔记

MySQL字符串类型（超级详细）

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。

MySQL 支持两类字符型数据：文本字符串和二进制字符串。

MySQL文本字符串类型

文本字符串可以进行区分或者不区分大小写的串比较，还可以进行模式匹配查找。

在 MySQL 中，文本字符串类型是指 CHAR、VARCHAR、TEXT、ENUM 和 SET，下表列出了 MySQL 中的文本字符串数据类型。

表 1 MySQL中文本字符串数据类型
类型名称 说明 存储需求
CHAR(M) 固定长度非二进制字符串 M 字节，1≤M≤255
VARCHAR(M) 变长非二进制字符串 L+1字节，在此 L≤M 和 1≤M≤255
TINYTEXT 非常小的非二进制字符串 L+1字节，在此 L<28
TEXT 小的非二进制字符串 L+2字节，在此 L<216
MEDIUMTEXT 中等大小的非二进制字符串 L+3字节，在此 L＜224
LONGTEXT 大的非二进制字符串 L+4 字节，在此 L<232
ENUM 枚举类型，只能有一个枚举字符串值 1 或 2 字节，取决于枚举值的数目（最大值为 65535）
SET 一个设置，字符串对象可以有零个或多个 SET 成员 1、2、3、4 或 8 字节，取决于集合成员的数量（最 多为 64 个成员）

VARCHAR 和 TEXT 类型与后续讲到的 BLOB 都是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示）。例如，一个 VARCHAR(10) 列能保存最大长度为 10 个字符的字符串，实际的存储需要是字符串的长度 L 加上 1 字节（记录字符串的长度）。对于字符 “abcd”，L 是 4，而存储要求是 5 字节。

1) CHAR和VARCHAR类型

CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时在右侧填充空格，以达到指定的长度。M 表示列长度，M 的范围是 0~255 个字符。

例如，CHAR(4) 定义了一个固定长度的字符串列，其包含的字符个数最大为 4。当检索到 CHAR 值时，尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串，M 表示最大列长度。M 的范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，其实际占用的空间为字符串的实际长度加 1。

例如，VARCHAR(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。

【实例】下面将不同字符串保存到 CHAR(4) 和 VARCHAR(4) 列，说明 CHAR 和 VARCHAR 之间的差别，如下表所示。

表 2 CHAR(4)与VARCHAR(4)存储区别
插入值 CHAR(4) 存储需求 VARCHAR(4) 存储需求
'' '    ' 4 字节 ' ' 1 字节
'ab' 'ab  ' 4 字节 'ab' 3 字节
'abc' 'abc' 4 字节 'abc' 4 字节
'abed' 'abed' 4 字节 'abed' 5 字节
'abedef' 'abcd' 4 字节 'abcd' 5 字节

对比结果可以看到，CHAR(4) 定义了固定长度为 4 的列，不管存入的数据长度为多少，所占用的空间均为 4 个字节；VARCHAR(4) 定义的列所占的字节数为实际长度加1。

查询时，CHAR(4) 和 VARCHAR(4) 的值并不一定相同。

【实例】创建 tmp8 表，定义字段 ch 和 vch 数据类型依次为 CHAR(4)、VARCHAR(4)，向表中插入数据'ab '，SQL 语句如下：

① 创建表tmp8： 
CREATE TABLE tmp8(ch CHAR(4), vch VARCHAR(4));
② 输入数据： 
INSERT INTO tmp8 VALUES('ab  ', 'ab  ');
③ 查询结果： 
mysql> SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;
+----------------------+---------------------+
| concat('(', ch, ')') | concat('(',vch,')') |
+----------------------+---------------------+
| (ab)               | (ab  )                |
+----------------------+---------------------+
1 row in set (0.00 sec)
从查询结果可以看到，ch 在保存 'ab ' 时将末尾的两个空格删除了，而 vch 字段保留了末尾的两个空格。

在表 2 中，最后一行的值只有在使用“不严格”模式时，字符串才会被截断插入；如果 MySQL 运行在“严格”模式，则超过列长度的值不会被保存，并且会出现错误信息“ERROR 1406(22001): Data too long for column”，即字符串长度超过指定长度，无法插入。

2) TEXT类型

TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

Text 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

3) ENUM类型

ENUM 是一个字符串对象，其值为表创建时在列规定中枚举的一列值。语法格式如下： 
字段名 ENUM('值1','值2',...,'值n')
其中，“字段名”指将要定义的字段，“值n”指枚举列表中的第 n 个值。

ENUM 类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。创建的成员中有空格时，其尾部的空格将自动被删除。

ENUM 值在内部用整数表示，并且每个枚举值均有一个索引值：列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号。枚举最多可以有 65535 个元素。

例如，定义 ENUM 类型的列('first'，'second'，'third')，该列可以取的值和每个值的索引如下表所示。

表 3 ENUM类型的取值范围
值 索引
NULL NULL
!! 0
first 1
second 2
third 3

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有的枚举值前。这一点也可以从表 3 中看到。

在这里，有一个方法可以查看列成员的索引值，如下例所示。

【实例】创建表 tmp9，定义 ENUM 类型的列 enm('first'，'second'，'third')，查看列成员的索引值，SQL 语句如下：

① 创建tmp9表： 
CREATE TABLE tmp9(enm ENUM('first','second','third'));
② 插入各个列值： 
INSERT INTO tmp9 values('first'),('second'),('third'),(NULL);
③ 查看索引值： 
mysql> SELECT enm, enm+0 FROM tmp9;
+--------+-------+
| enm    | enm+0 |
+--------+-------+
| first  |     1 |
| second |     2 |
| third  |     3 |
| NULL   |  NULL |
+--------+-------+
可以看到，这里的索引值和前面所述的相同。

提示，ENUM 列总有一个默认值，如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL；如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

【实例】创建表 tmp10，定义 INT 类型的 soc 字段，ENUM 类型的字段 level，并且列表值为('excellent','good', 'bad')，向表 tmp10 中插入数据 (70,'good')、(90,1)、(75,2)、(50,3)、(100,'best')，SQL 语句如下：

① 创建数据表： 
CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad'));
② 插入数据： 
INSERT INTO tmp10 values(70,'good'),(90,1),(75,2),(50,3);
③ 再次插入数据： 
mysql>INSERT INTO tmp10 values (100,'best');
ERROR 1265 (01000): Data truncated for column 'level' at row 1
这里系统提示错误信息，可以看到，由于字符串值 'best' 不在 ENUM 列表中，所以对数据进行了阻止插入操作，查询结果如下： 
mysql> SELECT * FROM tmp10;
+------+-----------+
| soc  | level     |
+------+-----------+
|   70 | good      |
|   90 | excellent |
|   75 | good      |
|   50 | bad       |
+------+-----------+
由结果可以看到，因为 ENUM 列表中的值在 MySQL 中都是以编号序列存储的，所以插入列表中的值 'good' 或者插入其对应序号 2 的结果是相同的。'best' 不是列表中的值，因此不能插入数据。

4) SET类型

SET 是一个字符串对象，可以有零个或多个值。SET 列最多可以有 64 个成员，其值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号（,）隔开。

SET 类型的语法格式如下： 
SET('值1','值2',...,'值n')
与 ENUM 类型相同，SET 值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动被删除。与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

如果插入 SET 字段中列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，并给出警告。

【实例】创建表 tmp11，定义 SET 类型的字段 s，取值列表为 ('a', 'b', 'c', 'd')，插入数据('a')、('a,b,a')、('c,a,d')、('a,x,b,y')，SQL 语句如下：
① 创建表tmp11： 
CREATE TABLE tmp11 (s SET('a', 'b', 'c', 'd'));
② 插入数据： 
INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');
③ 再次插入数据： 
mysql>INSERT INTO tmp11 values ('a,x,b,y');
ERROR 1265 (01000): Data truncated for column 's' at row 1
由于插入了 SET 列不支持的值，所以 MySQL 给出错误提示。
④ 查看结果： 
mysql> SELECT * FROM tmp11;
+-------+
| s     |
+-------+
| a     |
| a,b   |
| a,c,d |
+-------+
从结果可以看到，对于 SET 来说，如果插入的值是重复的，则只取一个，例如插入 'a,b,a'，则结果为“a,b”；如果插入了不按顺序排列的值，则自动按顺序插入，例如插入 'c,a,d'，结果为“a,c,d”；如果插入了不正确的值，那么该值将被阻止插入，例如插入值 'a,x,b,y'。

MySQL二进制字符串类型

MySQL 中的二进制数据类型有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。下表列出了 MySQL 中的二进制数据类型。

表 4 MySQL中的二进制字符串类型
类型名称 说明 存储需求
BIT(M) 位字段类型 大约 (M+7)/8 字节
BINARY(M) 固定长度二进制字符串 M 字节
VARBINARY(M) 可变长度二进制字符串 M+1 字节
TINYBLOB(M) 非常小的 BLOB L+1 字节，在此 L<28
BLOB(M) 小 BLOB L+2 字节，在此 L<216
MEDIUMBLOB(M) 中等大小的 BLOB L+3 字节，在此 L<224
LONGBLOB(M) 非常大的 BLOB L+4 字节，在此 L<232

1) BIT类型

BIT(M) 类型是位字段类型，M 表示每个值的位数，范围为 1~64。如果 M 被省略，默认为 1。

如果为 BIT(M)列分配的值的长度小于 M 位，就在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b'101'，其效果与分配 b’000101’相同。

BIT 数据类型用来保存位字段值。例如，以二进制的形式保存数据 13（13 的二进制形式为 1101），在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)，大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

【实例】创建表 tmp12，定义 BIT(4) 类型的字段 b，向表中插入数据 2、9、15。
① 创建表tmp12： 
CREATE TABLE tmp12(b BIT(4));
② 插入数据： 
mysql> INSERT INTO tmp12 VALUES(2), (9), (15);
③ 查询插入结果： 
mysql> SELECT BIN(b+0) FROM tmp12;
+------------+
| BIN(b+0)   |
+------------+
| 10         |
| 1001       |
| 1111       |
+------------+
b+0 表示将二进制的结果转换为对应的数字的值，BIN() 函数可以将数字转换为二进制。从结果可以看到，3 个数已被成功地插入表中。

默认情况下，MySQL 不可以插入超出该列允许范围的值，因而插入的数据要确保插入的值在指定的范围内。

2) BINARY和VARBINARY类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。其使用的语法格式如下： 
列名称 BINARY(M)或者VARBINARY(M)
BINARY 类型的长度是固定的，指定长度之后，不足最大长度的，将在它们右边填充“\0”补齐以达到指定长度。例如：指定列数据类型为 BINARY(3)，当插入“a”时，存储的内容实际为“a\0\0”；当插入“ab”时，实际存储的内容为“ab\0”；不管存储的内容是否达到指定的长度，其存储空间均为指定的值M。

VARBINARY 类型的长度是可变的，指定好长度之后，其长度可以在 0 到最大值之间。例如：指定列数据类型为 VARBINARY(20)，如果插入的值的长度只有 10，则实际存储空间为 10 加 1，即实际占用的空间为字符串的实际长度加 1。

【实例】创建表 tmp13，定义 BINARY(3) 类型的字段 b 和 VARBINARY(3) 类型的字段 vb，并向表中插入数据“5”，比较两个字段的存储空间。
① 创建表tmp13： 
CREATE TABLE tmp13(b binary(3), vb varbinary(3));
② 插入数据： 
INSERT INTO tmp13 VALUES(5,5);
③ 查看两个字段存储数据的长度： 
mysql> SELECT length(b), length(vb) FROM tmp13;
+-----------+---------------+
| length(b) | length(vb)    |
+-----------+---------------+
|      3    |          1    |
+-----------+---------------+
可以看到，b 字段的值数据长度为 3，而 vb 字段的数据长度仅为插入的一个字符的长度 1。

3) BLOB类型

BLOB 是一个二进制大对象，用来存储可变数量的数据。

BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它们可容纳值的最大长度不同，如下表所示。

表 5 BLOB类型的存储范围
数据类型 存储范围
TINYBLOB 最大长度为 255 (28-1) B
BLOB 最大长度为 65535 (216-1) B
MEDIUMBLOB 最大长度为 16777215 (224-1) B
LONGBLOB 最大长度为 4294967295 (232-1) B 或 4GB

BLOB 列存储的是二进制字符串（字节字符串），TEXT 列存储的是非二进制字符串（字符字符串）。BLOB 列没有字符集，并且排序和比较基于列值字节的数值；TEXT 列有一个字符集，并且根据字符集对值进行排序和比较。

推荐阅读