MySQL 数据类型

最后修改于 2023 年 1 月 10 日

在本篇 MySQL 教程中，我们将介绍 MySQL 数据类型。

数据类型是可表示值的集合。每个值属于一个数据类型。可以通过名称引用的项目，例如 SQL 参数、列、字段、属性和变量，也具有已声明的类型。

MySQL 支持以下数据类型组

• 数值
• 日期和时间
• String
• JSON

为列选择正确的数据类型是数据库初始设计的一部分。数据类型确保提供的数据的正确性。它们确保数据以有意义的方式使用。这在我们进行比较、数据排序时很重要。例如，日期的比较方式与数字不同。使用我们表的其他开发人员将知道数据库模式期望什么数据。数据类型使 MySQL 能够对插入的数据进行验证。最后，通过为表列使用正确的数据类型，我们可以让 MySQL 优化查询并使用更少的磁盘空间。

数字

数值类型可以是整数或浮点数。

1. 整数
   1. TINYINT
   2. SMALLINT
   3. MEDIUMINT
   4. INTEGER
   5. BIGINT
2. 浮点数
   1. FLOAT
   2. DOUBLE
   3. DECIMAL

整数

整数是实数的一个子集。它们没有分数或小数部分。整数属于集合 Z = {..., -2, -1, 0, 1, 2, ...}。整数是无限的。由于计算机的容量有限，计算机实际上只能处理整数值的一个子集。整数用于计数离散实体。我们可以有 3、4、6 辆汽车，但不能有 3.33 辆汽车。我们可以有 3.33 公斤。

下表是 MySQL 中的整数类型：TINYINT、MEDIUMINT 和 BIGINT 是 MySQL 对 SQL 标准的扩展。

表 1：有符号整数类型

数据类型	字节	最小值	最大值
TINYINT	1	-128	127
SMALLINT	2	-32768	32767
MEDIUMINT	3	-8388608	8388607
INTEGER	4	-2147483648	2147483647
BIGINT	8	-9223372036854775808	9223372036854775807

整数类型在存储上有所不同。我们可以选择适合我们需求的值。

mysql> CREATE TABLE Ages(Id SMALLINT, Age TINYINT) ENGINE=Memory;

我们创建了一个临时的 Ages 表。这只是一个临时的测试表，所以只有几行。SMALLINT 就足够了。我们不知道有人超过 130 岁，所以 Age 列使用 TINYINT 就可以了。

mysql> INSERT INTO Ages VALUES(1, 43);
Query OK, 1 row affected (0.00 sec)

我们将一行插入表中。

mysql> INSERT INTO Ages VALUES (2, 128);
ERROR 1264 (22003): Out of range value for column 'Age' at row 1

尝试插入超出列类型范围的值会导致错误。

当我们处理年龄时，我们不需要负整数值。MySQL 支持无符号整数。这样我们可以进一步优化我们的表定义。

表 2：无符号整数类型

数据类型	字节	最小值	最大值
TINYINT	1	0	255
SMALLINT	2	0	65535
MEDIUMINT	3	0	16777215
INTEGER	4	0	4294967295
BIGINT	8	0	18446744073709551615

我们使用 SQL 语句将 Age 列更改为 TINYINT UNSIGNED 数据类型。

mysql> ALTER TABLE Ages MODIFY Age TINYINT UNSIGNED;

现在我们可以插入从 0 到 255 的值。

mysql> INSERT INTO Ages VALUES(2, 128);
Query OK, 1 row affected (0,00 sec)

mysql> SELECT * FROM Ages;
+------+------+
| ID   | Age  |
+------+------+
|    1 |   43 |
|    2 |  128 |
+------+------+
2 rows in set (0,00 sec)

我们已经插入了一个假设的年龄 128。现在该列接受它。

浮点数值

浮点数在计算中表示实数。实数衡量连续的量，如重量、身高或速度。

MySQL 具有用于近似值的浮点类型：FLOAT 和 DOUBLE，以及用于精确值的定点类型：DECIMAL 和 NUMERIC。

