📖 本篇简介：上一章装好了 MySQL，这一章开始动手建库建表。本章覆盖 DDL（数据定义语言）：数据库与表的 CREATE、DROP、ALTER，MySQL 8.0 的常用数据类型，以及主键、外键、唯一、非空等约束。学完本篇，你能设计出一张结构规范、约束完整的数据表，为后续 DML 增删改查打好地基。前置知识：第 1 章安装与连接；示例基于 MySQL 8.0。
目录
- 1 认识数据库与表
- 2 数据库的创建、修改与删除
- 3 数据类型详解
- 4 创建表
- 5 约束详解
- 6 表的修改与删除
- 7 常见坑与注意事项
- 8 速查表
- 9 练习题
1 认识数据库与表
MySQL 的层次结构是：实例（服务）→ 数据库（Database）→ 表（Table）→ 行/列。一个 MySQL 服务可以建很多库，一个库里可以建很多表，表由行（记录）和列（字段）组成。
-- 查看当前有哪些数据库（系统库 sys/mysql/performance_schema 别动）
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql：用户、权限、系统配置，千万别删改；sys：性能相关的视图，是给 DBA 看的；performance_schema：性能监控数据；information_schema：元数据（表结构等信息）。
DDL（Data Definition Language，数据定义语言）包括：CREATE（创建）、ALTER（修改）、DROP（删除）、TRUNCATE（清空）等，作用于库和表的结构。本章把这些全部过一遍。
2 数据库的创建、修改与删除
2.1 创建数据库
-- 创建数据库：指定字符集与排序规则，避免乱码
CREATE DATABASE IF NOT EXISTS demo
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
IF NOT EXISTS：库已存在时不报错（幂等，脚本常用）；utf8mb4：四字节 UTF-8，支持 emoji；utf8mb4_0900_ai_ci：8.0 默认排序规则，ai=不区分重音，ci=不区分大小写。
-- 进入数据库（切换当前库）
USE demo;
-- 确认当前在哪个库
SELECT DATABASE();
2.2 修改数据库
-- 修改数据库默认字符集（不能重命名数据库，只能改属性）
ALTER DATABASE demo CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
注意：ALTER DATABASE 只改默认字符集等属性，不能改库名。要重命名库只能：建新库 → 搬表 → 删旧库（第 7 节有坑提醒）。
2.3 删除数据库
-- 删除数据库（连带里面所有表，不可恢复！）
DROP DATABASE IF EXISTS demo;
删除库是高危操作，生产环境几乎永远轮不到你执行。真要删，先备份（第 16 章）。
3 数据类型详解
选对数据类型是表设计的第一课。MySQL 8.0 常用类型分四类：数值、字符串、日期时间、JSON。
3.1 数值类型
| 类型 | 字节 | 范围（有符号） | 用途 |
|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 状态码、开关、性别（可 0/1） |
| SMALLINT | 2 | -32768 ~ 32767 | 小计数 |
| INT | 4 | -21 亿 ~ 21 亿 | 常规整数（订单量、ID） |
| BIGINT | 8 | ±9.2×10^18 | 自增主键、大数（必须用！） |
| DECIMAL(M,D) | 变长 | 精确小数 | 金额必须用，绝不用 FLOAT |
| FLOAT/DOUBLE | 4/8 | 近似 | 科学计算、坐标（精度有限） |
-- 金额示例：DECIMAL(10,2) 表示共 10 位、小数点后 2 位
price DECIMAL(10,2) -- 最大 99999999.99
3.2 字符串类型
| 类型 | 说明 | 特点 |
|---|---|---|
| CHAR(N) | 定长字符串 | 长度固定、存取快，适合定长码（手机号、订单号） |
| VARCHAR(N) | 变长字符串 | 最长 N 字符，适合名称、描述，最常用 |
| TEXT | 长文本 | 最长 65535 字节，不设默认值、无索引前缀问题 |
| ENUM | 枚举 | 限定取值（'男','女'），改枚举需 ALTER，慎用 |
-- VARCHAR(50) 的 50 是"字符"数不是字节数，中文英文都按 1 个字符算
name VARCHAR(50) -- 可存 50 个汉字或 50 个字母
3.3 日期时间类型
| 类型 | 格式 | 用途 |
|---|---|---|
| DATE | 2026-10-07 | 生日、日期 |
| DATETIME | 2026-10-07 10:30:00 | 订单时间，最常用 |
| TIMESTAMP | 2026-10-07 10:30:00 | 带时区转换，范围到 2038 年 |
| YEAR | 2026 | 年份 |
-- 建表时给时间列加默认值：当前时间 + 插入时自动更新
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 插入时自动取当前时间
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时自动改时间
3.4 JSON 类型
-- 8.0 起 JSON 是原生类型，可建索引、可函数提取
ext_info JSON
-- 插入 JSON 并提取字段
INSERT INTO t (ext_info) VALUES ('{"level": 3, "tags": ["a", "b"]}');
-- 提取 JSON 字段（8.0 支持 -> 和 ->> 运算符）
SELECT ext_info ->> '$.level' AS level FROM t;
3.5 特殊类型速览：ENUM / SET / BOOLEAN / BIT
除了四大类，还有几个常用特殊类型，用对能省不少事：
| 类型 | 说明 | 典型用法 | 注意 |
|---|---|---|---|
| ENUM('a','b') | 枚举，只能取列出的值 | 状态、性别、支付方式 | 改取值要 ALTER 表；插入非法值在严格模式下报错 |
| SET('a','b') | 集合，可同时选多个 | 标签、权限位 | 用位运算查询，业务上尽量用关联表代替 |
| BOOLEAN | 实为 TINYINT(1)，0/1 | 是否上架、是否删除 | 别指望它是真正的布尔 |
| BIT(M) | 位串 | 位掩码 | 日常业务很少用 |
-- ENUM 示例：合法值之外的插入在严格模式直接报错
CREATE TABLE t_flag (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
level ENUM('low','mid','high') NOT NULL DEFAULT 'low' COMMENT '级别'
);
INSERT INTO t_flag (level) VALUES ('mid'); -- ✅ 合法
-- INSERT INTO t_flag (level) VALUES ('ultra'); -- ❌ 报错：Data truncated
选型建议：能用 TINYINT/字典表表达的，别用 ENUM——ENUM 改枚举值必须 ALTER TABLE，线上很痛苦。
4 创建表
4.1 基础建表语法
USE demo;
-- 创建用户表：完整演示列定义、主键、自增、默认值、注释
CREATE TABLE user (
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '用户ID，自增主键',
username VARCHAR(50) NOT NULL COMMENT '用户名，唯一',
email VARCHAR(100) NOT NULL COMMENT '邮箱',
age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄，默认18',
balance DECIMAL(10,2) DEFAULT 0.00 COMMENT '账户余额，金额用DECIMAL',
status TINYINT DEFAULT 1 COMMENT '状态：1正常 0禁用',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (id), -- 主键：非空 + 唯一
UNIQUE KEY uk_username (username), -- 唯一索引：用户名不重复
KEY idx_email (email) -- 普通索引：邮箱查询加速
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
要点拆解：
| 片段 | 含义 |
|---|---|
| BIGINT UNSIGNED AUTO_INCREMENT | 无符号大整数自增，8.0 起建议显式 UNSIGNED |
| NOT NULL | 该列不允许为空 |
| DEFAULT 18 | 插入不传时用默认值 |
| COMMENT '...' | 列/表注释，强烈建议每列都写，团队协作靠它 |
| PRIMARY KEY (id) | 主键约束 |
| ENGINE=InnoDB | 存储引擎，8.0 默认就是它，可省略但建议写明 |
| DEFAULT CHARSET=utf8mb4 | 表级字符集，防止乱码 |
4.2 查看表结构
-- 查看建表语句（最权威）
SHOW CREATE TABLE user\G
-- 查看字段信息
DESC user;
-- 查看表注释等
SHOW TABLE STATUS LIKE 'user'\G
4.3 快速建表技巧：CREATE TABLE AS / LIKE
不需要一条条写列时，可以用已有表快速建表：
-- 方式一：CREATE TABLE ... LIKE 只复制结构（含索引、约束），不复制数据
CREATE TABLE user_bak LIKE user;
-- 方式二：CREATE TABLE ... AS SELECT 只复制数据（不含索引/约束/自增属性）
CREATE TABLE user_snapshot AS SELECT * FROM user WHERE status = 1;
-- 验证两张表的结构差异
SHOW CREATE TABLE user_bak\G -- 有主键索引
SHOW CREATE TABLE user_snapshot\G -- 无索引，列类型可能被收窄
两种方式的区别要记牢：
| 方式 | 复制结构 | 复制数据 | 复制索引/约束 | 适用场景 |
|---|---|---|---|---|
| CREATE TABLE t2 LIKE t1 | ✅ | ❌ | ✅ | 备份表结构、分区测试 |
| CREATE TABLE t2 AS SELECT | ✅（简化版） | ✅ | ❌ | 快照、临时汇总表 |
坑提醒：AS SELECT 建出来的表没有主键、没有 AUTO_INCREMENT、DECIMAL 可能被降级为 DOUBLE，只能当临时分析表用；线上正式的备份表请用 LIKE + INSERT INTO ... SELECT。
5 约束详解
约束是数据库帮你守的规则，比应用层校验更可靠。五大约束 + 自增：
| 约束 | 关键字 | 作用 |
|---|---|---|
| 主键 | PRIMARY KEY | 非空 + 唯一，一行数据的身份 |
| 唯一 | UNIQUE | 列值不重复（可多个 NULL） |
| 非空 | NOT NULL | 值不能为空 |
| 默认值 | DEFAULT | 不传时给默认值 |
| 外键 | FOREIGN KEY | 引用另一张表的主键，保证引用完整性 |
| 检查 | CHECK | 值满足条件（8.0.16 起生效） |
5.1 主键与唯一
CREATE TABLE t_user (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 行内写法
card_no CHAR(18) UNIQUE, -- 身份证号唯一
phone VARCHAR(20) NOT NULL
);
-- 表尾写法（多列联合唯一时用）
CREATE TABLE t_order_item (
order_id BIGINT NOT NULL,
item_id BIGINT NOT NULL,
PRIMARY KEY (order_id, item_id) -- 联合主键：同一订单内商品不重复
);
5.2 外键（慎用）
-- 订单表引用用户表：保证订单的 user_id 必须真实存在
CREATE TABLE t_order (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user(id)
);
外键在互联网大厂几乎不用（影响并发写入、分库分表没法用），用应用层逻辑保证完整性。学习要懂，生产慎用（第 7 节坑里细说）。
5.3 CHECK 约束（8.0.16+ 真正生效）
CREATE TABLE t_account (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
age TINYINT NOT NULL,
-- 检查：年龄必须 0~120
CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 120)
);
-- 违反约束会直接报错
INSERT INTO t_account (age) VALUES (200);
-- ERROR 3819 (HY000): Check constraint 'chk_age' is violated.
6 表的修改与删除
6.1 ALTER TABLE 增删改列
-- 增加列
ALTER TABLE user ADD COLUMN nickname VARCHAR(50) DEFAULT '' COMMENT '昵称';
-- 修改列类型
ALTER TABLE user MODIFY COLUMN email VARCHAR(200) NOT NULL COMMENT '邮箱';
-- 重命名列（8.0 语法：RENAME COLUMN ... TO ...）
ALTER TABLE user RENAME COLUMN nickname TO nick;
-- 删除列（数据一并没了，慎用）
ALTER TABLE user DROP COLUMN nick;
-- 重命名表
ALTER TABLE user RENAME TO t_user;
-- 等价写法
RENAME TABLE t_user TO user;
6.2 删除表与清空表
-- 删除整张表（结构和数据都没了，不可恢复）
DROP TABLE IF EXISTS user;
-- 清空表数据（保留结构；自增计数重置；不可回滚）
TRUNCATE TABLE t_log;
TRUNCATE 和 DELETE 的区别（第 3 章细讲，先记住结论）：
| 对比项 | TRUNCATE | DELETE |
|---|---|---|
| 是否走事务 | 不可回滚（隐式提交） | 可回滚 |
| 是否触发 DML 触发器 | 不触发 | 触发 |
| 自增计数 | 重置 | 保留 |
| 速度 | 快（DDL 方式清页） | 慢（逐行删） |
| 条件过滤 | 不支持，只能全清 | 支持 WHERE |
7 常见坑与注意事项
坑 1：自增主键类型用了 INT，几年后爆掉
业务量大了 INT（21 亿）不够，爆表后插入报错 Out of range，只能停机改表。主键直接用 BIGINT，一张表 92 亿亿条足够。
坑 2：金额用 FLOAT/DOUBLE
浮点数是二进制近似存储，0.1+0.2 会算出 0.30000000000000004，对账就崩。金额一律 DECIMAL：
-- 错误示范
price FLOAT
-- 正确做法
price DECIMAL(10,2)
坑 3：乱码的根源——建库建表没指定字符集
只靠 my.ini 还不够，建库建表都要显式 DEFAULT CHARSET=utf8mb4，否则继承默认（5.7 是 latin1，必乱码）。检查：
-- 看表字符集
SHOW CREATE TABLE t_user\G
坑 4：外键滥用
外键在写入时多做一次引用检查，高并发下性能损耗明显，分库分表后更是直接失效。互联网场景结论：主键、唯一、非空用数据库约束，外键用应用层保证。
坑 5：重命名数据库？MySQL 没这功能
有人想 ALTER DATABASE 旧名 RENAME 新名——不存在。正确迁移：建新库 → RENAME TABLE 旧库.t TO 新库.t 逐表搬 → 确认无误 → DROP DATABASE 旧库。
-- 搬表示例
CREATE DATABASE new_db DEFAULT CHARSET utf8mb4;
RENAME TABLE old_db.user TO new_db.user;
坑 6：VARCHAR 长度拍脑袋
VARCHAR(10) 存 10 个汉字没问题（按字符算），但索引长度、行大小有限制（InnoDB 单行约 8KB）。经验值：用户名 50、邮箱 100、标题 200、正文用 TEXT。
8 速查表
| 操作 | SQL |
|---|---|
| 创建数据库 | CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARSET utf8mb4; |
| 切换数据库 | USE demo; |
| 查看所有库 | SHOW DATABASES; |
| 修改库字符集 | ALTER DATABASE demo CHARACTER SET utf8mb4; |
| 删除数据库 | DROP DATABASE IF EXISTS demo; |
| 创建表 | CREATE TABLE t (...); |
| 查看建表语句 | SHOW CREATE TABLE t\G |
| 查看表结构 | DESC t; |
| 加列 | ALTER TABLE t ADD COLUMN c INT COMMENT '...'; |
| 改列 | ALTER TABLE t MODIFY COLUMN c VARCHAR(100); |
| 删列 | ALTER TABLE t DROP COLUMN c; |
| 改列名 | ALTER TABLE t RENAME COLUMN a TO b; |
| 重命名表 | RENAME TABLE a TO b; |
| 删表 | DROP TABLE IF EXISTS t; |
| 清空表 | TRUNCATE TABLE t; |
9 练习题
练 1：创建数据库 shop（utf8mb4），并在其中创建商品表，含：BIGINT 自增主键、商品名（唯一）、价格 DECIMAL、库存 INT、创建时间默认当前时间，每列带注释。
<details>
<summary>参考答案</summary>
CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8mb4;
USE shop;
CREATE TABLE product (
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '商品ID',
name VARCHAR(100) NOT NULL COMMENT '商品名',
price DECIMAL(10,2) NOT NULL COMMENT '售价',
stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (id),
UNIQUE KEY uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
</details>
练 2：给上面的商品表增加一列 status TINYINT DEFAULT 1 COMMENT '上架状态'，然后删除该列，再验证表结构。
<details>
<summary>参考答案</summary>
ALTER TABLE product ADD COLUMN status TINYINT DEFAULT 1 COMMENT '上架状态';
DESC product; -- 看到 status 列
ALTER TABLE product DROP COLUMN status;
DESC product; -- status 已消失
</details>
练 3：解释 TRUNCATE 与 DELETE 的 4 个核心区别。
<details>
<summary>参考答案</summary>
1. 可回滚性：TRUNCATE 不可回滚（隐式提交），DELETE 在事务内可回滚；
2. 触发机制：TRUNCATE 不触发 DML 触发器，DELETE 触发；
3. 自增计数：TRUNCATE 重置，DELETE 保留；
4. 条件：TRUNCATE 只能全表清空，DELETE 可带 WHERE。
</details>
下一篇预告：第 3 章 数据操作DML详解
文章回复
0 条公开回复