MySQL数据类型详解

MySQL 数据类型详解（附实战案例）

MySQL 数据类型是定义表列存储规则的核心，决定了列能存什么数据、占用多少空间、支持哪些运算。选对数据类型能节省存储空间、提升查询效率、保证数据完整性。

下面按「数值、字符串、日期时间」三大核心类别，结合实战案例讲解，所有案例均可直接在 MySQL 中执行测试。

一、数值类型（存储数字）

分为「整数」和「小数/浮点数」，核心原则：够用就好，不选大的（比如存年龄用 TINYINT 而非 INT）。

1. 整数类型（常用）

类型	取值范围	占用字节	核心场景
TINYINT	-128~127（无符号 0~255）	1	状态值、年龄、数量（小范围）
INT	-21亿~21亿	4	主键 ID、订单号、用户 ID
BIGINT	-9e18~9e18	8	雪花 ID、超大计数

案例：创建用户表（整数类型实战）

-- 创建用户表，合理选择整数类型
CREATE TABLE `user` (
  `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID（自增）',
  `age` TINYINT UNSIGNED NOT NULL COMMENT '年龄（无符号，0~255足够）',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1=正常，0=禁用',
  `snow_id` BIGINT COMMENT '雪花ID（超大数值）'
);

-- 插入测试数据
INSERT INTO `user` (age, status) VALUES (25, 1), (30, 0);

-- 查询验证
SELECT * FROM `user`;

关键说明：UNSIGNED 表示无符号（只能存正数），适合年龄、状态这类非负数值；AUTO_INCREMENT 让 ID 自动递增，是主键常用配置。

2. 小数/浮点数类型（重点：金额必须用 DECIMAL）

类型	精度特点	核心场景
FLOAT/DOUBLE	精度丢失（非精确）	温度、距离（无需精确）
DECIMAL(M,D)	高精度（无丢失）	金额、价格（必须精确）

M = 总位数（整数+小数），D = 小数位数（比如 DECIMAL(10,2) 表示最大存 99999999.99）。

案例：创建订单表（金额类型实战）

-- 创建订单表，金额用 DECIMAL 保证精确
CREATE TABLE `order` (
  `order_id` INT PRIMARY KEY AUTO_INCREMENT,
  `total_price` DECIMAL(10,2) NOT NULL COMMENT '订单总价（精确到分）',
  `discount` FLOAT(5,2) COMMENT '折扣率（如 0.85，无需高精度）'
);

-- 插入测试数据
INSERT INTO `order` (total_price, discount) VALUES (199.99, 0.8), (2999.50, 0.95);

-- 查询验证（DECIMAL 计算无精度丢失）
SELECT total_price * discount AS final_price FROM `order`;

⚠️ 避坑提醒： 金额绝对不能用 FLOAT/DOUBLE！比如 0.1 + 0.2 用 FLOAT 存会得到 0.30000001，而 DECIMAL 会精确得到 0.3。

二、字符串类型（存储文本）

核心原则：短文本用 VARCHAR，长文本用 TEXT；固定长度用 CHAR。

类型	特点	核心场景
CHAR(n)	固定长度，n≤255，查询快	手机号、身份证号（固定长度）
VARCHAR(n)	可变长度，n≤65535，节省空间	用户名、标题、地址（长度不固定）
TEXT	长文本，最大 65535 字符	文章内容、商品描述
LONGTEXT	超大文本，最大 4GB 字符	日志、富文本内容

案例：创建商品表（字符串类型实战）

-- 创建商品表，合理选择字符串类型
CREATE TABLE `product` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `phone` CHAR(11) NOT NULL COMMENT '手机号（固定11位，用CHAR）',
  `name` VARCHAR(100) NOT NULL COMMENT '商品名称（最长100字，用VARCHAR）',
  `description` TEXT COMMENT '商品详情（长文本，用TEXT）',
  `content` LONGTEXT COMMENT '商品富文本内容（超大文本，用LONGTEXT）'
);

-- 插入测试数据
INSERT INTO `product` (phone, name, description)
VALUES ('13800138000', '小米手机', '骁龙8 Gen3，6.78英寸屏幕...');

-- 查询验证
SELECT name, phone FROM `product` WHERE name LIKE '%小米%';

关键说明：CHAR 适合固定长度文本（比如手机号11位），即使存不满 11 位，MySQL 也会补空格，查询效率比 VARCHAR 高；VARCHAR 只存实际长度+1个字节，适合长度不固定的文本。

三、日期时间类型（存储时间）

核心原则：需时区用 TIMESTAMP，无需时区用 DATETIME；仅日期用 DATE，仅时间用 TIME。

类型	范围	特点	核心场景
DATE	1000-01-01 ~ 9999-12-31	仅存日期（年-月-日）	生日、下单日期
TIME	-838:59:59 ~ 838:59:59	仅存时间（时:分:秒）	上课时间、营业时长
DATETIME	1000-01-01 ~ 9999-12-31	存日期+时间，不受时区影响	订单创建时间、登录时间
TIMESTAMP	1970-01-01 ~ 2038-01-19	存日期+时间，受时区影响，占用空间更小	数据更新时间

案例：创建日志表（日期时间类型实战）

-- 创建操作日志表，记录时间相关信息
CREATE TABLE `operate_log` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT NOT NULL COMMENT '操作人ID',
  `operate_date` DATE COMMENT '操作日期',
  `operate_time` TIME COMMENT '操作时间',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（自动填充当前时间）',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间（自动更新）'
);

-- 插入测试数据（无需指定 create_time/update_time，自动填充）
INSERT INTO `operate_log` (user_id, operate_date, operate_time)
VALUES (1, '2026-03-11', '14:30:00');

-- 10秒后更新数据，观察 update_time 自动变化
UPDATE `operate_log` SET user_id = 2 WHERE id = 1;

-- 查询验证
SELECT * FROM `operate_log`;

💡 实用技巧： CURRENT_TIMESTAMP 表示当前时间，ON UPDATE CURRENT_TIMESTAMP 让字段在数据更新时自动刷新为当前时间，适合记录「最后修改时间」。

总结

数值类型：整数选「够用的最小类型」（年龄=TINYINT，ID=INT），金额必须用 DECIMAL 而非 FLOAT/DOUBLE；
字符串类型：固定长度用 CHAR（手机号），可变长度用 VARCHAR（用户名），长文本用 TEXT（文章）；
日期时间类型：无需时区用 DATETIME（创建时间），需自动更新用 TIMESTAMP（更新时间），仅日期/时间用 DATE/TIME。

选择数据类型的核心：匹配业务场景，兼顾性能和存储空间，避免「大材小用」（比如用 INT 存年龄）或「小材大用」（比如用 VARCHAR 存长文章）。

MySQL数据类型详解