[SQLite] 동적 자료형 (Dynamic Datatype)

보통 대부분의 테이블 컬럼은 특정 자료형만 저장할 수 있는 정적 자료형(Static datatype)을 사용한다. 즉 Integer 타입으로 선언된 컬럼은 Integer 값만 저장할 수 있고 Float 타입의 값은 저장할 수 없다는 것이다.

그러나 SQLite3에서는 일반적인 다른 DBMS와는 달리 동적 자료형(Dynamic datatype)을 지원하고 있다. SQLite3에서는 테이블을 생성할 때 이전에 했던 것 같이 컬럼의 자료형을 명시하지 않아도 된다.

 

sqlite> CREATE TABLE tblDynamic (id, value); sqlite> INSERT INTO tblDynamic VALUES (1, 'Test'); sqlite> INSERT INTO tblDynamic VALUES ('index', 100); sqlite> SELECT * FROM tblDynamic;   id          value      ----------  ---------- 1           Test       index       100

 

SQLite3에서는 이러한 동적 자료형 지원을 위해 스토리지 클래스 (Storage Class)와 타입 선호도(Type Affinity)라는 개념을 도입했다.

 

 

 

1. 스토리지 클래스 (Storage Class)

 

 

 

스토리지 클래스는 각각의 값에 할당되는 태그의 정보로서 SQLite3의 모든 값은 총 5개의 스토리지 클래스 중 하나에 속한다. 스토리지 클래스는 컬럼에 대한 자료형이 아닌 '값' 자체에 할당되는 것임을 주의하자.

 

스토리지 클래스	설명
NULL	값 자체가 NULL
INTEGER	부호를 갖는 정수
REAL	부호를 갖는 실수 (8 Byte)
TEXT	문자열
BLOB	바이너리 데이터 (Binary Large Object)

 

INTEGER의 경우는 가변 크기 할당을 통해 디스크 공간 사용을 최소화 한다. 값의 크기에 따라 1, 2, 3, 4, 6, 7 Byte로 저장되며 디스크에서 로딩되어 메모리 공간으로 올라갈 때는 내부적으로 고정 길이 8 Byte로 변환된다.

 

TEXT의 경우 기본 인코딩은 UTF-8이며, UTF-16BE, UTF-16LE로 변경 가능하다.

 

BLOB은 외부에서 전달된 바이너리 데이터를 그대로 저장한다.

 

SQLite3에서는 Boolean 타입이 별도로 존재하지 않기 때문에 이를 표현하기 위해 INTEGER값을 이용하며 거짓(False)는 '0', 참(True)은 '1'로 사용된다.

 

날짜/시간 데이터 저장을 위하여 TEXT("YYYY-MM-DD HH:MM:SS.SSS"), REAL(줄리안 날짜), INTEGER(유닉스 시간) 형태로 사용할 수 있고 이러한 형식으로 저장하고 값 추출을 위해 별도의 내장 함수를 지원한다.

 

 

 

2. 타입 선호도 (Type Affinity)

 

 

 

sqlite> CREATE TABLE tblType(id INTEGER, name TEXT);   sqlite> INSERT INTO tblType VALUES (1, 'Text'); sqlite> INSERT INTO tblType VALUES ('2', 10.1); sqlite> INSERT INTO tblType VALUES ('2', 10.);  sqlite> INSERT INTO tblType VALUES ('2', 10);  sqlite> SELECT * FROM tblType;   id          name       ----------  ---------- 1           Text       2           10.1       2           10.0       2           10

-> (INTEGER, TEXT) -> (TEXT, REAL) -> (TEXT, REAL) -> (TEXT, INTEGER)

 

위에 타입 선호도를 위해 만든 테이블을 확인해 보자. 테이블을 생성할 때 두 컬럼이 INTEGER와 TEXT 형을 가지도록 해주었다. 그러나 그 이후에 INTEGER, TEXT 형이 아닌 값들을 입력해도 어떠한 에러도 발생시키지 않고 정상적으로 입력되는 되는 것을 확인할 수 있다. 그렇다면 이렇게 입력된 값들이 어떠한 타입으로 저장되었는 지 한번 확인 해 보자

 

sqlite> SELECT id, typeof(id), name, typeof(name) FROM tblType;   id          typeof(id)  name        typeof(name) ----------  ----------  ----------  ------------ 1           integer     Text        text         2           integer     10.1        text         2           integer     10.0        text         2           integer     10          text

 

