실행 가능한 SQLite 문서: 타입 어피니티

Q: SQLite에서 타입 어피니티(type affinity)란 뭔가요?

타입 어피니티는 컬럼이 선호하는 저장 클래스라고 보면 됩니다. SQLite에는 TEXT, NUMERIC, INTEGER, REAL, BLOB 이렇게 5가지가 있어요. 값을 넣으면 SQLite가 일단 컬럼의 어피니티에 맞춰 변환을 시도하지만, 변환 시 손실이 생기거나 아예 불가능하면 들어온 값을 그대로 저장합니다. 어피니티는 강제 제약이 아니라 일종의 힌트인 셈이죠.

Q: SQLite는 컬럼의 어피니티를 어떻게 결정하나요?

CREATE TABLE에 적은 타입 이름을 문자열로 훑으면서 순서대로 매칭합니다. INT가 들어 있으면 INTEGER, 그게 아니면 CHAR·CLOB·TEXT 중 하나가 있으면 TEXT, 그 다음 BLOB이 있거나 타입을 안 적었으면 BLOB, 그 다음 REAL·FLOA·DOUB이 있으면 REAL, 어느 것에도 안 걸리면 NUMERIC이 됩니다. 그래서 VARCHAR(50)은 TEXT가 되고 BIGINT는 INTEGER가 되는 거예요. 우리가 적은 단어를 패턴 매칭하는 구조입니다.

Q: SQLite 컬럼에 타입이 안 맞는 값도 들어가나요?

일반 테이블이라면 들어갑니다. INTEGER로 선언한 컬럼에 'hello' 같은 문자열을 넣어도 그대로 저장돼요. 어피니티는 변환을 권하는 정도일 뿐이거든요. 타입을 엄격하게 강제하고 싶다면 STRICT 테이블을 쓰면 됩니다. 타입이 안 맞으면 아예 거부하죠. 이건 다음 글에서 다룹니다.

타입 어피니티는 강제 규칙이 아니라 '선호'다

SQLite는 동적 타이핑 데이터베이스다. 값마다 자신의 저장 클래스(storage class) 를 가지고 다니며(NULL, INTEGER, REAL, TEXT, BLOB), 컬럼에 선언한 타입이 그 안에 들어갈 값을 엄격하게 제한하지 않는다. 그럼 컬럼 타입을 선언하는 의미가 뭐냐면, 그 컬럼에 타입 어피니티(type affinity) 를 부여하는 것이다. 즉, 들어오는 값을 SQLite가 가급적 변환해서 맞추려고 시도하는 '선호 저장 클래스'를 정해준다.

어피니티만으로는 타입 불일치를 막지 못하는 경우를 직접 살펴보자:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, '사과');
INSERT INTO items VALUES ('two', '바나나');

SELECT id, typeof(id), name FROM items;

두 번째 행은 INTEGER 컬럼에 문자열 'two'를 저장합니다. SQLite는 'two'를 숫자로 변환해 보다가 실패하자(숫자가 아니니까요) 그냥 TEXT로 저장해 버립니다. typeof() 함수로 확인해 보면 각 값이 실제로 어떤 저장 클래스로 들어가 있는지 알 수 있는데, 컬럼 선언에서 짐작한 것과 다른 경우가 흔합니다.

Postgres나 MySQL을 쓰던 분들이라면 당황스러운 동작일 텐데요, 이건 SQLite가 의도적으로 설계한 방식입니다.

다섯 가지 타입 어피니티

STRICT 테이블이 아닌 일반 테이블의 모든 컬럼은 아래 다섯 가지 어피니티 중 정확히 하나를 갖습니다.

TEXT — 문자열을 선호합니다.
NUMERIC — 숫자를 선호하지만, 변환이 안 되면 텍스트도 그대로 받습니다.
INTEGER — NUMERIC과 비슷하지만, 소수부가 없는 값은 정수로 저장합니다.
REAL — 부동소수점 숫자를 선호합니다.
BLOB — 선호하는 타입이 없고, 주는 대로 그대로 저장합니다.

BLOB 어피니티는 "어피니티 없음(no affinity)"이라고도 부르며, 타입을 아예 선언하지 않으면 이 어피니티가 적용됩니다.

