MySQL 字段类型

MySQL 字段类型的分类

MySQL 的字段类型主要分为三大类：

1. 数值类型（Numeric Types）

2. 日期和时间类型（Date and Time Types）

3. 字符串类型（String Types）

---

1. 数值类型（Numeric Types）

数值类型用于存储数字，分为整数类型、浮点数类型和定点数类型。

1.1 整数类型（Integer Types）

类型	存储大小	范围（有符号）	范围（无符号）	描述
TINYINT	1 字节	-128 到 127	0 到 255	小整数，例如布尔值（0 或 1）。
SMALLINT	2 字节	-32,768 到 32,767	0 到 65,535	比 TINYINT 稍大的整数。
MEDIUMINT	3 字节	-8,388,608 到 8,388,607	0 到 16,777,215	中等大小的整数。
INT 或 INTEGER	4 字节	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295	常用的整数类型。
BIGINT	8 字节	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	0 到 18,446,744,073,709,551,615	大整数，例如存储用户 ID。

• 有符号（Signed）：可以存储正数和负数。

• 无符号（Unsigned）：只能存储正数，因此范围更大。

例子

1. 定义一个存储年龄的字段：

   age TINYINT UNSIGNED NOT NULL COMMENT '年龄，范围 0-255'

   • 年龄只能是正数，并且范围最多到 255。

2. 定义一个存储用户 ID 的字段：

   user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户唯一标识'

   • 使用 BIGINT 是因为用户 ID 可能非常大，并且只存储正数。

---

1.2 浮点数类型（Floating-Point Types）

类型	存储大小	描述
FLOAT(M, D)	4 字节	单精度浮点数，M 是总位数，D 是小数位数。
DOUBLE(M, D) 或 REAL	8 字节	双精度浮点数，精度更高。

• 浮点数适合存储近似值，比如科学计算。

• 注意：浮点数可能有精度问题，不适合存储需要精确计算的数据（如金额）。

例子

1. 存储温度：

   temperature FLOAT(5, 2) COMMENT '温度，最多 5 位数，其中 2 位小数'

   • 例如：123.45 或 -12.34。

---

1.3 定点数类型（Fixed-Point Types）

类型	描述
DECIMAL(M, D) 或 NUMERIC(M, D)	精确存储定点数，M 是总位数，D 是小数位数。

• 定点数用于需要高精度计算的场景，例如金额。

例子

1. 存储商品价格：

   price DECIMAL(10, 2) NOT NULL COMMENT '商品价格，最多 10 位数，其中 2 位小数'

   • 例如：99999999.99。

---

2. 日期和时间类型（Date and Time Types）

MySQL 提供了多种日期和时间类型，用于存储时间相关的数据。

类型	描述	范围	格式
DATE	仅存储日期（年-月-日）。	1000-01-01 到 9999-12-31	YYYY-MM-DD
DATETIME	存储日期和时间（年-月-日 时:分:秒）。	1000-01-01 00:00:00 到 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
TIMESTAMP	存储时间戳，表示 UTC 时间，常用于记录操作时间。	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC	YYYY-MM-DD HH:MM:SS
TIME	存储时间（时:分:秒）。	-838:59:59 到 838:59:59	HH:MM:SS
YEAR	存储年份。	1901 到 2155	YYYY

例子

1. 存储用户的出生日期：

   birth_date DATE NOT NULL COMMENT '用户出生日期'

2. 存储订单的创建时间：

   order_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单创建时间'

3. 存储更新时间：

   updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

---

3. 字符串类型（String Types）

MySQL 提供了多种字符串类型，用于存储文本或二进制数据。

类型	描述	最大长度
CHAR(M)	定长字符串，长度固定为 M，不足补空格。	最多 255 字符
VARCHAR(M)	变长字符串，长度最多为 M。	最多 65535 字节
TEXT	大文本数据，适合存储长内容。	最多 4,294,967,295 字节
TINYTEXT	最小文本数据。	最多 255 字节
BLOB	二进制大对象，适合存储图片、文件等。	最多 4,294,967,295 字节
ENUM	枚举类型，存储一个预定义的值。	最多 65,535 个值
SET	集合类型，存储多个预定义值的组合。	最多 64 个值

