当前位置： 首页 > news >正文

从“存得对”到“存得准”：MySQL 数据类型与约束全景指南

news 2025/8/14 8:13:25

目录

一、为什么需要数据类型与约束？

二、MySQL 数据类型全览

1. 数值类型：精确 VS 近似

2. 日期时间类型：别让“0000-00-00”出现

3. 字符串类型：CHAR、VARCHAR、TEXT、BLOB

4. JSON 类型：文档与关系共舞

5. 空间类型：GIS 场景

6. 二进制与位类型

三、约束：给数据装上“安检门”

1. 列级约束：NOT NULL、DEFAULT、UNIQUE、CHECK

2. 主键与复合主键

3. 外键：一把双刃剑

4. 表级 CHECK 与触发器

5. 视图与权限：最后一道软性约束

四、联动设计：把类型与约束串成故事

1. 用户表

2. 订单表

3. 支付表

4. 索引与性能

五、最佳实践 5 条

六、结语

一、为什么需要数据类型与约束？

想象你在咖啡馆点单：
“我要一杯拿铁，中杯，加一份浓缩，半糖，少冰。”
如果服务员只在本子上写“拿铁”，结果可能端来超大杯、全糖、冰多到溢出。
数据库同理：字段若不声明“多大”“什么格式”“能否为空”，就会像“拿铁”一样失控——数字被截断、日期变 0000-00-00、字符串乱码、金额出现负值……
数据类型解决“存得对”，约束保证“存得准”。二者共同构成 MySQL 的第一道防线，也是性能与可维护性的根基。

二、MySQL 数据类型全览

1. 数值类型：精确 VS 近似

大类典型字节范围（有符号）场景
整数TINYINT1-128~127性别、布尔
整数INT4-21 亿~21 亿主键、计数器
大整数BIGINT8很大雪花 ID
定点DECIMAL(M,D)变长精确小数金额
浮点FLOAT/DOUBLE4/8近似值温度、GPS

陷阱与建议：

  • 金额永远用 DECIMAL，不要用 DOUBLE。DOUBLE 的二进制浮点误差会让 0.1+0.2≠0.3。

  • 主键自增别用 BIGINT(20)“吓唬”自己，除非预估 9.22e18 行，否则 INT 足够。

  • UNSIGNED 让上限翻倍，但 BIGINT UNSIGNED 与 Java long 互转时会溢出，需在 ORM 层注意。

示例：

CREATE TABLE goods (id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,price     DECIMAL(10,2) NOT NULL,stock     INT UNSIGNED DEFAULT 0
);

2. 日期时间类型：别让“0000-00-00”出现

类型字节范围精确度备注
DATE31000-01-01~9999-12-31天生日
DATETIME8同上秒默认无时区
DATETIME(fsp)8+小数同上微秒MySQL 5.6+
TIMESTAMP41970-2038秒自动时区转换
TIME3-838:59:59~838:59:59秒时长
YEAR11901-2155年几乎不用

陷阱：

  • 旧版本 MySQL 允许 0000-00-00，但 JDBC、Python 驱动会抛异常；务必 sql_mode=NO_ZERO_DATE。

  • TIMESTAMP 受时区影响，跨地域系统用 DATETIME+fsp 更稳。

  • 存储毫秒级时间戳可直接用 BIGINT 存 Unix 毫秒，避免 DATETIME 精度不够或 TIMESTAMP 2038 问题。

示例：

CREATE TABLE event_log (id        BIGINT PRIMARY KEY,happen_at DATETIME(3) NOT NULL,INDEX idx_happen (happen_at)
);

3. 字符串类型：CHAR、VARCHAR、TEXT、BLOB

  • CHAR(n) 定长，最大 255，尾部空格自动截断；适合短且等长码值，如国家代码 CHAR(2)。

  • VARCHAR(n) 变长，最大 65535 字节，受行大小 65535 限制；utf8mb4 下一个字符 4 字节，所以 VARCHAR(16383) 是极限。

  • TEXT 家族（TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT）存大文本，不能设默认值，不能完整索引（需前缀索引）。

  • BLOB 家族存二进制，如图片、PDF；同样不能设默认值，读写会走磁盘临时文件，慎用。

陷阱：

  • VARCHAR(255) 不等于 255 字符，而是 255 字节；utf8mb4 下最多 63 个汉字。

  • 用 TEXT 存 JSON 不如直接用 JSON 类型（见下）。

  • 大字段会触发“行溢出”，InnoDB 把值存到页外，随机 IO 增加。

