SQLite 버전 2의 데이터타입

이 문서는 오래된 SQLite 버전 2.x의 데이터타입 규칙을 다룬 역사적 문서예요. SQLite 버전 2는 "typeless"(타입 없음) 방식으로 유명했죠. 어떤 컬럼에 선언된 데이터타입과 무관하게 어떤 종류의 데이터든 저장할 수 있었어요. 이 문서는 그 동작 방식을 설명합니다.

출처: 문서

본문

1.0 typelessness (타입 없음)

SQLite는 "typeless"예요. 즉, 컬럼의 선언된 데이터타입과 무관하게 어떤 테이블의 어떤 컬럼에도 원하는 어떤 종류의 데이터든 저장할 수 있다는 뜻이에요. (이 규칙의 유일한 예외는 아래 2.0 절을 참고해요.) 이 동작은 버그가 아니라 기능이에요. 데이터베이스는 데이터를 저장하고 검색하기 위한 것이며, 데이터가 어떤 형식인지는 데이터베이스에게 중요하지 않아야 해요. 대부분의 다른 SQL 엔진에서 발견되고 SQL 언어 스펙에 규정된 강한 타입 시스템은 misfeature(잘못된 기능)예요. 구현 세부 사항이 인터페이스로 드러나는 예죠. SQLite는 어떤 종류의 데이터든 어떤 종류의 컬럼에 저장할 수 있게 하고 데이터타입 지정에 유연성을 허용함으로써 이 misfeature를 극복하고자 해요.

SQLite에게 데이터타입은 0개 이상의 이름의 시퀀스이며, 선택적으로 괄호로 묶인 1~2개의 부호 있는 정수 목록이 뒤따를 수 있어요. 특히 데이터타입이 0개 이상의 이름일 수 있다는 것을 주목해요. 즉 빈 문자열도 SQLite가 보기에 유효한 데이터타입이라는 뜻이에요. 그래서 각 컬럼의 데이터타입을 지정하지 않은 채로 테이블을 선언할 수 있어요:

CREATE TABLE ex1(a,b,c);

SQLite가 데이터타입 생략을 허용하긴 하지만, CREATE TABLE 문에 데이터타입을 포함하는 게 여전히 좋아요. 데이터타입은 흔히 다른 프로그래머에게 그 컬럼에 무엇을 넣으려는지에 대한 좋은 힌트가 되기 때문이죠. 그리고 코드를 다른 데이터베이스 엔진으로 포팅한다면, 그 엔진은 아마 어떤 종류의 데이터타입을 요구할 거예요. SQLite는 모든 일반적인 데이터타입을 받아들여요. 예를 들어:

CREATE TABLE ex2(
  a VARCHAR(10),
  b NVARCHAR(15),
  c TEXT,
  d INTEGER,
  e FLOAT,
  f BOOLEAN,
  g CLOB,
  h BLOB,
  i TIMESTAMP,
  j NUMERIC(10,5)
  k VARYING CHARACTER (24),
  l NATIONAL VARYING CHARACTER(16)
);

기본적으로 괄호 안에 1~2개의 부호 있는 정수가 선택적으로 뒤따르는 어떤 이름 시퀀스든 괜찮아요.

2.0 INTEGER PRIMARY KEY

SQLite의 typelessness에 대한 한 가지 예외는 타입이 INTEGER PRIMARY KEY인 컬럼이에요. (그리고 "INT"가 아니라 반드시 "INTEGER"를 사용해야 해요. INT PRIMARY KEY 타입의 컬럼은 다른 컬럼들과 똑같이 typeless해요.) INTEGER PRIMARY KEY 컬럼은 반드시 32비트 부호 있는 정수를 담아야 해요. 정수가 아닌 데이터를 삽입하려는 시도는 오류를 일으켜요.

INTEGER PRIMARY KEY 컬럼은 AUTOINCREMENT와 동등한 것을 구현하는 데 사용할 수 있어요. INTEGER PRIMARY KEY 컬럼에 NULL을 삽입하려 하면, 그 컬럼은 실제로 테이블에 이미 있는 가장 큰 키보다 1 큰 정수로 채워져요. 또는 가장 큰 키가 2147483647이라면 컬럼은 임의의 정수로 채워져요. 어느 쪽이든 INTEGER PRIMARY KEY 컬럼에는 고유한 정수가 할당돼요. 이 정수는 sqlite_last_insert_rowid() API 함수나, 이후 SELECT 문에서 last_insert_rowid() SQL 함수로 검색할 수 있어요.

3.0 비교와 정렬 순서

SQLite는 컬럼에 어떤 데이터가 저장될 수 있는지 결정하는 목적에서는 typeless예요. 하지만 데이터를 정렬하고 비교할 때는 어떤 종류의 타입 개념이 작용해요. 이러한 목적에서 컬럼이나 표현식은 numeric과 text 두 가지 타입 중 하나가 될 수 있어요. 정렬이나 비교는 정렬/비교되는 데이터의 타입에 따라 다른 결과를 줄 수 있어요.

데이터가 text 타입이면 비교는 표준 C 데이터 비교 함수 memcmp() 또는 strcmp()로 결정돼요. 비교는 두 입력의 바이트를 하나씩 살펴보고 첫 번째 0이 아닌 차이를 반환해요. 문자열은 '\000'으로 끝나므로, 예상대로 짧은 문자열이 긴 문자열보다 먼저 정렬돼요.

