【MySQL】详解MySQL数据范例

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

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

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

×
核心结论与决策建议(BLUF)
在 MySQL 物理 Schema 设计中,数据类型的精准选型是决定存储成本、内存利用率(Buffer Pool 缓存容量)以及 B+ 树索引查询性能的基石。在关系型存储中,严禁一切“以宽代窄、无脑使用 BIGINT 与 VARCHAR(255)”的粗放设计。必须严格遵循“够用即可取最小、定长取 CHAR、变长取 VARCHAR、货币金额强制 DECIMAL、高精度时间首选 DATETIME、杜绝非规范枚举”的五大原则。错误的类型选型不仅会导致单表物理体积膨胀数倍,还会导致单个 16KB 数据页能够容纳的行记录数暴跌,进而导致磁盘 IO 次数倍增、整体吞吐断崖式下跌。

数据类型类别常用具体类型物理存储开销精度与取值范围特征生产设计推荐规范
整数数值TINYINT, INT, BIGINT1, 4, 8 字节TINYINT: -128~127 (Unsigned 0~255)状态标识用 TINYINT;主键推荐 BIGINT UNSIGNED
精确小数DECIMAL(M,D)变长(每 9 位数字占用 4 字节)精确存储,无任何浮点舍入误差涉及金融、财务、订单金额必须强制使用 DECIMAL
定长文本CHAR(N)固定 N 个字符长度空间不足长度右侧自动填充空格,读取时剥离适用于固定长度数据,如 MD5(32)、UUID(36)、手机号(11)
变长文本VARCHAR(N)实际字符长度 + 1~2 字节长度前缀按需分配,超过字符集上限报错业务文本首选;合理指定 N,防止排序内存溢出
日期时间DATETIME vs TIMESTAMP5 字节 vs 4 字节DATETIME: 范围宽(1000~9999年);TIMESTAMP: 2038年危机现代系统统一推荐 DATETIME(3) 存储毫秒


一、MySQL 数据类型完整全景
MySQL 提供了极为丰富的内建数据类型系统,涵盖数值型、字符串型、日期时间型、空间地理型与 JSON 类型:


二、各类核心数据类型深度对比与避坑指南

2.1 整数体系与显示宽度误区

如上图所示,整数类型按照存储字节划分为五种:
1. TINYINT(1 字节):适合状态位(如
  1. is_deleted
复制代码
、
  1. order_status
复制代码
);
2. SMALLINT(2 字节):适合年份、端口号等范围在 0~65535 的数据;
3. MEDIUMINT(3 字节):适合行政区划代码等百万级数据;
4. INT(4 字节):最大约 21 亿(无符号 42 亿);
5. BIGINT(8 字节):适合分布式全局 ID、核心交易单号。

致命认知误区:INT(11) 与 INT(4) 的区别:
无数初学者误以为
  1. INT(4)
复制代码
只能存 4 位数字。事实上,无论括号内写几,在物理底层占用的均是严格的 4 个字节!括号内的数字仅代表在使用
  1. ZEROFILL
复制代码
时的“显示填充宽度”,在现代 MySQL 8.0 中该语法已被正式废弃。

2.2 浮点型(FLOAT/DOUBLE)与定点型(DECIMAL)
-
  1. FLOAT
复制代码
(4 字节)与
  1. DOUBLE
复制代码
(8 字节)遵循 IEEE 754 标准,底层通过二进制科学计数法存储,存在无法消除的精度截断。例如计算
  1. 0.1 + 0.2
复制代码
可能产生
  1. 0.30000000000000004
复制代码
;
-
  1. DECIMAL(10, 2)
复制代码
:由 MySQL 底层通过特定结构体实现的高精度十进制数字存储。在金融清结算、优惠券金额、汇率等场景,必须严禁使用 FLOAT/DOUBLE,强制采用 DECIMAL。

2.3 字符串选型:CHAR vs VARCHAR vs TEXT

  1. -- 正确示范:严格依据字段特征选型
  2. CREATE TABLE user_profile (
  3.     id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  4.     -- 固定 11 位手机号,定长查询速度极快
  5.     mobile CHAR(11) NOT NULL DEFAULT '',
  6.     -- 密码哈希串固定 64 位(SHA256)
  7.     password_hash CHAR(64) NOT NULL DEFAULT '',
  8.     -- 昵称变长,最大 32 个字符足够
  9.     nickname VARCHAR(32) NOT NULL DEFAULT '',
  10.     -- 超长富文本单独隔离或存为 TEXT
  11.     bio TEXT
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制代码

VARCHAR(N) 核心避坑:
参数 N 指的是字符数,而非字节数!在
  1. utf8mb4
复制代码
字符集下,单个字符最多占用 4 字节。若定义为
  1. VARCHAR(255)
复制代码
,在内存排序执行
  1. filesort
复制代码
时,MySQL 会预先按照最大可能长度分配内存缓冲,导致极易耗尽临时表内存并落盘。

2.4 日期时间选型:DATETIME vs TIMESTAMP
1. TIMESTAMP:物理占用 4 字节,内部转换为 UTC 国际协调时存储,读取时根据客户端当前时区动态显示。但其最大时间仅支持到 2038 年 1 月 19 日(经典的 2038 年 32 位整型溢出危机);
2. DATETIME:自 MySQL 5.6.4 起采用全新存储格式,占用 5 字节,存储范围为
  1. 1000-01-01 00:00:00
复制代码
至
  1. 9999-12-31 23:59:59
复制代码
,不随时区变化而改变。
选型建议:现代系统推荐无脑选择
  1. DATETIME
复制代码
,若需记录毫秒可使用
  1. DATETIME(3)
复制代码
,并在应用层统一将时区转换为 UTC 存储。

常见问题(FAQ)
Q1: 为什么有些大厂的 DBA 强制要求把金额字段定义为 BIGINT,以“分”为单位存储,而不是 DECIMAL?
A1: 这是一个非常经典的工程权衡。DECIMAL 在计算时需要使用 CPU 软浮点库逐位模拟,运算效率略低于整数算术单元;而采用
  1. BIGINT
复制代码
并以“分”或“厘”为单位存储,既彻底规避了小数精度失真的问题,又能完全走 CPU 原生寄存器整数高效运算,非常适合每秒数十万笔高频支付交易系统。

Q2: 如果一个字段存储的数据只有几个固定枚举项(如 'MALE', 'FEMALE', 'UNKNOWN'),应该使用 ENUM 还是 TINYINT?
A2: 强烈建议使用
  1. TINYINT
复制代码
,严禁在核心业务表中使用 MySQL 内置的
  1. ENUM
复制代码
类型!因为一旦未来需要增加新的枚举状态,修改 ENUM 必须执行高昂的
  1. ALTER TABLE
复制代码
元数据变更锁表;此外,ENUM 底层按照整型索引存储但显示为字符串,在与其他系统数据交换或进行跨库同步时极易产生顺序混乱 Bug。



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

使用道具 举报

登录后关闭弹窗

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