FLOAT 是单精度浮点数。MySQL 使用四个字节存储 FLOAT 值。DOUBLE 是双精度浮点数。MySQL 使用八个字节存储 DOUBLE 值。

MySQL 将 DOUBLE 视为 DOUBLE PRECISION（非标准扩展）的同义词。此外，MySQL 还将 REAL 视为 DOUBLE PRECISION 的同义词，除非启用了 REAL_AS_FLOAT SQL 模式。

DECIMAL 和 NUMERIC 类型存储精确的数值数据。这些类型用于需要保留精确精度的情况，例如货币数据。在 MySQL 中，NUMERIC 是 DECIMAL 的同义词。

浮点数、双精度数和十进制数可以指定它们的精度和小数位数。在 DECIMAL[M, D] 中，M 是最大位数，即精度。D 是小数点右边的位数，即小数位数。如果你有一个 DECIMAL(3, 1) 列，你可以插入最多三位数的数字：小数点前两位，小数点后一位。

mysql> SELECT 1/3;
+--------+
| 1/3    |
+--------+
| 0.3333 |
+--------+
1 row in set (0,00 sec)

mysql> SELECT 0.3333 = 1/3;
+--------------+
| 0.3333 = 1/3 |
+--------------+
|            0 |
+--------------+
1 row in set (0,00 sec)

你可能期望第二个 SQL 语句中的比较返回 true，但它没有。原因是浮点值的存储方式。

处理浮点值时必须小心。浮点数和双精度数处理速度更快，但不是最后一位的精确值。存在一个小的舍入误差，这在很多情况下是可以接受的。在许多现实世界的情况下，我们只需要一个近似值。例如，你有一个商店，里面有 7.5321 公斤苹果和 4.372 公斤橙子。将这两个值存储为 7.5 公斤和 4.4 公斤是可以的。没关系。另一方面，在精确的科学或货币计算中，需要高精度。对于这种情况，我们使用 DECIMAL 数据类型。

mysql> CREATE TABLE Numbers (Id TINYINT, Floats FLOAT, Decimals DECIMAL(3, 2));

我们创建一个表，将在其中存储一些浮点数和十进制数。

mysql> INSERT INTO Numbers VALUES (1, 1.1, 1.1), (2, 1.1, 1.1), (3, 1.1, 1.1);

我们将三行插入新创建的表中。

mysql> SELECT * FROM Numbers;
+------+--------+----------+
| Id   | Floats | Decimals |
+------+--------+----------+
|    1 |    1.1 |     1.10 |
|    2 |    1.1 |     1.10 |
|    3 |    1.1 |     1.10 |
+------+--------+----------+
3 rows in set (0,00 sec)

表看起来是这样的。

mysql> SELECT SUM(Floats), SUM(Decimals) FROM Numbers;
+--------------------+---------------+
| SUM(Floats)        | SUM(Decimals) |
+--------------------+---------------+
| 3.3000000715255737 |          3.30 |
+--------------------+---------------+
1 row in set (0,08 sec)

两个结果不同。十进制计算更精确。由于一些内部舍入，浮点数的总和不准确。

日期和时间值

MySQL 具有用于存储日期和时间的类型：DATE、TIME、DATETIME、YEAR 和 TIMESTAMP。MySQL 尝试以多种格式解释日期和时间值，但日期部分必须始终按年/月/日顺序给出。MySQL 会自动将日期或时间值转换为数字（如果它在数字上下文中被使用），反之亦然。

Date

DATE 用于存储日期。MySQL 以 YYYY-MM-DD 格式检索和显示日期值。支持的范围从 1000-01-01 到 9999-12-31。

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2017-01-31 |
+------------+
1 row in set (0,00 sec)

CURDATE() 函数返回当前日期。

mysql> SELECT DATE('2017-01-31 12:01:00');
+-----------------------------+
| DATE('2017-01-31 12:01:00') |
+-----------------------------+
| 2017-01-31                  |
+-----------------------------+
1 row in set (0,00 sec)

DATE() 函数返回日期和时间值的日期部分。

