oracle, mssql, mysql 함수 비교

여러문서를 찾아봣지만 개인적으로 업무상 정리하기위해서 다시 정리해본당.

원래 업무자체는 oracle로 시작해서 mssql로 변환을 마친상태다. oracle를 mysql로 변환할가 했지만

아무래도 mssql을 mysql로 하는게 문법도 비슷한게 많아 더 편한것 같아서 mssql을 mysql로 변환한다.

슬쩍 본 결과 역시나 변환함수나 날씨관련 함수가 좀 짜증난다. 그래도 해보장..

 

PS - 솔직히 MSSQL을 제일 마지막에 하는걸 추천

ORACLE에서 이기종이면 ORACLE 을 MYSSQL로 변환후 MYSQL을 기반으로 MSSQL

MSSQL에서 이기종이면 MSSQL을 MYSQL로 변환후  ORACLE

MYSQL에서 이기종이면 MYSQL을 기준으로 ORACLE,MSSQL로 변환하는걸 추천하겠다.

MYSQL쪽이 이기종을 변환하기는 제일 편한것 같다.

단 페이징이나,랭크함수등 일부분은 ORACLE과 MSSQL이 서로 비슷하므로 그부분은 서로 참조시키는게 좋다.

 

1.  ISNULL 함수를  IFNULL 함수로 변경한다. (주의사항 있음)

이건 고대로 가져온건데 오라클의 경우 ISNULL이 아니라 NVL이 되겠다 이것은 뭐 그냥 바꾸기만 하면된다.

단 ISNULL과 IFNULL은 조금 다르단다. NVL과 IFNULL은 두번째인자에 컬럼이 들어갈수 있지만..

ISNULL은 value만 들어갈수 있단다.

위와 같은 케이스를 어디선가 봐서 써놨던것 같은데 실제로 해보면 문제 없넹.. 어떤케이스였던거지..

테스트 결과 ISNULL 이나 NVL이나 IFNULL이나 같다..

그냥 replace 몽땅

2.  GETDATE() 함수를 NOW() 로 변경한다.

뭐 그냥 마찬가지로 replcace 몽땅 오라클은 SYSDATE

SYSDATE()가 NOW()랑 비슷하지만 동작의 차이가 있다.

SELECT NOW(),SLEEP(2),NOW();

SELECT SYSDATE(),SLEEP(2),SYSDATE();

이 2개는 직접해보면 NOW()의 경우 시간차이가 없다.

하지만 SYSDATE()에는 차이가 있다.

잘 모르겠으면 레퍼런스에 12장 함수와연산자의  날짜와 시간함수 항목을 보면 잘 설명되어 있다.

 

3. CONVERT의 인자순서를 변경한다.

MSSQL은 CONVERT(NUMERIC,'1') 와같이 TYPE가 먼저 나오고 expr1(걍 인자라고 생각하면 됨) 이 나중에 나온다.

MYSQL은 CONVERT('1',DECIMAL)과 같이 expr1이 먼저 나오고 TYPE가 나중에 나온다.

 

4. 날짜는 다음과 같이..

이건 너무 케이스가 많아서 일일히 설명하긴 힘들공

몇가지 자주 쓰는 케이스만 정리한다

DATE_FORMAT(date,format)  date 형태를  형테를 문자열로 한다.

----------------

select DATE_FORMAT('2009-03-03','%Y%m%d')

결과:

20090303

%Y:4자리의 년도

%y:2자리의 년도

%m:2자리의 월

%d:2자리의 일자

%H:00~ 23시

%h:01~12시

%i:00~59분

%S:00~59초

물론 mssql처럼 여러조합으로 되어 있는것도 있지만 이것만 있어도 왠만한조합은 나오기에

 

 

-----------------

STR_TO_DATE(SRT,FORMAT) 이건 DATE_FORMAT()의 정반대라고 보면된다.]

-----------------

SELECT DATE_FORMAT(STR_TO_DATE('2009070603010','%Y%m%d'),'%m')

을하면 07이 나오면서 변환이 된걸 알수 있다.

