MySQL 数据库常见字段范例大全及具体分析

[复制链接]
发表于 昨天 22:32 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
核心结论与决策建议(BLUF)
数据表字段类型的选型是决定数据库底层物理存储密度、Buffer Pool 缓存命中率及索引检索吞吐的第一道基因工程。任何过度设计(如滥用 BIGINT 存储状态位、使用 VARCHAR(255) 存储固定代码、或使用 FLOAT 存储金额)都会导致单行体积膨胀,加剧磁盘I/O排队并引发精度灾难。字段设计核心军规:1. 货币与资产金额强制采用高精度 DECIMAL,严禁使用 FLOAT / DOUBLE(二进制浮点数精度丢失);2. 字符串长度固定且短(如MD5、UUID、性别代码)坚决用 CHAR,长度可变用 VARCHAR;3. 整数遵循最小够用原则(TINYINT、SMALLINT、INT、BIGINT 阶梯选型);4. 所有字段强推显式声明 NOT NULL 并设定 DEFAULT。
数据类型类别推荐数据类型底层物理存储开销选型核心准则与边界
高精度金融数值
  1. DECIMAL(M, D)
复制代码
变长(每9位十进制数占4字节)财务账本、商品单价,绝对杜绝浮点误差
状态与小型枚举
  1. TINYINT UNSIGNED
复制代码
1 字节 (范围 0 ~ 255)业务启用状态、性别、逻辑删除标志
常规主键/业务ID
  1. BIGINT UNSIGNED
复制代码
8 字节 (0 ~ 1800亿亿)全局雪花ID、分布式流水、电商订单号
固定长度文本
  1. CHAR(N)
复制代码
静态固定分配 N 个字符空间MD5哈希 (CHAR(32))、国密哈希、ISO编码
变长常规文本
  1. VARCHAR(N)
复制代码
实际字节数 + 1~2 字节长度前缀用户名、邮箱、地址,按需预估上限
非结构化扩展
  1. JSON
复制代码
二进制格式存储,支持生成列索引多变扩展属性,避免频繁变更物理 DDL


一、数值类型精细化辨析与避坑指南
1. 整数类型阶梯配置:
-
  1. TINYINT
复制代码
(1 字节):适合状态(0:禁用, 1:启用)、类型(0~255)。
-
  1. SMALLINT
复制代码
(2 字节):适合字典编码、年份、排序序号(-32768 ~ 32767)。
-
  1. MEDIUMINT
复制代码
(3 字节):适合中型分类ID(0 ~ 1600万)。
-
  1. INT
复制代码
(4 字节):常规自增主键(建议加
  1. UNSIGNED
复制代码
扩充上限至 42 亿)。
-
  1. BIGINT
复制代码
(8 字节):核心大表主键、毫秒级时间戳、分布式全局唯一 ID。
避坑参数 INT(11) 的认知误区:括号内的数字 11 仅代表客户端显示宽度(配合 ZEROFILL 补零),完全不影响其底层的 4 字节存储大小与数值范围上限!

2. 浮点数 vs 定点数的血泪教训:

  1. -- 错误写法:使用 DOUBLE 存储资产余额
  2. CREATE TABLE bad_account (balance DOUBLE);
  3. -- 插入 0.1 后累加三次,结果可能变为 0.30000000000000004!

  4. -- 正确规范:强制使用 DECIMAL 定点数
  5. CREATE TABLE good_account (balance DECIMAL(12, 2) NOT NULL DEFAULT 0.00);
  6. -- DECIMAL 在 MySQL 底层使用二进制编码的十进制格式存储,绝对保障 100% 精度无损!
复制代码


二、字符串类型深度抉择:CHAR vs VARCHAR vs TEXT
1. CHAR 的极致性能适用场景:
  1. CHAR(N)