CREATE TABLE samples (
    a,                 -- BLOB 친화성 (타입 미선언)
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

같은 입력값 '42'이 컬럼에 따라 서로 다른 5가지 타입으로 저장되었습니다. 각 컬럼의 어피니티에 따라 변환이 일어나거나, 일어나지 않은 결과죠.

SQLite는 컬럼 선언에서 어피니티를 어떻게 결정할까

여기서부터 많은 분들이 헷갈리기 시작합니다. SQLite에는 "유효한 타입" 목록이 따로 정해져 있지 않아요. 컬럼 이름 뒤에 거의 어떤 문자열이든 적을 수 있고, SQLite는 그 텍스트에서 특정 부분 문자열을 다음 순서대로 검사해 어피니티를 결정합니다:

INT 포함 → INTEGER
CHAR, CLOB, TEXT 중 하나 포함 → TEXT
BLOB 포함하거나 타입 선언이 아예 없음 → BLOB
REAL, FLOA, DOUB 중 하나 포함 → REAL
그 외 → NUMERIC

알고리즘은 이게 전부입니다. 이 규칙 하나로 SQLite의 별난 동작들이 꽤 많이 설명돼요:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (CHAR를 포함)
    b BIGINT,             -- INTEGER (INT를 포함)
    c DOUBLE PRECISION,   -- REAL (DOUB를 포함)
    d DECIMAL(10,2),      -- NUMERIC (위 어느 것에도 해당하지 않음)
    e BOOLEAN,            -- NUMERIC (위 어느 것에도 해당하지 않음)
    f DATETIME,           -- NUMERIC (위 어느 것에도 해당하지 않음)
    g FLOATING_POINTS,    -- INTEGER (INT를 포함 — 놀랍죠!)
    h GIBBERISH           -- NUMERIC (위 어느 것에도 해당하지 않음)
);

SELECT name, type FROM pragma_table_info('quirks');

FLOATING_POINTS가 INTEGER가 되는 이유는 POINTS 안에 INT라는 문자열이 들어 있기 때문입니다. 규칙은 위에서 아래로 훑으면서 가장 먼저 매칭되는 것이 적용되죠. 그래서 다른 DBMS에서 쓰던 타입을 그대로 가져다 붙이면 예상과 전혀 다른 결과가 나오기도 합니다.

실제 동작으로 보는 타입 어피니티: INSERT 시 변환 규칙

타입 어피니티가 진짜 중요해지는 순간은 SQLite가 입력값을 변환할지, 그대로 저장할지 결정할 때입니다. 규칙은 다음과 같습니다.

TEXT 어피니티: 숫자나 BLOB은 텍스트로 변환됩니다.
NUMERIC, INTEGER, REAL 어피니티: 숫자처럼 생긴 텍스트는 숫자로 변환되고, 그렇지 않은 텍스트는 그대로 텍스트로 남습니다.
BLOB 어피니티: 아무것도 변환되지 않습니다.

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

한 줄씩 살펴보겠습니다.

NUMERIC 컬럼에 들어간 '123'은 정수 123으로 바뀝니다. 텍스트에서 숫자로의 변환이 손실 없이 성공한 경우죠.
'12.5'는 실수 12.5가 됩니다.
NUMERIC에 들어간 'hello'는 그대로 텍스트로 남습니다. 숫자로 바꿀 방법이 없으니까요.
TEXT 컬럼은 숫자를 문자열 형태로 변환해서 저장합니다.
BLOB 컬럼은 들어온 값을 타입까지 그대로 보존합니다.

`INTEGER`와 `REAL`의 미묘한 차이

INTEGER 어피니티는 NUMERIC과 거의 똑같이 동작하는데, 한 가지 함정이 있습니다. 3.0처럼 소수부가 사실상 없는 값은 공간을 아끼기 위해 정수 3으로 저장된다는 점이죠.

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0은 두 컬럼 모두에서 INTEGER로 저장됩니다 — 이런 최적화는 NUMERIC에도 똑같이 적용됩니다. 반면 3.5는 소수점 아래 값을 유지하면서 REAL로 남죠. 여기서 얻을 교훈은 이겁니다. 컬럼이 INTEGER로 선언됐는지 REAL로 선언됐는지 확인할 때 typeof()에 의존하면 안 된다는 것. typeof()는 실제로 저장된 값의 타입을 알려줄 뿐이고, 그 값은 행마다 달라질 수 있으니까요.

