-- ============================================================ -- 安全生产 - 特种作业证持证人改为关联员工档案 -- 生成时间:2026-10-29 -- 背景:原设计把持证人做成纯文本姓名(employee_name / employee_no / dept_name), -- 无法与人力资源的员工档案、系统用户打通。 -- 做法:新增 employee_id 关联 hrm_employee.id,姓名/工号/部门改为员工档案带出; -- 旧文本字段保留为快照,便于员工档案变更后仍可追溯开证时的人事信息。 -- 说明:幂等,可重复执行 -- ============================================================ -- ---------- 1. 新增 employee_id 字段 ---------- SET @exist_col = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'safety_certificate' AND column_name = 'employee_id'); SET @sql = IF(@exist_col = 0, 'ALTER TABLE `safety_certificate` ADD COLUMN `employee_id` bigint NULL COMMENT ''持证人(员工编号,关联 hrm_employee.id)'' AFTER `id`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- 2. 索引 ---------- SET @exist_idx = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'safety_certificate' AND index_name = 'idx_employee_id'); SET @sql2 = IF(@exist_idx = 0, 'CREATE INDEX `idx_employee_id` ON `safety_certificate` (`employee_id`)', 'SELECT 1'); PREPARE stmt2 FROM @sql2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2; -- ---------- 3. 原三列改为快照语义(保留,不再作为唯一来源) ---------- ALTER TABLE `safety_certificate` MODIFY COLUMN `employee_name` varchar(50) NULL COMMENT '持证人姓名(快照,由员工档案带出)', MODIFY COLUMN `employee_no` varchar(50) NULL COMMENT '工号(快照,由员工档案带出)', MODIFY COLUMN `dept_name` varchar(100) NULL COMMENT '所属部门(快照,由员工档案带出)'; -- ---------- 4. 校验 ---------- SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'safety_certificate' AND COLUMN_NAME IN ('employee_id', 'employee_name', 'employee_no', 'dept_name') ORDER BY ORDINAL_POSITION;