[DB-SQL] Oracle -> MariaDB SQL 문법 변경사항 정리

https://kora1492.tistory.com/88

[DB] Oracle DB에서 MariaDB 로 데이터 이관하기

 

DB 를 Oracle DB 에서 MariaDB로 이관하고 나서, 

SQL Mapper(Mabatis) 를 쓰고 있는 쪽에서 에러가 발생하여 구문을 MariaDB 문법에 맞춰서 적용하기.

Syntax Error 메시지를 보고 문법에 맞추어 변경이 필요로 함

 * 실행계획 확인(Explain) 시, 이전 글에서 INDEX 등을 이관하지 않아 성능 이슈 확인

 

변경관련 Tool : SQLines / ChatGpt

 

버전 : mariaDB 1.8.0 Version 사용 (운영 중인 프로젝트의 JDK 버전의 호환 확인)

 

목차

1. DECODE() 함수는 MariaDB에서 지원되지 않음.
2. TO_CHAR 
3. NVL
4. START WITH   connect by prior - 계층 쿼리
5. rownum
6. 함수 생성 시 에러 발생
7. 대소문자 구분
8.  NULL, 빈 문자열(Empty String) 차이

 

 

쿼리 성능 개선 관련

인덱스 생성: 쿼리에서 자주 사용되는 컬럼에 인덱스를 생성하여 검색속도를 높입니다.

서브쿼리 최소화: 서브쿼리는 성능 저하의 주범 중 하나입니다. 서브쿼리가 사용된 부분을 조인으로 변경하거나, 필요한 부분만 추출하는 방법으로 최소화할 수 있습니다.

필요한 컬럼만 선택: SELECT 절에서 SELECT * 대신에 필요한 컬럼만 선택하여 조회합니다.

조인 최적화: 쿼리에서 자주 사용되는 조인을 최적화합니다.

대용량 데이터 처리: 조회 데이터가 매우 많을 경우, 데이터를 일정량씩 나누어 처리하거나, 인덱싱을 통해 처리속도를 높입니다.

 

 

 

1. DECODE() 함수는 MariaDB에서 지원되지 않음.

 

오라클에선 자주 볼 수 있는 DECODE() 가 에러가 발생

IFNULL()  혹은 인자가 많은 경우 COALESCE, CASE문 으로 해결함

-- 오라클
DECODE(C.MESSAGE, NULL, A.MESSAGE, C.MESSAGE)

-- MariaDB
IFNULL(C.MESSAGE, A.MESSAGE) AS MESSAGE

COALESCE(col1, col2, col3)

 

 

2. TO_CHAR 

-- Oracle
TO_CHAR(MAX(UPD_DTM),'yyyyMMddhh24miss')

-- MariaDB
DATE_FORMAT(MAX(UPD_DTM),'%Y%m%d%H%i%s')

 

 

 

 

 

 

3. NVL

IFNULL() 함수를 통해서 변경

 

 

 

 

4. START WITH   connect by prior - 계층 쿼리

WITH RECURSIVE 구문을 통해 변경

변경 시 계층적으로 조회되는 구문을 UNION ALL 할 때의 JOIN 조건으로 설정

WITH RECURSIVE CTE AS (
  SELECT id, p_id
  FROM table 
  WHERE id = 0
  UNION ALL
  SELECT id, p_id form table a
  JOIN ON CTE.p_id = a.id
)

 

 

 

5. rownum

Oracle의 ROWNUM은 검색된 데이터 세트의 행 번호를 반환

LIMIT 및 OFFSET을 사용하여 범위 설정 가능함.

-- Oracle
SELECT *
FROM (
  SELECT column1, column2, ROWNUM AS rnum
  FROM some_table
  WHERE ROWNUM <= 10
)
WHERE rnum >= 5;

-- MariaDB
SELECT column1, column2
FROM some_table
LIMIT 10 OFFSET 4;

 @ROWNUM:=@ROWNUM+1

SELECT
  @rownum:=@rownum+1 -- 조회 결과에 행 번호 
FROM some_table
WHERE (@rownum:=0)=0; -- 초기화

 

 

 

 

6. 함수 생성 시 에러 발생

RDS - 연결 상태에서 

: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 메시지 발생 시 변경

https://soye0n.tistory.com/271 

[AWS RDS] Access denied; you need (at least one of) the SUPER privilege(s) for this operation

 

 

 

7. 대소문자 구분

마리아DB 

:  lower_case_table_names 설정에 따라 대소문자를 구분함.

• lower_case_table_names=0 : 대/소문자 구분
• lower_case_table_names=1 : 대소문자 구분하지 않음(모두 소문자로 인식함)

:  설정을 변경하는 거나, Query 문자를 모두 소문자/대문자로 일치하게 변경

 

 

 

 

 

8.  NULL, 빈 문자열(Empty String) 차이

오라클 

: 빈 문자열 ''  인 경우에도 NULL 과 동일하게 처리됨.

 

마리아DB

: NULL 과 빈 문자열을 구분함.

 

-> IF, CASE, NULLIF 등을 이용해서 '' 인 경우 NULL 이 되도록 변경해야 함.

IF(expr1 = '', NULL)