Background Image
마이그레이션
2012.11.13 00:02

MySQL에서 CUBRID로 갈아탈 때 알아야 할 것

조회 수 22592 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

요즘 NHN에서 개발하는 서비스에 MySQL 대신 CUBRID를 적용하는 사례가 늘고 있습니다. 이 글은 NHN의 여러 부서에서 MySQL을 CUBRID로 전환하는 과정에서 경험한 두 데이터베이스 간의 차이점을 바탕으로 정리한 것으로, 해당 내용은 크게 '칼럼 타입,' 'SQL 구문,' '제공 기능'의 세 부분으로 나눌 수 있습니다.

이 글을 MySQL 5.5와 CUBRID 2008 R4.1 버전을 기준으로 작성했습니다.

칼럼 타입 관련 차이

문자 타입의 대소문자 구분

MySQL은 기본적으로 질의를 수행할 때 문자 타입의 값에 대해 대소문자를 구분하지 않으므로, MySQL에서 대소문자를 구분하려면 테이블을 생성하거나 질의문을 생성할 때 별도의 BINARY 키워드를 추가해야 한다. 이에 반해 CUBRID는 기본적으로 질의를 수행할 때 문자 타입의 값에 대해 대소문자 구분을 지원한다.

다음은 MySQL에서 테이블을 생성할 때 대상 칼럼에 BINARY를 지정하는 예다.

1
2
3
4
5
CREATE TABLE tbl (name CHAR(10) BINARY);
INSERT INTO tbl VALUES('Charles'),('blues');
SELECT * FROM tbl WHERE name='CHARLES';
  
Empty set (0.00 sec)

다음은 MySQL에서 SELECT 문을 수행할 때 대상 칼럼에 BINARY를 지정하는 예다.

1
2
3
4
5
6
7
SELECT * FROM tbl WHERE BINARY name='Charles';
  
+---------+
| name |
+---------+
| Charles |
+---------+

MySQL에서와 같이 CUBRID에서 대소문자를 구분하지 않게 하려면 다음 예에서 보는 바와 같이 대상 칼럼에 UPPER() 함수 혹은 LOWER() 함수를 적용한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * FROM tbl ORDER BY name;
  
name
======================
'Charles '
'blues '
  
  
SELECT * FROM tbl ORDER BY UPPER(name);
  
name
======================
'blues '
'Charles '

위의 예에서 ORDER BY 칼럼에 UPPER() 함수가 적용되어 ORDER BY를 최적화(skip order by: ORDER BY 순서가 곧 인덱스의 순서인 경우 정렬 과정 없이 인덱스의 순서대로 fetch함)할 수 없다. 이 경우 ORDER BY를 최적화하려면 대문자 혹은 소문자로만 구성된 별도의 칼럼을 생성하고 이 칼럼에 인덱스를 구성하는 방법을 생각해 볼 수 있다.

다음은 대소문자를 구분하지 않는 정렬 전용 칼럼 name2를 별도로 추가한 예다.

1
2
3
4
5
6
7
8
9
ALTER TABLE tbl ADD COLUMN (name2 CHAR(10));
UPDATE tbl SET name2=UPPER(name);
CREATE INDEX i_tbl_name2 ON tbl(name2);
SELECT * FROM tbl ORDER BY name2;
  
name name2
============================================
'blues ' 'BLUES '
'Charles ' 'CHARLES '

날짜 타입의 타입 자동 변환

MySQL은 타입 변환에 매우 유연하다. 숫자 타입에 문자열 입력을 허용하고, 그 반대(문자열 타입에 숫자 입력)도 허용할 뿐 아니라, 날짜 타입에 숫자 입력도 허용한다.

CUBRID는 2008 R4.0 버전부터 유연한 타입 변환을 지원하기 시작하여 숫자 타입에 문자열 입력이나 문자열 타입에 숫자 입력이 가능하다. 하지만 MySQL과 달리 날짜 타입에 숫자 입력은 허용하지 않는다.

다음은 MySQL에서 dt(날짜 타입) 칼럼에 숫자를 입력한 예다.

1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE dt_tbl(dt DATE);
mysql> INSERT INTO dt_tbl VALUES (20120515);
mysql> SELECT * FROM dt_tbl;
  
+------------+
| dt |
+------------+
| 2012-05-15 |
+------------+
1 row in set (0.00 sec)

다음은 CUBRID에서 dt(날짜 타입) 칼럼에 숫자를 입력한 예로 날짜 타입에 숫자를 입력한 경우 결과 값으로 오류가 반환되는 것을 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
csql> CREATE TABLE dt_tbl(dt DATE);
csql> INSERT INTO dt_tbl VALUES (20120515);
  
ERROR: before ' ); '
Cannot coerce 20120515 to type date.
  
csql> INSERT INTO dt_tbl VALUES ('20120515');
csql> SELECT * FROM dt_tbl;
  
dt
============
05/15/2012

날짜 함수 실행 결과로 오류가 발생하는 경우, 기본적으로 MySQL은 NULL을 반환하고 CUBRID는 오류를 반환한다. CUBRID에서 NULL을 반환하게 하려면 시스템 파라미터인 return_null_on_function_errors의 값을 yes로 설정한다.

다음 예는 MySQL에서 날짜 함수에 유효하지 않은 인자를 입력했을 때 NULL이 반환되는 것을 보여준다.

1
2
3
4
5
6
7
8
mysql> SELECT YEAR('12:34:56');
  
+------------------+
| YEAR('12:34:56') |
+------------------+
| NULL |
+------------------+
1 row in set, 1 warning (0.00 sec)

다음 예는 CUBRID에서 시스템 파라미터 return_null_on_function_error 값을 기본 값인 no로 설정한 상태에서 날짜 함수에 유효하지 않은 인자를 입력했을 때 오류가 반환되는 것을 보여준다.

