MySQL 数据类型

本章详细的介绍了 MySQL 中主要的数据类型，包括：整数类型、浮点类型、定点数类型、位类型、日期时间类型、文本字符串类型、枚举类型、集合类型、二进制字符串类型、JSON 类型。并详细介绍了它们的使用和区别，以及实际中的选择。

1 MySQL 的数据类型

1.1 常见的数据类型

对于任何一个数据表，每一行的每一列对应的元素都是下列数据类型的一种。

类型 关键字
整数类型 TINYINT SMALLINT MEDIUMINT INT (INTEGER) BIGINT
浮点类型 FLOAT DOUBLE
定点数类型 DECIMAL
位类型 BIT
日期时间类型 YEAR TIME DATE DATETIME TIMESTAMP
文本字符串类型 CHAR VARCHAR TINYTEXT TEXT MEDIUMTEXT LONGTEXT
枚举类型 ENUM
集合类型 SET
二进制字符串类型 BINARY VARBINARY TINYBLOB BLOB MEDIUMBLOB LONGBLOB
JSON 类型 JSON对象 JSON 数组
空间数据类型 (单值类型) GEOMETRY POINT LINESTRING POLYGON
空间数据类型 (集合类型) MULTIPOINT MULTILINESTRING MULTIPOLYGON GEOMETRYCOLLECTION

1.2 常见数据类型的属性

在常见数据表时，除了可以指定列的数据类型，还可以指定列的属性。

关键字 含义
NULL 数据列可包含 NULL 值
NOT NULL 数据列不允许包含 NULL 值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

下面分门别类的介绍这些数据类型：

2 整数类型

整数类型一共有 5 种，分别是：TINYINT SMALLINT MEDIUMINT INT (INTEGER) BIGINT 。下面的表展示了他们的区别和特征。

2.1 整数类型概述

整数类型 字节 有符号数取值范围 无符号数取值范围
TINYINT 1 −27∼27-2^7 \sim 2^7 0∼280 \sim 2^8
SMALLINT 2 −215∼215-2^{15} \sim 2^{15} 0∼2160 \sim 2^{16}
MEDIUMINT 3 −223∼223-2^{23} \sim 2^{23} 0∼2240 \sim 2^{24}
INT 4 −231∼231-2^{31} \sim 2^{31} 0∼2320 \sim 2^{32}
BIGINT 8 −263∼263-2^{63} \sim 2^{63} 0∼2640 \sim 2^{64}

2.2 可选属性

整数类型有 3 个可选属性：M UNSIGNED ZEROFILL

2.2.1 M 显示宽度

M : 表示显示宽度，取值范围是 (0, 255) 。例如，int(5) 表示当数据宽度小于 5 位的时候在数字前面需要用字符填满宽度。必须配合 ZEROFILL 表示用 0 填充，否则指定显示宽度无效。

如果数据宽度超过显示宽度限制，不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存。

不过，对于 MySQL8.0 ，整数数据类型【不推荐】使用显示宽度属性。如果不指定，则系统为每一种类型指定默认的宽度值。

1
2
3
4
5
6
7
8
CREATE TABLE test_int1
(
    x TINYINT,
    y SMALLINT,
    z MEDIUMINT,
    m INT,
    n BIGINT
);
1
2
3
4
5
6
7
8
9
10
11
mysql> DESC test_int1;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| x     | tinyint   | YES  |     | NULL    |       |
| y     | smallint  | YES  |     | NULL    |       |
| z     | mediumint | YES  |     | NULL    |       |
| m     | int       | YES  |     | NULL    |       |
| n     | bigint    | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.01 sec)

在 MySQL 8.0 中，不再推荐显式的使用 M 显示宽度属性。

2.2.2 UNSIGNED 无符号类型 (非负)

UNSIGNED ：无符号类型（非负），无符号整数类型的最小取值为 0 。所以，如果需要保存非负整数值时，可以将整数类型设置为无符号类型。