타입 어피니티가 발목을 잡을 때

이런 유연함은 편리하지만, 어느 순간부터는 골칫거리가 됩니다. 실제 코드에서 자주 만나는 두 가지 함정이 있어요:

1. 잘못된 데이터가 슬쩍 들어온다. 애플리케이션 버그로 'N/A' 같은 값이 INTEGER 컬럼에 들어가도 SQLite는 그냥 저장해 버립니다. 그러면 나중에 그 컬럼으로 산술 연산을 돌리는 쿼리가 이상한 결과를 내거나 NULL을 뱉죠. 오류도, 경고도 없는 조용한 데이터 오염입니다.

2. 비교 동작이 예상과 다르게 흘러간다. 정렬이나 동등 비교를 할 때, 저장 클래스(storage class)가 다른 값들은 서로 다르게 취급됩니다:

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

정수는 숫자 순서로 정렬된 다음, 문자열이 사전식으로 정렬됩니다. 그런데 문자열은 모든 숫자 뒤에 붙어서 정렬돼요. 그래서 결과가 2, 3, 10(정수가 숫자 순서대로) 다음에 '100', '20'(문자열이 알파벳 순서로) 이런 식으로 나옵니다. 보통 우리가 원하는 결과는 아니죠.

INSERT 쿼리를 직접 통제하면서 값을 꼼꼼히 검증한다면 일반 테이블로도 충분합니다. 하지만 그게 어렵거나, 아니면 그냥 DB가 타입을 강제해줬으면 좋겠다 싶을 때는 더 나은 선택지가 있습니다.

다음 주제: STRICT 테이블

SQLite 3.37부터 STRICT 테이블이 추가됐습니다. 타입 어피니티를 꺼버리고, 선언한 타입과 맞지 않는 값은 아예 거부해 주죠. 평소엔 동적 타이핑의 유연함을 누리다가, 필요할 때는 Postgres처럼 엄격한 타입 검사를 받을 수 있는 셈입니다. 자세한 내용은 다음 페이지에서 다룹니다.

자주 묻는 질문

SQLite에서 타입 어피니티(type affinity)란 뭔가요?

타입 어피니티는 컬럼이 선호하는 저장 클래스라고 보면 됩니다. SQLite에는 TEXT, NUMERIC, INTEGER, REAL, BLOB 이렇게 5가지가 있어요. 값을 넣으면 SQLite가 일단 컬럼의 어피니티에 맞춰 변환을 시도하지만, 변환 시 손실이 생기거나 아예 불가능하면 들어온 값을 그대로 저장합니다. 어피니티는 강제 제약이 아니라 일종의 힌트인 셈이죠.

SQLite는 컬럼의 어피니티를 어떻게 결정하나요?

CREATE TABLE에 적은 타입 이름을 문자열로 훑으면서 순서대로 매칭합니다. INT가 들어 있으면 INTEGER, 그게 아니면 CHAR·CLOB·TEXT 중 하나가 있으면 TEXT, 그 다음 BLOB이 있거나 타입을 안 적었으면 BLOB, 그 다음 REAL·FLOA·DOUB이 있으면 REAL, 어느 것에도 안 걸리면 NUMERIC이 됩니다. 그래서 VARCHAR(50)은 TEXT가 되고 BIGINT는 INTEGER가 되는 거예요. 우리가 적은 단어를 패턴 매칭하는 구조입니다.

SQLite 컬럼에 타입이 안 맞는 값도 들어가나요?

일반 테이블이라면 들어갑니다. INTEGER로 선언한 컬럼에 'hello' 같은 문자열을 넣어도 그대로 저장돼요. 어피니티는 변환을 권하는 정도일 뿐이거든요. 타입을 엄격하게 강제하고 싶다면 STRICT 테이블을 쓰면 됩니다. 타입이 안 맞으면 아예 거부하죠. 이건 다음 글에서 다룹니다.

SQLite 타입 어피니티(Type Affinity) 완벽 정리