引言 回顾与展望 前9篇文章中，我们学习了：
✅ 数据库基础概念 ✅ MySQL安装配置 ✅ DDL（创建表） ✅ 数据类型选择 ✅ DML（增删改查） ✅ 约束与完整性 ✅ 字符集处理 ✅ 数据导入导出 现在，让我们综合运用这些知识，搭建一个完整的用户管理系统！
系统需求 实现一个用户管理系统，支持：
用户注册（唯一用户名、邮箱验证） 用户登录（密码加密存储） 个人信息管理（修改资料、上传头像） 角色权限管理（管理员、普通用户） 登录日志记录 数据统计（用户数、登录次数） 需求分析 功能模块 用户管理系统 ├── 用户模块 │ ├── 注册 │ ├── 登录 │ ├── 修改资料 │ └── 注销账户 ├── 角色权限模块 │ ├── 角色管理 │ ├── 权限分配 │ └── 权限验证 └── 日志模块 ├── 登录日志 └── 操作日志 数据库设计 需要以下数据表：
users：用户表（核心表） roles：角色表 permissions：权限表 role_permissions：角色权限关联表 user_roles：用户角色关联表 login_logs：登录日志表 数据库设计 创建数据库 -- 创建数据库 CREATE DATABASE IF NOT EXISTS user_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 使用数据库 USE user_management; 1. 用户表（users） CREATE TABLE users ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID', username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名（唯一）', password VARCHAR(255) NOT NULL COMMENT '密码（SHA256加密）', email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱（唯一）', phone VARCHAR(20) COMMENT '手机号', real_name VARCHAR(50) COMMENT '真实姓名', nickname VARCHAR(50) COMMENT '昵称', avatar VARCHAR(255) COMMENT '头像URL', gender ENUM('male', 'female', 'other') DEFAULT 'other' COMMENT '性别', birthday DATE COMMENT '生日', bio TEXT COMMENT '个人简介', status TINYINT DEFAULT 1 COMMENT '状态：1正常 0禁用 2待激活', login_count INT UNSIGNED DEFAULT 0 COMMENT '登录次数', last_login_at TIMESTAMP NULL COMMENT '最后登录时间', last_login_ip VARCHAR(45) COMMENT '最后登录IP', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', deleted_at TIMESTAMP NULL COMMENT '删除时间（软删除）', INDEX idx_username (username), INDEX idx_email (email), INDEX idx_phone (phone), INDEX idx_status (status), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'; 2. 角色表（roles） CREATE TABLE roles ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称', display_name VARCHAR(100) COMMENT '显示名称', description VARCHAR(255) COMMENT '角色描述', level INT DEFAULT 0 COMMENT '角色级别（数字越大权限越高）', status TINYINT DEFAULT 1 COMMENT '状态：1启用 0禁用', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_name (name), INDEX idx_level (level) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表'; 3. 权限表（permissions） CREATE TABLE permissions ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL UNIQUE COMMENT '权限名称（如user.create）', display_name VARCHAR(100) COMMENT '显示名称', description VARCHAR(255) COMMENT '权限描述', module VARCHAR(50) COMMENT '所属模块（user、order、product）', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_name (name), INDEX idx_module (module) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表'; 4. 角色权限关联表（role_permissions） CREATE TABLE role_permissions ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, role_id INT UNSIGNED NOT NULL COMMENT '角色ID', permission_id INT UNSIGNED NOT NULL COMMENT '权限ID', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_role_permission (role_id, permission_id), INDEX idx_role_id (role_id), INDEX idx_permission_id (permission_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表'; 5. 用户角色关联表（user_roles） CREATE TABLE user_roles ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL COMMENT '用户ID', role_id INT UNSIGNED NOT NULL COMMENT '角色ID', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_user_role (user_id, role_id), INDEX idx_user_id (user_id), INDEX idx_role_id (role_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表'; 6. 登录日志表（login_logs） CREATE TABLE login_logs ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL COMMENT '用户ID', login_ip VARCHAR(45) NOT NULL COMMENT '登录IP', user_agent TEXT COMMENT '浏览器UA', status TINYINT DEFAULT 1 COMMENT '状态：1成功 0失败', failure_reason VARCHAR(255) COMMENT '失败原因', login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间', INDEX idx_user_id (user_id), INDEX idx_login_at (login_at), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录日志表'; 初始化数据 插入默认角色 -- 插入角色 INSERT INTO roles (name, display_name, description, level) VALUES ('admin', '超级管理员', '拥有所有权限', 100), ('manager', '管理员', '拥有大部分管理权限', 50), ('user', '普通用户', '基础用户权限', 1); 插入权限 -- 插入权限 INSERT INTO permissions (name, display_name, description, module) VALUES -- 用户模块 ('user.create', '创建用户', '可以创建新用户', 'user'), ('user.read', '查看用户', '可以查看用户信息', 'user'), ('user.update', '修改用户', '可以修改用户信息', 'user'), ('user.delete', '删除用户', '可以删除用户', 'user'), -- 角色模块 ('role.create', '创建角色', '可以创建新角色', 'role'), ('role.read', '查看角色', '可以查看角色', 'role'), ('role.update', '修改角色', '可以修改角色', 'role'), ('role.delete', '删除角色', '可以删除角色', 'role'), -- 系统模块 ('system.setting', '系统设置', '可以修改系统设置', 'system'), ('system.log', '查看日志', '可以查看系统日志', 'system'); 分配权限给角色 -- 超级管理员拥有所有权限 INSERT INTO role_permissions (role_id, permission_id) SELECT 1, id FROM permissions; -- 管理员拥有用户模块权限 INSERT INTO role_permissions (role_id, permission_id) SELECT 2, id FROM permissions WHERE module = 'user'; -- 普通用户只有查看权限 INSERT INTO role_permissions (role_id, permission_id) SELECT 3, id FROM permissions WHERE name IN ('user.read'); 创建管理员账户 -- 创建超级管理员 INSERT INTO users (username, password, email, real_name, status) VALUES ( 'admin', SHA2('Admin@123456', 256), -- 密码加密 'admin@example.com', '系统管理员', 1 ); -- 分配超级管理员角色 INSERT INTO user_roles (user_id, role_id) VALUES (1, 1); -- user_id=1, role_id=1（超级管理员） 核心功能实现 功能1：用户注册 -- 注册新用户 INSERT INTO users (username, password, email, phone, real_name) VALUES ( 'zhangsan', SHA2('Password@123', 256), -- 密码加密 'zhangsan@example.com', '13800138000', '张三' ); -- 获取刚插入的用户ID SET @new_user_id = LAST_INSERT_ID(); -- 分配默认角色（普通用户） INSERT INTO user_roles (user_id, role_id) VALUES (@new_user_id, 3); -- role_id=3（普通用户） 功能2：用户登录 -- 验证用户名和密码 SELECT u.id, u.username, u.email, u.real_name, u.nickname, u.avatar, u.status, GROUP_CONCAT(r.name) AS roles FROM users u LEFT JOIN user_roles ur ON u.id = ur.user_id LEFT JOIN roles r ON ur.role_id = r.id WHERE u.username = 'zhangsan' AND u.password = SHA2('Password@123', 256) AND u.status = 1 GROUP BY u.id; -- 如果查到记录，登录成功 -- 更新登录信息 UPDATE users SET login_count = login_count + 1, last_login_at = CURRENT_TIMESTAMP, last_login_ip = '192.168.1.100' WHERE id = 2; -- 记录登录日志 INSERT INTO login_logs (user_id, login_ip, user_agent, status) VALUES (2, '192.168.1.100', 'Mozilla/5.0...', 1); 功能3：权限验证 -- 检查用户是否拥有指定权限 SELECT COUNT(*) AS has_permission FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE u.id = 2 AND p.name = 'user.create'; -- 如果结果>0，说明有权限 功能4：修改个人信息 -- 修改昵称和头像 UPDATE users SET nickname = '张小三', avatar = 'https://example.com/avatars/zhangsan.jpg', updated_at = CURRENT_TIMESTAMP WHERE id = 2; 功能5：修改密码 -- 验证旧密码，修改新密码 UPDATE users SET password = SHA2('NewPassword@456', 256), updated_at = CURRENT_TIMESTAMP WHERE id = 2 AND password = SHA2('Password@123', 256); -- 验证旧密码 -- 检查affected rows -- 如果=1，说明旧密码正确，修改成功 -- 如果=0，说明旧密码错误 功能6：软删除用户 -- 软删除（推荐） UPDATE users SET status = 0, deleted_at = CURRENT_TIMESTAMP WHERE id = 2; -- 查询时排除已删除用户 SELECT * FROM users WHERE deleted_at IS NULL -- 未删除 AND status = 1; -- 正常状态 功能7：数据统计 -- 统计总用户数 SELECT COUNT(*) AS total_users FROM users WHERE deleted_at IS NULL; -- 统计各角色用户数 SELECT r.display_name AS role_name, COUNT(ur.user_id) AS user_count FROM roles r LEFT JOIN user_roles ur ON r.id = ur.role_id LEFT JOIN users u ON ur.user_id = u.id AND u.deleted_at IS NULL GROUP BY r.id, r.display_name ORDER BY user_count DESC; -- 统计最近7天注册用户 SELECT DATE(created_at) AS date, COUNT(*) AS register_count FROM users WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY DATE(created_at) ORDER BY date; -- 统计最近7天登录日志 SELECT DATE(login_at) AS date, COUNT(*) AS login_count, COUNT(DISTINCT user_id) AS unique_users FROM login_logs WHERE login_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND status = 1 GROUP BY DATE(login_at) ORDER BY date; 数据完整性验证 测试1：唯一约束 -- 尝试注册重复用户名 INSERT INTO users (username, password, email) VALUES ('zhangsan', SHA2('test', 256), 'zhangsan2@example.com'); -- ERROR 1062: Duplicate entry 'zhangsan' for key 'username' -- 尝试注册重复邮箱 INSERT INTO users (username, password, email) VALUES ('zhangsan2', SHA2('test', 256), 'zhangsan@example.com'); -- ERROR 1062: Duplicate entry 'zhangsan@example.com' for key 'email' 测试2：非空约束 -- 尝试注册时不提供密码 INSERT INTO users (username, email) VALUES ('test', 'test@example.com'); -- ERROR 1364: Field 'password' doesn't have a default value 测试3：数据一致性 -- 查询用户的角色和权限 SELECT u.username, r.display_name AS role, GROUP_CONCAT(p.display_name) AS permissions FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id LEFT JOIN role_permissions rp ON r.id = rp.role_id LEFT JOIN permissions p ON rp.permission_id = p.id WHERE u.id = 2 GROUP BY u.id, u.username, r.display_name; 性能优化 索引优化 -- 查看表的索引 SHOW INDEX FROM users; -- 为常用查询字段添加索引 CREATE INDEX idx_last_login_at ON users(last_login_at); CREATE INDEX idx_login_count ON users(login_count); -- 复合索引（用户名+状态） CREATE INDEX idx_username_status ON users(username, status); 查询优化 -- ❌ 不推荐：SELECT * SELECT * FROM users WHERE username = 'zhangsan'; -- ✅ 推荐：只查询需要的列 SELECT id, username, email, real_name FROM users WHERE username = 'zhangsan'; -- ✅ 推荐：使用LIMIT限制结果 SELECT * FROM users ORDER BY created_at DESC LIMIT 10; 安全加固 1. 密码安全 -- ✅ 使用SHA256加密（基础） SHA2('password', 256) -- ✅ 更安全：加盐（Salt） CONCAT(SHA2(CONCAT('password', 'random_salt'), 256)) -- ✅ 最佳实践：使用bcrypt（应用层实现） 2. SQL注入防护 -- ❌ 危险：字符串拼接 SELECT * FROM users WHERE username = '{$username}'; -- 如果username = "admin' OR '1'='1"，会查出所有用户 -- ✅ 安全：使用预处理语句（Prepared Statement） -- 在应用层使用PDO或MySQLi的预处理 3. 权限最小化 -- 创建只读用户（用于查询） CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT ON user_management.* TO 'readonly'@'localhost'; -- 创建普通用户（不能删除） CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT, INSERT, UPDATE ON user_management.* TO 'webapp'@'localhost'; 完整测试流程 1. 创建测试用户 -- 注册3个测试用户 INSERT INTO users (username, password, email, real_name) VALUES ('alice', SHA2('Alice@123', 256), 'alice@example.com', 'Alice'), ('bob', SHA2('Bob@123', 256), 'bob@example.com', 'Bob'), ('charlie', SHA2('Charlie@123', 256), 'charlie@example.com', 'Charlie'); -- 分配角色 INSERT INTO user_roles (user_id, role_id) VALUES (LAST_INSERT_ID()-2, 2), -- alice: manager (LAST_INSERT_ID()-1, 3), -- bob: user (LAST_INSERT_ID(), 3); -- charlie: user 2. 测试登录 -- Alice登录 INSERT INTO login_logs (user_id, login_ip, status) VALUES (2, '192.168.1.101', 1); -- Bob登录 INSERT INTO login_logs (user_id, login_ip, status) VALUES (3, '192.168.1.102', 1); -- 登录失败（密码错误） INSERT INTO login_logs (user_id, login_ip, status, failure_reason) VALUES (4, '192.168.1.103', 0, '密码错误'); 3. 测试权限 -- Alice（管理员）创建用户 SELECT '权限验证：Alice创建用户' AS action; SELECT COUNT(*) AS has_permission FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE u.username = 'alice' AND p.name = 'user.create'; -- 结果：1（有权限） -- Bob（普通用户）创建用户 SELECT '权限验证：Bob创建用户' AS action; SELECT COUNT(*) AS has_permission FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE u.username = 'bob' AND p.name = 'user.create'; -- 结果：0（无权限） 总结与展望 本案例涵盖的知识点 ✅ 数据库设计：
...