复制代码
属于定长存储。写入时若不足 N 个字符,MySQL 会在末尾自动补空格(读取时自动截断空格)。
- 优势:因为每行长度固定,InnoDB 在修改该列时不会引发数据页内行迁移或碎片重组。
- 最佳落地:适用于固定位数的字段,如手机号
  1. CHAR(11)
复制代码
、MD5 摘要
  1. CHAR(32)
复制代码
、身份证号
  1. CHAR(18)
复制代码
。
2. VARCHAR 的变长节省哲学:
  1. VARCHAR(N)
复制代码
仅保存实际字符所需的字节数,并在头部追加 1 字节(总长 <= 255)或 2 字节(总长 > 255)作为长度标识符。
- 预估原则:虽为变长,但绝不能随手写
  1. VARCHAR(255)
复制代码
!因为在内存排序(Memory Engine)或创建临时表时,系统会预分配完整的字符集最大宽度(
  1. utf8mb4
复制代码
下 255 字符会占用 1020 字节内存),严重浪费 Buffer 空间。
3. 谨慎使用 TEXT 与 BLOB:
对于超过数千字符的大文本,字段会被下沉到溢出页(Off-page / Overflow Page)存储。跨页读取会引发额外的磁盘随机寻道,严重拖累主表的批量查询。大段描述字段强推垂直拆分到独立的扩展表中。

三、JSON 类型的现代化融合实战
自 MySQL 5.7.8 起引入了原生的二进制 JSON 类型:

  1. CREATE TABLE t_device_metric (
  2.     id BIGINT PRIMARY KEY AUTO_INCREMENT,
  3.     device_id INT NOT NULL,
  4.     payload JSON NOT NULL,
  5.     -- 基于 JSON 虚拟列建立高性能 B+ 树索引
  6.     cpu_usage DECIMAL(5,2) GENERATED ALWAYS AS (payload->>'$.cpu') VIRTUAL,
  7.     INDEX idx_cpu (cpu_usage)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制代码

JSON 提供了无结构模式(Schema-Free)的巨大灵活性,配合生成列(Generated Column)解决了 NoSQL 无法高效利用关系型二级索引的痛点。

四、为什么生产规范强制要求尽量定义 NOT NULL?
1. 三值逻辑陷阱:NULL 参与的逻辑判断(如
  1. = NULL
复制代码
,
  1. != NULL
复制代码
)均返回 UNKNOWN,导致统计失准。
2. 存储开销与索引效率:InnoDB 行记录头必须额外维护一个 NULL 标志位位图(NULL Value Bitmap);在构建索引时,包含 NULL 的复合索引扫描与覆盖索引优化逻辑更复杂。显式指定
  1. NOT NULL DEFAULT ''
复制代码
或
  1. DEFAULT 0
复制代码
能够大幅提升代码鲁棒性。

常见问题(FAQ)
Q1: 为什么在
  1. utf8mb4
复制代码
字符集下,单表所有 VARCHAR 列的总长度不能超过 65535 字节?

A1: 这是 MySQL 内部对单行记录最大长度的物理硬限制(除 TEXT/BLOB 溢出存储外,单行普通列总宽不得超过 65535 字节)。在
  1. utf8mb4
复制代码
编码下,单个字符最多占用 4 字节。加上变长字段的长度标识与 NULL 标志位开销,一张表中单列 VARCHAR 的最大字符数上限约为 $(65535 - 2 - 1) / 4 \approx 16383$ 个字符。

Q2: 存储 IP 地址字段,应该用 VARCHAR(15) 还是 INT UNSIGNED?
A2: 强烈推荐使用
  1. INT UNSIGNED
复制代码
(仅占 4 字节)!通过 MySQL 内置函数
  1. INET_ATON('192.168.1.1')
复制代码
转为整数存储,查询展示时调用
  1. INET_NTOA()
复制代码
转回点分十进制。相比
  1. VARCHAR(15)
复制代码
节省了高达 70% 的存储与索引空间,且整数范围查询与排序在 CPU 底层远比字符串比对快速。



qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。
回复

使用道具 举报

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表