例子

1. 存储用户名：

   username VARCHAR(50) NOT NULL COMMENT '用户名'

2. 存储文章内容：

   content TEXT COMMENT '文章内容'

3. 使用枚举存储性别：

   gender ENUM('男', '女', '未知') NOT NULL COMMENT '用户性别'

4. 存储文件数据：

   file_data BLOB COMMENT '文件的二进制数据'

---

4. 选择字段类型的建议

1. 数值类型：

   • 如果是计数值（如 ID、数量），尽量用整数类型（INT 或 BIGINT）。

   • 如果需要高精度计算，比如金额，选择 DECIMAL 而不是 FLOAT。

2. 日期和时间类型：

   • 如果只需要存储日期，选择 DATE。

   • 如果需要存储日期和时间，选择 DATETIME。

   • 如果需要记录修改时间，选择 TIMESTAMP 并设置 ON UPDATE CURRENT_TIMESTAMP。

3. 字符串类型：

   • 如果内容长度固定，使用 CHAR。

   • 如果内容长度不固定，使用 VARCHAR。

   • 如果需要存储长文本，使用 TEXT。

   • 如果是有限的选项列表（如状态、性别），使用 ENUM。

---

总结表格

类型分类	常用字段类型	适用场景
数值类型	INT、BIGINT、DECIMAL	存储 ID、计数值、金额等。
日期时间类型	DATE、DATETIME、TIMESTAMP	存储日期、时间戳、记录更新时间等。
字符串类型	VARCHAR、TEXT、BLOB、ENUM	存储用户名、文章内容、文件数据、状态等。

新增或不常用的类型

1. 枚举类型（ENUM）和集合类型（SET）

1.1 枚举类型（ENUM）

ENUM 是一个字符串对象，允许存储一组预定义的值中的一个。

特点：

• 每个字段值必须是定义好的枚举值之一。

• 枚举值在内部以整数存储（从 1 开始编号）。

• 如果插入的值不在枚举列表中，默认会插入空字符串（''）。

语法：

ENUM('值1', '值2', '值3', ...)

适用场景：

适用于字段的值是固定的、且选择范围相对有限的情况，例如性别、状态、类别。

例子：

1. 存储用户性别：

   gender ENUM('男', '女', '保密') NOT NULL COMMENT '用户性别';

   • 如果插入 男 或 女，则存储对应的值。

   • 插入值 保密 存储为内部编号 3。

2. 存储订单状态：

   status ENUM('待支付', '已支付', '已取消') NOT NULL COMMENT '订单状态';

---

1.2 集合类型（SET）

SET 是一个字符串对象，可以存储一组预定义的值的组合，最多可以选择 64 个值。

特点：

• 一个字段可以同时包含多个值的组合。

• 每个值在内部以位（bit）表示，多个值的组合以位运算存储。

语法：

SET('值1', '值2', '值3', ...)

适用场景：

适用于允许多个选择的字段，例如用户权限、兴趣标签等。

例子：

1. 存储用户兴趣：

   interests SET('运动', '音乐', '阅读', '旅行') NOT NULL COMMENT '用户兴趣';

   • 如果用户选择了 音乐 和 阅读，则存储为 音乐,阅读。

---

2. JSON 类型

简介：

JSON 是 MySQL 5.7 及以上版本引入的一种数据类型，用于存储和操作JSON 格式的数据。它非常适合存储灵活或结构化的对象数据。

特点：

• 数据以 JSON 格式存储，支持嵌套。

• 提供了许多 JSON 函数，支持查询和操作 JSON 数据。

适用场景：

• 适用于字段内容不固定、但有一定的结构化需求的场景，例如存储用户设置、配置文件、日志数据等。

例子：

1. 存储用户配置：

   settings JSON NOT NULL COMMENT '用户个性化设置';

2. 插入 JSON 数据：

   INSERT INTO user_settings (settings)
   VALUES ('{"theme": "dark", "notifications": {"email": true, "sms": false}}');

3. 查询 JSON 数据：

   SELECT settings->"$.theme" AS theme FROM user_settings;

   • 提取 JSON 中的 theme 字段。

