$$config title: [MySQL] 개인정보 2년 파기 쿼리문 개선 (4개 필드 중 null 또는 2년이상인 값의 데이터만 나오도록) keywords: - IT - ... $$ # 참고자료 [[SQL] MySQL 함수 활용하기 -if(), ifnull(), nullif() | 코딩 바다에서의 항해일지](https://makecodework.tistory.com/entry/SQL-MySQL-%ED%95%A8%EC%88%98-%ED%99%9C%EC%9A%A9%ED%95%98%EA%B8%B0-if-ifnull-nullif) [[DB] MySQL NULL 처리(IFNULL, CASE, COALESCE) | gil.log](https://velog.io/@gillog/DB-MySQL-NULL-%EC%B2%98%EB%A6%ACIFNULL-CASE-COALESCE) # 팀장님이 짜신 쿼리문 ifnull : 값이 null일 때, 대체할 값 출력 null이 아니라면 본인 값 출력 2년 이상인 컬럼 중에서 null이면 1? 근데 1(null)인걸 찾아? 왜 null을 찾는거지 null이 아닌 애들은 왜 1로 나오는, , 라고 생각했었는데요 왜 `ifnull`이겠니. . ifnull(조건, 대체값)일 경우에는 조건컬럼이 null일 경우, 대체값 조건이 true일 경우, 1 조건이 false일 경우, 0 그래서 null이거나 조건에 맞는 애들은 1로 나오니까 1인 애들만 추린 것.. 검증한 결과를 가지고 조건으로 활용하는 쪽은 생각치도 못해벌였다 역시 팀장님 짱이다 ```sql /* FRST_REG_DT datetime (NULL) YES (NULL) select,insert,update,references 가입일 LAST_CHG_DT datetime (NULL) YES (NULL) select,insert,update,references 수정일 MBER_PRVC_AGRE_DT datetime (NULL) YES (NULL) select,insert,update,references 개인정보동의여부일자 MBER_INFO_REG_DT datetime (NULL) YES (NULL) select,insert,update,references 정보등록일자 MBER_LGN_ATPT_DT datetime (NULL) YES (NULL) select,insert,update,references 로그인시도일자 MBER_PASSWORD_CHG_DT datetime (NULL) YES (NULL) select,insert,update,references 비밀번호 변경일자 MBER_PRVC_DEL_DT datetime (NULL) YES (NULL) select,insert,update,references 회원 개인정보 삭제 일시 */ SELECT DATE_SUB(NOW(), INTERVAL 2 YEAR) AS '2년전 기준일자', IFNULL(a.LAST_CHG_DT <= DATE_SUB(NOW(), INTERVAL 2 YEAR),1) AS LAST_CHG_DT_1, a.LAST_CHG_DT, IFNULL(a.MBER_PRVC_AGRE_DT <= DATE_SUB(NOW(), INTERVAL 2 YEAR),1) AS MBER_PRVC_AGRE_DT_1, a.MBER_PRVC_AGRE_DT, IFNULL(a.MBER_INFO_REG_DT <= DATE_SUB(NOW(), INTERVAL 2 YEAR),1) AS MBER_INFO_REG_DT_1, a.MBER_INFO_REG_DT, IFNULL(a.MBER_LGN_ATPT_DT <= DATE_SUB(NOW(), INTERVAL 2 YEAR),1) AS MBER_LGN_ATPT_DT_1, a.MBER_LGN_ATPT_DT, IFNULL(a.MBER_PASSWORD_CHG_DT <= DATE_SUB(NOW(), INTERVAL 2 YEAR),1) AS MBER_PASSWORD_CHG_DT_1, a.MBER_PASSWORD_CHG_DT, a.MBER_NM, a.MBER_BRDT, a.MBER_MBTLNUM, a.MBER_ID, a.FRST_REG_DT, a.MBER_PRVC_DEL_DT -- ,a.* FROM mbr_tb AS a WHERE a.MBER_AUTHOR_LEVEL = '10' AND (a.MBER_PRVC_DEL_DT IS NULL OR a.MBER_PRVC_DEL_DT = '') AND (a.FRST_REG_DT IS NULL OR a.FRST_REG_DT <= DATE_SUB(NOW(), INTERVAL 2 YEAR)) HAVING LAST_CHG_DT_1 = 1 AND MBER_PRVC_AGRE_DT_1 = 1 AND MBER_INFO_REG_DT_1 = 1 AND MBER_LGN_ATPT_DT_1 = 1 AND MBER_PASSWORD_CHG_DT_1 = 1 ORDER BY a.FRST_REG_DT DESC ; ``` # 해결 조건에 일치하는 데이터를 찾아 조회하는 방식으로 하느라 경우의 수를 다 생각했어야 했음. 근데 그건 너무 비효율적인 방법 같아 제외시키는 방법으로 방향을 돌림 null인 데이터는 나오게 하고, NOT으로 2년 이내인 데이터를 제외시켜, null이 아니라면 무조건 2년 이상 경과한 데이터만 나오도록 했다 굿 ```sql WHERE T.MBER_AUTHOR_LEVEL = 10 -- AND T.MBER_NM != '' AND T.MBER_PRVC_DEL_DT IS NULL AND ( T.MBER_PRVC_AGRE_DT IS NULL OR NOT (T.MBER_PRVC_AGRE_DT BETWEEN (CURRENT_TIMESTAMP - INTERVAL '2' YEAR) AND NOW())) AND ( T.MBER_LGN_ATPT_DT IS NULL OR NOT (T.MBER_LGN_ATPT_DT BETWEEN (CURRENT_TIMESTAMP - INTERVAL '2' YEAR) AND NOW())) AND ( T.LAST_CHG_DT IS NULL OR NOT (T.LAST_CHG_DT BETWEEN (CURRENT_TIMESTAMP - INTERVAL '2' YEAR) AND NOW())) AND ( T.FRST_REG_DT IS NULL OR NOT (T.FRST_REG_DT BETWEEN (CURRENT_TIMESTAMP - INTERVAL '2' YEAR) AND NOW())) ``` # 개요 수산공익직불제 - 개인정보2년 파기 기능 쿼리문 조정 본서버는 초기화버튼 숨겨놨으니 해결되면 다시 활성화 지금 현재 쿼리가 개인정보동의 일시만 보고 있어서 2023년 데이터도 날려질 위험이 있음 - 마지막로그인일시 MBER_LGN_ATPT_DT - 회원변경기록일시 LAST_CHG_DT - 최초등록일시 FRST_REG_DT 등등 모두 활용하는 쪽으로 + 비밀번호 변경일자 MBER_PASSWORD_CHG_DT - 개인정보동의일시 MBER_PRVC_AGRE_DT 포함 and로 해야하나 or로 해야 하나 음 and로 해야겠지 안전하게 하려면 *** 시간이 지날수록 데이터 갯수 다를수있음 수집일로부터 2년이 지난 개인정보를 가진 회원을 찾아서 이름, 연락처, 생년월일을 지울거야 지금 현재 쿼리가 개인정보동의 일시만 보고 있어서 2023년 데이터도 날려질 위험이 있어서 아래 필드를 활용해야 해 - 마지막로그인일시 MBER_LGN_ATPT_DT - 회원변경기록일시 LAST_CHG_DT - 최초등록일시 FRST_REG_DT 등등 모두 활용하는 쪽으로 + 비밀번호 변경일자 MBER_PASSWORD_CHG_DT - 개인정보동의일시 MBER_PRVC_AGRE_DT 포함 개인정보동의일시 먼저 보고 있으면 조회되어야 하고 없으면 다음으로 마지막로그인일시 보고 있으면 조회되어야 하고 없으면 다음으로 최종변경일시 보고 있으면 조회되어야 하고 없으면 다음으로 최초등록일시 보고 있으면 조회되어야 함 SELECT * FROM 회원테이블 WHERE (MBER_PRVC_AGRE_DT IS NOT NULL AND MBER_PRVC_AGRE_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR)) OR (MBER_LGN_ATPT_DT IS NOT NULL AND MBER_LGN_ATPT_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR)) OR (LAST_CHG_DT IS NOT NULL AND LAST_CHG_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR)) OR (FRST_REG_DT IS NOT NULL AND FRST_REG_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR)) ORDER BY MBER_PRVC_AGRE_DT DESC, MBER_LGN_ATPT_DT DESC, LAST_CHG_DT DESC, FRST_REG_DT DESC; *** 만약에 MBER_PRVC_AGRE_DT을 보고 최근 2년안에 포함된다면 조회목록에서 제외 다음으로 MBER_LGN_ATPT_DT이 최근 2년안에 포함된다면 조회목록에서 제외 다음으로 LAST_CHG_DT이 최근 2년안에 포함된다면 조회목록에서 제외 다음으로 FRST_REG_DT이 최근 2년안에 포함된다면 조회목록에서 제외 MBER_PRVC_AGRE_DT, MBER_LGN_ATPT_DT, LAST_CHG_DT, FRST_REG_DT 이것들중에 하나라도 2년 이내 데이터가 있으면 안됨 필드값이 null이거나 타겟필드 <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR) 인것만 나와야 하는데 MBER_LGN_ATPT_DT이 (CURRENT_TIMESTAMP - INTERVAL '2' YEAR) 이것보다 최근인게 나오거든 안나오게 하고 싶음 * 최종 SELECT P.* FROM (SELECT -- T.*, -- T.`MBER_PRVC_DEL_DT`, T.`MBER_PRVC_AGRE_DT`, T.MBER_LGN_ATPT_DT, T.LAST_CHG_DT, T.FRST_REG_DT, T.`MBER_ID`, T.`MBER_NM`, T.`MBER_TELNO`, T.`MBER_BRDT`, C1.CD_NM AS SIDO_CD_NM, C2.CD_NM AS SIGNGU_CD_NM FROM MBR_TB AS T LEFT JOIN TDP_EDU_COMPL_CODE AS C0 ON C0.SE_CD = T.MBER_BIZ_SE_CD LEFT JOIN all_code_set_tb AS C1 ON C1.CD_NO = T.MBER_CTPRVN_CD LEFT JOIN all_code_set_tb AS C2 ON C2.CD_NO = T.MBER_SIGNGU_CD WHERE T.MBER_AUTHOR_LEVEL = 10 AND T.MBER_NM != '' AND T.MBER_PRVC_DEL_DT IS NULL AND ( (T.MBER_PRVC_AGRE_DT IS NULL OR T.MBER_PRVC_AGRE_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR)) OR (T.MBER_LGN_ATPT_DT IS NULL OR T.MBER_LGN_ATPT_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR)) OR (T.LAST_CHG_DT IS NULL OR T.LAST_CHG_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR)) OR (T.FRST_REG_DT IS NULL OR T.FRST_REG_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR)) ) -- AND T.MBER_ID = 'MID_2104309962185318' ) AS P null에 대한 처리 하나라도 true면 무조건 나와서 문제임... 아예 잘못된 쿼리임 *** 조건에 대한 타겟필드는 MBER_PRVC_AGRE_DT, MBER_LGN_ATPT_DT, LAST_CHG_DT, FRST_REG_DT이고 필드값이 null이거나 타겟필드 <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR) 인것만 나와야 함 타겟필드 중에 하나라도 2년 이내 데이터가 있으면 안됨 null 처리를 잘해야 할 것 같음.. 하나라도 true면 아무 데이터나 다 나오는 것 같음 FRST_REG_DT는 2년 이내이고, MBER_PRVC_AGRE_DT, MBER_LGN_ATPT_DT, LAST_CHG_DT는 null임 걍 엉망임 SELECT a.* FROM `mbr_tb` AS a WHERE 1 = 1 [60960] AND a.MBER_AUTHOR_LEVEL = 10 [60925] -- AND a.MBER_NM != '' [35105] -- AND a.MBER_PRVC_DEL_DT IS NULL [35105] -- AND a.FRST_REG_DT <= (CURRENT_TIMESTAMP - INTERVAL '2' YEAR) [16872] MBER_PRVC_AGRE_DT, MBER_LGN_ATPT_DT, LAST_CHG_DT, FRST_REG_DT 만약에 4개중 랜덤으로 한 필드가 2년 이상이고, 나머지 3개 필드는 null이 아니라면 이 3개 필드는 무조건 2년 이상임 만약에 4개중 랜덤으로 2개 필드가 2년 이상이고, 나머지 2개 필드는 null이 아니라면 이 2개 필드는 무조건 2년 이상임 만약에 4개중 랜덤으로 3개 필드가 2년 이상이고, 나머지 1개 필드는 null이 아니라면 이 1개 필드는 무조건 2년 이상임 만약에 4개 중 한 필드가 2년 이상인데 나머지 필드 중 하나라도 2년 이내인 데이터가 나온다면 잘못된 쿼리임 MBER_PRVC_AGRE_DT, MBER_LGN_ATPT_DT, LAST_CHG_DT, FRST_REG_DT 위 4개 필드 중 아무 필드나 null일수도 있으며, 만약 null이 아닐때는 2년 이상이어야 함 아무필드나 null일때 나머지 필드가 null이 아니라면 2년 이상이어야 함