1
2
3
csql> SELECT YEAR('12:34:56');
  
ERROR: Conversion error in date format.

다음 예는 CUBRID에서 시스템 파라미터 return_null_on_function_errors의 값을 yes로 변경한 상태에서 날짜 함수에 유효하지 않은 인자를 입력했을 때 NULL이 반환되는 것을 보여준다.

1
2
3
4
5
csql> SELECT YEAR('12:34:56');
  
year('12:34:56')
======================
NULL

정수 나누기 정수의 결과 값 타입

정수 값끼리 나누기 연산을 수행할 때 MySQL에서는 DECIMAL(m, n)의 실수 타입의 값이 출력되지만, CUBRID에서는 결과 값을 반올림한 정수 타입의 값이 출력된다. CUBRID에서는 피연산자 타입이 동일하면 해당 타입으로 값이 출력되기 때문이다. 이 경우 출력 값의 타입을 실수로 변경하고 싶다면, CAST() 함수를 사용하여 입력 값 타입을 변경하거나, 둘 중 하나의 값을 실수 타입으로 바꾼다.

다음은 MySQL에서 정수 값끼리 나누기 연산을 수행한 예다. 결과 값은 실수 타입으로 출력된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT 4/3;
  
+--------+
| 4/3 |
+--------+
| 1.3333 |
+--------+
  
mysql> SELECT 4/2;
  
+--------+
| 4/2 |
+--------+
| 2.0000 |
+--------+

다음은 CUBRID에서 정수 값끼리 나누기 연산을 수행한 예다. 결과 값은 정수 타입으로 출력된다.

1
2
3
4
5
6
7
8
9
10
11
csql> SELECT 4/3;
  
4/3
=============
1
  
csql> SELECT 4/2;
  
4/2
=============
2

다음은 CUBRID에서 CAST() 함수를 사용하여 정수 값끼리 나누기 연산을 수행한 예다. 결과 값은 실수 타입으로 출력된다.

1
2
3
4
5
csql> SELECT CAST(4 AS DECIMAL(5,4))/CAST(3 AS DECIMAL(5,4));
  
cast(4 as numeric(5,4))/ cast(3 as numeric(5,4))
======================
1.333333333

다음은 CUBRID에서 정수 값을 실수로 나누기 연산을 수행한 예다. 입력 값 중 하나가 실수 타입이므로 결과 값이 실수(DOUBLE) 타입으로 출력된다.

1
2
3
4
5
csql> SELECT 4/3.0;
  
4/3.0
======================
1.333333333

입력 값 타입의 최댓값보다 큰 총합(SUM) 처리

SUM의 결과가 입력 값 타입의 최댓값보다 크면 결과는 어떻게 출력될까?

MySQL에서느 SUM한 결과를 미리 정의한 큰 자릿수의 DECIMAL 타입으로 변환하지만, CUBRID에서는 이를 오버플로우(overflow) 오류로 처리한다. 즉, CUBRID는 입력 칼럼의 타입이 결과 타입을 결정한다. 따라서, CUBRID에서 오버플로우 오류를 방지하려면 SUM의 결과 값을 수용할 수 있는 타입으로 입력 칼럼의 타입을 변환한 후 연산을 수행해야 한다.

이 경우 타입 변환 시에 CAST() 함수의 실행으로 인한 추가 비용이 발생하므로 처음부터 결과 값을 고려하여 칼럼 타입을 결정할 것을 권장한다.

MySQL과 CUBRID 두 데이터베이스에 다음과 같이 테이블을 동일하게 구성한다..

1
2
3
CREATE TABLE t (code SMALLINT);
INSERT INTO t VALUES(32767);
INSERT INTO t VALUES (32767);

MySQL에서는 SUM을 실행한 결과 값이 결과 타입보다 작으므로 정상적으로 값이 출력된다. 하지만 CUBRID에서는 입력 타입으로 출력 타입이 결정되므로 SUM 실행의 결과 값이 결과 타입보다 커서 오버플로우 오류가 발생한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> SELECT SUM(code) FROM t;
  
+-----------+
| sum(code) |
+-----------+
| 65534 |
+-----------+
  
mysql> SHOW COLUMNS FROM ttt;
  
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| sum(code) | decimal(27,0) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
  
csql> SELECT SUM(code) FROM t;
  
ERROR: Overflow occurred in addition context.

다음은 CUBRID에서 칼럼의 타입을 변환한 후 SUM을 실행한 예다. 정상적으로 값이 출력된 것을 알 수 있다.

1
2
3
4
5
csql> SELECT SUM(CAST (CODE AS INT)) FROM t;
  
sum( cast(code as integer))
======================
65534

다음은 CUBRID에서 테이블을 생성하는 단계에서 SUM의 결과 값 크기를 감안하여 칼럼 타입을 잡은 후 SUM을 실행한 예다. 정상적으로 값이 출력된 것을 알 수 있다.

1
2
3
4
5
6
7
8
csql> CREATE TABLE t (code INT);
csql> INSERT INTO t VALUES(32767);
csql> INSERT INTO t VALUES (32767);
csql> SELECT SUM(code) FROM t;
  
sum(code)
======================
65534

VARCHAR 타입의 결과 값 타입

MySQL과 CUBRID 모두 VARCHAR 타입인 칼럼의 값이 숫자로 구성된 문자열인 경우 수치 연산을 허용한다. 이때 연산 결과의 타입은 양쪽 모두에서 DOUBLE이다(단, 수치 연산이 필요한 값을 문자열 타입으로 저장하는 예는 어디까지나 내용 설명을 위한 것일 뿐 바람직한 방법은 아니다).

다음은 MySQL과 CUBRID의 질의 결과 타입을 비교하는 예다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> CREATE TABLE tbl(col VARCHAR(10));
mysql> INSERT INTO tbl VALUES('1'),('2'),('3'),('4'),('5');
mysql> CREATE TABLE ttbl AS SELECT SUM(col) FROM tbl;
mysql> SHOW COLUMNS FROM ttbl;
  