示例：

CREATE TABLE article (id      BIGINT PRIMARY KEY,title   VARCHAR(200) NOT NULL,body    MEDIUMTEXT,cover   LONGBLOB
) CHARSET=utf8mb4;

4. JSON 类型：文档与关系共舞

MySQL 5.7+ 原生 JSON，二进制存储、可部分更新。支持函数 ->、->>、JSON_EXTRACT()、JSON_SET()。
优点：schema-less，适合动态字段。缺点：无法直接建外键、无法默认值。
示例：

CREATE TABLE user_ext (user_id BIGINT PRIMARY KEY,profile JSON,CHECK (JSON_VALID(profile))
);-- 查询
SELECT profile->>'$.nickname' AS nick
FROM user_ext
WHERE JSON_CONTAINS(profile->'$.tags', '"vip"');

5. 空间类型：GIS 场景

  • GEOMETRY、POINT、LINESTRING、POLYGON……

  • 需表引擎 InnoDB 或 MyISAM，建 SPATIAL INDEX。

  • 8.0 引入 SRID 强制坐标系，避免“经纬度颠倒”。

示例：

CREATE TABLE shop (id   BIGINT PRIMARY KEY,loc  POINT NOT NULL SRID 4326,SPATIAL INDEX idx_loc (loc)
);

6. 二进制与位类型

  • BINARY/VARBINARY：与 CHAR/VARCHAR 类似，但存字节而非字符，适合存哈希。

  • BIT：最大 64 位，存布尔标志位，省空间但可读性差。

  • ENUM/SET：背后用 1~8 字节存位图，可节省空间，但迁移成本高，不建议滥用。

三、约束：给数据装上“安检门”

1. 列级约束：NOT NULL、DEFAULT、UNIQUE、CHECK

  • NOT NULL：拒绝 NULL；NULL 与任何值比较都未知，导致索引失效。

  • DEFAULT：显式优于隐式；DEFAULT CURRENT_TIMESTAMP 记录创建时间。

  • UNIQUE：允许 NULL，但 NULL≠NULL，因此可出现多条 NULL；8.0.13 前不能有重复 NULL。

  • CHECK：8.0.16 原生支持，之前仅解析忽略；可写表达式 CHECK (age BETWEEN 0 AND 150)。

示例：

CREATE TABLE member (id     BIGINT PRIMARY KEY,email  VARCHAR(255) NOT NULL UNIQUE,age    TINYINT CHECK (age BETWEEN 0 AND 150),status ENUM('NEW','VIP','BAN') DEFAULT 'NEW'
);

2. 主键与复合主键

  • 主键 = NOT NULL + UNIQUE；InnoDB 聚簇索引，整张表物理顺序按主键排序。

  • 业务主键 vs 代理主键：自增 BIGINT 简单，但分布式场景用雪花 ID、UUID；后者无序会导致页分裂。

  • 复合主键 (tenant_id, id) 可做分库分表“联合主键”，但所有二级索引都需回表两次。

3. 外键：一把双刃剑

CREATE TABLE orders (id      BIGINT PRIMARY KEY,user_id BIGINT NOT NULL,FOREIGN KEY (user_id) REFERENCES user(id)ON UPDATE CASCADEON DELETE RESTRICT
);

优点：保证引用完整性；缺点：高并发写入时级联更新/删除会锁多表，互联网大厂常关闭外键，靠业务层保证。

4. 表级 CHECK 与触发器

  • 如果 CHECK 表达式复杂（如跨列、跨行），可用 BEFORE INSERT 触发器。

  • 触发器可写业务逻辑，但隐藏、难调试，尽量收敛到“数据校验”而非“业务流程”。

示例：

DELIMITER $$
CREATE TRIGGER trg_order_amount
BEFORE INSERT ON orders
FOR EACH ROW
BEGINIF NEW.amount <= 0 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'amount must be positive';END IF;
END$$
DELIMITER ;

5. 视图与权限：最后一道软性约束

  • 通过只读视图屏蔽危险列；

  • 用 DEFINER 与 SQL SECURITY INVOKER 做行级安全。

四、联动设计：把类型与约束串成故事

场景：设计“用户-订单-支付”核心表，要求：

  • 用户手机号唯一；

  • 订单金额必须大于 0；

  • 支付记录必须与订单同币种；

  • 支持软删除。

1. 用户表