숫자 데이터의 경우 상황이 더 복잡해요. 두 입력 모두 잘 형성된 숫자처럼 보이면 atof()를 사용해 부동소수점 값으로 변환하고 숫자로 비교해요. 한 입력은 잘 형성된 숫자가 아니고 다른 하나는 그렇다면, 숫자가 숫자가 아닌 것보다 작은 것으로 간주돼요. 두 입력 모두 잘 형성된 숫자가 아니면 비교에 strcmp()를 사용해요.

컬럼에 "numeric" 데이터타입이 있다고 해서 혼동하지 마세요. 그렇다고 컬럼에 숫자만 담을 수 있다는 뜻은 아니에요. 단지 컬럼에 숫자가 담기면 그 숫자가 숫자 순서로 정렬된다는 뜻일 뿐이에요.

text와 numeric 값 모두에서 NULL은 다른 어떤 값보다 먼저 정렬돼요. "<"나 ">=" 같은 연산자를 사용한 NULL과의 어떤 값 비교도 항상 거짓이에요.

4.0 SQLite가 데이터타입을 결정하는 방법

SQLite 2.6.3 이하 버전에서는 모든 값이 numeric 데이터타입을 사용했어요. text 데이터타입은 2.7.0 이상 버전에서 등장했죠. 이후 내용에서는 SQLite 2.7.0 이상 버전을 사용한다고 가정할게요.

표현식의 경우 결과의 데이터타입은 흔히 가장 바깥쪽 연산자로 결정돼요. 예를 들어 산술 연산자("+", "*", "%")는 항상 numeric 결과를 반환해요. 문자열 연결 연산자("||")는 text 결과를 반환해요. 이런 식이죠. 표현식의 데이터타입이 의심스러우면 특수 typeof() SQL 함수를 사용해 데이터타입을 확인할 수 있어요. 예를 들어:

sqlite> SELECT typeof('abc'+123);
numeric
sqlite> SELECT typeof('abc'||123);
text

테이블 컬럼의 경우 데이터타입은 CREATE TABLE 문의 타입 선언으로 결정돼요. 데이터타입이 text가 되는 것은 오직 타입 선언이 다음 문자열 중 하나 이상을 포함할 때뿐이에요:

BLOB
CHAR
CLOB
TEXT

물론 타입 선언에서 이 문자열들을 찾는 검색은 대소문자를 구분하지 않아요. 위 문자열 중 하나라도 타입 선언 어디에든 나타나면 그 컬럼의 데이터타입은 text예요. "VARCHAR" 타입은 부분 문자열로 "CHAR"를 포함하므로 text로 간주된다는 점에 주의해요.

위 문자열 중 어떤 것도 타입 선언 어디에도 나타나지 않으면 데이터타입은 numeric이에요. 특히 빈 타입 선언을 가진 컬럼의 데이터타입은 numeric이라는 점을 기억해요.

5.0 예제

다음 두 명령 시퀀스를 고려해보세요:

CREATE TABLE t1(a INTEGER UNIQUE);        CREATE TABLE t2(b TEXT UNIQUE);
INSERT INTO t1 VALUES('0');               INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES('0.0');             INSERT INTO t2 VALUES(0.0);

왼쪽 시퀀스에서 두 번째 삽입은 실패해요. 이 경우 '0'과 '0.0' 문자열은 numeric 컬럼에 삽입되므로 숫자로 취급되는데, 0==0.0이라서 유일성 제약을 위반하죠. 하지만 오른쪽 시퀀스의 두 번째 삽입은 동작해요. 이 경우 상수 0과 0.0은 문자열로 취급되어 서로 구별되기 때문이에요.

SQLite는 비교 목적을 위해 숫자를 항상 배정밀도(64비트) 부동소수점으로 변환해요. 이 말은 유효하지 않은 자릿수에서만 다른 긴 숫자 시퀀스는 numeric 컬럼에서는 같게 비교되지만 text 컬럼에서는 다르게 비교된다는 뜻이에요. 다음을 봐요:

INSERT INTO t1                            INSERT INTO t2
   VALUES('12345678901234567890');           VALUES(12345678901234567890);
INSERT INTO t1                            INSERT INTO t2
   VALUES('12345678901234567891');           VALUES(12345678901234567891);

앞선 경우처럼 왼쪽의 두 번째 삽입은 실패해요. 비교가 두 문자열을 먼저 부동소수점 숫자로 변환하는데, 문자열의 유일한 차이는 64비트 부동소수점의 해상도를 초과하는 20번째 자리이기 때문이에요. 반대로 오른쪽의 두 번째 삽입은 동작해요. 이 경우 삽입되는 숫자들이 문자열이고 memcmp()로 비교되기 때문이죠.

numeric과 text 타입은 DISTINCT 키워드에서도 차이를 만들어요:

CREATE TABLE t3(a INTEGER);               CREATE TABLE t4(b TEXT);
INSERT INTO t3 VALUES('0');               INSERT INTO t4 VALUES(0);
INSERT INTO t3 VALUES('0.0');             INSERT INTO t4 VALUES(0.0);
SELECT DISTINCT * FROM t3;                SELECT DISTINCT * FROM t4;

왼쪽의 SELECT 문은 '0'과 '0.0'이 숫자로 취급되어 구별되지 않으므로 단일 행을 반환해요. 하지만 오른쪽의 SELECT 문은 0과 0.0이 서로 다른 문자열로 취급되므로 두 행을 반환해요.

SQLite 버전 2의 데이터타입