+----------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------+------+-----+---------+-------+
| SUM(col) | double | YES | | NULL | |
+----------+--------+------+-----+---------+-------+
  
  
csql> CREATE TABLE tbl(col VARCHAR(10));
csql> INSERT INTO tbl VALUES('1'),('2'),('3'),('4'),('5');
csql> CREATE TABLE ttbl AS SELECT SUM(col) FROM tbl;
csql> ;sc ttbl
  
  
<class name="">
ttbl
<attributes>
sum(a) DOUBLE
</attributes></class>

그런데, MySQL에서는 레코드의 필드 값에 숫자가 아닌 일반 문자가 있을 경우 이를 0으로 취급하여 연산을 수행하지만, CUBRID에서는 타입을 변환할 수 없다는 오류를 출력한다. 다음 예를 확인해 보자.

1
2
3
4
5
6
7
8
9
10
11
12
mysql> INSERT INTO tbl VALUES('a');
mysql> SELECT SUM(col) FROM tbl;
  
+--------+
| SUM(a) |
+--------+
| 15 |
+--------+
csql> INSERT INTO tbl VALUES('a');
csql> SELECT SUM(col) FROM tbl;
  
ERROR: Cannot coerce value of domain "character varying" to domain "double".

SQL 구문 관련 차이

START WITH … CONNECT BY 지원

CUBRID는 MySQL은 지원하지 않는 계층 관계를 질의로 표현할 수 있는 START WITH … CONNECT BY 구문을 지원한다(참고로 이 구문은 Oracle도 지원).

다음과 같은 데이터를 가지고 상사와 부하 직원을 출력하되, 같은 레벨 간에는 입사일이 빠른 순서로 결과 값을 정렬해 보자. id는 부하 직원의 사번이며, mgrid는 상사의 사번이다.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE tree(id INT, mgrid INT, name VARCHAR(32), birthyear INT);
  
  
INSERT INTO tree VALUES (1,NULL,'Kim', 1963);
INSERT INTO tree VALUES (2,NULL,'Moy', 1958);
INSERT INTO tree VALUES (3,1,'Jonas', 1976);
INSERT INTO tree VALUES (4,1,'Smith', 1974);
INSERT INTO tree VALUES (5,2,'Verma', 1973);
INSERT INTO tree VALUES (6,2,'Foster', 1972);
INSERT INTO tree VALUES (7,6,'Brown', 1981);

MySQL은 계층 관계 구문을 지원하지 않기 때문에 위의 요청을 만족하는 결과 값을 출력하려면 다음과 같은 순서로 여러 개의 질의문을 실행해야 한다.

1
2
3
4
5
6
7
8
9
10
11
1)
SELECT id, mgrid, name, 1 AS level FROM tree WHERE mgrid IS NULL;
  
2)
SELECT id, mgrid, name, 2 AS level FROM tree WHERE mgrid=1;
  
3)
SELECT id, mgrid, name, 2 AS level FROM tree WHERE mgrid=2;
  
4)
SELECT id, mgrid, name, 3 AS level FROM tree WHERE mgrid=6;

위 예를 설명하면 다음과 같다.

  1. mgrid가 NULL인 level 1인 직원을 출력한다. mgrid가 1인 level 2인 직원을 출력한다. mgrid가 2인 level 2인 직원을 출력한다. mgrid가 6인 level 3인 직원을 출력한다.

반면 CUBRID는 계층 관계를 나타내는 질의를 지원하므로 다음과 같이 질의문을 작성할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT id, mgrid, name, LEVEL
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY id;
  
id mgrid name level
===============================================
1 null Kim 1
3 1 Jonas 2
4 1 Smith 2
2 null Moy 1
5 2 Verma 2
6 2 Foster 2
7 6 Brown 3

위 내용은 부모 노드(상사)와 그에 따르는 자식 노드(부하 직원)를 출력하되, 같은 레벨의 자식 노드(부하 직원) 간에는 id 순서로 값을 정렬하라는 것을 의미한다.

집계 함수가 있는 SELECT LIST에 비집계 항목 포함

ONLY FULL GROUP BY

MySQL과 CUBRID는 모두 기본적으로 GROUP BY 절을 실행할 때 GROUP BY 절에 포함되지 않는 비집계 칼럼이 SELECT 리스트에 나타나는 것을 허용한다. 그런데, GROUP BY 절에 포함되지 않은 비집계 칼럼은 여러 개의 값 중 처음으로 fetch되는 레코드의 값을 선택한다. 따라서 MySQL과 CUBRID의 fetch 순서에 따라 이 값이 서로 다를 수 있으므로 주의해야 한다. 비집계 칼럼은 여러 개의 값 중 어떤 값을 선택하는 것이 적합한지 명확하지 않으므로, 가능하면 GROUP BY 절에 포함되지 않은 칼럼이 SELECT 리스트에 나타나지 않도록 ONLY FULL GROUP BY 기능을 활성화한다.

참고로 ONLY FULL GROUP BY 기능을 활성화하려면, MySQL에서는 my.cof 설정 파일에서 sql_mode 값을 ONLY_FULL_GROUP_BY로 설정하고, CUBRID에서는 cubrid.conf 설정 파일에서 only_full_group_by 값을 yes로 설정한다.

CUBRID는 2008 R3.0 버전부터 only_full_group_by 시스템 파라미터를 지원하고 있으며, R3.0 이전 버전에서는 ONLY FULL GROUP BY 기능이 항상 활성화된 것처럼 동작했다.

