-- =====================================================
|
-- HRM 考勤节假日配置：补齐「时间段」结构（start_date / end_date）
|
--
|
-- 背景：
|
-- 2026-09-30 的提交 a797b580 将「节假日配置」由单日(date)改为时间段(startDate/endDate)，
|
-- DO/VO/Mapper 已按 start_date、end_date 查询，但数据库表结构没有同步。
|
-- 2026-10-17 新增的 GET /hrm/attendance/record/my-status 第一次走到节假日查询，
|
-- 报 Unknown column 'start_date' in 'field list'。
|
--
|
-- 内容：
|
-- ① hrm_attendance_holiday 新增 start_date、end_date
|
-- ② 存量数据按 date 回填（date 列保留兼容，保存时仍写入 start_date）
|
-- ③ 回填后收紧为 NOT NULL，避免后续写入半条时间段
|
--
|
-- 幂等：可重复执行（MySQL 不支持 ADD COLUMN IF NOT EXISTS，先查 information_schema）
|
-- 执行方式：
|
-- mysql -h<host> -P<port> -u<user> -p --default-character-set=utf8mb4 <db> < docs/sql/hrm_attendance_holiday_date_range_20261017.sql
|
-- =====================================================
|
|
SET NAMES utf8mb4;
|
|
-- ------------------------------------------------------------
|
-- ① 新增 start_date
|
-- ------------------------------------------------------------
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = 'hrm_attendance_holiday'
|
AND column_name = 'start_date');
|
SET @ddl = IF(@col_exists = 0,
|
'ALTER TABLE `hrm_attendance_holiday` ADD COLUMN `start_date` date DEFAULT NULL COMMENT ''开始日期'' AFTER `date`',
|
'SELECT 1');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ------------------------------------------------------------
|
-- ② 新增 end_date
|
-- ------------------------------------------------------------
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = 'hrm_attendance_holiday'
|
AND column_name = 'end_date');
|
SET @ddl = IF(@col_exists = 0,
|
'ALTER TABLE `hrm_attendance_holiday` ADD COLUMN `end_date` date DEFAULT NULL COMMENT ''结束日期'' AFTER `start_date`',
|
'SELECT 1');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ------------------------------------------------------------
|
-- ③ 存量数据回填：单日配置的起止日期都等于原 date
|
-- ------------------------------------------------------------
|
UPDATE `hrm_attendance_holiday` SET `start_date` = `date` WHERE `start_date` IS NULL;
|
UPDATE `hrm_attendance_holiday` SET `end_date` = `date` WHERE `end_date` IS NULL;
|
|
-- ------------------------------------------------------------
|
-- ④ 回填完成后收紧为 NOT NULL（幂等）
|
-- ------------------------------------------------------------
|
ALTER TABLE `hrm_attendance_holiday`
|
MODIFY COLUMN `start_date` date NOT NULL COMMENT '开始日期',
|
MODIFY COLUMN `end_date` date NOT NULL COMMENT '结束日期';
|
|
-- ------------------------------------------------------------
|
-- 校验
|
-- ------------------------------------------------------------
|
SELECT 'start_date' AS item, COUNT(*) AS cnt FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_holiday' AND column_name = 'start_date'
|
UNION ALL
|
SELECT 'end_date', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_holiday' AND column_name = 'end_date';
|
