MySQL数据类型

MySQL 数据类型全解析：选择合适类型优化存储与性能

MySQL 提供了丰富的数据类型，涵盖数值、日期时间、字符串等类别。选择合适的数据类型不仅能节省存储空间，还能提升查询性能和数据准确性。本文详细解析各类数据类型的特性、适用场景及最佳实践。

数值类型：整数与浮点数的精确选择

数值类型用于存储数字，按精度和范围可分为整数类型、浮点类型和定点类型。

整数类型（精确值）

整数类型适用于存储没有小数部分的数字，支持有符号（默认）和无符号（UNSIGNED）两种模式。

类型	字节数	有符号范围	无符号范围（`UNSIGNED`）	适用场景
`TINYINT`	1	`-128 ~ 127`	`0 ~ 255`	状态标识（如 0/1 表示开关）
`SMALLINT`	2	`-32768 ~ 32767`	`0 ~ 65535`	小范围计数（如订单状态码）
`MEDIUMINT`	3	`-8388608 ~ 8388607`	`0 ~ 16777215`	中等范围计数（如用户等级）
`INT`/`INTEGER`	4	`-2147483648 ~ 2147483647`	`0 ~ 4294967295`	常规计数（如 ID、数量）
`BIGINT`	8	`-9223372036854775808 ~ 9223372036854775807`	`0 ~ 18446744073709551615`	极大范围计数（如雪花 ID）

示例：

-- 存储用户年龄（0~120，用 UNSIGNED TINYINT 更节省空间）
CREATE TABLE users (
  age TINYINT UNSIGNED  -- 范围 0~255，足够存储年龄
);

浮点类型（近似值）

浮点类型用于存储带小数的数字，但精度有限（存在舍入误差），适用于无需精确计算的场景。

类型	字节数	范围（近似）	特点
`FLOAT`	4	±1.175494351e-38 ~ ±3.402823466e+38	单精度，精度较低
`DOUBLE`	8	±2.2250738585072014e-308 ~ ±1.7976931348623157e+308	双精度，精度高于 FLOAT

示例：

-- 存储商品重量（允许一定精度误差）
CREATE TABLE products (
  weight FLOAT(8,2)  -- 总长度8位，小数位2位（如 12345.67）
);

定点类型（精确值）

DECIMAL 用于存储需要精确计算的小数（如金额），精度可控，无舍入误差。

语法：DECIMAL(M, D)
- M：总位数（整数 + 小数，最大 65）。
- D：小数位数（最大 30，且 ≤ M）。

示例：

-- 存储金额（精确到分，总位数10，小数位2）
CREATE TABLE orders (
  amount DECIMAL(10,2)  -- 范围 -9999999.99 ~ 9999999.99
);

日期时间类型：精准记录时间信息

日期时间类型用于存储时间相关数据，选择时需考虑范围、精度和存储效率。

类型	字节数	范围	格式	适用场景
`DATE`	3	`1000-01-01 ~ 9999-12-31`	`YYYY-MM-DD`	仅需日期（如生日）
`TIME`	3	`-838:59:59 ~ 838:59:59`	`HH:MM:SS`	仅需时间或持续时间（如时长）
`YEAR`	1	`1901 ~ 2155`	`YYYY`	仅需年份（如出生年份）
`DATETIME`	8	`1000-01-01 00:00:00 ~ 9999-12-31 23:59:59`	`YYYY-MM-DD HH:MM:SS`	日期 + 时间（如订单创建时间）
`TIMESTAMP`	4	`1970-01-01 00:00:00 ~ 2038-01-19 03:14:07`	同上	时间戳（如日志时间）

关键区别：

DATETIME 不受时区影响，TIMESTAMP 会随数据库时区自动转换。
TIMESTAMP 范围较小（到 2038 年），但存储更高效（4 字节）。

示例：

CREATE TABLE logs (
  create_time DATETIME,  -- 记录日志创建时间（不受时区影响）
  update_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 自动更新时间戳
);

字符串类型：字符与二进制数据的存储

字符串类型用于存储文本或二进制数据，按长度特性分为定长和变长两类。

常规字符串类型（CHAR 与 VARCHAR）

类型	存储方式	长度范围	适用场景
`CHAR(M)`	定长，不足用空格填充	`0 ~ 255` 字符	长度固定的字符串（如手机号）
`VARCHAR(M)`	变长，存储实际长度 + 1/2 字节	`0 ~ 65535` 字符	长度可变的字符串（如用户名）

注意：

M 表示字符数（非字节），如 CHAR(10) 可存 10 个汉字（UTF8 占 30 字节）。
CHAR 读取速度快（长度固定），VARCHAR 更节省空间（仅存实际长度）。

示例：

CREATE TABLE user_info (
  phone CHAR(11),  -- 手机号固定11位，用CHAR更高效
  username VARCHAR(50)  -- 用户名长度可变，用VARCHAR
);

大文本与二进制类型

用于存储超长文本或二进制数据（如文件、图片）。

类型	长度范围	用途
`TINYTEXT`	0 ~ 255 字节	短文本（如备注）
`TEXT`	0 ~ 65535 字节	普通文本（如文章内容）
`MEDIUMTEXT`	0 ~ 16MB	中等文本（如长篇小说）
`LONGTEXT`	0 ~ 4GB	超大文本（如日志文件）
`TINYBLOB` ~ `LONGBLOB`	同对应 TEXT 类型	二进制数据（如图片、文件）

注意：

大文本类型不建议建索引，查询性能差。
二进制文件（如图片）通常建议存储路径，而非直接存在数据库。

枚举与集合类型

ENUM：单选枚举（只能从指定值中选一个）。
SET：多选集合（可从指定值中选多个）。

示例：

CREATE TABLE user_preferences (
  gender ENUM('male', 'female', 'other'),  -- 性别（单选）
  hobbies SET('reading', 'sports', 'music')  -- 爱好（多选）
);

-- 插入数据
INSERT INTO user_preferences VALUES ('male', 'reading,music');

特殊场景：IP 地址的存储优化

IP 地址通常以字符串形式（如 192.168.1.1）存储，但可通过函数转换为整数节省空间：

INET_ATON(ip)：将 IP 转为整数（如 192.168.1.1 → 3232235777）。
INET_NTOA(num)：将整数转回 IP。

示例：

CREATE TABLE ip_logs (
  ip_int INT UNSIGNED,  -- 用 INT 存储 IP（4字节，比 VARCHAR(15) 节省空间）
  login_time DATETIME
);

-- 插入 IP
INSERT INTO ip_logs VALUES (INET_ATON('192.168.1.1'), NOW());

-- 查询时转回 IP 格式
SELECT INET_NTOA(ip_int) AS ip FROM ip_logs;

数据类型选择最佳实践

最小够用原则：选择能覆盖需求的最小类型（如年龄用 TINYINT 而非 INT）。
避免过度设计：如无需精确到毫秒，用 DATETIME 而非 TIMESTAMP(6)。
考虑索引效率：大文本类型（如 TEXT）不适合建索引，建议用 VARCHAR 存储短文本。
注意字符集影响：UTF8MB4 中每个汉字占 4 字节，设计 VARCHAR 长度时需预留空间。
谨慎使用 ENUM：新增枚举值需修改表结构，频繁变更场景建议用关联表