【MySQL深入详解】第12篇：数据类型选择——让数据库更高效的第一步

开篇引入

选对数据类型，查询快一倍；选错数据类型，后患无穷。

很多人觉得数据类型随便选，反正都能存下。这种想法会害死人——INT(1)和INT(20)存储空间一样，但VARCHAR(10)和VARCHAR(200)可不是。选错类型，轻则浪费存储空间，重则影响索引效率、拖慢整个系统。

《高性能MySQL》第6章专门讲了数据类型选择，这篇文章帮你建立正确的数据类型选择观念。

数据类型选择三原则

1. 越小越好

能用TINYINT就别用INT，能用SMALLINT就别用MEDIUMINT。更小的数据类型：

• 占用更少的磁盘空间
• 占用更少的内存
• 占用更少的CPU缓存
• 处理时需要的CPU周期更少

常见整数类型：

类型	存储空间	有符号范围	无符号范围
TINYINT	1字节	-128~127	0~255
SMALLINT	2字节	-32768~32767	0~65535
MEDIUMINT	3字节	-8388608~8388607	0~16777215
INT	4字节	-21亿~21亿	0~42亿
BIGINT	8字节	非常大	0~非常大

-- 错误示例：用户年龄用INT
CREATE TABLE users_bad (
    age INT
);

-- 正确示例：用户年龄用TINYINT（0-255足够）
CREATE TABLE users_good (
    age TINYINT UNSIGNED
);

注意：INT(11)中的11不代表存储范围，只影响显示宽度：

-- INT(1) 和 INT(20) 存储范围完全一样！
CREATE TABLE t1 (id INT(1));
CREATE TABLE t2 (id INT(20));
-- 存储范围都是 -2147483648 ~ 2147483647

2. 简单就好

简单数据类型的操作更快。整型比较比字符比较代价低得多。

日期时间存储：

-- 错误示例：用字符串存储日期
CREATE TABLE orders_bad (
    order_date VARCHAR(10)  -- '2024-01-15'
);

-- 正确示例：用DATE/DATETIME存储
CREATE TABLE orders_good (
    order_date DATE,
    order_time DATETIME
);

IP地址存储：

-- 错误示例：用字符串存储IP
CREATE TABLE access_log_bad (
    ip_address VARCHAR(15)  -- '192.168.1.100'
);

-- 正确示例：用INT存储IP（MySQL提供函数）
CREATE TABLE access_log_good (
    ip_address INT UNSIGNED
);

-- 存储时转换
INSERT INTO access_log_good (ip_address) 
VALUES (INET_ATON('192.168.1.100'));

-- 查询时转换回来
SELECT INET_NTOA(ip_address) FROM access_log_good;

3. 尽量避免NULL

NULL值会让索引、索引统计和值比较变得更复杂。

-- 错误示例：大量可空列
CREATE TABLE users_bad (
    nickname VARCHAR(50) NULL,    -- 多占空间
    birthday DATE NULL,            -- 索引变复杂
    phone VARCHAR(20) NULL
);

-- 正确示例：明确NOT NULL
CREATE TABLE users_good (
    nickname VARCHAR(50) NOT NULL DEFAULT '',
    birthday DATE NOT NULL,
    phone VARCHAR(20) NOT NULL DEFAULT ''
);

什么时候可以用NULL：

• 确实需要表示"未知"
• 无法确定一个合理的默认值
• 使用0或空字符串会产生歧义

VARCHAR vs CHAR

VARCHAR：可变长度字符串

-- VARCHAR(10) 存储 'hello' 只需要6字节
-- 1字节长度 + 5字节数据
CREATE TABLE t1 (
    name VARCHAR(10)
);

适合VARCHAR的场景：

• 字符串长度差异大
• 平均长度远小于最大长度
• 更新不频繁（避免碎片）

注意：

• VARCHAR需要1-2字节存储长度
• 超过255字节需要2字节

CHAR：固定长度字符串

-- CHAR(10) 存储 'hello' 需要10字节
-- 不足部分用空格填充
CREATE TABLE t2 (
    code CHAR(10)
);

适合CHAR的场景：

• 长度固定（如邮编、MD5值）
• 经常更新（不会有碎片）
• 非常短的字符串

-- 演示CHAR vs VARCHAR的行为
CREATE TABLE demo (
    c CHAR(10),
    v VARCHAR(10)
);

INSERT INTO demo VALUES ('hello', 'hello');

SELECT CONCAT('[', c, ']') AS char_result, 
       CONCAT('[', v, ']') AS varchar_result 
FROM demo;

-- CHAR会截断尾部空格，VARCHAR不会
-- char_result: [hello     ]
-- varchar_result: [hello]

VARCHAR长度选择

关键原则：只分配真正需要的空间。

-- 错误：预留过大空间
CREATE TABLE users_bad (
    name VARCHAR(200),    -- 大多数名字不超过20字符
    email VARCHAR(200)    -- 大多数邮箱不超过50字符
);

-- 正确：按需分配
CREATE TABLE users_good (
    name VARCHAR(50),
    email VARCHAR(100)
);

原因：MySQL会分配固定大小的内存来保存VARCHAR值。

整数类型详解

-- 无符号 vs 有符号
CREATE TABLE demo (
    a TINYINT,           -- -128 ~ 127
    b TINYINT UNSIGNED   -- 0 ~ 255
);

什么时候用无符号：