1
2
3
4
CREATE TABLE test_int3
(
    f1 INT UNSIGNED
);
1
2
3
4
5
6
7
mysql> DESC test_int3;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1    | int unsigned | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

2.2.3 ZEROFILL 0 填充

ZEROFILL ：0填充，（如果某列设置了 ZEROFILL 属性，那么 MySQL 会自动为当前列添加 UNSIGNED 属性），ZEROFILL 只是表示不够 M 位时，用 0 在左边填充。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE test_int2
(
    f1 INT,
    f2 INT(5),
    f3 INT(5) ZEROFILL
);

INSERT INTO test_int2(f1, f2, f3)
VALUES (1, 123, 123);

INSERT INTO test_int2(f1, f2)
VALUES (123456, 123456);

INSERT INTO test_int2(f1, f2, f3)
VALUES (123456, 123456, 123456);
1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM test_int2;
+--------+--------+--------+
| f1     | f2     | f3     |
+--------+--------+--------+
|      1 |    123 |  00123 |  -- 填充 0
| 123456 | 123456 |   NULL |
| 123456 | 123456 | 123456 |  -- 超过显示宽度不影响
+--------+--------+--------+
3 rows in set (0.00 sec)

3 浮点类型

浮点数和定点数类型的都可以处理小数。MySQL 支持的浮点数类型，分别是 FLOAT DOUBLE REAL 。

3.1 浮点类型概述

  • FLOAT 表示单精度浮点数；

  • DOUBLE 表示双精度浮点数；

  • REAL 默认就是 DOUBLE 。

若启用 REAL_AS_FLOAT 则 MySQL 就认为 REAL 是 FLOAT 。

1
SET sql_mode = "REAL_AS_FLOAT";
类型 占有字节数
FLOAT 4
DOUBLE 8

3.2 数据精度【不推荐使用】

使用 FLOAT(M, D) 或 DOUBLE(M, D) 可以指定浮点数的精度：M 代表整数位+小数位，D 代表小数位。

  • 如果存储时，整数部分超出了范围，MySQL 就会报错，不允许存这样的值
  • 如果存储时，小数点部分若超出范围，则观察四舍五路后是否满足

从 MySQL 8 开始，FLOAT(M, D) 和 DOUBLE(M, D) 用法在官方文档中已经明确【不推荐使用】。另外，关于浮点型的 UNSIGNED 也【不推荐使用】。

1
2
3
4
5
6
7
CREATE TABLE test_double1
(
    f1 FLOAT,
    f2 FLOAT(5, 2),
    f3 DOUBLE,
    f4 DOUBLE(5, 2)
);
1
2
3
4
5
6
7
8
9
10
mysql> DESC test_double1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| f1    | float       | YES  |     | NULL    |       |
| f2    | float(5,2)  | YES  |     | NULL    |       |
| f3    | double      | YES  |     | NULL    |       |
| f4    | double(5,2) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

存储报错/警告情况：超出范围

1
2
3
mysql> INSERT INTO test_double1
    -> VALUES (123.456, 1234.456, 123.4567, 123.45);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1

正常插入数据：

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO test_double1
VALUES (123.456, 123.456, 123.4567, 123.45);

SELECT *
FROM test_double1;

+---------+--------+----------+--------+
| f1      | f2     | f3       | f4     |
+---------+--------+----------+--------+
| 123.456 | 123.46 | 123.4567 | 123.45 |
+---------+--------+----------+--------+
1 row in set (0.00 sec)

3.3 精度误差

采用浮点数存储小数会出现明显的精度误差，因为浮点数采用二进制的方式存储小数。以 9.6259.625 为例，其在 MySQL 的浮点数的存储方式为 1001.1011001.101 即 1.001101×231.001101 \times 2^3 。此时如果结尾不是 0, 50,\ 5 ，则无法用二进制精确表达。

因此，如果用到浮点数，由于浮点数是不准确的，所以避免使用 == 判断两个浮点数是否相等。在一些对精确度要求较高的项目中，不要使用浮点数。

使用 MySQL 中精准的数据类型：定点数类型 DECIMAL 。

