[DB] Oracle - MariaDB 차이

 

팀장님이 Oracle과 MariaDB 차이점에 대해 분석해오라는 업무를 주셨다!

열심히 정리했는데 아까워서 블로그에도 올려봄 후훗

 

 

Oracle - MariaDB 차이

 

1.  데이터 타입

Oracle			MySQL
1	BFILE	이진자료(BINARY DATA)를 저장, ⇐ 4G	VARCHAR(255)
2	BINARY_FLOAT	32비트 고정 숫자	FLOAT
3	BINARY_DOUBLE	64비트 고정 숫자	DOUBLE
4	BLOB	이진 자료 처리, ⇐ 4G	LONGBLOB
5	CHAR(n), CHARACTER(n)	고정 문자열, 1 ⇐ n ⇐ 255	CHAR(n), CHARACTER(n)
6	CHAR(n), CHARACTER(n)	고정 문자열, 256 ⇐ n ⇐ 2000	VARCHAR(n)
7	CLOB	대용량 데이터를 저장, ⇐ 4G	LONGTEXT
8	DATE	날짜 및 시간	DATETIME
9	DECIMAL(p,s), DEC(p,s)	고정 소수점 숫자	DECIMAL(p,s), DEC(p,s)
10	DOUBLE PRECISION	부동 소수점 숫자	DOUBLE PRECISION
11	FLOAT(p)	부동 소수점 숫자	DOUBLE
12	INTEGER, INT	38자리 정수	INT	DECIMAL(38)
13	INTERVAL YEAR(p) TO MONTH	시간 간격(연, 월)	VARCHAR(30)
14	INTERVAL DAY(p) TO SECOND(s)	시간 간격(일, 시간, 분, 초)	VARCHAR(30)
15	LONG	문자 데이터, ⇐ 2G	LONGTEXT
16	LONG RAW	바이너리 데이터, ⇐ 2G	LONGBLOB
17	NCHAR(n)	고정 길이 UTF-8 문자, 1 ⇐ n ⇐ 255	NCHAR(n)
18	NCHAR(n)	고정 길이 UTF-8 문자, 256 ⇐ n ⇐ 2000	NVARCHAR(n)
19	NCHAR VARYING(n)	고정 길이 UTF-8 문자, 1 ⇐ n ⇐ 4000	NCHAR VARYING(n)
20	NCLOB	가변 길이 유니코드 문자, ⇐ 4G	NVARCHAR(max)
21	NUMBER(p,0), NUMBER(p)	8비트 정수, 1 <= p < 3	TINYINT	(0 to 255)
		16비트 정수, 3 <= p < 5	SMALLINT
		32비트 정수, 5 <= p < 9	INT
		64비트 정수, 9 <= p < 19	BIGINT
		고정 소수점 숫자, 19 <= p <= 38	DECIMAL(p)
22	NUMBER(p,s)	고정 소수점 숫자, s > 0	DECIMAL(p,s)
23	NUMBER, NUMBER(*)	부동 소수점 숫자	DOUBLE
24	NUMERIC(p,s)	고정 소수점 숫자	NUMERIC(p,s)
25	NVARCHAR2(n)	고정 길이 UTF-8 문자, 1 ⇐ n ⇐ 4000	NVARCHAR(n)
26	RAW(n)	가변 길이 이진 문자열, 1 ⇐ n ⇐ 255	BINARY(n)
27	RAW(n)	가변 길이 이진 문자열, 256 ⇐ n ⇐ 2000	VARBINARY(n)
28	REAL	부동 소수점 숫자	DOUBLE
29	ROWID	물리적 행 주소	ROW
30	SMALLINT	38자리 정수	DECIMAL(38)
31	TIMESTAMP(p)	날짜와 시간값	DATETIME(p)
32	TIMESTAMP(p) WITH TIME ZONE	날짜와 시간값(타임존 포함)	DATETIME(p)
33	UROWID(n)	논리적 행 주소, 1 ⇐ n ⇐ 4000	VARCHAR(n)
34	VARCHAR(n)	가변 길이 문자열, 1 ⇐ n ⇐ 4000	VARCHAR(n)
35	VARCHAR2(n)	가변 길이 문자열, 1 ⇐ n ⇐ 4000	VARCHAR(n)
36	XMLTYPE	XML 데이터	LONGTEXT

---

