一句话总结
数据类型选型原则：越小越好、越简单越好、尽量避免 NULL。整型用 TINYINT/INT/BIGINT 按需选择；字符串定长用 CHAR，变长用 VARCHAR；时间用 DATETIME（范围大）或 TIMESTAMP（自动时区）；金额用 DECIMAL 不用 FLOAT；布尔用 TINYINT(1)；IP 用 INT UNSIGNED（INET_ATON 转换）。
初级理解
整型类型
| 类型 | 字节 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
| MEDIUMINT | 3 | -838万 ~ 838万 | 0 ~ 1677万 |
| INT | 4 | -21亿 ~ 21亿 | 0 ~ 42亿 |
| BIGINT | 8 | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 |
字符串类型
| 类型 | 存储 | 最大长度 | 适用场景 |
|---|---|---|---|
| CHAR(N) | 定长 N 字符 | 255 字符 | 固定长度：手机号、MD5、身份证 |
| VARCHAR(N) | 变长（实际+1~2字节） | 65535 字节 | 变长：姓名、邮箱、地址 |
| TEXT | 变长+2字节 | 65535 字节 | 长文本：文章内容 |
| MEDIUMTEXT | 变长+3字节 | 16MB | 超大文本 |
| LONGTEXT | 变长+4字节 | 4GB | 超大文本 |
# CHAR vs VARCHAR
# CHAR(10): 存 'abc' → 占 10 字符空间（空格填充）
# VARCHAR(10): 存 'abc' → 占 3 字符 + 1 字节长度
# 选型建议：
# 手机号 → CHAR(11)（固定长度）
# 姓名 → VARCHAR(50)（变长）
# 文章内容 → TEXT（长文本）
# 状态码 → TINYINT（0~255 够用）
一句话总结：定长用 CHAR，变长用 VARCHAR；整型按范围选最小的；金额用 DECIMAL。
中级深入
时间类型对比
| 类型 | 字节 | 范围 | 时区 | 适用场景 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 ~ 9999-12-31 | 无关 | 生日、日期 |
| TIME | 3 | -838:59:59 ~ 838:59:59 | 无关 | 时长 |
| DATETIME | 5~8 | 1000-01-01 ~ 9999-12-31 | 不转换 | 创建时间（推荐） |
| TIMESTAMP | 4~7 | 1970-01-01 ~ 2038-01-19 | 自动转换 | 更新时间（2038问题） |
# DATETIME vs TIMESTAMP
# DATETIME: 存什么就是什么，与时区无关
# TIMESTAMP: 存时转为 UTC，取时转为当前时区
# 示例
CREATE TABLE test_time (
dt DATETIME,
ts TIMESTAMP
);
SET time_zone = '+08:00';
INSERT INTO test_time VALUES ('2025-01-01 12:00:00', '2025-01-01 12:00:00');
SET time_zone = '+00:00';
SELECT * FROM test_time;
# dt: 2025-01-01 12:00:00（不变）
# ts: 2025-01-01 04:00:00（自动转为 UTC 时区）
# 建议：
# 创建时间 → DATETIME（范围大，不受 2038 限制）
# 更新时间 → TIMESTAMP（自动更新，但注意 2038 问题）
# MySQL 8.0.28+ TIMESTAMP 支持到 3000 年
DECIMAL — 精确小数
# FLOAT/DOUBLE：近似值，有精度丢失
# DECIMAL(M,D)：精确值，M 总位数，D 小数位数
# 金额必须用 DECIMAL！
CREATE TABLE orders (
id INT PRIMARY KEY,
amount DECIMAL(10, 2) # 最大 99999999.99
);
# DECIMAL(10,2) 存储：
# 整数部分 8 位，小数部分 2 位
# 内部以字符串形式存储，无精度丢失
# ❌ 错误示例
SELECT 0.1 + 0.2 = 0.3; # 结果 0（FLOAT 精度问题）
# ✅ 正确示例
SELECT CAST(0.1 AS DECIMAL(10,1)) + CAST(0.2 AS DECIMAL(10,1)) = 0.3; # 结果 1
中级要点：DATETIME 不受时区影响范围大；TIMESTAMP 自动时区但有 2038 限制；金额用 DECIMAL。
高级拓展
JSON 类型（MySQL 5.7+）
# JSON 列
CREATE TABLE users (
id INT PRIMARY KEY,
profile JSON
);
INSERT INTO users VALUES (1, '{"name":"张三","tags":["java","mysql"]}');
# JSON 函数查询
SELECT profile->'$.name' AS name FROM users; # "张三"（带引号）
SELECT profile->>'$.name' AS name FROM users; # 张三（不带引号）
SELECT * FROM users WHERE JSON_CONTAINS(profile->'$.tags', '"java"');
# JSON 虚拟列索引
ALTER TABLE users ADD name VARCHAR(50)
GENERATED ALWAYS AS (profile->>'$.name') VIRTUAL;
CREATE INDEX idx_name ON users(name);
# JSON vs 关联表
# JSON 优点：灵活，适合非结构化数据
# JSON 缺点：查询效率低，不支持外键
# 建议：结构化数据用列，非结构化扩展用 JSON
IP 地址存储
# ❌ 用 VARCHAR(15) 存 IP（占 15 字节，索引效率低）
# ✅ 用 INT UNSIGNED（占 4 字节，索引效率高）
# IP 转整数
SELECT INET_ATON('192.168.1.1'); # 3232235777
# 整数转 IP
SELECT INET_NTOA(3232235777); # 192.168.1.1
# 建表
CREATE TABLE access_log (
id INT PRIMARY KEY,
ip INT UNSIGNED,
INDEX idx_ip(ip)
);
# 查询
SELECT * FROM access_log
WHERE ip BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');
枚举 vs TINYINT
# ❌ ENUM：修改需要 ALTER TABLE，排序按定义顺序
# ✅ TINYINT + 注释：灵活，性能好
CREATE TABLE orders (
id INT PRIMARY KEY,
status TINYINT COMMENT '0:待支付 1:已支付 2:已取消 3:已退款',
INDEX idx_status(status)
);
# 查询时用常量或枚举类映射
SELECT * FROM orders WHERE status = 1; # 已支付
实战场景
场景：用户表数据类型最佳实践
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
username VARCHAR(50) NOT NULL COMMENT '用户名',
phone CHAR(11) NOT NULL COMMENT '手机号（定长）',
email VARCHAR(100) NOT NULL COMMENT '邮箱',
gender TINYINT NOT NULL DEFAULT 0 COMMENT '0:未知 1:男 2:女',
status TINYINT NOT NULL DEFAULT 1 COMMENT '0:禁用 1:正常',
balance DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '余额',
birthday DATE NULL COMMENT '生日',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_phone(phone),
INDEX idx_status(status),
INDEX idx_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
面试模拟
面试官：CHAR 和 VARCHAR 有什么区别？怎么选？
你：CHAR 定长，存不满会用空格填充，适合固定长度的字段（手机号、MD5、身份证）；VARCHAR 变长，只存实际数据+1~2字节长度标记，适合变长字段（姓名、邮箱）。CHAR 读写效率略高（不需要计算长度），但浪费空间；VARCHAR 节省空间但更新时可能产生页分裂。
面试官：DATETIME 和 TIMESTAMP 有什么区别？
你：1）DATETIME 范围 1000~9999 年，TIMESTAMP 范围 1970~2038 年（MySQL 8.0.28+ 已扩展）；2）TIMESTAMP 存时转 UTC，取时转当前时区，DATETIME 不转换；3）TIMESTAMP 占 4~7 字节，DATETIME 占 5~8 字节；4）TIMESTAMP 支持 DEFAULT CURRENT_TIMESTAMP 自动更新。建议创建时间用 DATETIME，更新时间用 TIMESTAMP。