扩展：

JSON 类型支持索引（MySQL 8.0+），通过 JSON PATH 提高查询性能。

---

3. 空间数据类型（Spatial Data Types）

简介：

空间数据类型用于存储地理信息，例如点、线、多边形等几何数据。它们通常与 MySQL 的 GIS (Geographic Information System) 功能一起使用。

常见类型：

类型	描述
GEOMETRY	存储任意几何类型的空间数据
POINT	存储一个点（经纬度坐标）
LINESTRING	存储一条线
POLYGON	存储一个多边形

适用场景：

• 存储地理信息数据，例如用户位置、地图数据等。

例子：

1. 存储用户坐标：

   location POINT NOT NULL COMMENT '用户位置';

2. 插入和查询地理数据：

   INSERT INTO locations (location) VALUES (ST_GeomFromText('POINT(120.15 30.28)'));
   SELECT ST_AsText(location) FROM locations;

---

4. 二进制数据类型（Binary Data Types）

简介：

二进制数据类型用于存储文件、图片、音频等非文本数据。

常见类型：

类型	描述	最大长度
BINARY(M)	定长二进制字符串，长度固定为 M。	最多 255 字节
VARBINARY(M)	变长二进制字符串，长度最多为 M。	最多 65535 字节
TINYBLOB	存储小型二进制对象。	最多 255 字节
BLOB	存储二进制大对象。	最多 65535 字节
MEDIUMBLOB	存储中型二进制大对象。	最多 16,777,215 字节
LONGBLOB	存储大型二进制大对象。	最多 4,294,967,295 字节

适用场景：

• 存储文件（如图片、音频、视频等）。

例子：

1. 存储文件内容：

   file_data LONGBLOB NOT NULL COMMENT '文件数据';

2. 存储文件名和文件内容：

   CREATE TABLE files (
       file_id INT AUTO_INCREMENT PRIMARY KEY,
       file_name VARCHAR(255) NOT NULL,
       file_data MEDIUMBLOB NOT NULL
   );

---

5. IPv4 和 IPv6 地址类型（MySQL 8.0+）

MySQL 8.0 引入了对 IPv4 和 IPv6 地址的原生支持，使用 INET6 数据类型进行存储。

特点：

• 使用二进制格式存储 IP 地址。

• 提供专用函数进行 IP 地址的转换和操作。

适用场景：

• 存储和管理用户 IP 地址。

例子：

1. 存储 IP 地址：

   ip_address VARBINARY(16) NOT NULL COMMENT '存储 IPv4 或 IPv6 地址';

2. 插入和查询：

   INSERT INTO user_ips (ip_address) VALUES (INET6_ATON('192.168.1.1'));
   SELECT INET6_NTOA(ip_address) FROM user_ips;

---

6. UUID 类型（MySQL 8.0+）

MySQL 8.0 提供了原生的 UUID 函数，用于生成全局唯一标识符（Universal Unique Identifier）。

特点：

• UUID 是 128 位的唯一标识符。

• 通常存储为字符串（CHAR(36)），也可以使用二进制格式（BINARY(16)）。

适用场景：

• 用于分布式系统中生成唯一标识符。

例子：

1. 存储 UUID：

   id CHAR(36) NOT NULL PRIMARY KEY COMMENT '全局唯一标识符';

2. 使用二进制存储优化性能：

   id BINARY(16) NOT NULL PRIMARY KEY COMMENT '优化存储的 UUID';

3. 插入 UUID 数据：

   INSERT INTO my_table (id) VALUES (UUID());

---

总结

类型	适用场景	注意事项
ENUM/SET	固定选项的字段（状态、标签、类别）	ENUM 适合单选，SET 适合多选
JSON	存储灵活的结构化数据（如配置、日志）	需要 MySQL 5.7+，查询性能需注意
空间数据类型	地理信息（经纬度、地图数据）	需要 GIS 函数支持
二进制数据类型	存储文件（图片、音频、视频）	数据量大时建议使用文件存储而非数据库
IPv4/IPv6 类型	存储 IP 地址	需要 MySQL 8.0+
UUID	分布式系统中的唯一标识符	用 BINARY(16) 提升存储效率