MySQL数据类型-字符串类型 (CHAR VARCHAR等)

字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。

MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。

MySQL字符串类型

字符串类型没有像数字类型列那样的“取值范围"，但它们都有长度的概念。

• 如果需要存储的字符串短于 256 个字符，那么可以使用 CHAR、VARCHAR 或 TINYTEXT。
• 如果需要存储更长一点的字符串，则可以选用 VARCHAR 或某种更长的 TEXT 类型。
• 如果某个字符串列用于表示某种固定集合的值，那么可以考虑使用数据类型 ENUM 或 SET。

下表中列出了 MySQL 中的字符串数据类型，括号中的 M 表示可以为其指定长度，表格中用 L表示存储需求取决于列值的实际长度。

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

VARCHAR 和 TEXT 类型是可变类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

CHAR 和 VARCHAR 类型

• CHAR(M) 为固定长度字符串。M 表示列的长度，范围是 0～255 个字符。会自动删除插入数据的尾部空格。MyISAM 存储引擎推荐使用。

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

• VARCHAR(M)是长度可变的字符串，M 表示最大列的长度，M 的范围是 0～65535。不会删除尾部空格。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。InnoDB存储引擎推荐使用。

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

存储引擎对于选择 CHAR 和VARCHAR 的影响：

• 对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
• 对于 InnoDB 存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

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

插入值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
’ ’	’ ’	4字节	‘’	1字节
‘ab’	'ab ’	4字节	‘ab’	3字节
‘abc’	'abc ’	4字节	‘abc’	4字节
‘abcd’	‘abcd’	4字节	‘abcd’	5字节
‘abcdef’	‘abcd’	4字节	‘abcd’	5字节

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

mysql> CREATE TABLE vc_diff(v varchar(4),c char(4));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO vc_diff(v,c) VALUES('abc ','abc ');
Query OK, 1 row affected (0.01 sec)

mysql> desc vc_diff;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| v     | varchar(4) | YES  |     | NULL    |       |
| c     | char(4)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT LENGTH(v), LENGTH(c) FROM vc_diff;
+-----------+-----------+
| LENGTH(v) | LENGTH(c) |
+-----------+-----------+
|         4 |         3 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(v,'-'), CONCAT(c,'-') FROM vc_diff;
+---------------+---------------+
| CONCAT(v,'-') | CONCAT(c,'-') |
+---------------+---------------+
| abc -         | abc-          |
+---------------+---------------+
1 row in set (0.00 sec)

TEXT 类型

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

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

• TINYTEXT 表示长度为 255（28-1）字符的 TEXT 列。
• TEXT 表示长度为 65535（216-1）字符的 TEXT 列。
• MEDIUMTEXT 表示长度为 16777215（224-1）字符的 TEXT 列。
• LONGTEXT 表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。

ENUM 类型

ENUM 是一个字符串对象，只能 单选 一个值，值为表创建时列规定中枚举的一列值。其语法格式如下：

<字段名> ENUM( '值1', '值1', …, '值n' )

• 字段名指将要定义的字段，值 n 指枚举列表中第 n 个值。

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

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

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

值	索引
NULL	NULL
‘’	0
’first	1
second	2
third	3

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。

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

mysql> create table e_diff(e enum('a','b','c'));
Query OK, 0 rows affected (0.03 sec)

mysql> desc e_diff;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| e     | enum('a','b','c') | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into e_diff values('a'),('b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from e_diff;
+------+
| e    |
+------+
| a    |
| b    |
+------+
2 rows in set (0.00 sec)

mysql> insert into e_diff values('a,b'),('b,c');
ERROR 1265 (01000): Data truncated for column 'e' at row 1

SET 类型

SET 是一个字符串的对象，可以 **多选 **有零或多个值，SET 列最多可以有 64 个成员，空字符串也是一个合法的 SET值，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号 , 隔开，语法格式如下：

SET( '值1', '值2', …, '值n' )

• 与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

• 但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

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

• 在需要取多个值的时候，适合使用 SET 类型，比如，要存储一个人兴趣爱好，最好使用SET类型。

• ENUM 和 SET 的值是以字符串形式出现的，但在内部，MySQL 以数值的形式存储它们。

mysql> create table s_diff(s set('a','b','c'));
Query OK, 0 rows affected (0.03 sec)

mysql> desc s_diff;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| s     | set('a','b','c') | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into s_diff values('a'),('b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from s_diff;
+------+
| s    |
+------+
| a    |
| b    |
+------+
2 rows in set (0.00 sec)

mysql> insert into s_diff values('a,b'),('b,c');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from s_diff;
+------+
| s    |
+------+
| a    |
| b    |
| a,b  |
| b,c  |
+------+
4 rows in set (0.00 sec)
mysql> insert into s_diff values('a,b'),('b,c'),('a,b,c');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from s_diff;
+-------+
| s     |
+-------+
| a     |
| b     |
| a,b   |
| b,c   |
| a,b   |
| b,c   |
| a,b,c |
+-------+
7 rows in set (0.01 sec)

mysql> insert into s_diff values('a,b'),('b,c'),('a,b,c'),('a,c,c');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from s_diff;
+-------+
| s     |
+-------+
| a     |
| b     |
| a,b   |
| b,c   |
| a,b   |
| b,c   |
| a,b,c |
| a,b   |
| b,c   |
| a,b,c |
| a,c   |
+-------+
11 rows in set (0.00 sec)

mysql> insert into s_diff values('a,d,f');
ERROR 1265 (01000): Data truncated for column 's' at row 1

可以看出set类型可以从允许值的集合中选择任意1个或多个元素进行组合，所以对于输入的值只要是在允许值的组合范围内，都可以正确的写入到set类型中，对于超过的允许值范围如（‘a,d,f’）是不能写入到上面的例子中的，而对于（‘a,c,c’）这样包含的重复成员将只取一次，写入后的结果为’a,c’。