오라클에서 mySql로의 쿼리문 변경

개요

기존 오라클을 사용하고 있던 어플리케이션을 mysql로 변경하게 되었다. 오라클과 mysql을 사용하는데 있어서 몇가지 차이점이 있기에 쿼리문 역시 수정하게 되었다.

작업방식

쿼리문을 변경하고 제대로 변경되었는지 테스트 하기 위해서는 오라클과 mysql의 DB를 동일하게 구성하고 결과가 같은지 비교하는 것이 최선이지만 상황이 여의치 않았다.
수정된 쿼리문이 mysql에서 오류없이 수행되는지만 확인하는 방법으로 작업을 진행했다.

사전준비

1. mysql 원격서버 연결
   mysql의 workbench를 사용했고 원격서버에 연결하는 방법은 다음과 같다.

• 그림에서 보이는 (+) 클릭
  

• Ip 주소 및 username, password 입력후 test Connection클릭
  

2. 원격 dbms에 접속후 사용할 스키마 지정하기

• 사용할 스키마를 우클릭후 set as default schema클릭

3. ibatis 정리

• 태그 속성
  prepend - 태그 조건에 맞아 실행될 sql문에 선행하여 붙을 속성(AND, OR등)
  property - 매개변수 명
  compareProperty - 비교할 다른 매개변수명
  compareValue - 비교대상이 될 값

• 태그

<isEqual> - property의 값이 같을 때만 태그내 쿼리를 실행
<isGreaterThan> - property의 값이 비교값보다 클 경우 태크 내 쿼리를 실행

ibatis 에서는 괄호 및 부등호를 인식하지 못하기 때문에 “>=”을 
 <![CDATA[ >= ]]>
와 같이표현

• Iterate 태그 – 배열을 인자로 주어 sql문을 반복하여 구성하는 태그

<iterate open="(" conjunction="," close=")" property="columnA_List" prepend="columnA in">
	#columnA_List[]#
</iterate>
  
sql문에서 다음과 같이 표현됨 -  columnA in (1, 2, 3)

Prepend – 쿼리로 쓰여질 문자
Open – 시작문자
Conjunction – 구분문자
Close – 종료문자
Property – 입력인자(리스트)

오라클과 mysql의 차이

1. 레코드에 번호를 매기는 rownum

   오라클
   select rownum from table

   mysql
   select @ROWNUM := @ROWNUM + 1 AS rownum
   from testTable, (SELECT @ROWNUM:=0) R

2. 자료형

   오라클
   정수 : number
   문자열 : varchar2( )

   mysql
   정수 : int
   문자열 : varchar( )

3. 시퀸스에서 다음 숫자

   오라클
   test_seq.nextval

   mysql
   nextval(test_seq.nextval)

4. insert문 차이

• insert문을 사용할 때 오라클은 자신 테이블을 사용가능 하지만 mysql은 자신 테이블을 사용하지 못한다.(delet, update는 자신 테이블 사용가능)

오라클
Insert into testTable(column) values( (select column from testTable where seq = 1))

mysql
Insert into testTable(column) values( (select column from testTable A where seq = 1))

• 중첩된 쿼리의 테이블에 alias이름 추가

5. trim( )함수차이

• trim() 함수는 문자열에서 양쪽공백을 제거

• rtrim(), ltrim( ) 함수는 오른쪽공백을 제거

• 공백대신 제거할 문자열 설정가능

• 오라클과 mysql모두 trim( ), rtrim( ), ltrim( )을 사용가능하지만
  mysql에서는 rtrim( )과 ltrim( ) 사용시 공백만 제거가능하고 다른 문자열을 제거하는 기능이 없다

  오라클
  select rtrim('aaa .....‘ , ‘ . ’ ) from dual

  mysql ( trailing는 우측제거, leading는 좌측제거 )
  select trim(trailing ' . ' from 'aaa .....' ) from dual

6. 소수점 표현

• 소수를 문자열로 변환시 소수점이상이 0이면(0.3) 앞에 0을 제거한 .3으로 변환되는데 ‘FM9990D99’ 포맷을 통해 앞에 0이같이 출력되도록 한다.
  하지만 mysql은 0이 제거되어 출력되지 않기 때문에 필요없다.

  오라클
  to_char(0.3, 'FM9990D99')

7. 자료형 변환

   • 문자열을 날짜로 변환
     오라클 – to_date(‘20220830’,’YYYYMMDD’)
     Mysql – STR_TO_DATE(‘20220830’,'%Y%m%d')

   • 날짜를 문자열로 변환
     오라클 – to_char(sysdate, ‘YYYYMMDD’) from dual
     Mysql – date_format(sysdate(), ‘%Y%m%d’)

8. 문자열을 바이트 단위로 짜르기

• 문자 단위로 짜르는 것은 substr( ) 로 동일함

  문자열 바이트단위로 짜르기
  오라클 – substrb( ‘sdfsafsfs’, 0,50)
  Mysql – left(‘sfdsdfsf’, 50)

9. mysql에서 중첩쿼리 사용시 alias이름을 꼭 붙여야 한다.

   특히 FROM절에는 꼭 붙여야함