6.8.4 字段类型选择与长度规划

字段类型选择与长度规划的核心目标是在满足业务语义的前提下最小化存储与索引成本，并降低转换与溢出风险。选型原则是“最小、最稳、可扩展”：优先使用定长或较小变长类型，避免使用过宽类型与无必要的精度。

原理草图：类型与索引成本关系

整数类型选择
- 依据业务范围选择最小可覆盖类型：TINYINT(1字节) → SMALLINT(2字节) → MEDIUMINT(3字节) → INT(4字节) → BIGINT(8字节)。
- 仅在确需负数时使用有符号类型，默认优先 UNSIGNED 以扩大正数范围并减少异常。
- 自增主键建议使用 BIGINT UNSIGNED 以满足长期扩展与分布式场景。

示例：整数与长度规划

-- 创建示例库与表
CREATE DATABASE IF NOT EXISTS ops_db;
USE ops_db;

CREATE TABLE user_profile (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gender TINYINT UNSIGNED NOT NULL DEFAULT 0,
  age TINYINT UNSIGNED NOT NULL,
  status TINYINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- 解释：
-- id 用 BIGINT UNSIGNED 保障长期增长
-- gender/age/status 用 TINYINT 节省空间

小数与浮点
- 金额、精度敏感字段使用 DECIMAL(M,D)，避免浮点误差；M、D按业务精度设定，如 DECIMAL(12,2)。
- FLOAT/DOUBLE 仅用于统计、地理坐标等可容忍误差场景。

示例：金额与统计字段

CREATE TABLE orders (
  order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  amount DECIMAL(12,2) NOT NULL,
  tax_rate DECIMAL(5,4) NOT NULL,
  score DOUBLE NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (order_id)
) ENGINE=InnoDB;

-- 解释：
-- amount/tax_rate 用 DECIMAL 避免浮点误差
-- score 用 DOUBLE 可接受误差

字符与文本
- 固定长度字段（如性别、状态码）用 CHAR；变长字段用 VARCHAR。
- VARCHAR长度应基于实际数据分布与索引需求评估，避免统一设为 255。
- 大文本用 TEXT 系列，需注意无法直接全列索引，需配合前缀索引或外部全文检索。

示例：VARCHAR 与前缀索引

CREATE TABLE account (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(128) NOT NULL,
  url VARCHAR(512) NULL,
  bio TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_email (email),
  KEY idx_url_prefix (url(64))  -- 前缀索引减少索引体积
) ENGINE=InnoDB;

时间与日期
- 选择与业务精度匹配的类型：DATE、TIME、DATETIME、TIMESTAMP。
- 需要时区敏感与自动更新的用 TIMESTAMP；存储历史业务时间建议 DATETIME。

示例：时间字段

CREATE TABLE audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  action VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  biz_time DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

枚举与集合
- ENUM 可用于状态类字段，值集合稳定且较小；避免频繁变更枚举值以降低维护成本。
- 对扩展性要求高的状态字段推荐使用 TINYINT + 映射表。

示例：枚举与映射表

CREATE TABLE task (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  status ENUM('new','running','done','failed') NOT NULL DEFAULT 'new',
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE task_status_dict (
  code TINYINT UNSIGNED NOT NULL,
  name VARCHAR(32) NOT NULL,
  PRIMARY KEY (code)
) ENGINE=InnoDB;

长度规划与索引影响
- 索引字段长度越短越利于缓存命中与B+树扇出，优先缩短索引列长度。
- 复合索引列顺序建议“高选择性、短字段优先”，以降低索引体积。
- VARCHAR长度需考虑字符集字节数（如 utf8mb4 每字符最多 4 字节），避免触发索引长度限制。

命令：查看表结构与索引

# 连接 MySQL（示例）
mysql -uroot -p -h127.0.0.1 -P3306

USE ops_db;
SHOW CREATE TABLE account\G;
SHOW INDEX FROM account\G;

预期效果
- 可看到 url 使用前缀索引。
- 可确认字段类型与长度是否符合规划。

排错与验证
1. 溢出或截断

-- 插入超范围值会报错（严格模式）
SET sql_mode='STRICT_TRANS_TABLES';
INSERT INTO user_profile (gender, age, status) VALUES (1, 300, 1);

预期报错：Out of range value for column 'age'。
处理：调整字段类型或校验输入范围。

索引长度超限（utf8mb4）

CREATE TABLE t_long (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

可能报错：Specified key was too long。
处理：缩短长度或使用前缀索引 KEY idx_name (name(100))，或升级到支持更大索引长度的版本并启用 innodb_large_prefix。

查表实际占用

SELECT
  table_name,
  data_length,
  index_length
FROM information_schema.tables
WHERE table_schema='ops_db';

用于评估类型与索引体积是否合理。

练习
1. 为订单表新增 buyer_email 字段，评估合理长度与索引策略，并给出 DDL。
2. 将 task.status 改为 TINYINT UNSIGNED 并建立映射表，写出迁移步骤与回滚方案。
3. 对 account.url 的前缀索引从 64 调整到 32，比较 SHOW INDEX 与 EXPLAIN 的差异。

业务常见字段建议
- 用户ID、订单ID：BIGINT UNSIGNED。
- 状态码：TINYINT UNSIGNED。
- 金额：DECIMAL(12,2) 或更高精度视业务而定。
- URL、邮箱：VARCHAR(128~255) 需评估索引需求。
- JSON 结构：优先使用 JSON 类型并配合生成列索引。

规范总结
- 禁止使用过宽类型作为主键或频繁索引字段（如 TEXT/BLOB）。
- 长度规划应基于数据分布、增长预期与索引需求，定期复盘并在版本迭代中优化。