mysql> SELECT ADDDATE('2017-01-20', 8);
+--------------------------+
| ADDDATE('2017-01-20', 8) |
+--------------------------+
| 2017-01-28               |
+--------------------------+
1 row in set (0,00 sec)

ADDDATE() 函数将天数添加到日期。它返回计算出的日期。

mysql> CREATE TABLE Dates(Id TINYINT, Dates DATE);
mysql> INSERT INTO Dates VALUES(1, '2017-01-24');
mysql> INSERT INTO Dates VALUES(2, '2017/01/25');
mysql> INSERT INTO Dates VALUES(3, '20170126');
mysql> INSERT INTO Dates VALUES(4, '170127');
mysql> INSERT INTO Dates VALUES(5, '2017+01+28');

日期在 MySQL 中以一种格式显示，但我们可以在 SQL 语句中使用各种日期格式。YYYY-MM-DD 是标准格式。可以在日期部分之间使用任何标点符号。

mysql> SELECT * FROM Dates;
+------+------------+
| Id   | Dates      |
+------+------------+
|    1 | 2017-01-24 |
|    2 | 2017-01-25 |
|    3 | 2017-01-26 |
|    4 | 2017-01-27 |
|    5 | 2017-01-28 |
+------+------------+
5 rows in set (0,00 sec)

我们使用了多种格式将日期插入表中。MySQL 使用一种格式来显示日期。

mysql> INSERT INTO Dates VALUES (6, '10000-01-01');
ERROR 1292 (22007): Incorrect date value: '10000-01-01' for column 'Dates' at row 1

如果我们超出支持的日期值范围，就会发生错误。

时间

TIME 数据类型用于在 MySQL 中显示时间。它以 HH:MM:SS 格式显示值。MySQL 以 'HH:MM:SS' 格式或对于较大的小时值以 'HHH:MM:SS' 格式检索和显示 TIME 值。范围是从 -838:59:59 到 838:59:59。时间格式的小时部分可能大于 24。这是因为 TIME 数据类型可用于表示时间间隔。这也是为什么我们可能拥有负时间值的原因。

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 11:47:36  |
+-----------+
1 row in set (0,00 sec)

CURTIME() 函数返回当前时间。

mysql> SELECT TIMEDIFF('23:34:32', '22:00:00');
+----------------------------------+
| TIMEDIFF('23:34:32', '22:00:00') |
+----------------------------------+
| 01:34:32                         |
+----------------------------------+
1 row in set (0,02 sec)

TIMEDIFF() 函数用于减去两个时间值。

mysql> SELECT TIME('2017-01-31 11:06:43');
+-----------------------------+
| TIME('2017-01-31 11:06:43') |
+-----------------------------+
| 11:06:43                    |
+-----------------------------+
1 row in set (0,00 sec)

我们可以使用 TIME() 函数从日期和时间值中提取时间部分。

mysql> SELECT TIMEDIFF('211344', 201123);
+----------------------------+
| TIMEDIFF('211344', 201123) |
+----------------------------+
| 01:02:21                   |
+----------------------------+
1 row in set (0,00 sec)

我们也可以用不同的格式写时间值。第一个参数是字符串格式的时间值，没有分隔符。第二个是以数字指定的时间值。

日期时间

DATETIME 值同时包含日期和时间。MySQL 以 YYYY-MM-DD HH:MM:SS 格式检索和显示值。支持的范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59。

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2017-01-31 11:57:53 |
+---------------------+
1 row in set (0,00 sec)

NOW() 函数返回当前日期和时间。

mysql> SELECT DAYNAME('2017@01@31 11@12@12');
+--------------------------------+
| DAYNAME('2017@01@31 11@12@12') |
+--------------------------------+
| Tuesday                        |
+--------------------------------+
1 row in set (0,02 sec)

MySQL 只以一种格式显示日期和时间。但在我们的 SQL 语句中，我们可以使用不同的格式。任何标点符号都可以用作日期部分或时间部分之间的分隔符。在我们的例子中，我们使用了 @ 字符。

年份