4 定点数类型

4.1 定点数概述

使用 DECIMAL(M, D) 的方式表示高精度小数。其中，M 被称为精度，D 被称为标度。其中范围为 0 <= M <= 65 0 <= D <= 30 D < M 。例如， DECIMAL(5, 2) 表示该列取值范围是 -999.99~999.99 。

类型 字节数 含义
DECIMAL(M, D) M+2 字节 有效范围由 M 和 D 决定

4.2 精度

DECIMAL(M, D) 的最大取值范围与 DOUBLE 类型一样，但是有效的数据范围是由 M 和 D 决定的

  • DECIMAL 的存储空间并不是固定的，由精度值 M 决定，总共占用的存储空间为 M+2 个字节。
  • 定点数在 MySQL 内部以 字符串 的形式进行存储，这就决定了它一定是精准的。
  • 当 DECIMAL 类型不指定精度和标度时，其默认为 DECIMAL(10, 0) 。当数据的精度超出了定点数类型的精度范围时，同样会进行四舍五入处理。

浮点数 vs 定点数

  • 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景。
  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景。
1
2
3
4
5
CREATE TABLE test_decimal1
(
    f1 DECIMAL,
    f2 DECIMAL(5, 2)
);
1
2
3
4
5
6
7
8
mysql> DESC test_decimal1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| f1    | decimal(10,0) | YES  |     | NULL    |       |
| f2    | decimal(5,2)  | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

5 位类型

BIT 类型中存储的是二进制值。

5.1 位类型概述

类型 长度 长度范围 占有空间
BIT(M) M 1 <= M <= 64 约 (M + 7) / 8 个字节

位数 M 的含义：

  • BIT 类型，如果没有指定 M ，默认是 1 位，表示只能存 1 位的二进制值。

  • M 是表示二进制的位数，位数最小值为 1 ，最大值为 64 。

5.2 实际使用

创建表并指定属性

1
2
3
4
5
6
CREATE TABLE test_bit1
(
    f1 BIT,
    f2 BIT(5),
    f3 BIT(64)
);
1
2
3
4
5
6
7
8
9
mysql> DESC test_bit1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| f1    | bit(1)  | YES  |     | NULL    |       |
| f2    | bit(5)  | YES  |     | NULL    |       |
| f3    | bit(64) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

在向 BIT 类型的字段中插入数据时，一定要确保插入的数据在 BIT 类型支持的范围内，即位数不可超过 M 。

1
2
3
mysql> INSERT INTO test_bit1(f1)
    -> VALUES (2);
ERROR 1406 (22001): Data too long for column 'f1' at row 1

使用 SELECT 命令查询位字段时，可以用 BIN() 或 HEX() 或 + 0 的方法进行读取

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM test_bit1;
+------------+------------+------------+
| f1         | f2         | f3         |
+------------+------------+------------+
| 0x01       | NULL       | NULL       |
| NULL       | 0x17       | NULL       |
| 0x01       | NULL       | NULL       |
+------------+------------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
mysql> SELECT BIN(f2), HEX(f2), f2 + 0
    -> FROM test_bit1;
+---------+---------+--------+
| BIN(f2) | HEX(f2) | f2 + 0 |
+---------+---------+--------+
| NULL    | NULL    |   NULL |
| 10111   | 17      |     23 |
| NULL    | NULL    |   NULL |
+---------+---------+--------+
3 rows in set (0.00 sec)

BIN() 查看二进制；HEX() 查看十六进制；+ 0 间接转换为十进制。

6 日期与时间类型

日期和时间类型主要有：YEAR TIME DATE DATETIME TIMESTAMP

  • YEAR 类型通常用来表示 年
  • DATE 类型通常用来表示 年、月、日
  • TIME 类型通常用来表示 时、分、秒
  • DATETIME 类型通常用来表示 年、月、日、时、分、秒
  • TIMESTAMP 类型通常用来表示 带时区的年、月、日、时、分、秒

6.1 日期与时间类型概述

