创建临时测试表
1 -- 初始化数据;
2
3 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-01-01 00:00:00',1,0);
4 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-01-03 00:00:00',1,0);
5 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-01-31 00:00:00',1,0);
6 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-02-01 00:00:00',1,0);
7 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-02-02 00:00:00',1,0);
8 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-02-03 00:00:00',1,0);
9 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-02-04 00:00:00',1,0);
1 drop procedure if exists Proc_ClearHoliday;
2 DELIMITER $$
3 CREATE PROCEDURE Proc_ClearHoliday()
4 BEGIN
5 declare tempWorkDay datetime;
6 declare done int;
7 -- 创建游标,并存储数据
8 declare cur_test CURSOR for
9 select WorkDate from temp_workdaykind where DayKind=1;
10 -- 游标中的内容执行完后将done设置为1
11 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
12 -- 打开游标
13 open cur_test;
14 -- 执行循环
15 posLoop:LOOP
16 -- 判断是否结束循环
17 IF done=1 THEN
18 LEAVE posLoop;
19 END IF;
20 -- 取游标中的值
21 FETCH cur_test into tempWorkDay;
22 -- 备份数据
23 insert into `rms`.`temp_WorkDayBak` (`WD_ID`,`WD_PrjCode`,`WD_RCID`,`WD_EmpNo`,`WD_WorkDay`,`DayKind`,`IsDelete`,`CreateTime`)
24 select WD_ID,WD_PrjCode, WD_RCID, WD_EmpNo,WD_WorkDay,1,0,now() from resconfig_workday where WD_WorkDay=tempWorkDay;
25 -- 删除节假日
26 delete from resconfig_workday where WD_WorkDay=tempWorkDay;
27 -- 更新备份表状态
28 update temp_WorkDayBak set IsDelete =1 where WD_WorkDay=tempWorkDay;DELIMITER $$
29 CREATE DEFINER=`root`@`%` PROCEDURE `Proc_ClearHoliday`()
30 BEGIN
31 declare tempWorkDay datetime;
32 declare done int;
33 -- 创建游标,并存储数据
34 declare cur_test CURSOR for
35 select WorkDate from temp_workdaykind where DayKind=1;
36 -- 游标中的内容执行完后将done设置为1
37 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
38 -- 打开游标
39 open cur_test;
40 -- 执行循环
41 posLoop:LOOP
42 -- 判断是否结束循环
43 IF done=1 THEN
44 LEAVE posLoop;
45 END IF;
46 -- 取游标中的值
47 FETCH cur_test into tempWorkDay;
48 -- 备份数据
49 insert into `rms`.`temp_WorkDayBak` (`WD_ID`,`WD_PrjCode`,`WD_RCID`,`WD_EmpNo`,`WD_WorkDay`,`DayKind`,`IsDelete`,`CreateTime`)
50 select WD_ID,WD_PrjCode, WD_RCID, WD_EmpNo,WD_WorkDay,1,0,now() from resconfig_workday where WD_WorkDay=tempWorkDay;
51 -- 删除节假日
52 delete from resconfig_workday where WD_WorkDay=tempWorkDay;
53 -- 更新备份表状态
54 update temp_WorkDayBak set IsDelete =1 where WD_WorkDay=tempWorkDay;
55 END LOOP posLoop;
56 -- 释放游标
57 CLOSE cur_test;
58
59 END
60 DELIMITER $$;
61
62 END LOOP posLoop;
63 -- 释放游标
64 CLOSE cur_test;
65
66 END;
67
68 DELIMITER $$
69 CALL Proc_ClearHoliday();
70 DELIMITER $$
给大表加索引方法一:
①创建一个临时的新表,首先复制旧表的结构(包含索引)
②给新表加上新索引
③把旧表的数据复制过来
④重命名旧表,重命名新表的名字为旧表的名字
-- 在线mysql>5.5 Online DDL不锁表加索引二:
alter table resconfig_workday
add index Idx_WD_WorkDay(WD_WorkDay), ALGORITHM=INPLACE, LOCK=NONE;