CREATE TABLE user (id        BIGINT PRIMARY KEY,phone     CHAR(11) NOT NULL UNIQUE,nickname  VARCHAR(50),deleted   TINYINT(1) DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARSET=utf8mb4;

2. 订单表

CREATE TABLE orders (id        BIGINT PRIMARY KEY,user_id   BIGINT NOT NULL,amount    DECIMAL(10,2) NOT NULL CHECK (amount > 0),currency  CHAR(3) DEFAULT 'CNY',status    ENUM('PENDING','PAID','CLOSED') DEFAULT 'PENDING',deleted   TINYINT(1) DEFAULT 0,FOREIGN KEY (user_id) REFERENCES user(id)ON DELETE RESTRICTON UPDATE CASCADE
);

3. 支付表

CREATE TABLE payment (id        BIGINT PRIMARY KEY,order_id  BIGINT NOT NULL,currency  CHAR(3) NOT NULL,pay_amount DECIMAL(10,2) NOT NULL CHECK (pay_amount > 0),paid_at   DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (order_id) REFERENCES orders(id)ON DELETE RESTRICT,CONSTRAINT chk_currency_matchCHECK (currency = (SELECT currency FROM orders WHERE id = order_id))
);

注意：MySQL 8.0.16+ 才支持子查询 CHECK；低版本需触发器实现。

4. 索引与性能

  • 外键会自动创建索引，但 deleted 列需手动加联合索引 (deleted, status) 以便软删除列表查询。

  • DECIMAL 精确字段可用“整数分”代替：存分为 INT，避免浮点运算。

五、最佳实践 5 条

  1. 先选类型，再加约束：类型决定存储空间与运算方式，约束只是“护栏”。

  2. 金额用 DECIMAL(10,2) + CHECK>0；时间用 DATETIME(3) + DEFAULT CURRENT_TIMESTAMP(3)。

  3. 枚举值用 TINYINT 或 VARCHAR 存代码，留扩展空间；ENUM 仅用于非常稳定的状态机。

  4. 外键在 OLTP 关闭，在 OLAP 打开；或只在测试环境打开做 CI。

  5. 上线前跑 pt-osc 或 gh-ost 做无锁变更，防止加约束导致表锁。

六、结语

数据类型与约束是 MySQL 的地基。
类型选错，查询再花哨也如沙上建塔；约束缺失，业务再健壮也靠运气生存。
愿你在每一次 CREATE TABLE 时，都能像挑剔的点单顾客，把“中杯、半糖、少冰”说得清清楚楚，让数据库端出的每一杯“数据拿铁”都刚刚好。

http://www.lryc.cn/news/619610.html

相关文章：

  • opencv：直方图
  • Java pdf工具
  • 想要PDF翻译保留格式？用对工具是关键
  • java中数组和list的区别是什么?
  • 双屏加固笔记本电脑C156-2：坚固与高效的完美融合
  • 如何在 Ubuntu 24.04 LTS Noble Linux 上安装 FileZilla Server
  • Prompt工程师基础技术学习指南：从入门到实战
  • 为什么要使用消息队列呢？
  • STM32学习笔记10—DMA
  • 408每日一题笔记 41-50
  • 2023 年全国硕士研究生招生考试真题笔记
  • C语言零基础第15讲：字符函数和字符串函数
  • 一个接口多个实现类，如何动态调用
  • 长篇音频制作（小说自动配音）完整教程
  • 15.卷积神经网络
  • 【题解】[CQOI2006] 洛谷P4196 凸多边形 /【模板】半平面交
  • 钻井泥浆搅拌机的设计cad1张三维图＋设计说明书
  • 【Abp.VNext】Abp.Vnext框架模块学习
  • 服务器如何应对SYN Flood攻击？
  • 如何管理需求文档的版本历史
  • 【嵌入式电机控制#31】FOC：霍尔安装误差的补偿
  • MyBatis 中 XML 与 DAO 接口的位置关系及扫描机制详解
  • 深度学习——03 神经网络（2）-损失函数
  • Flutter网络请求实战：Retrofit+Dio完美解决方案
  • 51单片机-51单片机最小系统
  • 区块链DApp：颠覆未来的去中心化应用
  • 性能优化之通俗易懂学习requestAnimationFrame和使用场景举例
  • PyTorch生成式人工智能——基于Transformer实现文本转语音
  • 浅谈TLS 混合密钥交换：后量子迁移过渡方案
  • [TG开发]简单的回声机器人