SQL 타입별 비교 (CUBRID, MySQL, Oracle)
CUBRID vs MySQL vs ORACLE SQL 타입별 비교
CUBRID 8.4.1 vs MySQL 5.5.27 vs ORACLE 10g 에 대한 SQL 관련 정보들을 비교한다.
1. CUBRID와 데이터 타입
1). DBMS별 데이터 타입 비교
구분
|
CUBRID
|
MySQL
|
Oracle
|
수치형
|
-
|
-
|
BINARY_FLOAT
|
BIGINT
|
BIGINT
|
-
|
|
NUMERIC(20)
|
|||
DECIMAL
|
DECIMAL
|
DECIMAL
|
|
DOUBLE
|
DOUBLE
|
BINARY_DOUBLE
|
|
DOUBLE PRECISION
|
DOUBLE PRECISION
|
-
|
|
FLOAT
|
FLOAT
|
FLOAT
|
|
INT
|
MEDIUMINT
|
-
|
|
INT, INTEGER
|
|||
SMALLINT
|
|||
INTEGER
|
INTEGER
|
-
|
|
MONETARY
|
-
|
-
|
|
NUMERIC(p, s)
|
-
|
NUMBER(p, s)
|
|
NUMERIC(38, 0)
|
-
|
NUMBER(p,0)
|
|
NUMERIC(38, 15)
|
-
|
NUMBER
|
|
NUMERIC(M,D)
|
FLOAT(M,D) or REAL(M,D)
|
-
|
|
REAL
|
-
|
-
|
|
SHORT
|
-
|
-
|
|
SMALLINT
|
BIT(1) (>MySQL-5.0)
|
-
|
|
BOOL, BOOLEAN
|
|||
TINYINT
|
|||
SMALLINT[(M)]
|
|||
비트열
|
BIT VARYING (1073741823)
|
LONGBLOB
|
BLOB
|
LONG RAW
|
|||
BIT VARYING(16277215)
|
MEDIUMBLOB
|
|
|
BIT VARYING(255)
|
TINYBLOB
|
-
|
|
BIT VARYING(65535)
|
BLOB
|
-
|
|
BIT(n)
|
BIT( > 1) (>MySQL-5.0)
|
-
|
|
BITVARYING(n)
|
BINARY(M)
|
RAW(n)
|
|
VARBINARY(M)
|
|||
날짜/시간
|
DATE
|
DATE
|
DATE
|
: MM/DD/YYYY
|
: YYYY-MM-DD
|
: YY/MM/DD
|
|
DATETIME
|
DATETIME
|
TIMESTAMP
|
|
: YYYY-MM-DD HH:MI:SS.FFF
|
: YYYY-MM-DD HH:MI:SS
|
TIMESTAMP WITH LOCAL TIMESTAMP WITH TIME ZONE
|
|
|
|
: YY/MM/DD HH:MI:SS.FFFF
|
|
TIME
|
TIME
|
-
|
|
: HH:MI:SS
|
: HH:MI:SS
|
||
TIMESTAMP
|
TIMESTAMP
|
TIMESTAMP
|
|
: YYYY-MM-DD HH:MI:SS
|
: YYYY-MM-DD HH:MI:SS
|
: YY/MM/DD HH:MI:SS.FFFFFFFFF
|
|
-
|
YEAR[(2|4)]
|
-
|
|
: YYYY
|
|||
문자열
|
CHAR
|
CHAR
|
CHAR
|
NCHAR VARYING
|
-
|
-
|
|
NCHAR
|
-
|
NCHAR
|
|
VARCHAR(16277215)
|
MEDIUMTEXT
|
-
|
|
VARCHAR(255)
|
TINYTEXT
|
INTERVAL DAY TO SECOND
|
|
VARCHAR(255)
|
ENUM('value1','value2',...)
|
INTERVAL YEAR TO MONTH
|
|
VARCHAR(64)
|
-
|
ROWID
|
|
VARCHAR(64)
|
-
|
UROWID
|
|
VARCHAR(65535)
|
TEXT
|
-
|
|
VARCHAR
|
VARCHAR
|
NVARCHAR2
|
|
VARCHAR2
|
|||
BLOB
|
BLOB
|
BLOB
|
BLOB
|
/CLOB
|
CLOB
|
-
|
CLOB
|
집합형
|
SET(VARCHAR(255))
|
SET('value1','value2',...)
|
-
|
MULTISET
|
-
|
-
|
|
LIST
|
-
|
-
|
|
SEQUENCE
|
-
|
-
|
|
STRING
|
LONGTEXT
|
CLOB
|
|
-
|
-
|
LONG
|
|
-
|
-
|
NCLOB
|
2). 마이그레이션 할 때 권장하는 데이터 타입 맵핑 테이블- MySQL to CUBRID
Date Types in MySQL
|
Date Types in CUBRID
|
BIT(1) (>MySQL-5.0)
|
SMALLINT
|
BIT( > 1) (>MySQL-5.0)
|
BIT(n)
|
TINYINT
|
SMALLINT
|
BOOL, BOOLEAN
|
SMALLINT
|
SMALLINT[(M)]
|
SMALLINT
|
INT
|
|
MEDIUMINT[(M)]
|
INT
|
INT, INTEGER[(M)]
|
INT
|
BIGINT
|
|
BIGINT[(M)]
|
BIGINT
|
NUMERIC(20)
|
|
FLOAT[(M,D)]
|
FLOAT
|
DOUBLE[(M,B)]
|
DOUBLE
|
DECIMAL[(M[,D])]
|
DOUBLE
|
DATE
|
DATE
|
DATETIME
|
DATETIME
|
TIMESTAMP[(M)]
|
TIMESTAMP
|
TIME
|
TIME
|
YEAR[(2|4)]
|
CHAR(4)
|
CHAR(M)
|
CHAR(n)
|
VARCHAR(M) [BINARY]
|
VARCHAR(n)
|
BINARY(M)
|
BIT VARYING(n)
|
VARBINARY(M)
|
BIT VARYING(n)
|
TINYBLOB
|
BIT VARYING(255)
|
TINYTEXT
|
VARCHAR(255)
|
BLOB
|
BIT VARYING(65535)
|
TEXT
|
VARCHAR(65535)
|
MEDIUMBLOB
|
BIT VARYING(16277215)
|
MEDIUMTEXT
|
VARCHAR(16277215)
|
LONGBLOB
|
BIT VARYING (1073741823)
|
LONGTEXT
|
STRING
|
ENUM('value1','value2',...)
|
VARCHAR(255)
|
SET('value1','value2',...)
|
SET(VARCHAR(255))
|
FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D)
|
NUMERIC(M,D)
|
Date Types in Oracle
|
Date Types in CUBRID
|
BINARY_DOUBLE
|
DOUBLE
|
BINARY_FLOAT
|
FLOAT
|
BLOB
|
BIT VARYING (1073741823)
|
CHAR(n)
|
CHAR(n)
|
CLOB
|
STRING
|
DATE
|
DATE
|
DECIMAL(p, s)
|
NUMERIC(p, s)
|
FLOAT
|
FLOAT
|
INTERVAL DAY TO SECOND
|
VARCHAR(255)
|
INTERVAL YEAR TO MONTH
|
VARCHAR(255)
|
LONG
|
STRING
|
LONG RAW
|
BIT VARYING (1073741823)
|
NCHAR(n)
|
CHAR(n)
|
NCLOB
|
STRING
|
NUMBER(p, s)
|
NUMERIC(p, s)
|
NUMBER
|
NUMERIC(38, 15)
|
NUMBER(p,0)
|
NUMERIC(38, 0)
|
NVARCHAR2(n)
|
VARCHAR(n)
|
RAW(n)
|
BIT VARYING(n)
|
ROWID
|
VARCHAR(64)
|
TIMESTAMP
|
DATETIME
|
TIMESTAMP WITH LOCAL TIME ZONE
|
DATETIME
|
TIMESTAMP WITH TIME ZONE
|
DATETIME
|
UROWID
|
VARCHAR(64)
|
VARCHAR2(n)
|
VARCHAR(n)
|
2. CUBRID 지원 연산자와 함수
1). CUBRID 지원연산자
구분
|
CUBRID 연산자
|
MySQL 지원여부
|
논리 연산자
|
AND, &&
|
O
|
OR, ||
|
O
|
|
XOR
|
O
|
|
NOT, !
|
O
|
|
비교 연산자
|
=
|
O
|
<=>
|
O
|
|
<>, !=
|
O
|
|
>
|
O
|
|
<
|
O
|
|
>=
|
O
|
|
<=
|
O
|
|
IS boolean_value
|
O
|
|
IS NOT boolean_value
|
O
|
|
산술 연산자
|
+
|
O
|
-
|
O
|
|
*
|
O
|
|
/
|
O
|
|
DIV
|
O
|
|
%, MOD
|
O
|
|
포함 연산자
|
A SETEQ B
|
X
|
A SETNEQ B
|
X
|
|
A SUPERSET B
|
X
|
|
A SUBSET B
|
X
|
|
A SUPERSETEQ B
|
X
|
|
A SUBSETEQ B
|
X
|
|
비트 연산자
|
&
|
O
|
|
|
O
|
|
^
|
O
|
|
~
|
O
|
|
<<
|
O
|
|
>>
|
O
|
2). CUBRID 지원함수
구분
|
CUBRID 함수(옵션)
|
MySQL지원여부
|
비트함수
|
BIT_AND(expr)
|
O
|
BIT_OR(expr)
|
O
|
|
BIT_XOR(expr)
|
O
|
|
BIT_COUNT (expr)
|
O
|
|
문자열 함수
|
ASCII(str)
|
O
|
BIN( n )
|
O
|
|
BIT_LENGTH ( string )
|
O
|
|
CHAR_LENGTH( string )
|
O
|
|
CHARACTER_LENGTH( string )
|
O
|
|
CHR( number_operand )
|
CHAR
|
|
CONCAT( string1, string2 [,string3 [, ... [, stringN]...]])
|
O
|
|
CONCAT_WS( string1, string2 [,string3 [, ... [, stringN]...]])
|
O
|
|
ELT(N, string1, string2, ... )
|
O
|
|
ELT(N, string1, string2, ... )
|
O
|
|
FIELD( search_string, string1 [,string2 [, ... [, stringN]...]])
|
O
|
|
FIND_IN_SET(str, strlist)
|
O
|
|
INSERT( str, pos, len, string )
|
O
|
|
INSTR( string , substring [, position] )
|
O
|
|
LCASE ( string )
|
O
|
|
LEFT( string , length )
|
O
|
|
LENGTH( string )
|
O
|
|
LENGTHB( string )
|
X
|
|
LOCATE ( substring, string [, position] )
|
LOCATE
|
|
LOAD_FILE
|
||
LOWER ( string )
|
O
|
|
LPAD( char1, n, [, char2 ] )
|
O
|
|
LTRIM( string [, trim_string])
|
O
|
|
MID( string, position, substring_length )
|
O
|
|
OCTET_LENGTH ( string )
|
O
|
|
POSITION ( substring IN string )
|
O
|
|
REPEAT( string, count )
|
O
|
|
REPLACE( string, search_string [, replacement_string ] )
|
O
|
|
REVERSE( string )
|
O
|
|
RIGHT( string , length )
|
O
|
|
RPAD( char1, n, [, char2 ] )
|
O
|
|
RTRIM( string [, trim_string])
|
O
|
|
SPACE(N)
|
O
|
|
STRCMP( string1 , string2 )
|
O
|
|
SUBSTR( string, position [, substring_length])
|
O
|
|
SUBSTRING( string FROM position [FOR substring_length] )
|
O
|
|
SUBSTRING( string, position [, substring_length])
|
O
|
|
SUBSTRING_INDEX (string, delim, count)
|
O
|
|
TRANSLATE( string, from_substring, to_substring )
|
& |
등록된 코멘트가 없습니다.