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

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

news 2025/8/14 6:11:50

目录

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

二、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.xdnf.cn/news/1290511.html

相关文章：

  • 算法题打卡力扣第11题：盛最多水的容器（mid）
  • 音视频处理新纪元：12款AI模型的语音转录和视频理解能力横评
  • 洛谷 P2607 [ZJOI2008] 骑士-提高+/省选-
  • 从钢板内部应力视角，重新认识护栏板矫平机
  • 猫头虎AI分享｜ 智谱开源了为 RL scaling 设计的 LLM post‑training 框架用于GLM-4.5强化学习训练：slime
  • 深入解析C语言嵌套结构体的内存管理与操作实践
  • 基于CNN与Transformer的无人机应急救援网络异常流量检测
  • 在前端js中使用jsPDF或react-to-pdf生成pdf文件时，不使用默认下载，而是存储到服务器
  • SQL详细语法教程（一）--数据定义语言（DDL）
  • Android SurfaceView TextureView
  • 【Qt开发】常用控件（三） -＞ geometry
  • kernel pwn 入门(四) ret2dir详细
  • 大模型推理框架vLLM 中的Prompt缓存实现原理
  • GitHub分支保护介绍（Branch Protection）（git分支保护）（通过设置规则和权限来限制对特定分支的操作的功能）
  • 嵌入式系统学习Day17(文件编程-库函数调用)
  • AuthController类讲解
  • SQL 合并两个时间段的销售数据：FULL OUTER JOIN + COALESCE
  • 测试环境下因网络环境变化导致集群无法正常使用解决办法
  • SQL注入学习笔记
  • LeetCode Day5 -- 栈、队列、堆
  • 前后端分离项目中Spring MVC的请求执行流程
  • 肖臻《区块链技术与应用》第十讲：深入解析硬分叉与软分叉
  • 用 Spring 思维快速上手 DDD——以 Kratos 为例的分层解读
  • provide()函数和inject()函数
  • 数据结构：后缀表达式：结合性 (Associativity) 与一元运算符 (Unary Operators)
  • ZKmall开源商城的容灾之道：多地域部署与故障切换如何守护电商系统
  • 21.Linux HTTPS服务
  • 【GESP】C++一级知识点之【集成开发环境】
  • 备战国赛算法讲解——马尔科夫链，2025国赛数学建模B题详细思路模型更新
  • UE5.3 C++ 动态多播实战总结