MySQL中常见的数据类型有：

• 数字类型
• 日期时间类型
• 字符串类型
• JSON类型
• 空间类型

一、数字类型

MySQL 支持所有标准的 SQL 数字数据类型。这些类型包括精确的数值数据类型：

• INTEGER
• SMALLINT
• DECIMAL
• NUMERIC
• FLOAT
• REAL
• DOUBLE PRECISION

关键字 INT 是 INTEGER 的同义词；
关键字 DEC 和 FIXED 是 DECIMAL 的同义词。
MySQL 将 DOUBLE 视为 DOUBLE PRECISION (非标准扩展)的同义词。
MySQL 将 REAL 视为 DOUBLE PRECISION (一种非标准的变体)的同义词，除非启用了 REAL_AS_FLOAT SQL 模式。

BIT 数据类型存储位值，支持 MyISAM、 MEMORY、 InnoDB 和 NDB 表。

1.1 数字类型与存储空间

DECIMAL (和 NUMERIC) 列的值使用二进制格式表示，该格式将9个十进制数字(以10为基数)打包成4个字节。
每个值的整数和小数部分的存储单独确定。每个九位数字的倍数需要四个字节，而“leftover”数字需要四个字节的一些分数。下表给出了多余数字所需的存储空间。

1.2 整数类型所需的存储空间和范围

二、日期时间类型

表示时间值的日期和时间数据类型是：

• DATE
• TIME
• DATETIME
• TIMESTAMP
• YEAR

每个时态类型都有一个有效值的范围，以及一个“0”值，当您指定 MySQL 无法表示的无效值时，可以使用这个值。

TIMESTAMP 和 DATETIME 数据类型提供对当前日期和时间的自动初始化和更新。

2.1 使用日期和时间类型时的一般注意事项

• MySQL 以标准输出格式检索给定日期或时间类型的值，但它试图解释您提供的输入值的各种格式(例如，当您指定要分配给日期或时间类型的值或与之进行比较时)。有关允许的日期和时间类型格式的说明，请参阅第9.1.3节“日期和时间文字”。希望您提供有效的值。如果在其他格式中使用值，可能会出现不可预知的结果。

• 日期部分必须始终以年月日顺序(例如，“98-09-04”) 提供，而不是在其他地方常用的月日年或日月年顺序(例如，“09-04-98”、“04-09-98”)中提供。若要将其他订单中的字符串转换为年月日顺序，可以使用 STR_TO_DATE() 函数。

• 包含两位数年份值的日期是模糊的，因为世纪是未知的:

  • 70-99年的年值变为1970-1999年；
  • 00-69范围内的年值变为2000-2069；

• 可以将值从一种时间类型转换为另一种时间类型。

• 如果日期或时间值在数值上下文中使用，则 MySQL 会自动将其转换为数字，反之亦然。

• 默认情况下，当 MySQL 遇到超出范围的日期或时间类型的值或该类型的其他无效值时，它将该值转换为该类型的“零”值。例外情况是将超出范围的 TIME 值截断到 TIME 范围的适当端点。

• 通过将 SQL 模式设置为适当的值，您可以更准确地指定希望 MySQL 支持的日期类型。(见5.1.10节“ Server SQL Modes”)通过启用 ALLOW_INVALID_DATES SQL 模式，可以让 MySQL 接受某些日期，如“2009-11-31”。当您希望在数据库中存储用户指定的“可能是错误的”值(例如，在 web 表单中)以便将来处理时，这种方法非常有用。在这种模式下，MySQL 只验证月份在1到12之间，日期在1到31之间。

• MySQL 允许您将“0”值“0000-00-00”存储为“虚拟日期”在某些情况下，这比使用 NULL 值更方便，并且使用更少的数据和索引空间。若要禁用“0000-00-00”，请启用“ NO_ZERO_IN_DATE”模式。

• 通过 Connector/ODBC 使用的“ 0”日期或时间值会自动转换为 NULL，因为 ODBC 不能处理这些值。

2.2 数据类型与 “0” 值