2.  DUAL 테이블

DESC DUAL 구문은 MariaDB에서 작동하지 않으며, 

MySQL의 CURRENT_TIMESTAMP가 시간대를 포함하지 않기 때문에 결과도 다름

 

Oracle (DUAL)

SQL> 
DESC DUAL;
 Name                                      Null?    Type
---------------------------------------- --------- ------------
 DUMMY                                              VARCHAR2(1)

SQL> 
SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP
---------------------------------------------------------------
16-FEB-19 04.16.18.910331 AM +08:00

 

MariaDB (DUAL)

MariaDB [test]> 
DESC DUAL;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DUAL' at line 1

MariaDB [test]> 
SELECT CURRENT_TIMESTAMP FROM DUAL;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2019-02-27 04:11:01 |
+---------------------+
1 row in set (0.000 sec)

---

3.  SYSDATE

	사용 함수	반환 타입
Oracle	SYSDATE	시간 없이 날짜만 반환
MariaDB	SYSDATE()	시간, 날짜 둘 다 반환

 

Oracle (SYSDATE)

SQL> 
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
NOW
-------------------
02-16-2019 04:39:00

SQL> 
SELECT SYSDATE FROM DUAL;
SYSDATE
---------
16-FEB-19

 

MariaDB (SYSDATE())

MariaDB [test]> 
SELECT SYSDATE() FROM DUAL;
+---------------------+
| SYSDATE() |
+---------------------+
| 2019-02-27 04:11:57 |
+---------------------+
1 row in set (0.000 sec)

---

4.  TO_DATE

	사용 함수	반환 타입
Oracle	TO_DATE()	DATE 데이터 유형을 반환
MariaDB	STR_TO_DATE()	DATETIME 데이터 유형을 반환

 

Oracle (TO_DATE)

SQL> 
SELECT TO_DATE ('20190218121212','yyyymmddhh24miss') as "NOW" FROM DUAL;
NOW
-------------------------
18-FEB-19

 

MariaDB (STR_TO_DATE())

MariaDB [test]> 
SELECT STR_TO_DATE('2019-02-18 12:12:12','%Y-%m-%d %H:%i:%s') as "NOW" FROM DUAL;
+---------------------+
| NOW |
+---------------------+
| 2019-02-18 12:12:12 |
+---------------------+
1 row in set (0.000 sec)

---

5.  NULL 및 빈 문자열

	사용 함수
Oracle	NVL, NVL2
MariaDB	IFNULL

 

Oracle (NULL)

SQL> 
SELECT 
NVL(COMPANY_NO,'')
FROM COMPANY_TABLE
WHERE NATION= 'KR';

SELECT 
NVL2(COMPANY_NO,'계열사','비계열사')
FROM COMPANY_TABLE
WHERE NATION= 'KR';

 

MariaDB (NULL)

MariaDB [test]>
SELECT IFNULL(NULL, “TEST”);
--- 
TEST

---

	빈 문자열의 경우
Oracle	NULL로 취급
MariaDB	NULL로 취급하지 않음

 

Oracle (빈 문자열)

SQL> 
SELECT CASE WHEN '' IS NULL THEN 'Yes' ELSE 'No' END AS "Null Eval" FROM dual;
Nul
---
Yes

 

MariaDB (빈 문자열)

MariaDB [test]> 
SELECT CASE WHEN '' IS NULL THEN 'Yes' ELSE 'No' END AS "Null Eval" FROM dual;
+-----------+
| Null Eval |
+-----------+
| No |
+-----------+
1 row in set (0.001 sec)

---

6.  시퀀스

MariaDB 10.3부터 Oracle 호환 시퀀스 및 Oracle PL/SQL과 호환되는 저장 프로시저 언어가 도입됨

MariaDB에서 시퀀스 생성은 Oracle의 SEQUENCE와 매우 유사함

 

Oracle (SEQUENCE)

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;
CREATE SEQUENCE s2 START WITH -100 INCREMENT BY -10;

 

MariaDB (SEQUENCE)

CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10 MINVALUE=-100 MAXVALUE=1000;

---

7.  DML

Oracle의 INSERT/UPDATE/DELETE 문은 MariaDB에서 일치함

Oracle의 INSERT ALL/INSERT FIRST 는 MariaDB에서 지원되지 않음

 

Oracle (DML)

