本项目适用于后台管理系统、电商用户中心、SaaS 用户模块等场景，特别适合开发者进行实战演练与面试准备。
一、项目背景与需求概述
我们将构建一个基础版的用户管理系统，具备以下业务功能：
- 用户注册与登录
- 用户角色与权限分配
- 日志记录与用户状态追踪
- 多条件用户查询与分页
涉及的核心业务对象包括：用户、角色、权限、日志等。
二、数据库建模与表结构设计
2.1 实体关系图（ER图）简要说明
- 一位用户可以拥有多个角色（多对多）
- 一个角色可以拥有多个权限（多对多）
- 用户与登录日志是一对多关系
2.2 用户表（users）
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）
CREATE TABLE roles (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(255)
);
2.4 权限表（permissions）
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）
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）
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）
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 插入初始角色与权限
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 插入测试用户
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 查询所有启用用户及其角色
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 查询某用户拥有的所有权限
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天登录日志
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 用户分页查询（带关键字搜索）
SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;
五、事务控制与一致性保障
在角色授权或用户注册等业务流程中，可以使用事务来确保数据完整性。
5.1 注册用户 + 分配默认角色（事务）
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 授权失败时回滚
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 优化
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);
6.2 执行计划分析
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 扫描数量等。
更多推荐【MySQL完整系列】：MySQL数据库从0到拿捏系列
MySQL数据库零基础入门教程：从安装配置到数据查询全掌握
关键词：安装、登录、客户端、库表基础、简单查询MySQL数据表操作全指南：建表、修改、删除一步到位
关键词：DDL语句、字段类型、主键/外键、约束、规范设计
聚焦表结构的创建和维护，配合真实业务建表案例（如用户表、订单表）。MySQL增删改查基础教程：熟练掌握DML语句操作
关键词：INSERT、UPDATE、DELETE、SELECT、WHERE、ORDER BY
实战演练日常的数据库操作命令，重点讲解查询语句的条件与排序。MySQL高级查询技巧：分组、聚合、子查询与分页
关键词：GROUP BY、HAVING、聚合函数、LIMIT、子查询
向中级进阶，涵盖常见报表需求与分页列表的查询实现。MySQL多表查询详解：内连接、外连接、自连接通通搞懂
关键词：JOIN、INNER JOIN、LEFT JOIN、UNION、自连接
深度讲解表与表之间如何通过字段建立关联并进行数据整合。MySQL索引与性能优化入门：让查询提速的秘密武器
关键词：索引原理、EXPLAIN、慢查询、查询优化
开启性能优化之路，适合准备应对数据量增长或面试的人。MySQL事务与锁机制详解：确保数据一致性的关键
关键词：事务四大特性、锁类型、死锁案例、隔离级别
涉及电商、支付系统等对数据一致性要求高的业务场景。MySQL项目实战演练：搭建用户管理系统的完整数据库结构
关键词：业务建模、表关系设计、数据初始化、查询场景
以实战带动知识回顾，模拟真实业务项目，整合前面所学内容。