• ID（通常为正整数）
• 计数器
• 不会出现负值的业务数据

计算时的注意：

-- MySQL默认整数计算使用BIGINT
SELECT 1 / 3;  -- 结果: 0.3333333333333333
SELECT 1 / 3 = 0.3333333333333333;  -- 0 (FALSE)

实数类型：DECIMAL vs FLOAT

DECIMAL：精确小数

-- DECIMAL用于需要精确计算的场景
-- 如：金融数据
CREATE TABLE accounts (
    balance DECIMAL(15, 2)  -- 总共15位，小数点后2位
);

DECIMAL特点：

• 精确存储，不丢失精度
• 存储空间大（每4位数字占1字节）
• 计算代价高

FLOAT/DOUBLE：近似小数

-- FLOAT：4字节
-- DOUBLE：8字节
CREATE TABLE measurements (
    temperature FLOAT,
    pressure DOUBLE
);

什么时候用FLOAT：

• 对精度要求不高
• 需要快速计算
• 存储科学数据

大数据量的货币存储

-- 如果数据量很大，可以用BIGINT存储最小单位
-- 例如：存储到分为单位的人民币
CREATE TABLE orders (
    amount BIGINT  -- 单位：分，1000000 = 10000.00元
);

-- 应用程序处理显示
-- 数据库只存储整数

日期时间类型

类型	存储空间	范围	备注
DATE	3字节	1000-01-01 ~ 9999-12-31	只有日期
TIME	3字节	-838:59:59 ~ 838:59:59	只有时间
DATETIME	8字节	1000-01-01 ~ 9999-12-31	日期+时间
TIMESTAMP	4字节	1970-2038	依赖时区

-- 两种存储时间的对比
CREATE TABLE events (
    dt DATETIME,
    ts TIMESTAMP
);

INSERT INTO events VALUES ('2024-01-15 10:30:00', '2024-01-15 10:30:00');

-- MySQL默认时区
SELECT @@time_zone;  -- SYSTEM

-- 转为东八区
SET time_zone = '+08:00';

-- TIMESTAMP会自动转换
SELECT dt, ts FROM events;

建议：

• 存储用户可见的时间 → TIMESTAMP（自动转换时区）
• 存储固定业务时间 → DATETIME（不随服务器时区变化）

ENUM枚举类型

-- 使用ENUM代替字符串
CREATE TABLE orders (
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
);

-- 存储的是整数，不是字符串
-- 'pending' = 1, 'processing' = 2, ...

ENUM的优点：

• 存储紧凑（1-2字节）
• MySQL内部存储位置
• 保证数据合法性

ENUM的缺点：

• 添加新值需要ALTER TABLE
• 列表值不易扩展
• 排序基于位置，不是字母

谨慎使用ENUM的场景：

-- 不要把应该独立成表的数据做成ENUM
-- 错误示例：
CREATE TABLE products (
    category ENUM('electronics', 'clothing', 'food', 'books', 'sports', 'home', 'toys', ...)
);

-- 正确做法：用外键关联
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE products (
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

JSON类型（MySQL 5.7+）

-- MySQL原生支持JSON
CREATE TABLE user_preferences (
    id INT PRIMARY KEY,
    user_id INT,
    preferences JSON
);

-- 插入JSON
INSERT INTO user_preferences (user_id, preferences) VALUES (1, '{"theme": "dark", "notifications": true, "language": "zh"}');

-- 查询JSON字段
SELECT 
    preferences->>'$.theme' AS theme,
    preferences->>'$.language' AS language
FROM user_preferences;

-- 索引JSON字段（MySQL 8.0+）
ALTER TABLE user_preferences ADD INDEX ((CAST(preferences->>'$.language' AS CHAR(10))));

BIT位字段

-- 不推荐用BIT存储布尔值
CREATE TABLE flags_bad (
    flag BIT(8)
);

-- 用TINYINT更清晰
CREATE TABLE flags_good (
    flag TINYINT DEFAULT 0
);

类型选择实战

用户表

CREATE TABLE users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash CHAR(32) NOT NULL,  -- MD5固定32字符
    status TINYINT NOT NULL DEFAULT 1,  -- 1=正常, 0=禁用
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

订单表

CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(15, 2) NOT NULL,  -- 金额要精确
    status TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    paid_at DATETIME NULL,
    shipped_at DATETIME NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

日志表

CREATE TABLE access_logs (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NULL,  -- 可能为匿名访问
    ip_address INT UNSIGNED NOT NULL,
    action VARCHAR(100) NOT NULL,
    request_time INT UNSIGNED NOT NULL,  -- 毫秒
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_ip (ip_address),
    INDEX idx_created_at (created_at)
);

小结

1. 越小越好：用能容纳数据的最小类型
2. 简单就好：用内置类型而不是字符串
3. 尽量避免NULL：除非真的需要表示"未知"
4. VARCHAR按需分配：不要预留过大空间
5. CHAR适合固定长度：如MD5、邮编
6. 日期时间优先用内置类型：DATETIME vs TIMESTAMP按需选择
7. 货币用DECIMAL：精确计算，不丢失精度
8. ENUM要慎用：适合变化少、合法的选项列表

数据类型选择是Schema设计的第一步，选对了能让性能提升，选错了后患无穷。

---

延伸阅读

• 《高性能MySQL》第6章 schema设计与管理
• MySQL 8.0 Reference Manual: Data Types
• MySQL Performance Blog