下表显示了每种类型的“0”值的格式。“ 0”值是特殊的，但是您可以使用表中显式显示的值来存储或引用它们。您还可以使用更容易编写的值“0”或 0 来完成此操作。对于包含日期部分(DATE、 DATETIME 和 TIMESTAMP)的时间类型，使用这些值可能会产生警告或错误。精确的行为取决于启用哪种 strict 和 NO_ZERO_DATE SQL 模式(如果有的话) ; 请参阅第5.1.10节“ Server SQL 模式”。

2.3 DATE 类型

DATE 类型用于带有日期部分但没有时间部分的值。
MySQL 以“ YYYY-MM-DD”格式检索并显示日期值。
支持范围是’1000-01-01’到’9999-12-31’。

2.4 DATETIME 类型

DATETIME 类型用于同时包含日期和时间部分的值。
MySQL 以‘ YYYY-MM-DD hh: mm: ss’格式检索和显示 DATETIME 值。
范围是’1000-01-0100:00:00’到’9999-12-3123:59:59’。

2.5 TIMESTAMP 类型

TIMESTAMP 类型用于同时包含日期和时间部分的值。
范围是’1970-01-0100:00:01’UTC 到’2038-01-1903:14:07’UTC。

一个定义为允许 NULL 值自动初始化的 TIMESTAMP 列只有在它的定义包含 DEFAULT CURRENT _ TIMESTAMP 时才能自动初始化:

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

2.6 DATETIME、TIMESTAMP 共同点

2.6.1 对毫秒的支持

DATETIME 或 TIMESTAMP 值可以包括最高达微秒(6位)精度的结尾小数秒部分。
特别是，插入到 DATETIME 或 TIMESTAMP 列中的值的任何小数部分都会被存储，而不是丢弃。

包含小数部分后，这些值的格式是： YYYY-MM-DD hh: mm: ss

• DATETIME 值的范围是’1000-01-0100:00:00.000000’到’9999-12-3123:59:59.999999’，
• TIMESTAMP 值的范围是’1970-01-0100:00:01.000000’到’2038-01-1903:14:07.999999’。

小数部分应始终用小数点与时间的其余部分分隔，不识别其他小数秒分隔符。

2.6.2 自动初始化和更新

TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前日期和时间(即当前时间戳)。
对于表中的任何 TIMESTAMP 或 DATETIME 列，可以将当前时间戳分配为默认值、自动更新值或两者兼有:

• 对于没有为列指定值的插入行，将自动初始化的列设置为当前时间戳。
• 当行中的任何其他列的值从当前值更改时，自动更新的列将自动更新为当前时间戳。如果所有其他列都设置为其当前值，则自动更新的列将保持不变。若要防止在其他列更改时自动更新列，请显式将其设置为其当前值。若要在其他列未更改时更新自动更新的列，请显式地将其设置为它应该具有的值(例如，将其设置为 CURRENT_TIMESTAMP)。

  2.7 TIME 类型

  MySQL 检索并显示“ hh: mm: ss”格式的 TIME 值(对于大时间值，则为“ hhh: mm: ss”格式)。
  范围：’-838:59:59’到’838:59:59’。
  小时部分可能非常大，因为 TIME 类型不仅可以用来表示一天中的某个时间(必须小于24小时) ，还可以用来表示两个事件之间的经过时间或时间间隔(可能大于24小时，甚至是负值)。

在为 TIME 列分配缩写值时要小心。

• MySQL 将带冒号的缩写 TIME 值解释为一天中的时间。也就是说，“11:12”是指“11:12:00”，而不是“00:11:12”。
• MySQL 解释没有冒号的缩写值时假设最右边的两个数字代表秒(即，用运行时间而不是一天中的时间)。例如，您可能认为“1112”和“1112”表示“11:12:00”(11点后12分) ，但 MySQL 将它们解释为“00:11:12”(11分12秒)。类似地，12和12被解释为00:00:12。

在时间部分和小数秒部分之间唯一可识别的分隔符是小数点。