类型 字节 日期格式 最小值
YEAR 1 YYYY YY 1901 - 2155
TIME 3 HH:MM:SS -838:59:59- 838:59:59
DATE 3 YYYY-MM-DD 1000-01-01 - 9999-12-03
DATETIME 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 - 9999-12-31 23:59:59
TIMESTAMP 4 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 UTC - 2038-01-19 03:14:07 UTC

TIME 的范围是因为可以用来表示时间间隔。

6.2 各种类型

6.2.1 YEAR 类型

YEAR 类型用来表示年份，只需要 1 个字节的存储空间。

以 4 位字符串或数字格式表示 YEAR 类型，其格式为 YYYY ，最小值为 1901 ，最大值为 2155 。以 2 位字符串格式表示 YEAR 类型，最小值为 00 ，最大值为 99 ：

  • 当取值为 01 到 69 时，表示 2001 到 2069

  • 当取值为 70 到 99 时，表示 1970 到 1999

  • 当取值为整数的 0 或 00 添加时，表示 0000 年

  • 当取值为日期/字符串的 '0' 添加时，表示 2000 年

【不推荐】使用 2 位格式。默认 YEAR 就是 YEAR(4) 即为 4 位。同时，也【不推荐】指定显示宽度 (M) 。最好直接使用 YEAR 。

创建数据表

1
2
3
4
5
CREATE TABLE test_year
(
    f1 YEAR,  -- 推荐
    f2 YEAR(4)
);

插入数据

1
2
3
4
5
6
7
8
INSERT INTO test_year
VALUES ('2020', '2021');  -- 推荐

INSERT INTO test_year
VALUES ('45', '71');  -- 不推荐

INSERT INTO test_year
VALUES (0, '0');  -- 0 和 '0' 对应 0000 年和 2000 年
1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM test_year;
+------+------+
| f1   | f2   |
+------+------+
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
+------+------+
3 rows in set (0.00 sec)

6.2.2 DATE 类型

DATE 类型表示日期，格式为 YYYY-MM-DD ，其中，YYYY 表示年份，MM 表示月份，DD 表示日期。需要 3 个字节的存储空间。

在向 DATE 类型的字段插入数据时：

  • 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期（YYYYMMDD 格式会被转化为YYYY-MM-DD 格式）
  • 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期
  • 使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期

仍然【不推荐】使用 2 位表达年。

创建数据表

1
2
3
4
CREATE TABLE test_date1
(
    f1 DATE
);

插入数据：字符串/整型/CURRENT_DATE() NOW() 函数

1
2
3
4
5
6
7
8
INSERT INTO test_date1
VALUES ('2024-04-01'),
       ('20240401'),
       (20201001);

INSERT INTO test_date1
VALUES (CURRENT_DATE()),
       (NOW());

6.2.3 TIME 类型

TIME 类型用来表示时间。需要 3 个字节的存储空间，格式为 HH:MM:SS 其中，HH 表示小时，MM 表示分钟，SS 表示秒。

在向 TIME 类型的字段插入数据时：

  • 可以使用带有冒号的字符串，格式为 'D HH:MM:SS' 'HH:MM:SS' 'HH:MM' 'D HH:MM' 'D HH' 'SS' 。其中 D 表示天，其最小值为 0 ，最大值为 34 ，D 会被转化为小时，D * 24 + HH
  • 可以使用不带有冒号的字符串或者数字，格式为 'HHMMSS' HHMMSS 。特别地，1210 表示 00:12:10
  • 使用 CURRENT_TIME() NOW() 插入当前系统的时间

创建数据表

1
2
3
4
CREATE TABLE test_time1
(
    f1 TIME
);

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO test_time1
VALUES ('2 12:30:29'),
       ('12:35:29');

INSERT INTO test_time1
VALUES ('123520'),
       (124011),
       (1210);

INSERT INTO test_time1
VALUES (NOW()),
       (CURRENT_TIME());

6.2.4 DATETIME 类型