다음은 MySQL과 CUBRID에서 ONLY FULL GROUP BY 기능이 활성화된 상태에서 GROUP BY를 실행한 결과를 보여주는 예다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE sales_tbl
(dept_no int, name VARCHAR(20) PRIMARY KEY, sales_month int, sales_amount int DEFAULT 100);
INSERT INTO sales_tbl VALUES
(501, 'Stephan', 4, DEFAULT),
(201, 'George' , 1, 450),
(201, 'Laura' , 2, 500),
(301, 'Max' , 4, 300),
(501, 'Chang' , 5, 150),
(501, 'Sue' , 6, 150),
(NULL, 'Yoka' ,4, NULL);
  
SELECT dept_no, avg(sales_amount) FROM sales_tbl
GROUP BY dept_no ORDER BY dept_no;
  
dept_no avg(sales_amount)
================================
NULL NULL
201 475
301 300
501 133

GROUP BY 절이 없는 경우에 SELECT LIST에 비집계 항목이 있는데 질의가 가능한가?

MySQL은 SELECT 리스트에 집계 함수를 사용하는 항목이 있고, 또 다른 어떤 항목의 값이 여러 개인 경우 처음에 fetch되는 값 하나로 수행하는 반면, CUBRID는 이 경우 값을 결정할 수 없다고 판단하고 오류를 반환한다.

집계 함수에 의해 집계되지 않는 칼럼이 SELECT LIST에 존재한다는 것은 해당 칼럼에 대해 여러 개의 값 중 아무 것이나 하나만 선택한다는 의미이므로, 가능한 이런 질의를 실행하지 않는 것이 바람직하다.

다음과 같이 MySQL과 CUBRID에 동일한 데이터를 구성해 보자.

1
2
3
4
5
CREATE TABLE tbl(a int, b date);
INSERT INTO tbl VALUES (1,'20000101');
INSERT INTO tbl VALUES (2,'20000102');
INSERT INTO tbl VALUES (3,'20000103');
INSERT INTO tbl VALUES (4,'20000104');

이 경우 다음과 같은 질의는 MySQL과 CUBRID에서 모두 실행될 수 있다.

1
2
3
4
5
6
7
SELECT COUNT(a), DATE_ADD(MAX(b), INTERVAL 10 DAY) FROM tbl;
  
+----------+-----------------------------------+
| COUNT(a) | DATE_ADD(MAX(b), INTERVAL 10 DAY) |
+----------+-----------------------------------+
| 4 | 2000-01-14 |
+----------+-----------------------------------+

MySQL에서는 a 칼럼의 값이 여러 개인 경우 처음 fetch되는 레코드의 칼럼 값으로 계산하는데, 처음 fetch되는 a 칼럼의 값이 유동적이므로 이 방식은 적합하다고 할 수 없다.

1
2
3
4
5
6
7
mysql> SELECT COUNT(a), DATE_ADD(MAX(b), INTERVAL a DAY) FROM tbl;
  
+----------+----------------------------------+
| COUNT(a) | DATE_ADD(MAX(b), INTERVAL a DAY) |
+----------+----------------------------------+
| 4 | 2000-01-05 |
+----------+----------------------------------+

CUBRID에서는 a 칼럼의 값이 여러 개인 경우 여러 개의 레코드 중 어느 레코드의 칼럼 값을 계산해야 할지 결정할 수 없다고 판단하고 오류를 반환한다.

1
2
3
4
csql> SELECT COUNT(a), DATE_ADD(MAX(b), INTERVAL a DAY) FROM tbl;
  
ERROR: tbl.a is not single valued. Attributes exposed in
aggregate queries must also appear in the group by clause.

위와 같이 오류를 반환하는 경우 이를 실행 가능하게 하려면, 다음 예에서 보는 바와 같이 INTERVAL a의 값이 하나만 나오도록 a를 MAX(a)나 MIN(a)와 같은 형태로 변경한다. 만약 모든 a의 값이 항상 같다면, 이러한 형태로 변경하기 전의 질의를 MySQL에서 수행한 결과와 변경한 후의 질의를 CUBRID에서 수행한 결과는 같을 것이다.

1
2
3
4
5
csql> SELECT COUNT(a), DATE_ADD(MAX(b), INTERVAL MAX(a) DAY) FROM tbl;
  
count(a) date_add(max(b), INTERVAL max(a) DAY)
=====================================================
4 01/08/2000

예약어를 칼럼 이름, 테이블 이름으로 사용

MySQL과 CUBRID 모두 칼럼 이름, 테이블 이름, 별명(alias) 등에 예약어를 사용할 수 없다. 칼럼 이름, 테이블 이름, 별명 등의 식별자에 예약어를 사용하려면 예약어를 따옴표(" 또는 ')와 같은 부호로 감싸야 한다. CUBRID에서는 따옴표 외에 대괄호([ ])를 사용할 수 있다.

DBMS별로 다른 예약어를 지원한다. 예를 들어, CUBRID는 예약어로 사용하는 rownum, type, names, file, size 등을 MySQL은 예약어로 사용하지 않는다. CUBRID 예약어에 대해 좀 더 자세히 알고 싶다면 CUBRID 온라인 매뉴얼(www.cubrid.com/manual)의 "CUBRID SQL 설명서 > 예약어"를 참고한다.

다음은 CUBRID에서 예약어를 칼럼 이름으로 사용하는 예다.

1
2
3
4
5
6
7
CREATE TABLE `names` (`file` VARCHAR(255), `size` INT, `type` CHAR(10));
CREATE TABLE "names" ("file" VARCHAR(255), "size" INT, "type" CHAR(10));
CREATE TABLE [names] ([file] VARCHAR(255), [size] INT, [type] CHAR(10));
SELECT [file], [size], [type] FROM [names];
  
  
csql> CREATE INDEX ON test_tbl(no ASC, ndate DESC);

기능 관련 차이

내림차순 인덱스 지원

MySQL에서는 인덱스를 생성할 때 문법적으로 칼럼의 내림차순(DESC)을 지정할 수 있지만, 실제로는 내림차순 인덱스가 생성되지 않는다. 반면 CUBRID에서는 내림차순 인덱스를 생성할 수 있다.

다음은 CUBRID에서 ndate 칼럼을 내림차순으로 지정하여 인덱스를 생성하는 예다.

1
csql> CREATE INDEX ON test_tbl(no ASC, ndate DESC);

참고로 CUBRID에서 인덱스를 생성할 때 REVERSE 키워드를 사용하면 칼럼에 DESC를 주는 것과 같은 순서로 인덱스를 생성한다.

다음은 CUBRID에서 ndate 칼럼에 대해 내림차순으로 인덱스를 생성하기 위해 REVERSE 키워드와 DESC 키워드를 사용하는 예다.

1
2
3
4
5
1)
CREATE REVERSE INDEX ON test_tbl(ndate);
  
