MySQL 数据类型

数字
- 整数
- 浮点值
日期&时间值
字符串
JSON 格式

原文： http://zetcode.com/databases/mysqltutorial/datatypes/

在 MySQL 教程的这一部分中，我们介绍了 MySQL 数据类型。

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

MySQL 支持以下数据类型组：

为列选择正确的数据类型是数据库初始设计的一部分。数据类型可确保所提供数据的正确性。他们确保以有意义的方式使用数据。当我们进行比较，数据排序时，这一点很重要。例如，日期比较与数字比较不同。使用我们的表的其他开发者将知道期望从数据库架构中获取哪些数据。数据类型使 MySQL 可以对插入的数据进行验证。最后，使用正确的表列数据类型，我们允许 MySQL 优化查询并使用更少的磁盘空间。

数字

数值类型可以是整数，也可以是浮点数。

整数
1. 天音
2. SMALLINT
3. 中号
4. 整数
5. 比金特
浮点数
1. 浮动
2. 双
3. 十进制

整数

整数是实数的子集。它们写时没有小数或小数部分。整数落入集合Z = {..., -2, -1, 0, 1, 2, ......}中，整数是无限的。实际上，计算机只能使用整数值的子集，因为计算机的容量有限。整数用于计算离散实体。我们可以有 3、4、6 辆车，但不能有 3.33 辆车。我们可以有 3.33 公斤。

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

Table 1: Signed integer types

数据类型	字节数	最小值	最大值
`TINYINT`	1	-128	127
`SMALLINT`	2	-32768	32767
`MEDIUM`	3	-8388608	8388607
`INT`	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 支持无符号整数。这样，我们可以进一步优化表定义。

Table 2: Unsigned integer types

数据类型	字节数	最大值
`TINYINT`	1 个	255
`SMALLINT`	2	65535
`MEDIUM`	3	16777215
`INT`	4	4294967295
`BIGINT`	8	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（非标准扩展名）的同义词。另外，除非启用REAL_AS_FLOAT SQL 模式，否则 MySQL 还将REAL视为DOUBLE PRECISION的同义词。

DECIMAL和NUMERIC类型存储精确的数值数据值。当保留精确度非常重要时，例如使用货币数据，则使用这些类型。在 MySQL 中，NUMERIC是DECIMAL的同义词。

浮点数，双精度数和小数可能已指定其精度和小数位数。在DECIMAL[M, D]中，M是最大位数，即精度。 D是小数点右边的位数。它是规模。如果您有一列带有DECIMAL(3, 1)的列，则可以插入最多三位数的数字：小数点之前和之后的wwo。

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 kg 和 4.4 kg。没什么大不了。另一方面，在精确的科学或货币计算中，需要高精度。在这种情况下，我们使用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用于存储日期。 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 和插入字符的长度。之前我们已经说过，字符以固定大小存储。为什么现在行的大小值不同？我们希望每一行都包含 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有四种类型：

`BLOB`类型	范围（以字节为单位）
`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`

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

文本类型	范围（以字节为单位）
`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 对象表示法）是一种轻量级的数据交换格式。人类很容易读写，机器也很容易解析和生成。

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 数据类型。

数字

整数

浮点值

日期&时间值

日期

时间

时间日期

年

时间戳

字符串

CHAR

VARCHAR

BINARY和VARBINARY

BLOB

TEXT

ENUM

SET