目录

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

二、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 近似

大类	典型	字节	范围（有符号）	场景
整数	TINYINT	1	-128~127	性别、布尔
整数	INT	4	-21 亿~21 亿	主键、计数器
大整数	BIGINT	8	很大	雪花 ID
定点	DECIMAL(M,D)	变长	精确小数	金额
浮点	FLOAT/DOUBLE	4/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”出现

类型	字节	范围	精确度	备注
DATE	3	1000-01-01~9999-12-31	天	生日
DATETIME	8	同上	秒	默认无时区
DATETIME(fsp)	8+小数	同上	微秒	MySQL 5.6+
TIMESTAMP	4	1970-2038	秒	自动时区转换
TIME	3	-838:59:59~838:59:59	秒	时长
YEAR	1	1901-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 时，都能像挑剔的点单顾客，把“中杯、半糖、少冰”说得清清楚楚，让数据库端出的每一杯“数据拿铁”都刚刚好。