YEAR 是用于表示年份的数据类型。MySQL 以 YYYY 格式显示 YEAR 值。它允许我们使用字符串或数字为 YEAR 列赋值。允许的范围是 1901 年到 2155 年，或 0000 年。无效的年份值将被转换为 0000。

mysql> SELECT YEAR(CURDATE()) AS 'Current year';
+--------------+
| Current year |
+--------------+
|         2017 |
+--------------+
1 row in set (0,02 sec)

在上面的 SQL 语句中，我们检索了当前年份。

时间戳

时间戳 是一系列字符，表示某个事件发生的日期和/或时间。时间戳通常用于记录事件。在 MySQL 中，我们有一个 TIMESTAMP 数据类型用于创建时间戳。TIMESTAMP 列对于记录 INSERT 或 UPDATE 操作的日期和时间很有用。如果你自己不赋值，它会自动设置为最近一次操作的日期和时间。TIMESTAMP 数据类型的范围是 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。

下表总结了支持的 TIMESTAMP 格式。

数据类型	格式
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

TIMESTAMP 数据类型提供了自动初始化和更新。我们可以将此数据类型限制为仅具有自动初始化或仅自动更新。

mysql> CREATE TABLE Prices(Id TINYINT PRIMARY KEY, Price DECIMAL(8, 2), Stamp TIMESTAMP);
mysql> INSERT INTO Prices(Id, Price) VALUES(1, 234.34);
mysql> INSERT INTO Prices(Id, Price) VALUES(2, 344.12);

我们创建了一个带有 TIMESTAMP 列的表。我们将两行插入表中。Stamp 列未包含在 SQL 语句中。MySQL 会自动填充该列。

mysql> SELECT * FROM Prices;
+----+--------+---------------------+
| Id | Price  | Stamp               |
+----+--------+---------------------+
|  1 | 234.34 | 2017-01-31 12:12:25 |
|  2 | 344.12 | 2017-01-31 12:15:10 |
+----+--------+---------------------+
2 rows in set (0,00 sec)

已创建这两行的时间戳。这是 TIMESTAMP 数据类型的自动初始化。可以在创建表时使用 Stamp TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP SQL 代码将其关闭。

mysql> UPDATE Prices SET Price=250.50 WHERE Id=1;

我们执行 SQL 语句来更新第一行的 Price 列。

mysql> SELECT * FROM Prices;
+----+--------+---------------------+
| Id | Price  | Stamp               |
+----+--------+---------------------+
|  1 | 250.50 | 2017-01-31 12:17:21 |
|  2 | 344.12 | 2017-01-31 12:15:10 |
+----+--------+---------------------+
2 rows in set (0,00 sec)

第一列的时间戳已更新。如果我们要关闭 TIMESTAMP 的自动更新，可以在创建表时使用 Stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP SQL 代码。

字符串

MySQL 具有以下字符串数据类型

• CHAR
• VARCHAR
• BINARY
• VARBINARY
• BLOB
• TEXT
• ENUM
• SET

Char

CHAR 是固定长度的字符数据类型。它声明时带有长度 CHAR(x)，其中 x 可以是 0 到 255。CHAR 每次条目使用相同的存储空间。如果我们指定一个比声明长度短的项，则该值会右填充空格以达到指定的长度。检索值时会删除尾随空格。

mysql> CREATE TABLE Chars(Id TINYINT PRIMARY KEY, Chars CHAR(3));
mysql> INSERT INTO Chars VALUES (1, 'a'), (2, 'ab'), (3, 'abc'), (4, 'abb');

在上面的 SQL 代码中，我们创建了一个 Chars 表，其中有一个 CHAR 数据类型的列。最大长度设置为三个字符。

mysql> INSERT INTO Chars VALUES (5, 'abcd');
ERROR 1406 (22001): Data too long for column 'Chars' at row 1

尝试插入比指定的字符串更大的字符串会导致错误。

mysql> SELECT * FROM Chars;
+------+-------+
| Id   | Chars |
+------+-------+
|    1 | a     |
|    2 | ab    |
|    3 | abc   |
|    4 | abb   |
+------+-------+
4 rows in set (0,00 sec)

表中包含的内容是这样的。

