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

太过爱你忘了你带给我的痛 2022-11-19 11:11 360阅读 0赞

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

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

## MySQL字符串类型 ##

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

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

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

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

> 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 之间的差别，如下表所示。

<table> 
 <thead> 
  <tr> 
   <th>插入值</th> 
   <th>CHAR(4)</th> 
   <th>存储需求</th> 
   <th>VARCHAR(4)</th> 
   <th>存储需求</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>’ ’</td> 
   <td>’ ’</td> 
   <td>4字节</td> 
   <td>‘’</td> 
   <td>1字节</td> 
  </tr> 
  <tr> 
   <td>‘ab’</td> 
   <td>'ab ’</td> 
   <td>4字节</td> 
   <td>‘ab’</td> 
   <td>3字节</td> 
  </tr> 
  <tr> 
   <td>‘abc’</td> 
   <td>'abc ’</td> 
   <td>4字节</td> 
   <td>‘abc’</td> 
   <td>4字节</td> 
  </tr> 
  <tr> 
   <td>‘abcd’</td> 
   <td>‘abcd’</td> 
   <td>4字节</td> 
   <td>‘abcd’</td> 
   <td>5字节</td> 
  </tr> 
  <tr> 
   <td>‘abcdef’</td> 
   <td>‘abcd’</td> 
   <td>4字节</td> 
   <td>‘abcd’</td> 
   <td>5字节</td> 
  </tr> 
 </tbody> 
</table>

对比结果可以看到，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’），该列可以取的值和每个值的索引如下表所示。

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>索引</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>‘’</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>’first</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>second</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>third</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

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’。