[DBMS/MySQL] MySQL과Oracle 문법 차이 (+예약어)

 

※정리된 내용보다 더 많은 차이가 있다.

 

0. 대소문자 구분

Oracle에서는 대소문자를 구분하지 않지만 MySQL에서는 대소문자를 구분한다.

 

1. 시퀀스 사용 함수

Oracle	board_sq.NEXTVAL
MySQL	board_sq.CURRVAL

 

그 외에도 MySQL에서는 시퀀스를 사용하지 않고 컬럼 제약조건으로 AUTO_INCREMENT를 사용하고 insert할 때 해당 컬럼을 제외하고하면 시퀀스와 같은 효과를 낸다. (Oracle은 없음)

 

2. 형변환 

Oracle에서는 TO_CHAR, TO_NUMBER 등을 사용한 반면 MySQL에서는 CAST라는 예약어를 활용하여 형변환한다.

Oracle	SELECT TO_CHAR(100) FROM DUAL
MySQL	SELECT CAST(100 AS CHAR) FROM DUAL

 

날짜의 경우

Oracle	SELECT TO_CHAR(writedate, ‘YYYYMMDD HH24MISS’) FROM board
MySQL	SELECT DATE_FORMAT(writedate, ‘%Y%m%d%H%i%s’) FROM board

 

4. SYSDATE

Oracle	SELECT SYSDATE FROM DUAL
MySQL	SELECT NOW() FROM DUAL

 

5. 요일변환

Oracle에서는 일,월,화,수,목,금,토 ⇒ 1,2,3,4,5,6,7 

MySQL에서는 일,월,화,수,목,금,토 ⇒ 0,1,2,3,4,5,6 (자바스크립트와 동일)

Oracle	SELECT TO_DATE(SYSDATE, ‘D’) FROM DUAL
MySQL	SELECT DATE_FORMAT(NOW(), ‘w’) FORM DUAL

 

*날짜표기 방법에 대해 특히 다른 부분이 많으므로 주의

 

6. 문자연결

Oracle	SELECT * FORM board WHERE name LIKE ‘yeon’
MySQL	SELECT * FROM board WHERE name LIKE CONCAT(’yeon’, ‘woojin’)

 

7. Null 처리

Oracle	SELECT NVL(name, ‘X’) FORM table
MySQL	SELECT IFNULL(name, ‘X’) FORM table

 

8. 페이징 처리 방식

Oracle	SELECT * FROM (SELECT ROWNUM, A * FROM (SELECT * FROM DEPT) A) WHERE ROWNUM BETWEEN 0 AND 10;
MySQL	SELECT * FROM DEPT LIMIT 0, 10;

 

 

9. 컬럼 명이 예약어 일때

Oracle	"컬럼명"
MySQL	`컬럼명`

 

 

10. MySQL 예약어 목록

ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	ASENSITIVE	BEFORE
BETWEEN	BIGINT	BINARY
BLOB	BOTH	BY
CALL	CASCADE	CASE
CHANGE	CHAR	CHARACTER
CHECK	COLLATE	COLUMN
CONDITION	CONSTRAINT	CONTINUE
CONVERT	CREATE	CROSS
CURRENT_DATE	CURRENT_TIME	CURRENT_TIMESTAMP
CURRENT_USER	CURSOR	DATABASE
DATABASES	DAY_HOUR	DAY_MICROSECOND
DAY_MINUTE	DAY_SECOND	DEC
DECIMAL	DECLARE	DEFAULT
DELAYED	DELETE	DESC
DESCRIBE	DETERMINISTIC	DISTINCT
DISTINCTROW	DIV	DOUBLE
DROP	DUAL	EACH
ELSE	ELSEIF	ENCLOSED
ESCAPED	EXISTS	EXIT
EXPLAIN	FALSE	FETCH
FLOAT	FLOAT4	FLOAT8
FOR	FORCE	FOREIGN
FROM	FULLTEXT	GRANT
GROUP	HAVING	HIGH_PRIORITY
HOUR_MICROSECOND	HOUR_MINUTE	HOUR_SECOND
IF	IGNORE	IN
INDEX	INFILE	INNER
INOUT	INSENSITIVE	INSERT
INT	INT1	INT2
INT3	INT4	INT8
INTEGER	INTERVAL	INTO
IS	ITERATE	JOIN
KEY	KEYS	KILL
LEADING	LEAVE	LEFT
LIKE	LIMIT	LINES
LOAD	LOCALTIME	LOCALTIMESTAMP
LOCK	LONG	LONGBLOB
LONGTEXT	LOOP	LOW_PRIORITY
MATCH	MEDIUMBLOB	MEDIUMINT
MEDIUMTEXT	MIDDLEINT	MINUTE_MICROSECOND
MINUTE_SECOND	MOD	MODIFIES
NATURAL	NOT	NO_WRITE_TO_BINLOG
NULL	NUMERIC	ON
OPTIMIZE	OPTION	OPTIONALLY
OR	ORDER	OUT
OUTER	OUTFILE	PRECISION
PRIMARY	PROCEDURE	PURGE
READ	READS	REAL
REFERENCES	REGEXP	RELEASE
RENAME	REPEAT	REPLACE
REQUIRE	RESTRICT	RETURN
REVOKE	RIGHT	RLIKE
SCHEMA	SCHEMAS	SECOND_MICROSECOND
SELECT	SENSITIVE	SEPARATOR
SET	SHOW	SMALLINT
SONAME	SPATIAL	SPECIFIC
SQL	SQLEXCEPTION	SQLSTATE
SQLWARNING	SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT	SSL	STARTING
STRAIGHT_JOIN	TABLE	TERMINATED
THEN	TINYBLOB	TINYINT
TINYTEXT	TO	TRAILING
TRIGGER	TRUE	UNDO
UNION	UNIQUE	UNLOCK
UNSIGNED	UPDATE	USAGE
USE	USING	UTC_DATE
UTC_TIME	UTC_TIMESTAMP	VALUES
VARBINARY	VARCHAR	VARCHARACTER
VARYING	WHEN	WHERE
WHILE	WITH	WRITE
XOR	YEAR_MONTH	ZEROFILL

 

 

참고 블로그1 : https://sjparkk-dev1og.tistory.com/23

참고 블로그2 : https://wooj-coding-fordeveloper.tistory.com/31