2025节假日表
dropTABLEcalendar_holiday;CREATETABLEcalendar_holiday(dateDATEPRIMARYKEY,yearINTNOTNULL,monthINTNOTNULL,dayINTNOTNULL,weekday_nameVARCHAR(10)NOTNULL,-- 星期一 ~ 星期日is_weekendTINYINTDEFAULT0,-- 是否为周末 (0:否, 1:是)is_holidayTINYINTDEFAULT0,-- 是否为法定节假日 (0:否, 1:是)is_workday_adjustedTINYINTDEFAULT0,-- 是否为调休上班日 (0:否, 1:是)holiday_nameVARCHAR(50)DEFAULTNULL,-- 节假日名称（如元旦、春节等）weekday_typeTINYINTDEFAULT0-- 日期类型（(0:正常上班日, 1:调休上班日, 2:法定节假日 , 3:周末 ）);select*fromcalendar_holiday-- =============================-- Step 1: 插入 2026 年所有日期（使用数字表生成）-- =============================INSERTIGNOREINTOcalendar_holiday(date,year,month,day,weekday_name,is_weekend,is_holiday,is_workday_adjusted,holiday_name,weekday_type)SELECTDATE_ADD('2026-01-01',INTERVALnDAY)ASdate,YEAR(DATE_ADD('2026-01-01',INTERVALnDAY))ASyear,MONTH(DATE_ADD('2026-01-01',INTERVALnDAY))ASmonth,DAY(DATE_ADD('2026-01-01',INTERVALnDAY))ASday,DAYNAME(DATE_ADD('2026-01-01',INTERVALnDAY))ASweekday_name,CASEWHENDAYOFWEEK(DATE_ADD('2026-01-01',INTERVALnDAY))IN(1,7)THEN1ELSE0ENDASis_weekend,0ASis_holiday,0ASis_workday_adjusted,NULLASholiday_name,0ASweekday_typeFROM(-- 生成 0 到 365 的数字（2026 是闰年，共 366 天）SELECTa.N+b.N*10+c.N*100ASnFROM(SELECT0ASNUNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)a,(SELECT0ASNUNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)b,(SELECT0ASNUNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4)c-- 最多 499，足够)numbersWHEREDATE_ADD('2026-01-01',INTERVALnDAY)<='2026-12-31';-- 元旦：2026-01-01（周四）UPDATEcalendar_holidaySETis_holiday=1,holiday_name='元旦',weekday_type=2WHEREdate='2026-01-01';-- 春节：除夕（2月16日）至初六（2月22日），共7天UPDATEcalendar_holidaySETis_holiday=1,holiday_name=CASEWHENdate='2026-02-16'THEN'春节（除夕）'WHENdate='2026-02-17'THEN'春节'WHENdate='2026-02-18'THEN'春节'WHENdate='2026-02-19'THEN'春节'WHENdate='2026-02-20'THEN'春节'WHENdate='2026-02-21'THEN'春节'WHENdate='2026-02-22'THEN'春节'ELSE'春节假期'END,weekday_type=2WHEREdateBETWEEN'2026-02-16'AND'2026-02-22';-- 春节调休上班：2026-02-15（周日）、2026-02-28（周六）UPDATEcalendar_holidaySETis_workday_adjusted=1,holiday_name='春节调休上班',is_weekend=0,weekday_type=1WHEREdateIN('2026-02-15','2026-02-28');-- 清明节：2026-04-04（周六）至 04-06（周一），共3天UPDATEcalendar_holidaySETis_holiday=1,holiday_name=CASEWHENdate='2026-04-05'THEN'清明节'ELSE'清明节假期'END,weekday_type=2WHEREdateBETWEEN'2026-04-04'AND'2026-04-06';-- 劳动节：2026-05-01（周五）至 05-03（周日），共3天UPDATEcalendar_holidaySETis_holiday=1,holiday_name='劳动节',weekday_type=2WHEREdateBETWEEN'2026-05-01'AND'2026-05-03';-- 劳动节调休：2026-04-26（周日）上班UPDATEcalendar_holidaySETis_workday_adjusted=1,holiday_name='劳动节调休上班',is_weekend=0,weekday_type=1WHEREdate='2026-04-26';-- 端午节：2026-06-20（周六）至 06-22（周一），共3天UPDATEcalendar_holidaySETis_holiday=1,holiday_name=CASEWHENdate='2026-06-21'THEN'端午节'ELSE'端午节假期'END,weekday_type=2WHEREdateBETWEEN'2026-06-20'AND'2026-06-22';-- 中秋节：2026-10-03（周六）UPDATEcalendar_holidaySETis_holiday=1,holiday_name='中秋节',weekday_type=2WHEREdate='2026-10-03';-- 国庆节：2026-10-01（周四）至 10-07（周三），共7天UPDATEcalendar_holidaySETis_holiday=1,holiday_name=CASEWHENdate='2026-10-01'THEN'国庆节'ELSE'国庆节假期'END,weekday_type=2WHEREdateBETWEEN'2026-10-01'AND'2026-10-07';-- 国庆调休上班：2026-09-27（周日）、2026-10-10（周六）UPDATEcalendar_holidaySETis_workday_adjusted=1,holiday_name='国庆节调休上班',is_weekend=0,weekday_type=1WHEREdateIN('2026-09-27','2026-10-10');UPDATEcalendar_holidaySETholiday_name=CASEWHENis_workday_adjusted=1THEN'调休上班'WHENis_holiday=1THENholiday_nameWHENis_weekend=1ANDis_holiday=0ANDis_workday_adjusted=0THEN'周末休息'ELSE'工作日'END,weekday_type=CASEWHENis_workday_adjusted=1THEN1WHENis_holiday=1THEN2WHENis_weekend=1THEN3ELSE0ENDWHEREyear=2026;SELECT*FROMcalendar_holidayWHEREyear=2026ORDERBYdateLIMIT10;)