2.8 YEAR 类型

YEAR 类型是用于表示年值的1字节类型。它可以声明为 YEAR，隐式显示宽度为4个字符，或者等效为 YEAR (4) ，显式显示宽度。

不推荐使用2位数的 YEAR (2)数据类型，并在 MySQL 5.7.5中删除了对它的支持。

MySQL 以 YYYY 格式显示 YEAR 值，范围为1901到2155和0000。

• 四位字符串：’1901’ 到 ‘2155’；
• 四位数字：1901 到 2155；

作为返回在 YEAR 上下文中可接受的值的函数的结果，例如 NOW ()。

三、字符串类型

四、JSON类型

从 MySQL 5.7.8开始，MySQL 支持 RFC 7159定义的本地 JSON 数据类型，支持对 JSON (JSON 文档)文档中数据的高效访问。JSON 数据类型提供了在字符串列中存储 JSON 格式字符串的优点:

• 存储在 JSON 列中的 JSON 文档的自动验证。无效文档会产生错误。
• 优化的存储格式。存储在 JSON 列中的 JSON 文档被转换为允许快速读取文档元素的内部格式。当服务器以后必须读取以这种二进制格式存储的 JSON 值时，不需要从文本表示形式解析该值。二进制格式的结构使服务器能够通过键或数组索引直接查找子对象或嵌套值，而不需要在文档中读取所有值之前或之后。

存储 JSON 文档所需的空间与存储 LONGBLOB 或 LONGTEXT 所需的空间大致相同。JSON 列中存储的任何 JSON 文档的大小都受限于max_allowed_packet system 变量的值。(当服务器在内存中内部操作 JSON 值时，它可能会大于这个值; 当服务器存储 JSON 值时，这个限制会适用。)

JSON 列的默认值不能为非 null。

JSON 列与其他二进制类型的列一样，不直接进行索引; 相反，您可以在生成的列上创建索引，该列从 JSON 列中提取标量值。有关详细示例，请参阅为生成的列建立索引以提供 JSON 列索引。MySQL 优化器还会在匹配 JSON 表达式的虚拟列上寻找兼容的索引。

MySQL NDB Cluster 7.5(7.5.2及以后版本)支持 JSON 列和 MySQL JSON 函数，包括在 JSON 列生成的列上创建一个索引，以解决无法索引 JSON 列的问题。每个 NDB 表最多支持3个 JSON 列。

可以用 CAST(valueAS JSON) 函数将其他数据类型转为 JSON 类型。

在 MySQL 中，JSON 值被写成字符串。

4.1 常用的 JSON 相关函数

4.1.1 JSON_TYPE()

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+
mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+
mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

4.1.2 JSON_ARRAY()

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

4.1.3 JSON_OBJECT()

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

4.1.4 JSON_MERGE()

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+

4.2 JSON 与非JSON类型转换

JSON 转换规则：

五、空间类型

MySQL 有与 OpenGIS 类似的数据类型。一些空间（spatial）数据类型包含单一的 geometry 值:

• GEOMETRY
• POINT
• LINESTRING
• POLYGON

GEOMETRY 可以存储任何类型的geometry值。其他单值类型(POINT、 LINESTRING 和 POLYGON)将其值限制为特定的 geometry类型。

其他空间数据类型包含值的集合:

• MULTIPOINT
• MULTILINESTRING
• MULTIPOLYGON
• GEOMETRYCOLLECTION

GEOMETRYCOLLECTION 可以存储任何类型的对象集合。其他集合类型(MULTIPOINT、 MULTILINESTRING 和 MULTIPOLYGON)将集合成员限制为具有特定geometry类型的成员。

要创建一个名为 geom 的表，其中有一个名为 g 的列，可以存储任何geometry类型的值，请使用以下语句:

CREATE TABLE geom (g GEOMETRY);

可以在 NOT NULL 空间列上创建 SPATIAL 索引，因此如果您计划对该列进行索引，请将其声明为 NOT NULL:

CREATE TABLE geom (g GEOMETRY NOT NULL);