==================================

****중요********

DATEDIFF(expr1,expr2)

 

expr1 - expr2 을 한다 하지만 이건 날짜로 계산을 한다. 내가 원하는 값은 보통 몇일 차이가 나느냐의 절대값을 구하려고 하는것이니

뒤에 위치할 날자를 expr1에 넣던지 구한다음 절대값을 구해야한다.

 

* 하지만... MSSQL 과 주의해서 사용해야하낟.

MSSQL 은 expr2-expr1을 하기때문에 함수가 같다고 똑같이 쓰면안된다.

******중요2**************

oracle나 mssql 처럼 + 혹은 - 연산자로 날짜계산은 절대엄금..

예를들어 now()+1을하면 mssql 처럼 1일을 더하는게 아니라 1초를 더한다.

꼭 DATE_ADD를 쓰도록하자.

--------- DATE_ADD -------------

SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);

이런식인데

http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21728&cat1=12&cat2=355&cat3=0&lang=k

여기가면 설명이 되어 있다.

뒤에 DAY에 해당하는 인자만 변경해주면 연도라던지 월이라던지 마음대로 컨트롤이 된다.

----------- DATE_ADD 끝 -------------

----- 요일계산 ---------

oracle -- select to_char(to_date('20091010','yyyymmdd'),'d') from dual

mssql -- select datepart(dw,convert(datetime,'20091010',112))

은 결과가 7이 나온다.

일:1,월:2,화3~ 토:7이라는 결과인데

mysql -- select weekday(str_to_date('20091010','%Y%m%d'))

은 5가나온다

월:0,화:1 ~ 일:6이라는 결과다.

그래서 맞추기 위해 mysql에서 다음과 같이 했다.

select CASE WHEN ((weekday(str_to_date('20091010','%Y%m%d'))+2) % 7)  = 0 THEN 7 ELSE ((weekday(str_to_date('20091010','%Y%m%d'))+2) % 7) END

좀 길다..

결론은 2를 더하고 7로 나눠서 나머지를 구한후 만약 0이면 7로 만들어버린다..

 

 

5. 문자열 연산

MSSQL 은 + 을 쓰고 ORACLE는 || 을 쓰지만 MYSQL은 CONCAT를 쓴다

CONCAT(STR1,STR2,STR3)

이런식으로 무한대~

-- 2010/6/11 첨부 시작 --

ORACLE 도 CONCAT로 문자열을 합칠수 있지만

2개만 된다.

CONCAT(STR1,STR2) 이렇게 까지만

3개하려면?

아마도

CONCAT(CONCAT(STR1,STR2) ,STR3) 이렇게 해야할거다..

-- 2010/6/11 첨부  끝--

 

 

 

 

 

------------------- SUBSTRING ------------------

SUBSTRING << 틀렸다 같기는 개뿔.. 미챠.. 다시 한번 보니 전혀 않같다..

select substring('123456789',2) mysql 결과: 23456789

select substring('123456789',2) mssql 결과: 인자가 3개필요하다고 함

select substring('123456789',1,2) mssql 결과: 12

select substring('123456789',1,2) mysql 결과: 12

즉 인자가 3개일때는 같지만 2개일때는 틀리다.

 

 

 

 

--------- synonym----------

oracle 나 mssql과 다르게 mysql 은 synonym이 없다.

대신 view에서 바로 update가 가능..

권한 조정이 가능하다고는 하는데 알바아님 -_-

 

mysql view 참조 : http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21113&cat1=19&cat2=571&cat3=0&lang=k

위에 글을 읽어보면 조건만 맞으면 view에도 insert,update가 가능하단다.

 

---------- CHARINDEX ------------

oracle : INSTR('123','2')   결과 : 2

mssql : CHARINDEX('1','123') 결과 : 2

mssql : CHARINDEX('2','123') 결과 : 2

mysql : INSTR('123','2')   결과 : 2

 

6. 페이징 관련

mysql 은 페이징 관련해서

rownum으로 계산해서 between을 사용하지 않고