2)
CREATE INDEX ON test_tbl(ndate DESC);

MySQL에서는 내림차순으로 인덱스를 생성할 수 없으므로 역순으로 값이 입력되도록 별도의 칼럼을 추가한 후 오름차순 인덱스를 생성하는 방법을 생각할 수 있다. 예를 들어, DATE'2012-05-18'의 값은 숫자 -20120518로 변환하여 별도의 칼럼에 입력한다.

다음은 MySQL에서 reverse_ndate 칼럼에 대한 오름차순 인덱스를 생성하는 예다.

1
2
3
4
5
6
7
8
9
10
11
1)
CREATE TABLE test_tbl(no INT, ndate DATE, reverse_ndate INT);
  
2)
CREATE INDEX ON test_tbl(ndate ASC);
  
3)
UPDATE test_tbl SET reverse_ndate = -ndate;
  
4)
CREATE INDEX ON test_tbl(reverse_ndate ASC);

하지만 ndate라는 칼럼에 내림차순 인덱스를 생성하여 사용하든, 역순의 값을 가지는 reverse_ndate 칼럼을 만들고 여기에 오름차순 인덱스를 생성하여 사용하든, 한 쪽이 UPDATE 혹은 DELETE이고 다른 한 쪽이 SELECT 혹은 UPDATE, DELETE여서 두 개의 인덱스 스캔이 각각 동시에 수행되는 환경이라면 서로 역방향으로 인덱스 스캔이 이루어지므로, 교착 상태(deadlock)에 빠질 가능성이 높아질 수 있으니 주의한다.

MySQL과 CUBRID 모두 인덱스 노드 간 양방향 링크가 있으므로 질의에서 내림차순으로 정렬(ORDER BY DESC)하더라도 질의 계획기에 의해 내림차순 인덱스를 이용한 순차 탐색보다 오름차순 인덱스로 역탐색하는 비용이 더 적다고 판단되면 오름차순 인덱스를 사용할 수 있다. 다만, 탐색 대상 레코드의 건수가 비교적 적은 경우에만 역탐색이 유리하다고 할 수 있다.

다음은 CUBRID에서 내림차순으로 정렬할 때 오름차순 인덱스를 사용하는 예다.

1
2
3
4
5
csql> CREATE TABLE test_tbl(a INT, b char(1024000));
csql> CREATE INDEX i_test_tbl_a ON test_tbl(a);
csql> INSERT INTO test_tbl (a, b) VALUES (10, 'a'), (20, 'b'), (30, 'c'), (40, 'd'), (50, 'e'), (60, 'f'), (70, 'g'), (80, 'h'), (90, 'i'), (100, 'j');
  
csql> SELECT a FROM test_tbl WHERE a > 70 ORDER BY a DESC;

ROWNUM 지원

ROWNUM은 SELECT 질의의 결과 행에 1부터 순서대로 번호를 매기는 기능으로, 테이블의 칼럼처럼 사용된다. 이를 이용하여 출력되는 레코드에 일련 번호를 붙일 수도 있고, WHERE 절의 조건을 이용하여 질의 결과의 레코드 개수를 제한할 수도 있다.

CUBRID는 ROWNUM을 지원하지만, MySQL은 ROWNUM을 지원하지 않기 때문에 MySQL에서 레코드에 일련 번호를 붙이려면 세션 변수를 사용해야 한다. 질의 결과의 레코드 개수를 제한하기 위한 용도로 MySQL과 CUBRID 모두 LIMIT … OFFSET도 지원하지만, 이 글에서는 ROWNUM만 다룬다.

다음에서 연속되는 두 예는 질의 결과의 레코드에 일련 번호를 출력하고자 할 때 사용하는 방법이다.

MySQL에서는 세션 변수를 이용하여 ROWNUM을 처리한다.

1
2
3
4
5
6
7
8
9
10
11
mysql> CREATE TABLE test_tbl(col CHAR(1));
mysql> INSERT INTO test_tbl VALUES ('a'), ('b'),('c'),('d');
mysql> SELECT @rownum := @rownum + 1 as rownum, col FROM test_tbl WHERE (@rownum := 0)=0;
+--------+------+
| rownum | col |
+--------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+--------+------+

CUBRID에서 ROWNUM을 실행한다.

1
2
3
4
5
6
7
8
csql> SELECT ROWNUM, col FROM test_tbl;
  
rownum col
============================================
1 'a'
2 'b'
3 'c'
4 'd'

ROWNUM은 Oracle에서도 지원하는데, GROUP BY 절, ORDER BY 절을 포함한 구문에서는 ROWNUM을 먼저 실행하고 GROUP BY, ORDER BY 절을 나중에 실행하기 때문에 정렬 순서대로 ROWNUM을 출력하지는 않는다. 따라서, GROUP BY 또는 ORDER BY 정렬을 포함한 질의가 먼저 수행되도록 하려면 이 질의를 FROM 절의 서브쿼리로 삼고 그것에 대한 ROWNUM을 수행해야 한다.

