本文全面梳理 MySQL 数据库表的创建（CREATE）、修改（ALTER）、删除（DROP）三大操作，涵盖 DDL 语句基础、常用字段类型选型、主外键与约束设计、规范化建模思路，并结合典型业务场景展示“用户表”“订单表”“订单明细表”完整建表与演进案例，帮助读者掌握表结构的日常维护与迭代技巧。
1. 数据库表操作概览在关系型数据库中，表是存储数据的核心载体。日常开发中，表的结构往往需要根据业务需求演进：新表上线、字段变更、索引优化、表拆分与归档，以及在项目废弃时的表删除。因此，熟练掌握 DDL（Data Definition Language）语句，能够保障线上变更的平滑与安全。
2. DDL 语句详解2.1 CREATE TABLE最常见的建表语句，用于定义新表及其列、索引、约束等。
代码语言：javascript代码运行次数：0运行复制CREATE TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL COMMENT '用户名，唯一',
`password` CHAR(60) NOT NULL COMMENT '加盐哈希后密码',
`email` VARCHAR(100) DEFAULT NULL COMMENT '用户邮箱',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1=活跃，0=禁用',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_username` (`username`),
INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';ENGINE=InnoDB：支持事务与外键。CHARSET/ COLLATE：统一字符集，推荐 utf8mb4。AUTO_INCREMENT：自增主键。UNIQUE/INDEX：唯一与普通索引。2.2 ALTER TABLE用于在已有表上新增、修改、删除列、约束和索引。
新增列
代码语言：javascript代码运行次数：0运行复制ALTER TABLE `user`
ADD COLUMN `mobile` VARCHAR(20) NULL AFTER `email`,
ADD INDEX `idx_mobile` (`mobile`);修改列
代码语言：javascript代码运行次数：0运行复制ALTER TABLE `user`
MODIFY COLUMN `username` VARCHAR(100) NOT NULL;重命名列或表
代码语言：javascript代码运行次数：0运行复制ALTER TABLE `user`
CHANGE COLUMN `status` `state` TINYINT NOT NULL DEFAULT 1 COMMENT '状态';
-- 或者：
RENAME TABLE `user` TO `app_user`;删除列或索引
代码语言：javascript代码运行次数：0运行复制ALTER TABLE `user`
DROP COLUMN `mobile`,
DROP INDEX `idx_mobile`;2.3 DROP TABLE彻底删除表及其数据、索引、约束，需要谨慎操作，建议配合事务或备份。
代码语言：javascript代码运行次数：0运行复制DROP TABLE IF EXISTS `order_item`;3. 常用字段类型选型在设计表结构时，合理选型能提高存储效率与查询性能。
3.1 数值类型类型
范围（有符号）
存储空间
适用场景
TINYINT
-128 ~ 127
1 字节
状态、标志位
SMALLINT
-32768 ~ 32767
2 字节
人数、评分等
INT
-2^31 ~ 2^31-1
4 字节
主键、计数、金额（小）
BIGINT
-2^63 ~ 2^63-1
8 字节
全局 ID、累计金额
DECIMAL(m,n)
精确小数，m 位总长度，n 位小数
可变
金额、汇率
3.2 字符串类型CHAR(n)：定长，查询速度快，适合固定长度。VARCHAR(n)：变长，存储节省，适合可变长度。TEXT / MEDIUMTEXT / LONGTEXT：海量文本，不建议建索引。3.3 时间与日期类型DATE：仅含年月日。DATETIME：精确到秒，不受时区影响。TIMESTAMP：精确到秒，自动转换时区，推荐记录业务事件时间。YEAR：记录年份。3.4 枚举与集合ENUM(‘A’,‘B’,…）：选项有限时，字段值更直观，存储占用 1～2 字节。SET：可存储多选，底层为位图，适合多标签。4. 主键、外键与约束策略良好的约束设计可保证数据一致性与完整性。
4.1 主键（PRIMARY KEY）单列主键：最常用，以自增或全局唯一 ID（UUID、雪花算法）为主。联合主键：多列联合，适合弱实体或映射表。代码语言：javascript代码运行次数：0运行复制PRIMARY KEY (`order_id`, `item_id`)4.2 唯一约束（UNIQUE）保证列值唯一，可防止重复数据代码语言：javascript代码运行次数：0运行复制UNIQUE KEY `uniq_email` (`email`)4.3 外键（FOREIGN KEY）强制参照完整性，通常在 InnoDB 引擎中使用注意：外键会带来锁竞争，OLTP 业务中应谨慎使用代码语言：javascript代码运行次数：0运行复制ALTER TABLE `order`
ADD CONSTRAINT `fk_order_user`
FOREIGN KEY (`user_id`)
REFERENCES `user`(`id`)
ON DELETE CASCADE
ON UPDATE RESTRICT;4.4 其他约束NOT NULL：防止空值
CHECK（MySQL 8.0.16+ 支持）：字段值校验
代码语言：javascript代码运行次数：0运行复制`status` TINYINT NOT NULL
CHECK (`status` IN (0,1,2));5. 规范化设计与范式原则5.1 第一范式（1NF）每个字段都是不可再分的数据项；5.2 第二范式（2NF）满足 1NF，且所有非主属性完全依赖主键；5.3 第三范式（3NF）满足 2NF，且非主属性不传递依赖；通过范式化保证数据不冗余、易维护。但对高并发、分析型场景，可考虑适度反范式或分表分库。
6. 业务实战案例6.1 用户表（user）代码语言：javascript代码运行次数：0运行复制CREATE TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` CHAR(60) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`status` TINYINT NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_username` (`username`),
UNIQUE KEY `uniq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;6.2 订单表（order）代码语言：javascript代码运行次数：0运行复制CREATE TABLE `order` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL,
`order_no` CHAR(32) NOT NULL COMMENT '唯一订单号',
`total_amount` DECIMAL(10,2) NOT NULL,
`status` ENUM('NEW','PAID','SHIPPED','CLOSED') NOT NULL DEFAULT 'NEW',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_order_no` (`order_no`),
INDEX `idx_user_id` (`user_id`),
CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`)
REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;6.3 订单明细表（order_item）代码语言：javascript代码运行次数：0运行复制CREATE TABLE `order_item` (
`order_id` BIGINT UNSIGNED NOT NULL,
`item_id` INT UNSIGNED NOT NULL,
`product_name` VARCHAR(200) NOT NULL,
`quantity` INT UNSIGNED NOT NULL,
`unit_price` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`order_id`,`item_id`),
CONSTRAINT `fk_item_order` FOREIGN KEY (`order_id`)
REFERENCES `order`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;6.4 表结构迭代与版本演进新增支付时间
代码语言：javascript代码运行次数：0运行复制ALTER TABLE `order`
ADD COLUMN `paid_at` DATETIME NULL AFTER `status`;分表分库：当单表行数或写入 QPS 达到千万级，可基于 order_no % N 进行分表；
归档老数据：定期将“超过一年未变更”的订单归档至历史表，减少主表压力。
7. 常见操作与注意事项DDL 原子性：MySQL 8.0+ 已支持部分 DDL 原子，低版本需谨慎并在运维窗口执行；ALTER ONLINE：使用在线 DDL（如 ALGORITHM=INPLACE）减少业务中断；字段变更慎重：大表修改列类型或增删列会触发全表复制；备份与回滚：关键变更前做好备份，可利用 pt-osc、gh-ost 等工具无损变更；索引维护：尽量避免冗余索引，定期清理不使用索引；本项目适用于后台管理系统、电商用户中心、SaaS 用户模块等场景，特别适合开发者进行实战演练与面试准备。
一、项目背景与需求概述我们将构建一个基础版的用户管理系统，具备以下业务功能：
用户注册与登录用户角色与权限分配日志记录与用户状态追踪多条件用户查询与分页涉及的核心业务对象包括：用户、角色、权限、日志等。
二、数据库建模与表结构设计2.1 实体关系图（ER图）简要说明一位用户可以拥有多个角色（多对多）一个角色可以拥有多个权限（多对多）用户与登录日志是一对多关系2.2 用户表（users）代码语言：javascript代码运行次数：0运行复制CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
email VARCHAR(100),
status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:启用',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);2.3 角色表（roles）代码语言：javascript代码运行次数：0运行复制CREATE TABLE roles (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(255)
);2.4 权限表（permissions）代码语言：javascript代码运行次数：0运行复制CREATE TABLE permissions (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL UNIQUE,
code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于权限标识，如 user:view'
);2.5 用户-角色关联表（user_role）代码语言：javascript代码运行次数：0运行复制CREATE TABLE user_role (
user_id INT,
role_id INT,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (role_id) REFERENCES roles(id)
);2.6 角色-权限关联表（role_permission）代码语言：javascript代码运行次数：0运行复制CREATE TABLE role_permission (
role_id INT,
permission_id INT,
PRIMARY KEY (role_id, permission_id),
FOREIGN KEY (role_id) REFERENCES roles(id),
FOREIGN KEY (permission_id) REFERENCES permissions(id)
);2.7 登录日志表（login_logs）代码语言：javascript代码运行次数：0运行复制CREATE TABLE login_logs (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
ip_address VARCHAR(45),
login_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);三、数据初始化脚本3.1 插入初始角色与权限代码语言：javascript代码运行次数：0运行复制INSERT INTO roles(name, description) VALUES ('admin', '系统管理员'), ('user', '普通用户');
INSERT INTO permissions(name, code) VALUES
('查看用户', 'user:view'),
('新增用户', 'user:create'),
('删除用户', 'user:delete');
-- 分配权限给角色
INSERT INTO role_permission(role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- admin 拥有全部权限
(2, 1); -- user 仅能查看用户3.2 插入测试用户代码语言：javascript代码运行次数：0运行复制INSERT INTO users(username, password, email) VALUES
('alice', 'hashed_pwd1', 'alice@example.com'),
('bob', 'hashed_pwd2', 'bob@example.com');
-- 分配角色
INSERT INTO user_role(user_id, role_id) VALUES
(1, 1), -- alice 为管理员
(2, 2); -- bob 为普通用户四、典型查询场景实现4.1 查询所有启用用户及其角色代码语言：javascript代码运行次数：0运行复制SELECT u.id, u.username, r.name AS role
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;4.2 查询某用户拥有的所有权限代码语言：javascript代码运行次数：0运行复制SELECT p.name, p.code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'alice';4.3 查询最近7天登录日志代码语言：javascript代码运行次数：0运行复制SELECT u.username, l.ip_address, l.login_time
FROM login_logs l
JOIN users u ON l.user_id = u.id
WHERE l.login_time >= NOW() - INTERVAL 7 DAY
ORDER BY l.login_time DESC;4.4 用户分页查询（带关键字搜索）代码语言：javascript代码运行次数：0运行复制SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;五、事务控制与一致性保障在角色授权或用户注册等业务流程中，可以使用事务来确保数据完整性。
5.1 注册用户 + 分配默认角色（事务）代码语言：javascript代码运行次数：0运行复制START TRANSACTION;
INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com');
SET @uid = LAST_INSERT_ID();
INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默认赋普通角色
COMMIT;5.2 授权失败时回滚代码语言：javascript代码运行次数：0运行复制START TRANSACTION;
-- 假设某权限不存在导致失败
INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999);
-- 失败时回滚
ROLLBACK;六、索引优化与执行分析6.1 建议加索引字段users.username：用于登录验证、搜索login_logs.user_id：日志查询user_role.user_id / role_permission.role_id：JOIN 优化代码语言：javascript代码运行次数：0运行复制CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);6.2 执行计划分析代码语言：javascript代码运行次数：0运行复制EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;可查看索引是否使用、JOIN 类型、Rows 扫描数量等。