数据类型文本字符串类型

9.3　文本字符串类型

在MySQL中，字符串类型可以存储文本字符串数据，也可以存储一些图片、音频和视频数据，也就是二进制数据。因此在MySQL中，字符串类型可以分为文本字符串类型和二进制字符串类型。本节就对MySQL中支持的文本字符串类型进行简单的介绍。

9.3.1　文本字符串类型概述

MySQL中，文本字符串总体上分为CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET和JSON等类型，每种存储类型所占的存储空间如表9-8所示。

表9-8　文本字符串类型所占用的存储空间

不同的文本字符串类型，其值的长度、长度范围和占用的存储空间都是不同的。在使用文本字符串类型存储数据时，需要综合考虑文本字符串类型的长度和存储空间，再决定使用哪些合适的数据类型。

9.3.2　CHAR与VARCHAR类型

CHAR和VARCHAR类型都可以存储比较短的字符串。

CHAR类型的字段长度是固定的，为创建表时声明的字段长度，最小取值为0，最大取值为255。如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。对于CHAR类型的数据来说，定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

VARCHAR类型修饰的字符串是一个可变长的字符串，长度的最小值为0，最大值为65535。检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

创建数据表t15，在t15表中包含两个字段，分别为vc和c，其中字段vc的数据类型为VARCHAR(4)，字段c的数据类型CHAR(4)。

mysql> CREATE TABLE t15 (
    -> vc VARCHAR(4),
    -> c CHAR(4)
    -> );
Query OK, 0 rows affected (0.09 sec)

分别向vc字段和c字段插入字符串abc。

mysql> INSERT INTO t15 (vc, c) VALUES ('abc', 'abc');
Query OK, 1 row affected (0.22 sec)

查看t15表中的数据。

mysql> SELECT * FROM t15;
+------+------+
| vc   | c    |
+------+------+
| abc  | abc  |
+------+------+
1 row in set (0.00 sec)

向t15表中正确插入了数据。接下来查看t15数据表中vc字段和c字段的长度。

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

此时vc字段和c字段的长度都是3。

接下来清空t15表中的数据，并向t15表中的vc字段和c字段插入字符串“a　”（注意a后面有两个空格）。

mysql> DELETE FROM t15;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t15 (vc, c) VALUES ('a  ', 'a  ');
Query OK, 1 row affected (0.00 sec)

查看t15表中的数据。

mysql> SELECT * FROM t15;
+------+------+
| vc   | c    |
+------+------+
| a    | a    |
+------+------+
1 row in set (0.00 sec)

此时看不出太大的差别。接下来再次查看字段vc和字段c的长度。

mysql> SELECT LENGTH(vc), LENGTH(c) FROM t15;
+------------+-----------+
| LENGTH(vc) | LENGTH(c) |
+------------+-----------+
|          3 |         1 |
+------------+-----------+
1 row in set (0.01 sec)

通过查看vc字段和c字段的长度可以发现，此时vc字段的长度为3，c字段的长度为1。这是因为MySQL在检索CHAR类型的字段时，会去除尾部的空格；而在检索VARCHAR类型的字段时，则不会去除尾部的空格。因此，当向CHAR类型的字段和VARCHAR类型的字段插入尾部带有空格的相同字符串时，其检索出的数据长度是不同的。

接下来，查询t15表的数据时，为字段vc和字段c后面追加一个字符b。

mysql> SELECT CONCAT(vc, 'b'), CONCAT(c, 'b') FROM t15;
+-----------------+----------------+
| CONCAT(vc, 'b') | CONCAT(c, 'b') |
+-----------------+----------------+
| a  b            | ab             |
+-----------------+----------------+
1 row in set (0.00 sec)

可以看到，VARCHAR类型的字段vc保留了尾部的空格，而CHAR类型的字段c则去除了尾部的空格。

9.3.3　TEXT类型

在MySQL中，Text用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT类型。在向TEXT类型的字段保存和查询数据时，不会删除数据尾部的空格，这一点和VARCHAR类型相同。其中，每种TEXT类型保存的数据长度和所占用的存储空间不同，具体如表9-8所示。

创建数据表t16，t16数据表中包含一个TEXT类型的字段t。

mysql> CREATE TABLE t16 (
    -> t TEXT
    -> );
Query OK, 0 rows affected (0.03 sec)

向t16表中的t字段插入数据“a　”，注意a后面有两个空格。

mysql> INSERT INTO t16 (t) VALUES ('a  ');
Query OK, 1 row affected (0.22 sec)

接下来，查看t16表中的数据长度。

mysql> SELECT LENGTH(t) FROM t16;
+-----------+
| LENGTH(t) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

数据长度为3。接下来，查询数据时在后面追加字符b。

mysql> SELECT CONCAT(t, 'b') FROM t16; 
+----------------+
| CONCAT(t, 'b') |
+----------------+
| a  b           |
+----------------+
1 row in set (0.00 sec)

可以看到，在保存和查询数据时，并没有删除TEXT类型的数据尾部的空格。

9.3.4　ENUM类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定，其所需要的存储空间由定义ENUM类型时指定的成员个数决定。当ENUM类型包含1～255个成员时，需要1个字节的存储空间；当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。ENUM类型的成员个数的上限为65535个。

