MySQL 数据类型详解

合理选择数据类型是数据库设计的基础，直接影响存储空间、查询性能和数据准确性。本文将详细解析 MySQL 的各类数据类型及其最佳实践。

一、数值类型

1.1 整数类型

-- ✅ 推荐：使用无符号类型（如果不需要负数）
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 0 ~ 42 亿
  age TINYINT UNSIGNED,                         -- 0 ~ 255
  status TINYINT                                -- -128 ~ 127（状态可能为负）
);

-- ✅ 状态字段使用 TINYINT
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status TINYINT DEFAULT 0  -- 0=待支付，1=已支付，2=已发货，3=已完成，-1=已取消
);

-- ❌ 避免：过度使用 BIGINT
CREATE TABLE logs (
  id BIGINT PRIMARY KEY  -- 如果数据量不大，INT 足够
);

ZEROFILL 属性

ZEROFILL 自动在数字前补零（实际已废弃，不推荐使用）：

-- ⚠️ 不推荐使用
CREATE TABLE products (
  id INT ZEROFILL  -- 显示为 00000001
);

-- ✅ 推荐：使用 LPAD 函数格式化
SELECT LPAD(id, 8, '0') FROM products;

1.2 浮点类型

-- ❌ 避免：使用 FLOAT/DOUBLE 存储金额（精度丢失）
CREATE TABLE orders (
  amount FLOAT  -- 可能出现 0.1 + 0.2 = 0.30000000000000004
);

-- ✅ 推荐：使用 DECIMAL 存储金额
CREATE TABLE orders (
  amount DECIMAL(10,2)  -- 精确表示金额，最大 99999999.99
);

-- DECIMAL 语法：DECIMAL(M, D)
-- M: 总位数（不含符号）
-- D: 小数位数

精度问题示例

-- 浮点数精度问题
SELECT 0.1 + 0.2;  -- 结果：0.30000000000000004

-- DECIMAL 精确计算
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));  -- 结果：0.30

1.3 位类型

-- BIT 类型：存储位值（1-64 位）
CREATE TABLE settings (
  permissions BIT(8)  -- 8 位权限标识
);

-- 插入位值
INSERT INTO settings VALUES (b'10101010');

-- 查询时转换为可读格式
SELECT BIN(permissions) FROM settings;  -- 二进制显示
SELECT HEX(permissions) FROM settings;  -- 十六进制显示

二、字符串类型

2.1 定长与变长

-- ✅ CHAR 适用场景
CREATE TABLE users (
  id CHAR(32),           -- MD5 值
  phone CHAR(11),        -- 手机号（固定 11 位）
  gender CHAR(1)         -- 性别（M/F）
);

-- ✅ VARCHAR 适用场景
CREATE TABLE users (
  name VARCHAR(50),      -- 姓名（长度不定）
  email VARCHAR(100),    -- 邮箱
  address VARCHAR(255)   -- 地址
);

存储计算

CHAR(10):  固定 10 字符，不足右填充空格
VARCHAR(10): 实际字符数 + 1 字节（长度≤255）或 +2 字节（长度>255）

示例（UTF8 编码，1 汉字=3 字节）：
- CHAR(10) 存储"你好"：10 字符 = 30 字节
- VARCHAR(10) 存储"你好"：2 字符 + 1 字节 = 7 字节

2.2 大文本类型

-- 文章表
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,           -- 文章内容
  summary TINYTEXT        -- 摘要
);

-- ⚠️ 注意：TEXT 字段不能有默认值
-- ❌ 错误
CREATE TABLE t (
  content TEXT DEFAULT ''  -- 语法错误
);

-- ✅ 正确
CREATE TABLE t (
  content TEXT NOT NULL
);

TEXT 类型优化

-- ✅ 推荐：TEXT 字段分离存储（提高主表查询性能）
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  created_at DATETIME
);

CREATE TABLE article_content (
  article_id INT PRIMARY KEY,
  content TEXT,
  FOREIGN KEY (article_id) REFERENCES articles(id)
);

-- ✅ 避免：SELECT * 查询包含 TEXT 的表
SELECT * FROM articles;  -- 会读取 TEXT 字段

-- ✅ 推荐：只查询必要字段
SELECT id, title FROM articles;

2.3 枚举和集合

-- ENUM：枚举类型（单选）
CREATE TABLE users (
  gender ENUM('M', 'F', 'U')  -- U=Unknown
);