다음은 Oracle에서 ORDERY BY한 순서대로 ROWNUM이 출력되도록 하는 예다.

1
SELECT ROWNUM, contents FROM (SELECT contents ORDER BY date) AS subtbl;

CUBRID는 GROUP BY, ORDER BY 정렬 결과를 순서대로 출력하게 하는 GROUPBY_NUM() 함수와 ORDERBY_NUM() 함수를 지원한다.

MySQL의 예에서는 ROWNUM이 없고 @rownum이라는 세션 변수를 대신 사용했기 때문에 GROUP BY 절과 ORDER BY 절이 있어도 최종 정렬 결과를 가지고 순서대로 값을 출력하지만, CUBRID는 ROWNUM을 이용하므로 서브쿼리를 이용하지 않고 출력되는 번호의 순서를 유지하려면 GROUPBY_NUM() 함수와 ORDERBY_NUM() 함수를 사용해야 한다.

다음에서 연속되는 두 예는 GROUPBY_NUM() 함수를 사용하지 않은 경우와 사용한 경우의 차이를 보여준다.

다음은 ROWNUM으로 결과 레코드 개수를 제한한 후, 해당 레코드 내에서 GROUP BY 정렬을 수행하는 예다.

1
2
3
4
5
6
7
csql> SELECT ROWNUM, host_year, MIN(score) FROM history
WHERE ROWNUM BETWEEN 1 AND 5 GROUP BY host_year;
  
rownum host_year min(score)
=========================================================
6 2000 '03:41.0'
6 2004 '01:45.0'

다음은 GROUP BY로 정렬한 레코드 집합을 대상으로 GROUPBY_NUM() 함수로 결과 레코드 개수를 제한하는 예다.

1
2
3
4
5
6
7
8
9
10
csql> SELECT GROUPBY_NUM(), host_year, MIN(score) FROM history
GROUP BY host_year HAVING GROUPBY_NUM() BETWEEN 1 AND 5;
  
groupby_num() host_year min(score)
==================================================
1 1968 '8.9'
2 1980 '01:53.0'
3 1984 '13:06.0'
4 1988 '01:58.0'
5 1992 '02:07.0'

다음에서 연속되는 두 예는 FOR ORDERBY_NUM() 함수를 사용하지 않은 경우와 사용한 경우의 차이를 보여준다.

다음은 ROWNUM으로 결과 레코드 개수를 제한한 후, 해당 레코드 내에서 ORDER BY 정렬을 수행하는 예다.

1
2
3
4
5
6
7
8
SELECT athlete, score FROM history
WHERE ROWNUM BETWEEN 3 AND 5 ORDER BY score;
  
athlete score
============================================
'Thorpe Ian' '01:45.0'
'Thorpe Ian' '03:41.0'
'Hackett Grant' '14:43.0'

다음은 ORDER BY로 정렬한 레코드 집합을 대상으로 ORDERBY_NUM() 함수로 결과 레코드 개수를 제한하는 예다.

1
2
3
4
5
6
7
SELECT athlete, score FROM history
ORDER BY score FOR ORDERBY_NUM() BETWEEN 3 AND 5;
athlete score
============================================
'Luo Xuejuan' '01:07.0'
'Rodal Vebjorn' '01:43.0'
'Thorpe Ian' '01:45.0'

AUTO_INCREMENT 차이

INSERT를 수행한 이후 생성된 기본 키를 알아야 한다면 어떻게 해야 할까?

LAST_INSERT_ID() 함수는 보통 데이터베이스에 연결한 하나의 프로그램 내에서 AUTO_INCREMENT 속성 칼럼에 값을 INSERT한 후, 해당 칼럼에 가장 마지막으로 INSERT한 값을 구하기 위해 사용된다. 예를 들어, 기본 키(primary key)가 있는 테이블에 값을 INSERT한 후, LAST_INSERT_ID() 함수 값으로 다른 테이블의 외래 키(foreign key) 값을 넣고 싶을 때 사용할 수 있다. MySQL과 CUBRID 모두 이 함수를 지원한다. 하지만 특정 상황에서 오류가 발생할 수 있으므로 CUBRID에서는 LAST_INSERT_ID() 함수를 사용하기 보다는 AUTO_INCREMENT의 값을 직접 얻어오는 방법을 사용할 것을 권장한다. 특정 상황에 대한 상세 설명은 다음과 같다.

MySQL 또는 CUBRID에서 AUTO_INCREMENT 속성 칼럼이 있는 테이블에 레코드를 INSERT하면 해당 칼럼의 값이 1씩 자동으로 증가한다.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE tbl (col INT AUTO_INCREMENT PRIMARY KEY, col2 INT);
INSERT INTO tbl (col2) VALUES(1);
INSERT INTO tbl (col2) VALUES(2);
INSERT INTO tbl (col2) VALUES(3);
SELECT LAST_INSERT_ID();
  
+------------------+
| last_insert_id() |
+------------------+
| 3 |
+------------------+

만약 A 테이블에 INSERT한 후, LAST_INSERT_ID() 함수 값을 가지고 (A 테이블을 참조하는) B 테이블에 외래 키 값을 INSERT하려는 도중 응용 프로그램이나 서버가 비정상으로 종료되면 어떻게 될까? (물론 이 과정이 하나의 트랜잭션이라는 가정하에서 말이다.)

MySQL에서는 AUTO_INCREMENT에 의해 증가한 값을 포함하여 모두 롤백된다. 만일 새로 INSERT하려는 레코드의 AUTO_INCREMENT 값이 3이었다면, 트랜잭션 롤백 이후 재개한 AUTO_INCREMENT 값은 다시 3이 된다. 하지만 CUBRID에서는 AUTO_INCREMENT 값의 증가가 트랜잭션 롤백에 영향을 받지 않는다. 따라서 기존의 AUTO_INCREMENT 값이 3이었다면, 트랜잭션 롤백 이후 재개한 AUTO_INCRMENT의 값은 4가 된다.