위에서 확인해 볼 수 있듯이 아까 id 컬럼에 TEXT형으로 저장한 값들도 모두 INTEGER로 저장된 것을 확인할 수 있다. name 컬럼도 마찬가지로 REAL, INTEGER로  입력했던 값들이 모두 TEXT로 변환 된 것을 확인할 수 있다. 이렇게 되는 이유는 SQLite3의 경우 컬럼의 타입 선호도에 따라 데이터의 값이 형변환 되어 저장되기 때문이다. 주의할 점은 비록 우리가 테이블 생성 시 컬럼의 데이터 타입을 명시적으로 설정해 주었다 하더라도 만약 입력되는 값이 해당 타입으로 형변환이 불가능하면 명시한 타입이 아닌 형변환 가능한 타입으로 변환된다는 것이다.

 

sqlite> INSERT INTO tblType VALUES ('2b', 'Test');   sqlite> SELECT id, typeof(id), name, typeof(name) FROM tblType;   id          typeof(id)  name        typeof(name) ----------  ----------  ----------  ------------ 1           integer     Text        text         2           integer     10.1        text         2           integer     10.0        text         2           integer     10          text         2b          text        Test        text

 

위의 예제를 살펴보면 비록 id 컬럼의 데이터 타입이 INTEGER로 명시되어 있었지만 입력하고자 하는 '2b'값이 INTEGER 형으로 변환 불가능해 TEXT 형으로 저장된 것을 볼 수 있다. 즉 테이블 생성 시 컬럼에 특정 타입을 선언하더라도 그거라는 무관하게 모든 스토리지 클래스 타입의 데이터로 변환/저장 될 수 있다.

 

 

타입 선호도	설명
TEXT	NULL, TEXT, BLOB 형태의 스토리지 클래스를 이용해서 저장 숫자형태 (INTEGER, REAL)의 데이터가 이 컬럼에 입력되면 문자열로 형변환 되어 저장
NUMERIC	문자 데이터가 NUMERIC 선호도를 갖는 컬럼에 입력되는 경우 무손실, 가역적인 경우에만 INTEGER나 REAL로 형변환 가능. 형변환 시 손실이 발생한다면 TEXT로 저장 NULL이나 BLOB은 형변환 없이 저장
INTEGER	NUMERIC 선호도와 동일 (CAST 연산 제외)
REAL	강제로 정수 타입을 부동 소수점 형태로 저장하는 것을 제외하면 NUMERIC과 동일
NONE	특별한 타입 선호도 없이 입력되는 값이 형변환 없이 그대로 입력 테이블 생성 시 BLOB이거나 인식할 수 없는 데이터 타입인 경우 NONE 선호도 지정

 

 

 

3. 명시적 형변환

 

 

 

CAST 연산자를 이용하면 명시적으로 데이터의 스토리지 클래스를 변경할 수 있다.

 

CAST([표현식] AS [스토리지 클래스 타입])

 

 

sqlite> SELECT 10.10,     ...> CAST (10.10 AS INTEGER),    ...> CAST (10.10 AS TEXT);   10.10       CAST (10.10 AS INTEGER)  CAST (10.10 AS TEXT) ----------  -----------------------  -------------------- 10.1        10                       10.1

 

부동 소수점 데이터를 INTEGER로 형변환하면서 소수부가 버려지는 데이터 손실이 발생했다. 이에 반해 TEXT로 변환하는 경우에는 데이터 손실 없이 변환되는 것을 확인할 수 있다.

 

이번에는 숫자와 문자열이 포함되어 있는 데이터를 명시적으로 형변환 시켜보도록 하자.

 

sqlite> SELECT '100ABC200',    ...> CAST ('100ABC200' AS INTEGER),    ...> CAST ('100ABC200' AS REAL);   '100ABC200'  CAST ('100ABC200' AS INTEGER)  CAST ('100ABC200' AS REAL) -----------  -----------------------------  -------------------------- 100ABC200    100                            100.0

 

문자와 숫자가 함께 존재하는 TEXT 데이터를 INTEGER와 REAL로 형변화 하는 경우 최대한 변환할 수 있는 만큼의 값으로 변환한 것을 알 수 있다. 만약 공백을 가지고 있는 데이터의 경우 공백은 자동으로 제거된 후 형변환 되며 형변환이 불가능한 값은 0, 0.0으로 변환된다.

 

 

 

4. 내장 정렬 함수

 

 

 

