쿼리를 짜다보니 한번씩 캘린더가 필요한 때가 있다.
검색해보니 동적으로 날짜를 생성해주는 쿼리도 있었다.
아래 쿼리는 2020년1월의 날짜를 출력하는 쿼리이다.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2020-01-01' and '2020-01-31'
출처: https://dyang34.tistory.com/entry/Mysql-전체-날짜-리스트-출력 [신문과 프로그래머.]
|
cs |
하지만 DBeaver로 쿼리 실행계획을 보면 Cost가 너무 높게 나온다..
고작 2020년 1월의 날짜만 출력하는데 Cost가 253.57....?!
그래서 캘린더 테이블을 생성해서 사용하기로 했다
(캘린더 테이블을 생성해서 사용할 수 있는 상황이 아니라면, 울며겨자먹기로 저 쿼리를 써야할것 같다..)
|
1
2
3
4
|
CREATE TABLE `CALENDAR_TBL` (
`c_date` date DEFAULT NULL,
UNIQUE KEY `CALENDAR_TBL_UN` (`c_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
cs |
DATE컬럼 하나짜리 간단한 테이블을 만들었다.
컬럼에는 UNIQUE인덱스를 걸어 중복을 방지하고, 검색속도를 올린다.
드디어 프로시저가 진가를 발휘할 시간이다.
공부겸 날짜 자동 INSERT 프로시저를 만들어봤다.
(혹시 잘못된부분, 개선할 부분 있으면 댓글 부탁드립니다..!)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
CREATE PROCEDURE `INSERT_CALENDAR`(IN STR_YEAR INT(4), IN END_YEAR INT(4), OUT RES_STR VARCHAR(20))
proc_main:BEGIN
DECLARE STR_DATE DATE;
DECLARE END_DATE DATE;
DECLARE S_YEAR INT(4);
DECLARE E_YEAR INT(4);
DECLARE FLAG INT(1);
DECLARE exit handler for SQLEXCEPTION
BEGIN
ROLLBACK;
SET RES_STR = 'ERROR';
END;
/* [START] PARAMETER VALIDATION */
/* VALIDATION FLAG*/
SET FLAG = 1;
IF STR_YEAR < 1970 THEN
SET FLAG = 0;
ELSEIF STR_YEAR > 2200 THEN
SET FLAG = 0;
ELSEIF END_YEAR < 1970 THEN
SET FLAG = 0;
ELSEIF END_YEAR > 2200 THEN
SET FLAG = 0;
END IF;
IF FLAG = 0 THEN
SET RES_STR = 'INVALID PARAMETER (1970 <= PARAM <= 2200)';
LEAVE proc_main;
END IF;
IF END_YEAR < STR_YEAR THEN
SET RES_STR = 'INVALID PARAMETER (PARAM1 < PARAM2)';
LEAVE proc_main;
END IF;
/* [END] PARAMETER VALIDATION */
START TRANSACTION;
SET STR_DATE = DATE(CONCAT(STR_YEAR, '0101'));
SET END_DATE = DATE(CONCAT(END_YEAR, '1231'));
SET S_YEAR = STR_YEAR;
SET E_YEAR = END_YEAR;
/* 파라미터로 받은 년도 사이의 날짜 모두 삭제 */
WHILE S_YEAR <= E_YEAR DO
DELETE FROM CALENDAR_TBL WHERE DATE_FORMAT(C_DATE, '%Y')=S_YEAR;
SET S_YEAR=S_YEAR+1;
END WHILE;
/* 파라미터로 받은 년도 사이의 날짜 모두 INSERT */
WHILE DATE_FORMAT(STR_DATE, '%Y') < (DATE_FORMAT(END_DATE, '%Y')+1) DO
INSERT INTO CALENDAR_TBL VALUES (DATE_FORMAT(STR_DATE, '%Y%m%d'));
SET STR_DATE = ADDDATE(STR_DATE, INTERVAL 1 DAY);
END WHILE;
COMMIT;
SET RES_STR = 'SUCCESS';
END
|
cs |
# 사용법
1 : CALL INSERT_CALENDAR(시작년도, 종료년도, @RES);
2 : SELECT @RES;
1번줄을 실행하고, 2번줄을 실행하면 프로시저 실행에 대한 결과를 볼 수 있다.
|
1
2
3
4
5
|
CALL INSERT_CALENDAR('1970', '2100', @RES);
SELECT @RES;
SELECT C_DATE FROM CALENDAR_TBL
WHERE C_DATE BETWEEN '2020-01-01' AND '2020-01-31';
|
cs |