mysql> SELECT Id, LENGTH(Chars) AS Length FROM Chars;
+------+--------+
| Id   | Length |
+------+--------+
|    1 |      1 |
|    2 |      2 |
|    3 |      3 |
|    4 |      3 |
+------+--------+
4 rows in set (0,00 sec)

我们已经检索了 Id 和插入的字符长度。之前我们说过 char 是固定大小存储的。为什么现在行的 size 值不同？我们期望每行正好有 3 个字符。原因是 MySQL 在检索数据时会修剪 char 的空格。通过将 sql_mode 设置为 PAD_CHAR_TO_FULL_LENGTH，空格也会被修剪。

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> SELECT Id, LENGTH(Chars) AS Length FROM Chars;
+------+--------+
| Id   | Length |
+------+--------+
|    1 |      3 |
|    2 |      3 |
|    3 |      3 |
|    4 |      3 |
+------+--------+
4 rows in set (0,00 sec)

通过更改 sql_mode，我们可以获得预期的结果。

Varchar

VARCHAR 数据类型存储可变长度的字符串。字符串长度可以从 0 到 65535。VARCHAR 值在存储时不会填充。存储和检索值时会保留尾随空格。大多数较短的字符串数据类型存储在此数据类型中；例如电子邮件、人名、商品名或地址。

mysql> CREATE TABLE FirstNames(Id TINYINT, Firstname VARCHAR(20));
mysql> INSERT INTO FirstNames VALUES (1, 'Tom'), (2, 'Lucy'), (3, 'Alice'),
    -> (4, 'Robert'), (5, 'Timothy'), (6, 'Alexander');

我们创建一个 FirstNames 表，并在其中存储六个名字。

mysql> SELECT Id, LENGTH(FirstName) AS Length FROM FirstNames;
+------+--------+
| Id   | Length |
+------+--------+
|    1 |      3 |
|    2 |      4 |
|    3 |      5 |
|    4 |      6 |
|    5 |      7 |
|    6 |      9 |
+------+--------+
6 rows in set (0,00 sec)

我们可以看到 VARCHAR 列类型中的名称以可变长度存储。这节省了磁盘空间。

Binary 和 varbinary

BINARY 和 VARBINARY 是二进制字节数据类型。它们包含字节字符串而不是字符字符串。它们没有字符集。排序和比较基于值中字节的数值。BINARY 数据类型的范围是 0 到 255。它以固定长度存储值。VARBINARY 的范围是 0 到 65535。

Blob

BLOB 是二进制大对象数据类型。它可以容纳可变数量的二进制数据。可用于存储二进制数据，如图像或文档。BLOB 有四种类型

Blog 类型	字节范围
TINYBLOB	0 - 255
BLOB	0 - 65535
MEDIUMBLOB	0 - 16777215
LONGBLOB	0 - 4294967295

接下来，我们将读取和写入一个图像。

mysql> CREATE TABLE Images(Id INT PRIMARY KEY, Img LONGBLOB);

创建了一个带有 LONGBLOB 列的表。

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0,02 sec)

MySQL 对加载和转储数据有限制。secure_file_priv 显示了允许此类操作的目录路径。

mysql> INSERT INTO Images VALUES (1, LOAD_FILE('/var/lib/mysql-files/image1.jpg'));

借助 LOAD_FILE() 函数，我们将图像插入 Images 表中。

mysql> SELECT Img FROM Images WHERE Id=1 INTO DUMPFILE '/var/lib/mysql-files/image_bck.jpg';

我们从表中选择图像并将其写入 /var/lib/mysql-files 目录中的文件。

$ sudo ls /var/lib/mysql-files/ -l
total 608
-rw-r--r-- 1 root  root  309262 jan 31 13:08 image1.jpg
-rw-rw-rw- 1 mysql mysql 309262 jan 31 13:12 image_bck.jpg

现在我们应该在该目录中有这两个文件。

文本

TEXT 数据类型用于存储大型文本数据。例如文章、博客或页面。当 VARCHAR 和其他基于字符串的数据对象不足以处理所需信息量时，最好使用 TEXT 值。