DATETIME 总共需要 8 个字节的存储空间。在格式上为 DATE 类型和 TIME 类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中 YYYY 表示年份，MM 表示月份，DD 表示日期，HH 表示小时，MM 表示分钟，SS 表示秒。

在向 DATETIME 类型的字段插入数据时：

  • 以 'YYYY-MM-DD HH:MM:SS' 格式或者 'YYYYMMDDHHMMSS' 格式的字符串插入，被转化为 YYYY-MM-DD HH:MM:SS 格式
  • 【不推荐】以 'YY-MM-DD HH:MM:SS' 格式或者 'YYMMDDHHMMSS' 格式的字符串插入，被转化为 YYYY-MM-DD HH:MM:SS 格式
  • 使用 CURRENT_TIME() NOW() 插入当前系统的时间

创建数据表

1
2
3
4
CREATE TABLE test_datetime1
(
    dt DATETIME
);

插入数据

1
2
3
4
5
6
7
8
9
10
INSERT INTO test_datetime1
VALUES ('2024-04-01 06:50:30'),
       ('20240401065030');

INSERT INTO test_datetime1
VALUES (20240402065030);

INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()),
       (NOW());
1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM test_datetime1;
+---------------------+
| dt                  |
+---------------------+
| 2024-04-01 06:50:30 |
| 2024-04-01 06:50:30 |
| 2024-04-02 06:50:30 |
| 2025-04-01 19:56:03 |
| 2025-04-01 19:56:03 |
+---------------------+
5 rows in set (0.00 sec)

6.2.5 TIMESTAMP 类型

TIMESTAMP 显示格式与 DATETIME 类型相同，都是 YYYY-MM-DD HH:MM:SS ，需要 4 个字节的存储空间。但是 TIMESTAMP 存储的时间范围比 DATETIME 要小很多，只能存储 '1970-01-01 00:00:01 UTC' 到 '2038-01-19 03:14:07 UTC' 之间的时间。其中，UTC 表示世界统一时间，也叫作世界标准时间。

  • TIMESTAMP 会对当前时间所在的时区进行转换。因此，使用 TIMESTAMP 存储的同一个时间值，在不同的时区查询时会显示不同的时间。
  • 插入的时间超出了 TIMESTAMP 类型的范围，则报错。

创建数据表

1
2
3
4
CREATE TABLE test_timestamp1
(
    ts TIMESTAMP
);

插入数据

1
2
3
4
5
6
7
INSERT INTO test_timestamp1
VALUES ('1970-01-02 03:04:50'),
       ('19700102030405');

INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()),
       (NOW());
1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM test_timestamp1;
+---------------------+
| ts                  |
+---------------------+
| 1970-01-02 03:04:50 |
| 1970-01-02 03:04:05 |
| 2025-04-01 20:02:42 |
| 2025-04-01 20:02:42 |
+---------------------+
4 rows in set (0.01 sec)

超出范围

1
2
3
mysql> INSERT INTO test_timestamp1
    -> VALUES ('2038-01-20 03:14:07');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-20 03:14:07' for column 'ts' at row 1

修改时区

1
SET time_zone = '+9:00';

TIMESTAMP 底层存储的是距离 1970-1-1 0:0:0 0 毫秒的毫秒值。

7 文本字符串类型

文本字符串分为： CHAR VARCHAR TINYTEXT TEXT MEDIUMTEXT LONGTEXT ENUM SET类型。除了传统的 CHAR VARCHAR TINYTEXT TEXT MEDIUMTEXT LONGTEXT 类型，ENUM 类型也被称为枚举类型，SET 类型被称为集合类型。

7.1 CHAR 与 VARCHAR 类型

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

类型 特点 长度范围 占有存储空间
CHAR(M) 固定长度 0 <= M <= 255 M 个字节
VARCHAR(M) 可变长度 0 <= M <= 65535 (实际长度 + 1) 个字节

7.1.1 CHAR 类型

