MySQL - 数据类型

MySQL 数据类型：现代指南

为表列选择正确的数据类型是数据库设计的一个基本方面。这不仅仅关乎你能存储什么类型的数据；这是一个影响存储效率、查询性能和数据完整性的关键决策。在现代 MySQL 中，数据类型的范围已经扩展，以支持从简单博客到复杂的地理空间和基于 JSON 的服务等新类型的应用程序。

最佳实践：始终选择能够可靠容纳你的数据的最小且最严格的数据类型。这能节省空间，提升性能，并防止存储无效数据。

核心数据类型分类

MySQL 的数据类型大致分为以下几类：

• 数值类型：用于数字，包括整数和小数。
• 日期和时间类型：用于日期、时间、时间戳等时间信息。
• 字符串类型：用于文本数据，从单个字符到长文章。
• 空间类型：用于地理数据。
• JSON 类型：用于存储 JSON 文档。

数值数据类型

数值类型用于存储数字。整数类型的一个关键属性是 UNSIGNED（无符号），它不允许负值并将最大正值范围加倍。

类型	存储（字节）	描述与常见用途
TINYINT	1	一个非常小的整数。用于标志、布尔值（BOOLEAN 是 TINYINT(1) 的同义词）或小型计数器。范围：-128 到 127（有符号）或 0 到 255（无符号）。
SMALLINT	2	一个小型整数。用于表示年龄或小型书籍的页数等。范围：-32,768 到 32,767（有符号）或 0 到 65,535（无符号）。
MEDIUMINT	3	一个中型整数。较不常见，但当你确定范围合适时，可以比 INT 节省一个字节。范围：-8,388,608 到 8,388,607（有符号）或 0 到 16,777,215（无符号）。
INT	4	标准整数类型。常用作主键（AUTO_INCREMENT）。范围：-21 亿到 21 亿（有符号）或 0 到 42 亿（无符号）。
BIGINT	8	一个大型整数。当 INT 不够大时使用，例如高并发系统中的事务 ID 或与 64 位系统交互时。范围非常大。
DECIMAL(M, D)	Varies	定点数。对于需要精确度的财务和货币计算至关重要。M 是总位数，D 是小数位数。示例：DECIMAL(10, 2)。
FLOAT	4	近似值、单精度浮点数。容易出现舍入误差。适用于对绝对精度要求不高的科学测量。(M,D) 语法不符合标准，不建议使用。
DOUBLE	8	近似值、双精度浮点数。比 FLOAT 更精确，但仍会产生舍入误差。REAL 是 DOUBLE 的同义词。

专家提示：DECIMAL 与 FLOAT/DOUBLE 的选择

切勿使用 FLOAT 或 DOUBLE 来存储货币值。它们的近似特性会导致累积的舍入误差。务必使用 DECIMAL 来存储财务数据。

日期和时间数据类型

存储时间数据是一个常见需求。MySQL 8+ 改进了这些类型，特别是支持小数秒。

• DATE：以 ‘YYYY-MM-DD’ 格式存储日期。范围：‘1000-01-01’ 到 ‘9999-12-31’。非常适合存储出生日期。
• TIME：以 ‘HH:MM:SS.fractional’ 格式存储时间。也可以用于存储时间间隔。范围足够大，适用于大多数应用。
• DATETIME(fsp)：日期和时间的组合，‘YYYY-MM-DD HH:MM:SS.fractional’。fsp（小数秒精度）可达 6 位（微秒）。它不感知时区。
• TIMESTAMP(fsp)：类似于 DATETIME，但有一个关键区别：它感知时区。MySQL 将 TIMESTAMP 值从当前时区转换为 UTC 进行存储，并在检索时从 UTC 转换回当前时区。其范围最高可达 ‘2038-01-19 03:14:07.999999’ UTC。它还具有特殊的自动更新功能，非常适合 created_at 或 updated_at 列。
• YEAR：以 4 位格式存储年份。范围：1901 到 2155。注意：2 位 YEAR(2) 格式已被弃用，不应在新设计中使用。

字符串数据类型

字符串类型存储文本。对于字符串来说，一个关键概念是字符集和排序规则。现代应用程序的最佳实践是使用 utf8mb4 字符集，以支持完整的 Unicode 字符范围，包括表情符号。

• CHAR(M)：定长字符串。如果存储的字符串短于 M，则用空格填充。用于长度一致的数据，如国家代码（‘US’, ‘DE’）或 MD5 哈希值。
• VARCHAR(M)：变长字符串。仅使用实际字符串所需的空间加上 1 或 2 个字节用于存储长度。适用于大多数文本数据，如姓名、用户名和标题。M 是最大长度。
• TEXT 类型：用于长文本。它们有四种大小：TINYTEXT (255 字节)、TEXT (64 KB)、MEDIUMTEXT (16 MB) 和 LONGTEXT (4 GB)。用于博客文章、文章或产品描述。
• BLOB 类型：二进制大对象（Binary Large Objects）。用于在数据库中直接存储二进制数据，如图像或文件。它们的大小与 TEXT 类型（TINYBLOB、BLOB 等）对应。注意：在数据库中存储大型文件会负面影响性能；通常，最好存储文件路径或 URL。
• ENUM：枚举。一个字符串对象，其值必须从预定义允许值列表中选择，例如，ENUM('pending', 'processing', 'shipped')。适用于状态列，但如果列表需要经常更改，则可能不灵活。
• SET：一个字符串对象，可以有零个或多个值，每个值都从预定义列表中选择。例如，SET('read', 'write', 'execute') 用于权限。

现代专用数据类型

现代 MySQL 包含强大的数据类型，适用于新的应用程序架构。

JSON 数据类型

自 MySQL 5.7 起，你可以原生存储 JSON 文档。MySQL 会自动验证 JSON 并提供高效的二进制存储格式。然后，你可以使用一套内置函数在 JSON 文档中查询和操作数据。

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  attributes JSON
);

INSERT INTO products (name, attributes) VALUES
('Laptop', '{"ram_gb": 16, "storage_gb": 512, "color": "silver"}');

-- Querying inside the JSON document
SELECT name, attributes->'$.color' AS color
FROM products
WHERE attributes->>'$.ram_gb' > 8;

空间数据类型

MySQL 支持 GEOMETRY、POINT、LINESTRING 和 POLYGON 等空间数据类型来存储地理信息。这允许你执行基于位置的查询，例如查找用户位置 5 公里半径范围内的所有商店。这是一个专业领域，但对于 GIS 和位置感知应用功能强大。