오라클 에서 MySQL 전환 작업 정리

이번에 기존 오라클을 사용하던 사이트를 MySQL로 컨버트하면서 경험했던 내용을 정리한다. 

결론부터 얘기하자면, 절대 하지 마라. 사람이 할짓이 아님. 

• 기본 오라클 문법 변경 정리

//ROWNUM 
, @rownum:=@rownum+1 as RNUM T1, (SELECT @rownum:=0) 
//T2 식으로 변수를 사용해 구현이 가능하나, 성능이 매우 취약함. 전체 데이터에 
//대해 상수를 적용하기 때문에, 데이터가 적은경우에만 사용함
//단순히 Paging 목적이라면, LIMIT 를 사용해 변경해야 한다. LIMIT 0,10 (주의 0부터 시작함 -> 오라클 Between 은 1부터 시작)

//TO_CHAR(A, 'yyy-mm-dd') 
DATE_FORMAT(A, '%Y-%m-%d %h:%i:%s')
//TO_CHAR 를 날짜 이외에 단순히 텍스트를 변경하려고 하는 용도로 사용할 수 도 있기 때문에, 찾기 바꾸기는 금물
//TO_CHAR(1000) -> CONVERT(1000, char)

//TO_DATE('2013-02-11', 'YYYY-MM-DD')
STR_TO_DATE('2013-02-11', '%Y-%m-%d')

//ADD_MONTHS(sysdate, -3)
DATE_ADD(now(), INTERVAL - 3 MONTH)
TIMESTAMPADD(month,-3,CURRENT_TIMESTAMP)

//TO_NUMBER('100')
CAST('100' AS UNSIGNED)

//TRUNC(sysdate)
TRUNCATE(now(),1)

//MONTHS_BETWEEN(startdate, enddate)
TIMESTAMPDIFF(MONTH, startdate, enddate)

//IF(A.UP_FLD_CD = 0, GRP_SEQ, UP_FLD_CD)  AS upfolderCode,
CASE A WHEN '1' THNE B ELSE END AS upfolderCode,

//DBMS_RANDOM.VALUE
RAND()

//SYS_CONNECT_BY_PATH
GROUP_CONCAT(GRP_nm SEPARATOR ',')

//GROUP BY ROLLUP(a.REG_DTM)
GROUP BY a.REG_DTM with rollup

• 주의사항

select * from ( )  이란 쿼리가 있다면, mySql 의 경우, 무조건 as alias 를 붙여야 한다. 안그럼 쿼리 오류 발생

그런데 패턴이 명확치 않아 찾기 바꾸기론 안됨. 특히 쿼리가 긴경우 (몇백라인정도) 되면 ) 찾아서 붙여주느라 눈돌아감.