CHAR 类型：

  • CHAR(M) 需要预先定义字符串长度。如果不指定，则长度默认是 1 个字符。
  • 如果数据的实际长度比 CHAR 类型声明的长度小，则会在右侧填充空格以达到指定的长度。
  • 当检索 CHAR 类型的数据时，会去除尾部的空格。
  • 定义 CHAR 类型字段时，声明的 M 即为 CHAR 类型字段所占的存储空间的字节数。

创建数据表

1
2
3
4
5
CREATE TABLE test_char1
(
    c1 CHAR,
    c2 CHAR(5)
);

插入数据

1
2
3
4
5
INSERT INTO test_char1
VALUES ('a', 'test');

INSERT INTO test_char1(c2)
VALUES('a  ');

检索数据

1
2
3
4
5
6
7
8
9
mysql> SELECT CHAR_LENGTH(c2)
    -> FROM test_char1;
+-----------------+
| CHAR_LENGTH(c2) |
+-----------------+
|               4 |  -- 'test' 4 个字符
|               1 |  -- 'a  '  1 个字符，说明删去了右边的空格
+-----------------+
2 rows in set (0.00 sec)

7.1.2 VARCHAR 类型

VARCHAR 类型：

  • VARCHAR(M) 必须指定长度 M ，否则报错。
  • MySQL 5.0 以上，VARCHAR(M) 指的是 M 个字符，不是 M 个字节。
  • 检索 VARCHAR 类型的字段数据时，会保留数据尾部的空格。
  • VARCHAR 类型的字段所占用的存储空间为字符串实际长度加1个字节。

创建、插入、检索方法与 CHAR 相同，只是检索 VARCHAR 类型的字段数据时，会保留数据尾部的空格，且二者的存储方式不同。

CHAR 和 VARCHAR 比较：存储很短的信息、固定长度信息、以及需要频繁修改数据时，都推荐使用 CHAR 。（最后一个是因为，VARCHAR 是可变的，其存储长度需要计算实际字符串长度，频繁修改会增加计算负担）

7.2 TEXT 类型

TEXT 用来保存文本类型的字符串，总共包含 4 种类型，分别为 TINYTEXT TEXT MEDIUMTEXT LONGTEXT 。保存和查询数据时，自动按照实际长度存储，不需要预先定义长度。

  • 由于实际存储的长度不确定，TEXT 类型的字段不能作主键。
  • 存储和查询时，都不删除右边的空格。

创建、插入和检索

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE test_text
(
    tx TEXT
);

INSERT INTO test_text
VALUES ('With space on the right.    ');

SELECT CHAR_LENGTH(tx)
FROM test_text;  
# 28 没有删除右边的空格
1
2
3
4
5
6
7
mysql> SELECT * FROM test_text;
+------------------------------+
| tx                           |
+------------------------------+
| With space on the right.     |
+------------------------------+
1 row in set (0.00 sec)

TEXT 文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用 CHAR、VARCHAR 来代替。

TEXT 和 BLOB 类型的数据是大文件，删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含 TEXT 类型字段，建议单独用一个表。

7.3 ENUM 类型

ENUM 类型（枚举类型），取值范围需要在定义字段时进行指定。设置字段值时，ENUM 类型只允许从成员中选取单个值，不能一次选取多个值。其所需要的存储空间由定义 ENUM 类型时指定的成员个数决定：

  • 当 ENUM 类型包含 1 ~ 255 个成员时，需要 1 个字节的存储空间
  • 当 ENUM 类型包含 256 ~ 65535 个成员时，需要 2 个字节的存储空间
  • ENUM 类型的成员个数的上限为 65535 个

下面使用例子说明：

1
2
3
4
CREATE TABLE test_enum
(
    season ENUM ('春', '夏', '秋', '冬', 'unknown')
);

添加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 按照值插入
INSERT INTO test_enum
VALUES ('春'),
       ('秋');

INSERT INTO test_enum
VALUES ('unknown');

# 允许按照数字索引的方式获取指定位置的枚举值
INSERT INTO test_enum
VALUES ('1'),
       (3);

-- 报错：未知数字索引
INSERT INTO test_enum
VALUES ('a');