두 문자열을 정렬하기 위해 어떤 문자열이 더 큰지 비교할 때 사용하는 함수를 정렬 함수 (Collating Function)라고 한다. 동일한 데이터라고 하더라도 정렬 방법에 따라 조회한 결과 집합의 순서가 달라 질다. SQLite3에서는 기본적으로 3개의 내장 정렬 함수를 제공한다.

 

내장 정렬 함수	설명
BINARY	문자열을 인코딩과 상관없이 memcmp()함수 (C언어)와 동일한 방식으로 비교
NOCASE	알파벳 26자에 대해서 대소문자 구별을 하지 않음 (그 외 BINARY와 동일)
RTRIM	문자열의 오른쪽 끝에 오는 공백은 비교 시 제외 (그 외 BINARY와 동일)

 

 

테이블을 생성할 때 CREATE TABLE 구문에서 컬럼 이름과 자료형 이후 "COLLATE [정렬 함수]" 추가를 통해 정렬 함수를 지정할 수 있다. 만약 SELECT 구문에서 정렬 함수를 변경하는 경우에는 ORDER BY 절 이후에 "COLLATE [정렬 함수]"를 추가해 주면 된다.

 

 

sqlite> CREATE TABLE tblCollating(    ...> seq INTEGER PRIMARY KEY AUTOINCREMENT,    ...> txt,    ...> txt_bin COLLATE BINARY,    ...> txt_nocase COLLATE NOCASE,    ...> txt_rtrim COLLATE RTRIM);   sqlite> INSERT INTO tblCollating (txt, txt_bin, txt_nocase, txt_rtrim) VALUES ('test', 'test', 'test', 'test'); sqlite> INSERT INTO tblCollating (txt, txt_bin, txt_nocase, txt_rtrim) VALUES ('test', 'test  ', 'test  ', 'test  '); sqlite> INSERT INTO tblCollating (txt, txt_bin, txt_nocase, txt_rtrim) VALUES ('test', 'TEST', 'TEST', 'TEST'); sqlite> INSERT INTO tblCollating (txt, txt_bin, txt_nocase, txt_rtrim) VALUES ('test', 'TEST  ', 'TEST  ', 'TEST  ');

 

WHERE절로 두 값을 비교하는 경우 기본적으로 연산자의 왼쪽에 위치한 정렬 함수를 기준으로 사용한다. 아래 예제는 BINARY와 RTRIM 정렬 함수를 이용하여 두 값을 비교해 본 것이다. RTRIM 정렬 함수를 사용한 경우 텍스트 뒤쪽의 공백이 제거 되어 seq 1, 2 값이 동일하게 비교되었음을 확인할 수 있다.

 

sqlite> SELECT * FROM tblCollating;   seq         txt         txt_bin     txt_nocase  txt_rtrim  ----------  ----------  ----------  ----------  ---------- 1           test        test        test        test         2           test        test        test        test       3           test        TEST        TEST        TEST       4           test        TEST        TEST        TEST      sqlite> SELECT seq FROM tblCollating WHERE txt = txt_rtrim ; -> BINARY 정렬 함수     seq        ---------- 1                 sqlite> SELECT seq FROM tblCollating WHERE txt_rtrim = txt;  -> RTRIM 정렬 함수 seq        ---------- 1          2

 

NOCASE 정렬 함수의 경우는 문자열 비교시 대소문자를 구분하지 않는다. 아래 예제는 GROUP BY, ORDER BY 절에서 명시적으로 다른 정렬 함수를 선언해서 사용한 것이다.

 

sqlite> SELECT * FROM tblCollating;   seq         txt         txt_bin     txt_nocase  txt_rtrim  ----------  ----------  ----------  ----------  ---------- 1           test        test        test        test         2           test        test        test        test       3           test        TEST        TEST        TEST       4           test        TEST        TEST        TEST      sqlite> SELECT seq FROM tblCollating .   -> NOCASE 정렬 함수    ...> WHERE 'Test' = txt_nocase;   seq        ---------- 1                 3            sqlite> SELECT seq FROM tblCollating     -> NOCASE 정렬 함수를 GROUP BY 절에서 사용    ...> GROUP BY txt_nocase ORDER BY 1;   seq        ---------- 3          4            sqlite> SELECT seq FROM tblCollating     -> NOCASE 정렬 함수를 ORDER BY 절에서 사용    ...> ORDER BY txt_bin COLLATE NOCASE;   seq        ---------- 1                  3          2          4