企业官网建设流程全解析

简介：《数据库系统课程设计-人事管理》是一份面向高校数据库课程设计场景的完整报告文档，适合正在完成人事管理主题选题、需要参考规范化设计流程的学生与指导教师。内容围绕多部门企业的人事业务展开，从需求分析、概念设计、逻辑设计、物理设计到数据库实施与功能实现逐层推进，覆盖员工基本信息维护、部门调动、模糊查询、按年月统计出勤、按日期查询部门迟到早退人数、按年统计调入调出等典型功能，并涉及ER图转关系模型、表结构约束、索引与存储过程等知识点。资源包内为1个doc文件，约1.23MB，便于直接查阅和整理课程设计报告。目前已有1110人学习，可作为梳理设计思路、对照章节结构、补充统计模块实现的参考材料。

1. 人事管理课程设计不是交一份建表脚本：先把“人、岗、薪”三条线跑成闭环

学期末交上来的《数据库系统课程设计-人事管理》文档，十份里有八份长一个样：一张 ER 图、七八张表、几段增删改查 SQL，最后附一张登录界面截图。答辩时老师问一句“员工调岗之后，上个月的工资单还算不算原部门的成本”，很多人就答不上来。人事管理的难点从来不是表不够多，而是人、岗、薪三条线要在时间轴上对齐——谁在什么时候属于哪个部门、拿哪一档薪、这段任职记录什么时候失效。这篇就按我自己做数据库系统课程设计的顺序，把一个人事管理库从建表、约束、索引进到事务、存储过程和踩坑排查，一步步写清楚，适合正在赶课程设计、又不想只交一份能跑 demo 的人。

2. 从组织架构到薪酬台账：人事库的实体拆解与建表取舍

动手写 DDL 之前，我一般先在纸上画三条线：组织线（部门树、岗位、编制）、人员线（员工档案、入职离职）、薪酬线（薪级标准、月度工资单）。三条线的交叉点只有一个，就是“任职记录”这张表——它记录了某个人在某个时间段内属于哪个部门、担任哪个岗位、套哪一档薪。把交叉点定下来，后面的表就好摆了。

很多人一上手就画十几张表，结果到写查询的时候发现每张表都缺字段，改一处牵连一片。课程设计的时间就两三周，表多不等于分高，能自圆其说、能跑通业务闭环才是关键。我一般会控制在 6 到 8 张核心表，剩下的用视图和存储过程补齐。

2.1 员工表里塞岗位信息，第一次改组织架构就会崩

新手最常见的设计是把dept_name、job_name、base_salary直接写进员工表。单看功能没问题，插入一个员工，姓名、部门、工资一应俱全，页面也好写。但只要业务动一下，这套结构立刻翻车：部门改名，你得 UPDATE 全表；员工调岗，历史记录没了，上个月的工资单没法追溯；一个人兼两个岗位，你只能再加一列job_name2。

根本原因在于，员工表承载了两种生命周期完全不同的数据。“人”的属性——姓名、身份证、出生日期、手机号——一辈子基本不变；“岗”的属性——部门、岗位、薪级——半年就可能变一次。把变化频率差一个数量级的东西塞进同一行，任何一次变更都是对历史的覆盖。

常见做法是拆成三层：employee只放人的静态属性和在职状态；assignment（任职记录）放“某人在某段时间内的部门、岗位、薪级”，用start_date/end_date表达有效期；department、job、salary_grade作为维度表被引用。这样一次调岗就是“关掉旧记录 + 插入新记录”，历史天然保留。

我通常会跟做课程设计的 A 同学强调一句：只要你的表里出现了xxx_history这种“备份表”，就说明主表的设计漏了时间维度。真正的做法是让主表本身就能表达时间，而不是事后再抄一份。

2.2 六张核心表的最小可用建表语句

下面这套 DDL 是我在 MySQL 8 上反复用过的骨架，去掉业务细节后可以直接放进课程设计的建库脚本。注意建表顺序：department的负责人字段引用employee，employee又引用部门，这是个循环，得先建表、最后再补外键。