MySQL에서는 AUTO_INCREMENT로 지정된 칼럼에 입력된 값을 확인할 방법이 LAST_INSERT_ID() 함수를 사용하는 방법밖에 없지만, CUBRID에서는 LAST_INSERT_ID() 함수를 사용하지 않고도 AUTO_INCREMENT의 값을 직접 얻어올 수 있다. 그 방법은 CUBRID의 SERIAL 객체를 이용하는 것과 같은데, CUBRID에서 AUTO_INCREMENT는 내부적으로 SERIAL 객체로 구현되어 있기 때문이다. CUBRID에서 AUTO_INCREMENT 칼럼을 생성하면, 해당 칼럼의 SERIAL 객체의 이름은 내부적으로 "<테이블이름>_ai_<칼럼이름>"으로 정해진다.

다음은 CUBRID의 AUTO_INCREMENT 칼럼에서 다음 값과 현재 값을 구하는 예다.

1
2
SELECT <테이블이름>_ai_<칼럼이름>.NEXT_VALUE;
SELECT <테이블이름>_ai_<칼럼이름>.CURRENT_VALUE;

CUBRID의 현재 버전에서는 다음과 같은 특정 상황에서 LAST_INSERT_ID() 함수에 오작동이 있을 수 있으므로 LAST_INSERT_ID() 함수를 사용하는 대신 AUTO_INCREMENT의 값을 직접 얻어오는 방법을 사용할 것을 권장한다. LAST_INSERT_ID()는 데이터베이스의 연결 단위마다 값을 관리하는 세션 변수 값의 하나인데, CUBRID에서 브로커가 절체되는 경우 혹은 HA 환경에서 마스터 노드 절체로 인해 failover가 발생하는 경우 등에서 오작동을 일으켜 기대한 값을 출력하지 못할 수 있다. 이는 추후 수정할 예정이다.

다음 예에서는 다음 AUTO_INCREMENT 값을 얻기 위한 중간 저장소로 세션 변수를 사용했는데, 응용 프로그램에서는 프로그램 내의 변수를 중간 저장소로 사용하면 된다. 참고로 CUBRID의 SERIAL은 Oracle의 SEQUENCE와 같은 기능을 수행한다.

다음은 SERIAL의 NEXT_VALUE 값을 얻어온 다음 기본 키와 외래 키로 각각 INSERT하는 예다.

1
2
3
csql> CREATE TABLE tblPK (col INT AUTO_INCREMENT PRIMARY KEY, col2 INT);
  
csql> CREATE TABLE tblFK (colfk INT AUTO_INCREMENT PRIMARY KEY, colfk2 INT, CONSTRAINT fk_col FOREIGN KEY (colfk2) REFERENCES tblPK (col));

이때 tblPK의 AUTO_INCREMENT에 의해 내부적으로 생성되는 SERIAL의 이름은 tblpk_ai_col인데, 이는 SELECT name FROM db_serial WHERE class_name='tblpk';로 확인할 수 있다.

tblPK의 기본 키와 tblFK의 외래 키에 직접 tblPK의 AUTO_INCREMENT 값을 넣는다.

tblFK의 colfk는 다른 테이블에서 참조하지 않으므로 별도의 변수에 저장할 필요가 없다.

1
2
3
csql> SET @a=(SELECT tblpk_ai_col.NEXT_VALUE);
csql> INSERT INTO tblPK VALUES (@a,10);
csql> INSERT INTO tblFK(colfk2) VALUES (@a);

iBatis의 sqlmaps에서 tblPK에 값을 INSERT할 때의 기본 키 값을 반환하는 코드는 다음 예와 같이 작성할 수 있다. tblFK의 외래 키는 "insert" 객체에서 반환된 값을 사용하면 된다.