가급적 limit를 사용해서 페이징 처리를 한다.

근데 이게 골떄리는게

어떠한 식도 용납하지 않는다

limit @startnum, 10

처럼 사용자 변수도 안돼고

limit (1-1),10

처럼 수식을 써서도 안된다

오로지.

limit 0,10 과 같이 숫자만 들어가야한다.

참고로 limit 인덱스(rownnum과 달리 0부터 시작), 가져올갯수

식으로 가져온다.

---- 페이징을 위한 ROWNUM 처리 방법 ----

****오라클****

SELECT *
  FROM (SELECT ROWNUM RNUM, TEMP_ALIAS.*
          FROM (SELECT   TEMP_DATA
                    FROM TEMP_TABLE
                ORDER BY TEMP_SORT) TEMP_ALIAS) TEMP_ALIAS_PAGE
 WHERE RNUM BETWEEN #STARTNUM# AND #ENDNUM#

2번 싸는 이유에 대해서 알 필요가 있다.

만약 처음 TEMP_TABLE에 ROWNUM을 주고 한다라고 치면

실제로 나오는 ROWNUM은 내가 원하는 소팅대로 해서 나온것이 아니라 소팅이 되기전의 ROWNUM이

소팅 순서에 따라 나오게 된다. 그렇기떄문에 한번 싸주는게 필요하다

2번째 RNUM을 가져온시점에서 BETWEEN을 해서 하지 않는 이유는 ROWNUM의 경우

ROWNUM < 5 와같은 것은 잘 먹지만 ROWNUM > 5와 같은것은 안먹는다 이것은 오라클의 처리문제 때문이며

그래서 넘버링을 한번 한후 처리할 필요가 있다

물론 처리방법중에는 ROWNUM < #ENDNUM#을 한후에 한번 싼후

ROWNUM > #STARTNUM#  으로 처리하는 방법도 있다.

물론 속도상으로 후자가 더 낳겠지..

****MSSQL****

SELECT TEMP_ALIAS.*
  FROM (SELECT ROW_NUMBER () OVER (ORDER BY TEMP_SORT) RNUM, TEMP_DATA
          FROM TEMP_TABLE) TEMP_ALIAS
 WHERE RNUM BETWEEN #STARTNUM# AND #ENDNUM#

 

MSSQL 2005 이상에서만 가능하며 ROW_NUMBER () 를 썼다. 이것은 오라클에서도 동일하게 쓸수 있는 방법이긴한데

ROW_NUMBER ()를 쓰는게 좀 껄쩍지근해서 MSSQL하는 친구에게 물어봤더니 상관없단다. 많이 쓰는 방법이라고 하니 이대로..

차이점은 ROW_NUMBER () 를  써서 테이블을 싸는게 한번 줄었다는거?

뭐 나는 그냥 변환의 용이성떄문에 그냥 오라클처럼 3번 감쌋다.. ㅎㅎㅎ

**** MYSQL****

SELECT   TEMP_DATA
    FROM TEMP_TABLE
ORDER BY TEMP_SORT
LIMIT #STARTNUM2# , #LISTBLOCK#

위에서도 썼지만 쿼리자체는 심플하지만 페이징 방법이 다르기때문에 페이징 넘버 처리부분을

별도로 처리해야한다.

페이지 블럭당 사이즈가 10일경우 1페이지는 STARTNUM가 1 , 2페이지는 11이지만

STARTNUM2 는 1페이지는 0, 2페이지는 10이어야한다.

그리고 뒤에 LISTBLOCK는 해당 페이지의 블럭사이즈..

즉 몇개나 보여줄지 여부이다.

 

-- 컬럼추가시 차이점 ----

default값을 지정시 달라진다

mssql은 default값을 지정하면서 추가시 기존 row에 그값이 들어가지 않는다 그냥 NULL이 들어갈뿐

단 NOT NULL일떄에는 들어간다

하지만 oracle이나 mysql은 상관없이 들어간다.

[출처] mssql to mysql (mssql -> mysql)|작성자 삽지리