-- SET：集合类型（多选）
CREATE TABLE users (
  hobbies SET('reading', 'music', 'sports', 'travel')
);

-- 插入数据
INSERT INTO users VALUES ('M', 'reading,music');

-- ⚠️ 注意：ENUM/SET 修改困难，慎用

三、时间类型

3.1 日期时间类型对比

3.2 DATETIME vs TIMESTAMP

-- DATETIME：绝对时间，不受时区影响
CREATE TABLE events (
  event_time DATETIME  -- 存储：2024-12-31 23:59:59
);

-- TIMESTAMP：相对时间（UTC 存储，查询时转换）
CREATE TABLE logs (
  log_time TIMESTAMP  -- 东八区查询自动+8 小时
);

-- ✅ 推荐场景
-- DATETIME：生日、节假日（固定日期）
-- TIMESTAMP：创建时间、更新时间（需要时区转换）

TIMESTAMP 自动更新

-- 自动记录创建和更新时间
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入数据时自动设置 created_at
INSERT INTO users (id, name) VALUES (1, '张三');

-- 更新数据时自动更新 updated_at
UPDATE users SET name = '李四' WHERE id = 1;

3.3 时间类型选择建议

-- ✅ 推荐：根据场景选择
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  
  -- 订单日期（精确到秒）
  order_date DATETIME,
  
  -- 支付时间（需要时区）
  paid_at TIMESTAMP,
  
  -- 预计配送时间（只需日期）
  delivery_date DATE,
  
  -- 配送时长
  delivery_duration TIME
);

-- ❌ 避免：使用字符串存储时间
CREATE TABLE events (
  event_time VARCHAR(20)  -- 无法进行时间计算
);

3.4 时间函数

-- 当前时间
SELECT NOW();           -- 2024-12-31 23:59:59
SELECT CURDATE();       -- 2024-12-31
SELECT CURTIME();       -- 23:59:59
SELECT UNIX_TIMESTAMP(); -- 1735660799

-- 时间计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);     -- 加 1 天
SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);    -- 减 1 小时
SELECT TIMESTAMPDIFF(DAY, '2024-01-01', NOW());  -- 相差天数

-- 时间提取
SELECT YEAR(NOW());     -- 2024
SELECT MONTH(NOW());    -- 12
SELECT DAY(NOW());      -- 31
SELECT HOUR(NOW());     -- 23
SELECT WEEKDAY(NOW());  -- 星期几（0=周一）

四、JSON 类型

4.1 JSON 基础

MySQL 5.7+ 支持 JSON 类型，提供原生 JSON 验证和函数：

-- 创建 JSON 字段
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  attributes JSON  -- 商品属性（颜色、尺寸等）
);

-- 插入 JSON 数据
INSERT INTO products VALUES (1, 'T 恤', '{"color": "red", "size": "L", "price": 99}');

-- ✅ 自动验证 JSON 格式
-- ❌ 错误：无效的 JSON 会报错
INSERT INTO products VALUES (2, 'T 恤', '{invalid json}');

4.2 JSON 查询

-- 提取 JSON 值
SELECT attributes->'$.color' AS color FROM products;  -- "red"
SELECT attributes->>'$.color' AS color FROM products; -- red（去除引号）

-- JSON 路径查询
SELECT * FROM products 
WHERE attributes->>'$.color' = 'red';

-- JSON 包含查询
SELECT * FROM products 
WHERE JSON_CONTAINS(attributes, '{"color": "red"}');

-- JSON 数组查询
SELECT * FROM products 
WHERE JSON_SEARCH(attributes, 'one', 'red') IS NOT NULL;

4.3 JSON 修改

-- 更新 JSON 字段
UPDATE products 
SET attributes = JSON_SET(attributes, '$.price', 89)
WHERE id = 1;

-- 添加 JSON 属性
UPDATE products 
SET attributes = JSON_INSERT(attributes, '$.stock', 100)
WHERE id = 1;

-- 删除 JSON 属性
UPDATE products 
SET attributes = JSON_REMOVE(attributes, '$.stock')
WHERE id = 1;

4.4 JSON vs 传统表设计

-- ❌ 传统设计：扩展困难
CREATE TABLE products (
  id INT PRIMARY KEY,
  color VARCHAR(20),
  size VARCHAR(10),
  weight DECIMAL(5,2),
  -- 新增属性需要 ALTER TABLE
);