创建数据表t17，t17数据表中含有一个ENUM类型的字段e，ENUM类型的成员为A、B、C。

mysql> CREATE TABLE t17 (
    -> e ENUM ('A', 'B', 'C')
    -> );
Query OK, 0 rows affected (0.04 sec)

接下来，向t17表中插入A和B。

mysql> INSERT INTO t17 (e) VALUES ('A'), ('B');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看t17表中的数据。

mysql> SELECT * FROM t17;
+------+
| e    |
+------+
| A    |
| B    |
+------+
2 rows in set (0.00 sec)

正确插入并显示了A和B。

清空t17表中的数据，并向t17表中插入a和b。

mysql> DELETE FROM t17;
Query OK, 2 rows affected (0.00 sec)
mysql> INSERT INTO t17 (e) VALUES ('a'), ('b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

接下来，查看t17表中的数据。

mysql> SELECT * FROM t17;
+------+
| e    |
+------+
| A    |
| B    |
+------+
2 rows in set (0.00 sec)

定义e字段时，ENUM类型的成员被定义为大写的A、B、C当插入小写的a和b时，MySQL会将其自动转化为大写的A和B进行存储。

再次清空t17表中的数据，并向t17表中插入字符1和2。

mysql> INSERT INTO t17 (e) VALUES ('1'), ('2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

再次查看t17表中的数据。

mysql> SELECT * FROM t17;
+------+
| e    |
+------+
| A    |
| B    |
+------+
2 rows in set (0.00 sec)

当向t17表中插入字符1和2时，查询数据时会显示A和B，也就是说在ENUM类型中，第一个成员的下标为1，第二个成员的下标为2，以此类推。

接下来，再次清空t17表中的数据，向表中插入NULL。

mysql> DELETE FROM t17;
Query OK, 2 rows affected (0.01 sec)
mysql> INSERT INTO t17 (e) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

查询表中的数据。

mysql> SELECT * FROM t17;
+------+
| e    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的。

注意：在定义字段时，如果将ENUM类型的字段声明为NULL时，NULL为有效值，默认值为NULL；如果将ENUM类型的字段声明为NOT NULL时，NULL为无效的值，默认值为ENUM类型成员的第一个成员。另外，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

9.3.5　SET类型

SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为64。当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的，具体如表9-9所示。

表9-9　SET类型所占用的存储空间

SET类型在存储数据时一定程度上，成员个数越多，其占用的存储空间越大。

注意：SET类型在选取成员时，可以一次选择多个成员，这一点与ENUM类型不同。

创建数据表t18，t18数据表中包含一个SET类型的字段s，SET类型的成员为A、B和C。

mysql> CREATE TABLE t18 (
    -> s SET ('A', 'B', 'C')
    -> );
Query OK, 0 rows affected (0.01 sec)

向t18表中插入数据A、A,B。

mysql> INSERT INTO t18 (s) VALUES ('A'), ('A,B');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看t18表中的数据。

mysql> SELECT * FROM t18;
+------+
| s    |
+------+
| A    |
| A,B  |
+------+
2 rows in set (0.00 sec)

可以向SET类型的字段中插入多个以逗号分隔的有效成员值。

清空t18表中的数据，并向t18表中插入数据A,B,C,A。

mysql> DELETE FROM t18;
Query OK, 2 rows affected (0.00 sec)
mysql> INSERT INTO t18 (s) VALUES ('A,B,C,A');
Query OK, 1 row affected (0.00 sec)

查看t18表中的数据。

mysql> SELECT * FROM t18;
+-------+
| s     |
+-------+
| A,B,C |
+-------+
1 row in set (0.00 sec)

当向t18表中的SET类型的字段s插入重复的SET类型成员时，MySQL会自动删除重复的成员。

向t18表中插入A,B,C,D。

mysql> INSERT INTO t18 (s) VALUES ('A,B,C,D');
ERROR 1265 (01000): Data truncated for column 's' at row 1

当向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。

9.3.6　JSON类型

在MySQL 5.7中，就已经支持JSON数据类型。在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效。

创建数据表t19，t19表中包含一个JSON类型的字段j。

mysql> CREATE TABLE t19 (
    -> j JSON
    -> );
Query OK, 0 rows affected (0.01 sec)

向t19表中插入JSON数据。

mysql> INSERT INTO t19 (j) VALUES ('{"name":"binghe", "age":18, "address":{"province":"sichuan", "city":"chengdu"}}');
Query OK, 1 row affected (0.00 sec)

查询t19表中的数据。

mysql> SELECT * FROM t19;
+--------------------------------------------------------------------------------------+
| j                                                                                    |
+--------------------------------------------------------------------------------------+
| {"age": 18, "name": "binghe", "address": {"city": "chengdu", "province": "sichuan"}} |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

当需要检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号。

mysql> SELECT j->'$.name' AS name, j->'$.address.province' AS province, j->'$.address.city' AS city FROM t19;
+----------+-----------+-----------+
| name     | province  | city      |
+----------+-----------+-----------+
| "binghe" | "sichuan " | "chengdu" |
+----------+-----------+-----------+
1 row in set (0.01 sec)

通过“->”和“->>”符号，从JSON字段中正确查询出了指定的JSON数据的值。