Blog 类型	字节范围
TINYTEXT	0 - 255
TEXT	0 - 65535
MEDIUMTEXT	0 - 16777215
LONGTEXT	0 - 4294967295

插入时没有填充，选择时也没有剥离字节。

Enum

ENUM 是一个字符串对象，其值从允许的值列表中选择。它们在列规范中明确枚举。我们只能从列表中插入一个值。

mysql> CREATE TABLE Sizes(Size ENUM('S', 'M', 'L', 'XL', 'XXL'));

我们创建一个表，其中有一个 ENUM 类型的列。允许值的列表已明确说明。

mysql> INSERT INTO SizeTable VALUES ('S'), ('L');

我们将两行插入表中。

mysql> INSERT INTO Sizes VALUES ('Large');
ERROR 1265 (01000): Data truncated for column 'Size' at row 1

由于列表中没有提到“Large”，我们会收到一条错误消息。

mysql> SELECT * FROM Sizes;
+------+
| Size |
+------+
| S    |
| L    |
+------+
2 rows in set (0,00 sec)

表中包含两个常规值。

Set

SET 是一个字符串对象，可以包含零个或多个值，每个值都必须从允许的值列表中选择。它类似于 ENUM 数据类型。区别在于它可以包含允许值列表中的零个或多个值。

mysql> CREATE TABLE Letters(Let SET('a', 'b', 'c', 'd', 'e'));

我们创建一个表，该表允许列中有一组字母。

mysql> INSERT INTO Letters VALUES ('a');
mysql> INSERT INTO Letters VALUES ('b');
mysql> INSERT INTO Letters VALUES ('b,a');
mysql> INSERT INTO Letters VALUES ('');
mysql> INSERT INTO Letters VALUES ('a,b,c');

mysql> SELECT * FROM Letters;
+-------+
| Let   |
+-------+
| a     |
| b     |
| a,b   |
|       |
| a,b,c |
+-------+
5 rows in set (0,00 sec)

我们添加了 SET 允许的各种字母组合。

JSON

自 MySQL 5.7.8 起，MySQL 支持原生的 JSON 数据类型。

JSON (JavaScript Object Notation) 是一种轻量级的数据交换格式。它易于人类阅读和编写，也易于机器解析和生成。

MySQL 会自动验证存储在 JSON 列中的 JSON 文档。无效的文档会产生错误。存储在 JSON 列中的 JSON 文档已针对高效访问进行了优化。JSON 列不能有默认值。

mysql> CREATE TABLE t1 (Doc JSON);

创建了一个带有 JSON 列的表。

mysql> INSERT INTO t1 VALUES('{"chair": "5", "table": "4", "lamp": "6"}');

一个文档被添加到表中。

mysql> SELECT * FROM t1;
+-------------------------------------------+
| Doc                                       |
+-------------------------------------------+
| {"lamp": "6", "chair": "5", "table": "4"} |
+-------------------------------------------+
1 row in set (0,00 sec)

我们显示表的内容。

mysql> SELECT JSON_ARRAY('pen', 4, 'pencil', 2, 'rubber', 1);
+------------------------------------------------+
| JSON_ARRAY('pen', 4, 'pencil', 2, 'rubber', 1) |
+------------------------------------------------+
| ["pen", 4, "pencil", 2, "rubber", 1]           |
+------------------------------------------------+
1 row in set (0,02 sec)

JSON_ARRAY() 函数接受一个值列表并将其转换为 JSON 数组。

mysql> SELECT JSON_OBJECT('pen', 4, 'pencil', 2, 'rubber', 1);
+-------------------------------------------------+
| JSON_OBJECT('pen', 4, 'pencil', 2, 'rubber', 1) |
+-------------------------------------------------+
| {"pen": 4, "pencil": 2, "rubber": 1}            |
+-------------------------------------------------+
1 row in set (0,00 sec)

JSON_OBJECT() 函数接受一个键/值对列表，并返回一个包含这些对的 JSON 对象。

本篇 MySQL 教程介绍了 MySQL 数据类型。