-- ✅ JSON 设计：灵活扩展
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  attributes JSON  -- 灵活添加属性
);

-- ⚠️ 注意：JSON 查询性能较差，频繁查询的字段应单独列出
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),  -- 频繁查询的字段
  attributes JSON       -- 其他属性
);

五、数据类型选择最佳实践

5.1 更小通常更好

-- ✅ 推荐：选择足够小的类型
CREATE TABLE users (
  age TINYINT UNSIGNED,    -- 不是 INT
  gender TINYINT,          -- 不是 VARCHAR(10)
  status TINYINT DEFAULT 0 -- 不是 INT
);

-- 存储空间对比（100 万行）：
-- TINYINT: 1MB
-- INT: 4MB
-- VARCHAR(10): 约 10MB

5.2 简单更好

-- ✅ 推荐：使用内置类型
CREATE TABLE users (
  created_at DATETIME
);

-- ❌ 避免：自定义格式
CREATE TABLE users (
  created_at VARCHAR(20)  -- 无法进行时间计算
);

5.3 避免 NULL

-- ❌ 避免：字段允许 NULL
CREATE TABLE users (
  phone VARCHAR(20) DEFAULT NULL  -- NULL 值需要额外存储
);

-- ✅ 推荐：使用 NOT NULL + 默认值
CREATE TABLE users (
  phone VARCHAR(20) NOT NULL DEFAULT ''
);

-- ⚠️ 注意：NULL 影响索引和统计
SELECT COUNT(column) FROM table;  -- 不统计 NULL 值

5.4 主键选择

-- ✅ 推荐：自增 BIGINT
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

-- ⚠️ 谨慎：UUID 作为主键（随机写入性能差）
CREATE TABLE users (
  id CHAR(36) PRIMARY KEY  -- UUID
);

-- ✅ 折中：UUID 作为业务 ID，自增 ID 作为主键
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) NOT NULL UNIQUE
);

六、常见场景数据类型推荐

6.1 用户表

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(32) NOT NULL,
  password_hash CHAR(60) NOT NULL,  -- bcrypt 哈希
  email VARCHAR(100) NOT NULL,
  phone CHAR(11) NOT NULL,
  avatar_url VARCHAR(255),
  gender TINYINT NOT NULL DEFAULT 0,  -- 0=未知，1=男，2=女
  birthday DATE,
  status TINYINT NOT NULL DEFAULT 1,  -- 0=禁用，1=正常
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_login_at DATETIME,
  INDEX idx_username (username),
  INDEX idx_email (email),
  INDEX idx_phone (phone)
) ENGINE = InnoDB;

6.2 订单表

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no CHAR(32) NOT NULL,  -- 订单号（业务唯一）
  user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  discount_amount DECIMAL(10,2) DEFAULT 0,
  pay_amount DECIMAL(10,2) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,  -- 0=待支付，1=已支付，2=已发货，3=已完成，-1=已取消
  payment_method TINYINT,  -- 支付方式
  shipping_address JSON,   -- 收货地址（可能变化）
  remark VARCHAR(255),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME,
  shipped_at DATETIME,
  completed_at DATETIME,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
) ENGINE = InnoDB;

6.3 日志表

CREATE TABLE operation_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED,
  action VARCHAR(50) NOT NULL,
  module VARCHAR(50),
  ip_address VARCHAR(45),  -- IPv6
  user_agent VARCHAR(255),
  request_data JSON,
  response_code INT,
  cost_time INT UNSIGNED,  -- 毫秒
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at),
  INDEX idx_action (action)
) ENGINE = InnoDB;

总结

数据类型选择原则：

1. 更小更好：选择满足需求的最小类型
2. 简单更好：使用内置类型，避免自定义格式
3. 避免 NULL：使用 NOT NULL + 默认值
4. 金额用 DECIMAL：避免浮点数精度问题
5. 时间用 DATETIME/TIMESTAMP：便于计算和索引
6. 灵活扩展用 JSON：但不要过度使用

存储效率对比（100 万行）：

• TINYINT vs INT：节省 3MB
• CHAR(11) vs VARCHAR(11)：节省约 1MB
• DATETIME vs TIMESTAMP：多 4 字节但无时区问题

下一篇：表设计与范式理论 - 深入解析数据库范式、反范式设计和表设计最佳实践