SQL> 
INSERT ALL
INTO CUSTOMERS (customer_id, customer_name, city) VALUES (1000, 'Jase Alagaban', 'Davao City')
INTO CUSTOMERS (customer_id, customer_name, city) VALUES (2000, 'Maximus Aleksandre Namuag', 'Davao City')
SELECT * FROM dual;
2 rows created.

 

MariaDB에서는 삽입을 한 번에 하나씩 실행해야 함

 

 

MariaDB (DML)

MariaDB [test]> 
INSERT INTO CUSTOMERS (customer_id, customer_name, city) VALUES (1000, 'Jase Alagaban', 'Davao City');
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> 
INSERT INTO CUSTOMERS (customer_id, customer_name, city) VALUES (2000, 'Maximus Aleksandre Namuag', 'Davao City');
Query OK, 1 row affected (0.00 sec)

---

8. 계층형 쿼리

Oracle	MariaDB
START WITH..CONNECT BY	CTE(Common Table Expression)

 

Oracle (START WITH..CONNECT BY)

WITH sales_product_year AS (
SELECT
	product,
	YEAR(ship_date) AS YEAR,
	SUM(price) AS total_amt
FROM
	item_sales
GROUP BY
	product,
	YEAR
)
SELECT
	*
FROM
	sales_product_year S1
WHERE
	total_amt >
(
	SELECT
		0.1 * SUM(total_amt)
	FROM
		sales_product_year S2
	WHERE
		S2.year = S1.year)

 

MariaDB (CTE)

WITH RECURSIVE bus_dst AS (
SELECT
	origin AS dst
FROM
	bus_routes
WHERE
	origin = 'New York'
UNION
SELECT
	bus_routes.dst
FROM
	bus_routes,
	bus_dst
WHERE
	bus_dst.dst = bus_routes.origin
)
SELECT
	*
FROM
	bus_dst;

---

9. MariaDB의 PL/SQL?

MariaDB에서 PL/SQL 호환성을 사용할 때마다 다음과 같이 SQL_MODE = 'Oracle'로 설정

SET SQL_MODE=‘ORACLE’;

 

해당 호환성 사용 시 다음 항목들 사용 가능

• 루프 구문
• 변수 선언
• Non-ANSI 저장 프로시저 구성
• 커서 구문
• 저장 프로시저 매개변수
• 데이터 유형 상속(%TYPE, %ROWTYPE)
• PL/SQL 스타일 예외
• 기본 SQL 유형의 동의어(VARCHAR2, NUMBER, …)

 

MariaDB (PL/SQL)

MariaDB [test]> 
CREATE OR REPLACE PACKAGE BODY hello AS
    -> 
    ->   vString VARCHAR2(255) := NULL;
    -> 
    ->   -- was declared public in PACKAGE
    ->   PROCEDURE helloFromS9s(pString VARCHAR2) AS
    ->   BEGIN
    ->     SELECT 'Severalnines showing MariaDB Package Procedure in ' || pString || '!' INTO vString FROM dual;
    ->     SELECT vString;
    ->   END;
    -> 
    -> BEGIN
    ->   SELECT 'called only once per connection!';
    -> END hello;
    -> /
Query OK, 0 rows affected (0.021 sec)


MariaDB [test]> 
DECLARE
    ->   vString VARCHAR2(255) := NULL;
    ->   -- CONSTANT seems to be not supported yet by MariaDB
    ->   -- cString CONSTANT VARCHAR2(255) := 'anonymous block';
    ->   cString VARCHAR2(255) := 'anonymous block';
    -> BEGIN
    ->   CALL hello.helloFromS9s(cString);
    -> END;
    
+----------------------------------+
| called only once per connection! |
+----------------------------------+
| called only once per connection! |
+----------------------------------+
1 row in set (0.000 sec)

+--------------------------------------------------------------------+
| vString                                                            |
+--------------------------------------------------------------------+
| Severalnines showing MariaDB Package Procedure in anonymous block! |
+--------------------------------------------------------------------+
1 row in set (0.000 sec)

Query OK, 1 row affected (0.000 sec)


MariaDB [test]> DELIMITER ;

 

 

출처

https://severalnines.com/blog/migration-oracle-database-mariadb-deep-dive/

Migration from Oracle Database to MariaDB - A Deep Dive | Severalnines

https://solbel.tistory.com/433