trunc (  ) 와 같이 ( 열기전에 공백이 들어가 있으면 내장 함수인지 인식을 못해 오류남. 공백모두 제거해야 함.

오라클에서 사용하는 "--" 주석류는 모두 빼야 함. 특히 myBatis XML 내에 저런 얼토당토 않은 주석질을 해댔으면 전부 찾아서 제거해야 하는데 역시 찾기 바꾸기 패턴으로 못찾는다. XML 주석인 <-- 등도 있음으로, 해보면 앎.

오라클의 시퀀스는 사용할 수 없다. selectKey 를 사용해서 넣어주면 되는데, 시퀀스 테이블이 많으면 패턴이 +1 이 아닌경우도 있을 수 있어 모두 확인해 봐야 한다. 자동으로 바꾸기 어렵고, 오류 소지가 많음.

• 툴추천

(http://www.sqlines.com/oracle-to-mysql)

DATA 이전은 OracleToMysql 도구를 사용했다. 데이터 이전시 테이블이나, 컬럼형태가 다르고, 데이터 인코딩등 여러 이슈가 있을줄 알았는데, 생각외로 이 부분은 크게 문제가 없었다. 인코딩도 다행히 UTF8 을 쓰고 있었음. EUC_KR 등을 사용하고 있었다면, ..... 흠.... 

(http://www.ispirer.com/)

FUNCTION 프로시져 이전등을 할때 추천함. share 로 받으면 1달정도 사용해 볼 수 있다. 물론 이건 완벽할 수가 없다. 단지, 좀 귀찮은 부본들을 이 툴로 생성해 놓고, 이후 상세히 보정작업을 해줘야 한다. 아예 컨버트가 안되는경우도 허다함. 펑션/프로시져/트리거 많으면 각오해야 함. ;;

기타 인덱싱이나 테이블스페이스 구조등은 자동으로 해줄수가 없다. 플랫폼이 다르다보니, 설정이나 다른 여러 가지를 고려해서 수동으로 해야 함. 데이터가 많을 수록 성능등에 심각한 영향을 미칠수 있다.

• 파티션하여 RNUM 매기기 

SELECT *
FROM (
   SELECT T1.*, 
           (CASE @PART WHEN T1.OC_SEQ THEN @ROWNUM:=@ROWNUM+1 ELSE @ROWNUM:=1 END) AS RNUM,
           (@PART:=T1.OC_SEQ) AS PART
   FROM CTN_OC_SITE T1, (SELECT @PART:=0, @ROWNUM:=0 FROM DUAL) T2
   ORDER BY T1.OC_SEQ, T1.GRP_SEQ                  
) LT1

• RANK() 쿼리

SELECT cu_distribution, status, @Rank:=@Rank + 1 AS user_rank

FROM

(

    SELECT cu_distribution, count(cu_status) AS status

    FROM cs_customer

    where cu_status=1

    GROUP BY cu_distribution

    ORDER BY status DESC

) Sub1

CROSS JOIN (SELECT @Rank:=0) Sub2

• 부모노드 역-리커시브

SELECT _ID AS MNU_SEQ FROM (

SELECT 

    @R AS _ID,

    (SELECT @R := UP_MNU_SEQ FROM CMM_MENU WHERE MNU_SEQ = _ID) AS _PARENT_ID,

    @I := @I + 1 AS LEVEL

    FROM (SELECT @R := 248) VARS, CMM_MENU

WHERE @R <> 0 AND (@I:=0)=0

) T1

WHERE _MNU_LVL = 1

• MERGE INTO

INSERT INTO BBS_ATCL_APP_USR  (ABC) VALUES ('111')  

ON DUPLICATE KEY UPDATE ATCL_SEQ = #{atclSeq}, APPR_YN = 'Y', REG_DTM = now(), REG_USR_ID = #{regUsrId}, REG_USR_NM = #{regUsrNm}

• 재귀호출 예제

CREATE FUNCTION TREE_CMM_MENU () RETURNS int
BEGIN
        DECLARE _code int;
        DECLARE _parent int;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @code = NULL;

        SET _parent = @code;
        SET _code = -1;

        IF @code IS NULL THEN
                RETURN NULL;
        END IF;

       LOOP
                SELECT  MIN(MNU_SEQ)
                INTO    @code
                FROM    CMM_MENU
                WHERE   UP_MNU_SEQ = _parent AND MNU_SEQ > _code AND USE_YN = 'Y';
                        IF @code IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @code;
                END IF;
                SET @level := @level - 1;
                SELECT  MNU_SEQ, UP_MNU_SEQ
                INTO    _code, _parent
                FROM    CMM_MENU
                WHERE   MNU_SEQ = _parent;
        END LOOP;
END
//재귀-사용
SELECT *
    FROM    (
        SELECT  TREE_CMM_MENU() AS code, @level as lev
            FROM    (
                        SELECT  @start_with := '10614',
                                @code := @start_with,
                                @level := 0
                    ) vars, CMM_MENU
            WHERE @code IS NOT NULL
            ) tb2
    JOIN CMM_MENU tb1 ON tb1.MNU_SEQ = tb2.code