-- ============================================= -- 薪酬核算:新增「岗位工资」「工龄工资」 -- -- 业务说明: -- 1) 岗位工资、工龄工资与「基本工资」同源,在【员工管理】的员工信息中维护 -- 2) 执行薪酬核算时带入核算单,并计入应发工资与实发工资 -- 3) 薪酬档案(hrm_employee_salary)同步加列,便于后续扩展;当前核算以员工信息为准 -- -- 脚本可重复执行(幂等) -- ============================================= SET NAMES utf8mb4; SET @sch := DATABASE(); -- ---------- 1. 员工信息:岗位工资 / 工龄工资 ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_employee' AND COLUMN_NAME = 'post_salary'); SET @sql := IF(@exists = 0, 'ALTER TABLE `hrm_employee` ADD COLUMN `post_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''岗位工资'' AFTER `base_salary`', 'SELECT ''hrm_employee.post_salary 已存在'' AS msg'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_employee' AND COLUMN_NAME = 'seniority_salary'); SET @sql := IF(@exists = 0, 'ALTER TABLE `hrm_employee` ADD COLUMN `seniority_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''工龄工资'' AFTER `post_salary`', 'SELECT ''hrm_employee.seniority_salary 已存在'' AS msg'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- 2. 薪酬核算单:岗位工资 / 工龄工资 ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_salary_calculation' AND COLUMN_NAME = 'post_salary'); SET @sql := IF(@exists = 0, 'ALTER TABLE `hrm_salary_calculation` ADD COLUMN `post_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''岗位工资'' AFTER `performance_salary`', 'SELECT ''hrm_salary_calculation.post_salary 已存在'' AS msg'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_salary_calculation' AND COLUMN_NAME = 'seniority_salary'); SET @sql := IF(@exists = 0, 'ALTER TABLE `hrm_salary_calculation` ADD COLUMN `seniority_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''工龄工资'' AFTER `post_salary`', 'SELECT ''hrm_salary_calculation.seniority_salary 已存在'' AS msg'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- 3. 薪酬档案(可选,当前核算不取此表的基本/岗位/工龄工资) ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_employee_salary' AND COLUMN_NAME = 'post_salary'); SET @sql := IF(@exists = 0, 'ALTER TABLE `hrm_employee_salary` ADD COLUMN `post_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''岗位工资'' AFTER `base_salary`', 'SELECT ''hrm_employee_salary.post_salary 已存在'' AS msg'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_employee_salary' AND COLUMN_NAME = 'seniority_salary'); SET @sql := IF(@exists = 0, 'ALTER TABLE `hrm_employee_salary` ADD COLUMN `seniority_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''工龄工资'' AFTER `post_salary`', 'SELECT ''hrm_employee_salary.seniority_salary 已存在'' AS msg'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- 4. 核对 ---------- SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME IN ('hrm_employee', 'hrm_salary_calculation', 'hrm_employee_salary') AND COLUMN_NAME IN ('post_salary', 'seniority_salary') ORDER BY TABLE_NAME, COLUMN_NAME; SELECT '岗位工资 / 工龄工资字段初始化完成' AS result;