-- 1. 部门表：自引用树，负责人外键先留空 CREATE TABLE department ( dept_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dept_name VARCHAR(64) NOT NULL, parent_id INT UNSIGNED NULL, manager_emp INT UNSIGNED NULL, dept_status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_dept_name (dept_name), CONSTRAINT fk_dept_parent FOREIGN KEY (parent_id) REFERENCES department (dept_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 2. 岗位表：job_level 决定能套哪几档薪级 CREATE TABLE job ( job_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_name VARCHAR(64) NOT NULL, job_level TINYINT NOT NULL, UNIQUE KEY uk_job_name (job_name), CONSTRAINT ck_job_level CHECK (job_level BETWEEN 1 AND 10) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 3. 员工表：只放"人"的属性和在职状态 CREATE TABLE employee ( emp_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, emp_no CHAR(8) NOT NULL, real_name VARCHAR(32) NOT NULL, gender CHAR(1) NOT NULL, birth_date DATE NOT NULL, id_card CHAR(18) NOT NULL, phone VARCHAR(20) NOT NULL, email VARCHAR(64) NULL, hire_date DATE NOT NULL, leave_date DATE NULL, emp_status TINYINT NOT NULL DEFAULT 1, -- 1 在职 2 试用 3 离职 cur_dept INT UNSIGNED NULL, -- 冗余的当前部门，见 2.3 cur_job INT UNSIGNED NULL, UNIQUE KEY uk_emp_no (emp_no), UNIQUE KEY uk_id_card (id_card), CONSTRAINT ck_gender CHECK (gender IN ('M','F')), CONSTRAINT ck_leave CHECK (leave_date IS NULL OR leave_date >= hire_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 4. 任职记录：current_flag 为 1 表示当前，NULL 表示历史 CREATE TABLE assignment ( assign_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, emp_id INT UNSIGNED NOT NULL, dept_id INT UNSIGNED NOT NULL, job_id INT UNSIGNED NOT NULL, grade_id INT UNSIGNED NOT NULL, start_date DATE NOT NULL, end_date DATE NULL, current_flag TINYINT NULL, UNIQUE KEY uk_current (emp_id, current_flag), KEY idx_dept_current (dept_id, current_flag), CONSTRAINT fk_asg_emp FOREIGN KEY (emp_id) REFERENCES employee (emp_id), CONSTRAINT fk_asg_dept FOREIGN KEY (dept_id) REFERENCES department (dept_id), CONSTRAINT fk_asg_job FOREIGN KEY (job_id) REFERENCES job (job_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 5. 薪级表 CREATE TABLE salary_grade ( grade_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, grade_name VARCHAR(32) NOT NULL, job_level TINYINT NOT NULL, base_salary DECIMAL(12,2) NOT NULL, UNIQUE KEY uk_grade (grade_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 6. 工资台账：(emp_id, period) 唯一键是幂等的基础 CREATE TABLE payroll ( payroll_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, emp_id INT UNSIGNED NOT NULL, period CHAR(7) NOT NULL, -- 形如 '2025-06' base_pay DECIMAL(12,2) NOT NULL DEFAULT 0.00, bonus DECIMAL(12,2) NOT NULL DEFAULT 0.00, deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00, insurance DECIMAL(12,2) NOT NULL DEFAULT 0.00, tax DECIMAL(12,2) NOT NULL DEFAULT 0.00, net_pay DECIMAL(12,2) NOT NULL DEFAULT 0.00, pay_status TINYINT NOT NULL DEFAULT 0, -- 0 草稿 1 已审核 2 已发放 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_emp_period (emp_id, period), KEY idx_period_status (period, pay_status), CONSTRAINT fk_pay_emp FOREIGN KEY (emp_id) REFERENCES employee (emp_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 7. 循环外键最后补：部门负责人 ALTER TABLE department ADD CONSTRAINT fk_dept_manager FOREIGN KEY (manager_emp) REFERENCES employee (emp_id);

这段脚本里有三个地方值得单独说清楚，答辩时也最容易被追问。

assignment上的UNIQUE KEY uk_current (emp_id, current_flag)是整个设计的核心。它的作用是保证“一个人最多只有一条当前任职记录”。MySQL 的唯一索引对 NULL 是宽松的——多行(emp_id, NULL)不冲突，所以历史记录可以随便堆；而(emp_id, 1)只能出现一次。这比在应用层用SELECT COUNT(*)判断再插入可靠一万倍，也不会因为并发插入两条当前记录导致数据错乱。

payroll的uk_emp_period同理，它让“同一个人同一月份只能有一张工资单”成为数据库层面的硬约束，后面 4.2 的幂等写入完全依赖这个键。

emp_no CHAR(8)和id_card CHAR(18)用定长字符而不是VARCHAR，是因为这两个字段全部等值查询、长度固定，定长能让索引页更紧凑。至于身份证这种敏感字段，课程设计里通常只存明文就够了，真做系统要考虑脱敏或者只存哈希加后四位。

2.3 哪些字段该反范式：把“当前部门”冗余进员工表

第三范式讲的是消除传递依赖，但真实系统里完全不冗余的表结构，查询会难看到没法写。最典型的就是“查某部门的在职员工名单”：如果只有assignment，每次都要JOIN加current_flag = 1过滤，再和employee关联，还要处理历史记录。

所以我在employee里保留了cur_dept和cur_job两个冗余字段，含义是“当前生效的部门和岗位”。它们不是主数据源，主数据源永远是assignment，这两个字段只是给高频查询用的快照，靠 4.1 的调岗存储过程在同一事务里同步更新。

这里的关键是：冗余字段必须有唯一的、受控的写入入口。如果允许应用层随便 UPDATEemployee.cur_dept，那它迟早会和assignment对不上。做法是把employee表的写权限收窄——调岗只能走存储过程，其余字段用普通 UPDATE。

判断一个字段该不该冗余，我一般看两条：一是查询频率是不是高到值得多维护一份，二是这份冗余能不能用一次事务保证一致。两条都满足才冗余，只满足一条的话，宁可多写一个 JOIN。课程设计报告里把这段取舍写清楚，比多画两张 ER 图更加分。

3. 约束、索引与视图：让数据库自己挡住脏数据

我见过太多课程设计的“业务校验”全部放在 Java 或者 Python 里：入职日期不能早于出生日期、性别只能是男女、工资不能为负。这些判断写在应用层没错，但数据库一旦被别的脚本、别的同学、或者答辩时老师直连的客户端碰一下，脏数据立刻就进去了。数据库系统课程设计要体现的，恰恰是“数据库自己会管自己”。

这一章的三件事——约束、索引、视图——分别对应三个目标：挡住不合法的数据、让合法数据的查询跑得快、把高频统计固化下来少写重复 SQL。三件事都不难，难的是知道该在哪一层做。

3.1 用唯一约束和检查约束替应用层挡掉一半脏数据

约束分四类，按强度从高到低是：主键、唯一键、外键、检查约束。主键和唯一键大家都会写，真正容易被忽略的是后两类。

外键的作用不只是“关联”。默认的ON DELETE RESTRICT意味着一件事：只要某个部门下还挂着任职记录，你就删不掉这个部门。这条规则会逼着你把“删除部门”改成“停用部门”——把dept_status置 0，而不是物理删除。这恰恰是人事系统该有的行为，组织架构的历史不能因为一次误操作消失。

如果想更明确一点，可以显式写出来：

ALTER TABLE assignment DROP FOREIGN KEY fk_asg_dept, ADD CONSTRAINT fk_asg_dept FOREIGN KEY (dept_id) REFERENCES department (dept_id) ON DELETE RESTRICT ON UPDATE CASCADE;

ON DELETE RESTRICT挡住误删，ON UPDATE CASCADE允许部门主键调整时自动跟随。注意不要用ON DELETE CASCADE，那意味着删一个部门会把整个部门的任职记录连带删掉，等于把历史抹了。

检查约束在 MySQL 8.0.16 之后才真正生效，之前的版本只是解析不报错。常用的几条：

ALTER TABLE salary_grade ADD CONSTRAINT ck_base_salary CHECK (base_salary >= 0); ALTER TABLE payroll ADD CONSTRAINT ck_net_pay CHECK (net_pay >= 0), ADD CONSTRAINT ck_period_fmt CHECK (period REGEXP '^[0-9]{4}-(0[1-9]|1[0-2])$');

ck_period_fmt这一条特别值。工资单的period是CHAR(7)，如果不加约束，'2025-6'、'2025-06 '、'202506'三种写法都能插进去，等到按月汇总时就会莫名其妙少数据。加上正则约束之后，格式错误在插入那一刻就被挡住。

net_pay >= 0也值得加。扣款算错导致实发工资为负，这在真实系统里是会出事的，让数据库直接拒绝比事后对账便宜得多。

提示：如果用的版本对 CHECK 支持不完整，退化方案是用 BEFORE INSERT / BEFORE UPDATE 触发器做同样的判断，用SIGNAL SQLSTATE '45000'抛错。功能等价，只是报错信息没那么友好。

3.2 索引按查询路径倒推：三个必建索引和一个别建的

索引不是越多越好，每多一个索引，插入和更新就多一份维护成本。我的习惯是先把手上的查询语句列出来，按调用频率排序，再倒推该建什么索引。

人事系统里跑得最勤的四类查询，基本长这样：

前三条都在第 2 章的建表语句里了，这里说第 4 条为什么单独拎出来：LIKE '%关键字%'这种前置通配的模糊查询，B+ 树索引完全用不上，建了也白建。数据量在几万行的课程设计里，全表扫描几十毫秒就回来了，强行加索引反而拖慢写入。

如果真想优化姓名搜索，正确方向不是加索引，而是换检索方式——比如建一张姓名分词表，或者干脆承认课程设计的量级不需要。答辩时被问到这一点，能说出“前置通配符导致索引失效”就已经够了。

还有一个容易踩的坑是复合索引的列顺序。idx_dept_current (dept_id, current_flag)能服务WHERE dept_id = 1，也能服务WHERE dept_id = 1 AND current_flag = 1，但服务不了WHERE current_flag = 1。这就是最左前缀原则。写索引时把区分度高的、等值查询用得多的列放左边，范围查询的列放右边。

反过来，gender、emp_status这种只有两三个取值的低基数列，单独建索引基本没意义——优化器一算，走索引回表还不如直接全表扫。真需要按状态过滤，就做成复合索引的一部分，和部门、时间这类高区分度列搭配。

3.3 视图把部门在职人数这类高频统计固化下来

课程设计里总有几个统计指标反复出现：每个部门的在职人数、每个岗位的平均薪资、本月待发工资总额。这些查询写一遍还行，写五遍就会出错，尤其是在JOIN和GROUP BY上。

拿“部门在职人数”举例，最直觉的写法是：

SELECT d.dept_id, d.dept_name, COUNT(*) AS headcount FROM department d JOIN assignment a ON a.dept_id = d.dept_id AND a.current_flag = 1 GROUP BY d.dept_id, d.dept_name;

这条语句在部门没有人的时候会把这个部门整行吞掉，因为内连接找不到匹配。这在仪表盘上就是“新成立的部门不显示”，属于隐蔽得很深的 bug，第 5 章会专门讲。正确写法是先固化成视图：

CREATE OR REPLACE VIEW v_dept_headcount AS SELECT d.dept_id, d.dept_name, d.parent_id, COUNT(a.emp_id) AS headcount FROM department d LEFT JOIN assignment a ON a.dept_id = d.dept_id AND a.current_flag = 1 WHERE d.dept_status = 1 GROUP BY d.dept_id, d.dept_name, d.parent_id;

LEFT JOIN保证零人的部门也在结果里，COUNT(a.emp_id)而不是COUNT(*)保证零人时算出 0 而不是 1。这两个细节合起来才是可用的统计口径。

再建一个员工全景视图，把人员、当前任职、岗位、薪级一次拼好：

CREATE OR REPLACE VIEW v_emp_full AS SELECT e.emp_id, e.emp_no, e.real_name, e.emp_status, d.dept_name, j.job_name, g.grade_name, g.base_salary, a.start_date FROM employee e LEFT JOIN assignment a ON a.emp_id = e.emp_id AND a.current_flag = 1 LEFT JOIN department d ON d.dept_id = a.dept_id LEFT JOIN job j ON j.job_id = a.job_id LEFT JOIN salary_grade g ON g.grade_id = a.grade_id;

视图在这里的作用不只是省事，更重要的是统一口径。列表页、导出、报表三个地方都从这个视图取数，就不会出现“列表显示 128 人、报表显示 130 人”这种对不上的情况。

但要清楚视图的边界：它只是保存了一条查询语句，不带存储，也不自动物化。每次查视图都会展开成底层 SQL 重新执行，复杂的多表视图叠加起来可能比手写还慢。课程设计里用它做展示层没问题，别拿它当缓存用。

4. 调岗、离职、发薪：用事务和存储过程把跨表动作一次做完

前三章解决的是“数据长什么样”，这一章解决“数据怎么变”。人事系统里真正会出事的地方，都是那些要同时改好几张表的操作：调岗、离职、发薪。这类操作如果没有事务保护，中间任何一步失败，数据库就会停在一个自相矛盾的状态上——员工已经不在原部门了，但工资单还挂在原部门头上。

我的原则很简单：一个业务动作如果会改两张以上的表，就必须包在一个事务里，而且这个事务最好做成存储过程，把入口收敛到一个地方。

4.1 一次调岗到底要动几张表：事务边界画在哪

一次调岗看起来只是“换个部门”，实际要动四张表：

1. assignment：把旧记录的end_date补上、current_flag置 NULL；
2. assignment：插入一条新记录，current_flag = 1；
3. employee：同步冗余的cur_dept、cur_job；
4. op_log：写一条操作流水。

这四步必须全成功或者全失败。如果只做了前两步，employee.cur_dept还是旧值，列表页就会显示错误的部门；如果只更新了employee，任职历史就断了。

还有一个更隐蔽的问题：两个管理员同时给同一个人调岗。没有并发控制的话，两个人各自读到“当前记录”，各自把它关掉，再各自插一条新的，结果就是两条当前记录。虽然uk_current唯一键会挡住第二条插入，但报错信息会很莫名其妙。

解决办法是在读取当前记录时加行锁：

DELIMITER $$ CREATE PROCEDURE sp_transfer( IN p_emp INT UNSIGNED, IN p_dept INT UNSIGNED, IN p_job INT UNSIGNED, IN p_grade INT UNSIGNED, IN p_date DATE, OUT p_code INT ) BEGIN DECLARE v_old_dept INT UNSIGNED DEFAULT NULL; DECLARE v_cnt INT DEFAULT 0; -- 任何 SQL 异常都回滚，并返回 -1 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_code = -1; END; START TRANSACTION; -- 锁住当前任职记录，防止并发调岗 SELECT dept_id INTO v_old_dept FROM assignment WHERE emp_id = p_emp AND current_flag = 1 FOR UPDATE; IF v_old_dept IS NULL THEN ROLLBACK; SET p_code = -2; -- 没有当前任职，数据异常 ELSE -- 1. 关闭旧记录 UPDATE assignment SET end_date = p_date, current_flag = NULL WHERE emp_id = p_emp AND current_flag = 1; -- 2. 插入新记录 INSERT INTO assignment(emp_id, dept_id, job_id, grade_id, start_date, end_date, current_flag) VALUES (p_emp, p_dept, p_job, p_grade, p_date, NULL, 1); -- 3. 同步冗余字段 UPDATE employee SET cur_dept = p_dept, cur_job = p_job WHERE emp_id = p_emp; -- 4. 审计流水 INSERT INTO op_log(target_tbl, target_id, action, old_value, new_value) VALUES ('assignment', p_emp, 'TRANSFER', CONCAT('dept=', IFNULL(v_old_dept, 0)), CONCAT('dept=', p_dept, ',job=', p_job, ',grade=', p_grade)); COMMIT; SET p_code = 0; END IF; END$$ DELIMITER ;

FOR UPDATE是这段的关键。它在事务里给查到的那一行加排他锁，第二个并发调用会在这里阻塞，直到第一个事务提交，再读到已经变成 NULL 的current_flag，走p_code = -2分支返回。虽然用户体验上是“操作失败”，但比写进两条当前记录干净得多。

DECLARE EXIT HANDLER FOR SQLEXCEPTION也必须写。没有它，存储过程中途报错时事务会挂在那里，连接池里的这条连接一直持着锁，后面的请求全部堵死。这类问题在本地单机测试时基本发现不了，一到多人同时用就暴露。

p_code的返回值我一般约定成：0 成功、-1 数据库异常、-2 业务前置条件不满足。这样上层调用只看一个整型就能决定是提示“系统繁忙”还是“该员工没有有效任职记录”，比解析错误字符串可靠。

4.2 月度发薪的幂等写入：重复跑三遍结果一样

发薪是人事系统里最典型的批量操作：一次给几百人算工资，算错了要能重跑，跑一半断了要能续。这三件事归结成一个词——幂等。同一个月份，无论调用几次发薪过程，结果都必须一样。

幂等的实现不靠代码判断，靠数据库的唯一键。第 2 章payroll上的uk_emp_period (emp_id, period)就是全部基础，配上ON DUPLICATE KEY UPDATE就成了一半。

DELIMITER $$ CREATE PROCEDURE sp_gen_payroll(IN p_period CHAR(7)) BEGIN DECLARE v_rows INT DEFAULT 0; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT -1 AS code, 0 AS affected; END; START TRANSACTION; INSERT INTO payroll(emp_id, period, base_pay, insurance, tax, net_pay, pay_status) SELECT e.emp_id, p_period, g.base_salary, ROUND(g.base_salary * 0.105, 2), ROUND(GREATEST(g.base_salary * 0.895 - 5000, 0) * 0.03, 2), 0, 0 FROM employee e JOIN assignment a ON a.emp_id = e.emp_id AND a.current_flag = 1 JOIN salary_grade g ON g.grade_id = a.grade_id WHERE e.emp_status IN (1, 2) ON DUPLICATE KEY UPDATE base_pay = IF(payroll.pay_status = 0, VALUES(base_pay), payroll.base_pay), insurance = IF(payroll.pay_status = 0, VALUES(insurance), payroll.insurance), tax = IF(payroll.pay_status = 0, VALUES(tax), payroll.tax); SET v_rows = ROW_COUNT(); -- 实发工资统一在这一步算，避免公式散落多处 UPDATE payroll SET net_pay = base_pay + bonus - deduction - insurance - tax WHERE period = p_period AND pay_status = 0; COMMIT; SELECT 0 AS code, v_rows AS affected; END$$ DELIMITER ;

逐段说一下逻辑。

INSERT ... SELECT从employee出发，只取在职和试用（emp_status IN (1,2)）的人，通过当前任职关联到薪级，把基本工资、社保、个税先算出来。社保按基本工资的 10.5% 估，个税用了极简的三档公式，课程设计里够用，真做系统要按累计预扣法单独写。

ON DUPLICATE KEY UPDATE是幂等的核心。第二次跑同一个月份时，插入会撞上唯一键，转成更新。但更新有条件：IF(payroll.pay_status = 0, ...)——只有草稿状态才允许覆盖。一旦某张工资单被审核（pay_status = 1）或者已发放（2），重跑不会改动它。这解决了“重跑把已发放数据冲掉”这个血泪问题。

ROW_COUNT()返回受影响行数，插入算 1、更新算 2，把它返回给上层，能大致判断这次是新增了多少人、更新了多少人。课程设计里拿来在页面显示“本次处理 128 条”已经足够。

实发工资单独用一条 UPDATE 算，是因为net_pay依赖前面几个字段的结果，在INSERT里算要重复写一遍公式，还容易和后面的调整脱节。分成两步，公式只有一处，改起来方便。

注意：MySQL 8.0.20 之后VALUES()函数在ON DUPLICATE KEY UPDATE里已经被标记为弃用，新写法是给INSERT加行别名AS new，然后引用new.base_pay。课程设计里两种写法都能跑，报告里最好提一句你知道这个变化。

4.3 触发器只做审计流水，别让它背业务逻辑

触发器在课程设计里几乎是必考项，但也是滥用最严重的地方。我见过把整个发薪逻辑写进AFTER INSERT ON employee的，结果插一条测试数据就生成一张工资单，删都删不干净。

我的用法很克制：触发器只干一件事——记录变更流水，不参与任何业务判断，不回写其他业务表。

DELIMITER $$ CREATE TRIGGER trg_emp_after_update AFTER UPDATE ON employee FOR EACH ROW BEGIN IF NOT (NEW.emp_status <=> OLD.emp_status) OR NOT (NEW.phone <=> OLD.phone) OR NOT (NEW.cur_dept <=> OLD.cur_dept) THEN INSERT INTO op_log(target_tbl, target_id, action, old_value, new_value, op_time) VALUES ('employee', NEW.emp_id, 'UPDATE', CONCAT('status=', IFNULL(OLD.emp_status,'-'), ',phone=', IFNULL(OLD.phone,'-'), ',dept=', IFNULL(OLD.cur_dept,'-')), CONCAT('status=', IFNULL(NEW.emp_status,'-'), ',phone=', IFNULL(NEW.phone,'-'), ',dept=', IFNULL(NEW.cur_dept,'-')), NOW()); END IF; END$$ DELIMITER ;

<=>是 NULL 安全的等值比较，NULL <=> NULL返回 1 而不是 NULL。这里必须用它，否则字段从 NULL 改成有值时，普通!=会返回 NULL，IF判断失效，变更就漏记了。

触发器里只写op_log，不改任何业务表，是为了避免链式触发。MySQL 不允许同一个表上的触发器递归调用自己，但 A 表触发器改 B 表、B 表触发器又改 A 表这种情况，排查起来是纯玄学。课程设计里保持“触发器只写日志表”这一条纪律，能省掉后面一大半调试时间。

还有一点：触发器会拖慢批量操作。一次导入 5000 条员工数据，触发器要执行 5000 次插入日志。所以我在批量导入前会临时SET session sql_log_bin = 0——不对，那是控制 binlog 的，正确做法是用一个会话变量做开关，在触发器开头判断：

IF @enable_audit IS NULL OR @enable_audit = 1 THEN -- 写日志 END IF;

导入脚本开头SET @enable_audit = 0;，结束再置回 1。这样批量导入不受影响，日常操作照常留痕。

5. 常见问题排查：字符集、隐式提交和外键循环依赖这五个坑

课程设计的时间紧，出问题的时候往往没耐心逐层排查，容易病急乱投医——改字符集、删外键、关掉事务，一个个试过去。这一章列的五个坑，是我这些年被问得最多、也是最初级但最耗时的，每条按“现象、原因、解决”写清楚，照着对号入座就行。

5.1 中文姓名变成问号：字符集只在建库时改是没用的

现象：建表时写了DEFAULT CHARSET=utf8，插入'张三'能成功，但查出来是??；或者插入带生僻字的名字（比如某些四字姓名）直接报Incorrect string value。

原因：MySQL 的utf8是utf8mb3的别名，最多 3 字节，只覆盖基本多文种平面。汉字里有一部分扩展字符是 4 字节的，插不进去。另外，如果客户端连接的字符集和表的不一致，服务端会先按连接的字符集解释再转换，中间就会丢字。

解决：建库建表统一用utf8mb4，连接串也指定。

CREATE DATABASE hr_demo DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci; -- 已建好的表要单独改，ALTER DATABASE 不会回溯 ALTER TABLE employee CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

应用侧连接串加上characterEncoding=utf8&useUnicode=true，用命令行客户端的话，连接后先SET NAMES utf8mb4;再操作。三处（库、表、连接）必须一致，只改一处等于没改。

5.2 存储过程报错了数据却没回滚：隐式提交在背后动手

现象：故意在存储过程中间写一条会报错的语句，执行报错，但前面几条 UPDATE 已经生效了，数据停在半截。

原因有两个。一是客户端开着自动提交，START TRANSACTION之前的语句各自成事务；二是更常见的——在事务中间执行了 DDL（CREATE TABLE、ALTER TABLE、TRUNCATE），MySQL 会隐式提交当前事务，之前的操作全部落盘，后面的ROLLBACK什么也回滚不了。

解决：先把建表和 DML 彻底分开，建库脚本只在初始化时跑一次，业务过程里绝不出现 DDL。然后确认自动提交状态：

SELECT @@autocommit; -- 1 表示自动提交 SET autocommit = 0; -- 会话级关闭，后续需显式 COMMIT

存储过程内部一律用START TRANSACTION ... COMMIT/ROLLBACK显式控制，并且配DECLARE EXIT HANDLER FOR SQLEXCEPTION做兜底回滚。测试方法很简单：在过程末尾临时加一句SELECT 1/0;制造除零错误，跑一遍看数据有没有残留，没有残留才算事务写对了。

5.3 建表脚本第 3 行就报 1215：外键的循环依赖

现象：整段脚本从头执行，department建成功，employee建成功，加fk_dept_manager的时候报ERROR 1215: Cannot add foreign key constraint，或者干脆在第 3 行就报 1824（引用的表不存在）。

原因：department.manager_emp引用employee.emp_id，而employee.cur_dept的概念上又依赖department。两个表互相引用，无论谁先建，总有一方找不到对方。这是自引用加互相引用叠加出来的典型问题。

解决：拆成两步——先建所有表（外键字段允许 NULL），再用ALTER TABLE补约束。顺序按依赖拓扑排：department自引用 →job、salary_grade→employee→assignment、payroll→ 最后补fk_dept_manager。

另有一个隐蔽原因是类型不匹配。department.dept_id是INT UNSIGNED，如果employee.cur_dept写成INT（有符号），外键会直接建不上，报错信息还不会告诉你是类型问题。建表时养成习惯：所有参与关联的主键、外键字段，类型和符号属性必须完全一致。

5.4 工资合计差三分钱：DECIMAL 不是可选项

现象：单条工资单看都对，按月汇总时总额和手工算的差几分钱；或者拿SUM(net_pay)和逐条导出再加一遍，结果不一样。

原因：用了FLOAT或者DOUBLE存金额。这两个是二进制浮点，0.1 + 0.2在计算机里不等于0.3，单条记录误差小到看不出来，几百条累加之后误差就显形了。

解决：所有金额字段一律DECIMAL(12,2)，中间计算用ROUND(x, 2)明确保留两位。改字段类型时注意，ALTER TABLE payroll MODIFY net_pay DECIMAL(12,2)会把已有数据四舍五入，改之前先备份。

-- 排查哪些字段还在用浮点 SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = 'hr_demo' AND data_type IN ('float', 'double');

顺带一提，DECIMAL的除法也要小心。base_salary * 0.105里0.105是精确小数，结果精度会扩展，不ROUND的话会存进一长串小数，等下次再算又不一样。金额计算的每一步都显式ROUND，是个不吃亏的习惯。

5.5 部门人数统计少了两行：COUNT(*) 把空部门算没了

现象：部门列表里明明有 12 个部门，统计报表只出 10 行，新成立的两个部门不显示，人数显示 0 的部门一个都没有。

原因：用了INNER JOIN。department JOIN assignment要求两边都有匹配，新建部门还没有人，自然被过滤掉。再叠加COUNT(*)，即使勉强用LEFT JOIN保住行，空部门也会数成 1（因为COUNT(*)数的是行数，不是非空值）。

解决：LEFT JOIN加上COUNT(具体列)，这组搭配要一起改。

-- 错误写法：空部门消失 SELECT d.dept_name, COUNT(*) FROM department d JOIN assignment a ON a.dept_id = d.dept_id AND a.current_flag = 1 GROUP BY d.dept_name; -- 正确写法：空部门保留，人数为 0 SELECT d.dept_name, COUNT(a.emp_id) FROM department d LEFT JOIN assignment a ON a.dept_id = d.dept_id AND a.current_flag = 1 GROUP BY d.dept_name;

还有一个变体：把a.current_flag = 1写到了WHERE里而不是ON里。这样LEFT JOIN会退化成INNER JOIN，因为WHERE在连接之后执行，NULL 值会被过滤掉。过滤条件放ON，这是LEFT JOIN的铁律。

6. 从“能跑”到“能交”：执行计划核对、并发压测和设计文档的写法

代码写完、页面能点，离交稿还有一段距离。课程设计的评分点里，“性能验证”和“设计说明”往往占不小比重，而这两块恰恰最容易被糊弄过去。我的做法是先自己当一次验收的人，把该问的问题问一遍。

6.1 用 EXPLAIN 确认索引没白建

建了索引不等于用上了索引。最直接的验证方式是EXPLAIN，看key列是不是你期望的那个索引，rows列估算扫了多少行。

EXPLAIN SELECT e.emp_no, e.real_name, d.dept_name FROM assignment a JOIN employee e ON e.emp_id = a.emp_id JOIN department d ON d.dept_id = a.dept_id WHERE a.dept_id = 3 AND a.current_flag = 1;

重点看三列：type是ref或eq_ref说明走了索引，ALL就是全表扫；key显示实际选用的索引名；rows是预估扫描行数，如果它接近表的总行数，索引基本没起作用。如果key是NULL但你明明建了索引，八成是列上做了函数运算或者隐式类型转换——比如WHERE emp_no = 1001，emp_no是CHAR(8)，传个整数进去会触发隐式转换，索引直接失效。

6.2 造数据与并发验证：两个人同时调岗会怎样

几行测试数据测不出问题。最少造到员工 1 万行、任职记录 3 万行、工资单 10 万行这个量级，方法是用存储过程循环插入，别手写INSERT。

-- 简易造数：批量插入 10000 名员工 INSERT INTO employee(emp_no, real_name, gender, birth_date, id_card, phone, hire_date, emp_status) WITH RECURSIVE seq(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 10000 ) SELECT CONCAT('E', LPAD(n, 7, '0')), CONCAT('员工', n), IF(n % 2 = 0, 'M', 'F'), DATE_SUB('1995-01-01', INTERVAL (n % 3000) DAY), CONCAT('1101011995', LPAD(n, 8, '0')), CONCAT('138', LPAD(n, 8, '0')), DATE_SUB('2020-01-01', INTERVAL (n % 150 <p> <a href="https://download.csdn.net/download/qq_41834798/19598651" style="color:#ec7500;font-size:14px;"> 本文还有配套的精品资源，点击获取 </a> <img alt="menu-r.4af5f7ec.gif" src="https://csdnimg.cn/release/wenkucmsfe/public/img/menu-r.4af5f7ec.gif" style="width:16px;margin-left:4px;vertical-align:text-bottom;cursor:text;"> </p>