1
2
3
4
5
6
7
<insert id="insert">
<selectkey keyproperty="id" resultclass="int">
SELECT tblpk_ai_col.NEXT_VALUE
</selectkey>
INSERT INTO tblPK(col, col2) VALUES (#id#,
#col2#)
</insert>

CHAR_LENGTH와 같은 문자열 함수의 길이 차이

문자열 함수에서 MySQL은 문자의 개수를 기준으로 길이를 계산하지만, CUBRID는 문자의 바이트 길이를 기준으로 길이를 계산한다. 이 차이는 문자셋(character set)의 지원 여부에 따른 것인데, MySQL은 문자셋을 지원하여 문자셋에 따라 문자의 길이를 계산하여 처리하지만, CUBRID는 문자셋을 지원하지 않아 모든 데이터를 바이트로 간주하기 때문에 바이트 길이로 처리한다.

UTF-8 문자셋의 경우, 한글 한 글자의 바이트 길이는 3바이트이며, 이때 CUBRID에서 CHAR_LENGTH의 반환 값은 한글인 경우 문자 개수의 3배가 될 수 있다. CUBRID에서는 문자의 길이와 관련된 POSITION, RPAD, SUBSTR 등의 함수 역시 입력 인자로 글자 길이가 아닌 바이트 길이를 사용한다. 테이블을 생성하면서 CHAR나 VARCHAR의 길이를 지정할 때도 바이트 길이를 사용한다. (올 하반기에 출시될 UTF-8 문자셋을 지원하는 CUBRID 버전에서는 바이트 길이가 아닌 문자 길이로 계산하도록 변경할 예정이다.)

다음은 CUBRID에서 CHAR_LENGTH의 수행 결과를 보여주는 예다.

1
2
3
4
5
SELECT CHAR_LENGTH('큐브리드a');
  
char length('큐브리드a')
==================
13

레코드셋 fetch 도중 커밋 이후 커서 유지

응용 프로그램이 명시적인 커밋 혹은 자동 커밋 이후에도 질의 결과의 레코드셋을 유지하여 다음 레코드를 fetch할 수 있도록 하는 것을 커서 유지(cursor holdability)라고 한다. 이와 관련하여 JDBC 스펙에는 ResultSet.HOLD_CURSORS_OVER_COMMIT과 ResultSet.CLOSE_CURSORS_AT_COMMIT을 지원한다. MySQL과 CUBRID 모두 conn.setHoldability() 함수를 이용한 설정은 무시하며, MySQL은 항상 HOLD_CURSORS_OVER_COMMIT, CUBRID는 항상 CLOSE_CURSORS_AT_COMMIT으로 동작한다. (그럼에도 불구하고 MySQL에서 conn.getHoldability()를 호출하면 현재의 동작과는 반대되는 CLOSE_CURSORS_AT_COMMIT 값을 반환한다. http://www.mysql.com/downloads/connector/j/의 mysql-connector-java-5.1.20.tar.gz의 소스 코드를 참고한다.)

즉, MySQL은 fetch 도중 커밋이 발생해도 커서를 유지하지만 CUBRID는 fetch 도중 커밋이 발생하면 커서를 닫는다. 따라서 현재의 CUBRID 2008 R4.x 이하 버전에서 SELECT한 레코드셋을 fetch하는 도중 커서를 유지하도록 하려면 자동 커밋 모드를 false로 설정하고 트랜잭션을 커밋하지 않은 상태에서 fetch해야 한다. (하반기에 출시될 CUBRID의 새 버전에서는 레코드셋을 닫기 전까지는 커밋 여부에 관계없이 커서를 유지하도록 변경할 예정이다.)

1
2
3
CREATE TABLE tbl1(a INT);
INSERT INTO tbl1 VALUES (1),(2),(3),(4);
CREATE TABLE tbl2(a INT);

다음 예는 자동 커밋이 true(autocommit=true)인 상태에서 테이블 tbl1에 4건의 데이터가 있고, 이를 SELECT하여 테이블 tbl2에 INSERT하는 프로그램을 보여준다. 이를 수행하면, MySQL은 자동 커밋이 수행되어도 커서가 유지되어 tbl2에 4건이 들어간다. 이에 비해, CUBRID는 INSERT 수행 시 자동 커밋이 수행되어 커서를 닫게 되고, 더 이상 fetch를 진행하지 않게 되어 tbl2에 1건만 들어가게 된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public static void executeTr(Connection conn) {
stmt=conn.createStatement();
stmt2=conn.createStatement();
String q1 = "SELECT a FROM tbl1";
String q2;
ResultSet rs = stmt.executeQuery(q1);
conn.commit();
while (rs.next())
{
int a = rs.getInt("a");
q2="INSERT INTO tbl2 VALUES ("+a+")";
stmt2.executeUpdate(q2);
}
stmt.close();
stmt2.close();
}

마치며

이상과 같이 MySQL에서 CUBRID로 전환하기 전에 알아야 할 두 데이터베이스 간의 차이에 대해 알아 보았다. 이러한 차이들을 이해하여 CUBRID를 서비스에 적용할 때 도움이 되었으면 한다.

참고 자료

큐브리드 공부하기 카페: http://helloworld.naver.com/helloworld/89910 참조


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
17 마이그레이션 CUBRID vs MySQL vs ORACLE SQL 타입별 비교 정만영 2013.07.12 69109
16 마이그레이션 타 DBMS를 CUBRID로 마이그레이션 시 varchar,char 컬럼 사이즈 관련 손승일 2010.07.01 38012
15 마이그레이션 MySQL의 limit 명령어 처리 1 admin 2008.11.21 32682
14 마이그레이션 CUBRID 하위버젼에서 CUBRID2008로의 업그레이드 방법 1 남재우 2008.12.06 30153
13 마이그레이션 CUBRID는 MySQL의 varchar(255)와 동일한 길이가 아닌가요? 손승일 2009.12.28 27784
12 마이그레이션 데이터베이스 마이그레이션(unloaddb & loaddb) 의 효과적인 수행방법 file cubebridge 2012.04.14 24534
11 마이그레이션 MySQL+XE를 CUBRID+XE로 운영하기 – mysqldump파일과 CMT사용 file cubebridge 2012.11.13 23069
» 마이그레이션 MySQL에서 CUBRID로 갈아탈 때 알아야 할 것 cubebridge 2012.11.13 22592
9 마이그레이션 타 DB 에서 마이그레이션시 질의 변경 샘플 - CASE 문 웁쓰 2009.12.31 21921
8 마이그레이션 오라클 to CUBRID로 마이그레이션 수행 시 주의사항 정만영 2012.03.02 21056
7 마이그레이션 오라클의 order by 시 first와 last 대체 사용법 file cubebridge 2012.11.12 20561
6 마이그레이션 CUBRID와 Oracle의 NULL과 '' (empty string)의 처리 차이점 1 권호일 2015.12.29 15340
5 마이그레이션 MySQL에서 사용하는 스키마 변환시 주의 사항 정만영 2012.03.02 13251
4 마이그레이션 CUBRID Migration Tookit 8.4.1 file cubebridge 2012.04.14 11079
3 마이그레이션 CM(CUBRID Manager)을 이용하여 행정표준코드시스템의 기관코드 가져오기 file 권호일 2015.12.28 6325
2 마이그레이션 MySQL 테이블 크기 조사하기-공유 주현 2015.12.31 6197
1 마이그레이션 CMT를 이용하여 원본 특정 테이블의 일부 데이터만 가져와 대상 테이블에 넣기 엄기호 2016.01.28 4550
Board Pagination Prev 1 Next
/ 1

Contact Cubrid

대표전화 070-4077-2110 / 기술문의 070-4077-2113 / 영업문의 070-4077-2112 / Email. contact_at_cubrid.com
Contact Sales