查看

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM test_enum;
+---------+
| season  |
+---------+
| 春      |
| 秋      |
| unknown |
| 春      |
| 秋      |
| NULL    |
+---------+
6 rows in set (0.01 sec)

7.4 SET 类型

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

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

创建

1
2
3
4
CREATE TABLE test_set
(
    s SET ('A', 'B', 'C')
);

插入数据

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO test_set (s)
VALUES ('A'),
       ('A,B');  -- 插入多个成员 'A' 和 'B'

# 插入重复的 SET 类型成员时，自动删除重复的成员
INSERT INTO test_set (s)
VALUES ('A,B,C,A');

-- 报错：插入 SET 成员中不存在的值
INSERT INTO test_set (s)
VALUES ('A, B, C, D');

查询

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM test_set;
+-------+
| s     |
+-------+
| A     |
| A,B   |
| A,B,C |
+-------+
3 rows in set (0.00 sec)

8 二进制字符串类型

MySQL 中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。MySQL 中支持的二进制字符串类型主要包括 BINARY VARBINARY TINYBLOB BLOB MEDIUMBLOB LONGBLOB 。

8.1 BINARY 与 VARBINARY 类型

BINARY(M) 为固定长度的二进制字符串，M 表示最多能存储的字节数，取值范围是 0~255 。如果未指定 M ，表示只能存储 1 个字节。如果字段值不足 M 个字节，将在右边填充 '\0' 以补齐指定长度。

VARBINARY(M) 为可变长度的二进制字符串，M 表示最多能存储的字节数。VARBINARY 类型的数据除了存储数据本身外，还需要 1 或 2 个字节来存储数据的字节数。VARBINARY类型**必须指定 M **，否则报错。

创建表

1
2
3
4
5
6
CREATE TABLE test_binary1
(
    f1 BINARY,
    f2 BINARY(3),
    f3 VARBINARY(10)
);

插入数据

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO test_binary1(f1, f2)
VALUES ('a', 'a');

INSERT INTO test_binary1(f1, f2)
VALUES ('2', '256');

INSERT INTO test_binary1(f2, f3)
VALUES ('8', '中文');

INSERT INTO test_binary1(f2, f3)
VALUES (8, 1024);
1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM test_binary1;
+------------+------------+----------------+
| f1         | f2         | f3             |
+------------+------------+----------------+
| 0x61       | 0x610000   | NULL           |
| 0x32       | 0x323536   | NULL           |
| NULL       | 0x380000   | 0xE4B8ADE69687 |
| NULL       | 0x380000   | 0x31303234     |
+------------+------------+----------------+
4 rows in set (0.00 sec)

8.2 BLOB 类型

BLOB 是一个二进制大对象，可以容纳可变数量的数据。BLOB 包括 TINYBLOB BLOB MEDIUMBLOB LONGBLOB ，它们可容纳值的最大长度不同。

实际上，往往不会在 MySQL 数据库中使用 BLOB 类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到 MySQL 中。

创建的例子

1
2
3
4
5
CREATE TABLE test_blob1
(
    id  INT,
    img MEDIUMBLOB
);

9 JSON 类型

JSON (JavaScript Object Notation) 是一种轻量级的数据交换格式。

举例说明：创建数据表，表中包含一个 JSON 类型的字段 js

1
2
3
4
CREATE TABLE test_json
(
    js JSON
);

向表中插入 JSON 数据

1
2
3
4
5
6
7
8
9
INSERT INTO test_json (js)
VALUES ('{
  "name": "Mike",
  "age": 21,
  "contact": {
    "tel": "123456789",
    "email": "123@work.com"
  }
}');

查询数据

**当需要检索 JSON 类型的字段中数据的具体值时，可以使用 -> 或 ->> **

1
2
3
4
5
6
SELECT js -> '$.name'          AS name,
       js -> '$.age'           AS age,
       js -> '$.contact.tel'
                               AS tel,
       js -> '$.contact.email' AS email
FROM test_json;