SQLite의 데이터타입
SQLite의 데이터타입
대부분의 SQL 데이터베이스 엔진은 정적이고 엄격한 타이핑을 사용하지만, SQLite는 더 일반적인 동적 타입 시스템을 사용해요. SQLite에서 값의 데이터타입은 값 자체와 연관되며, 값이 저장되는 컨테이너와 연관되지 않아요. 그래서 같은 컬럼에 여러 종류의 데이터를 저장할 수 있답니다.
출처: 문서
본문
1. SQLite의 데이터타입
대부분의 SQL 데이터베이스 엔진(우리가 아는 한 SQLite를 제외한 모든 SQL 데이터베이스 엔진)은 정적이고 엄격한 타이핑을 사용해요. 정적 타이핑에서는 값의 데이터타입이 그 컨테이너, 즉 값이 저장되는 특정 컬럼에 의해 결정돼요.
SQLite는 더 일반적인 동적 타입 시스템을 사용해요. SQLite에서 값의 데이터타입은 값 자체와 연관되며 컨테이너와 연관되지 않아요. SQLite의 동적 타입 시스템은 정적 타입 데이터베이스에서 동작하는 SQL 문이 SQLite에서도 같은 방식으로 동작한다는 점에서 다른 데이터베이스 엔진의 더 흔한 정적 타입 시스템과 하위 호환돼요. 하지만 SQLite의 동적 타이핑은 전통적인 엄격한 타입 데이터베이스에서는 불가능한 일들을 할 수 있게 해줘요. 유연한 타이핑은 SQLite의 기능이지 버그가 아니에요.
업데이트: 3.37.0 (2021-11-27) 버전부터, SQLite는 그런 것을 선호하는 개발자를 위해 엄격한 타입 강제를 하는 STRICT 테이블을 제공해요.
2. 스토리지 클래스와 데이터타입
SQLite 데이터베이스에 저장된(또는 데이터베이스 엔진이 조작하는) 각 값은 다음 스토리지 클래스 중 하나를 가져요:
NULL.값은 NULL 값이에요.INTEGER.값은 부호 있는 정수로, 값의 크기에 따라 0, 1, 2, 3, 4, 6, 또는 8바이트에 저장돼요.REAL.값은 부동소수점 값으로, 8바이트 IEEE 부동소수점 숫자로 저장돼요.TEXT.값은 텍스트 문자열로, 데이터베이스 인코딩(UTF-8, UTF-16BE 또는 UTF-16LE)으로 저장돼요.BLOB.값은 데이터 blob으로, 입력된 그대로 정확히 저장돼요.
스토리지 클래스는 데이터타입보다 더 일반적이에요. 예를 들어 INTEGER 스토리지 클래스는 길이가 다른 7개의 서로 다른 정수 데이터타입을 포함해요. 이것은 디스크에서 차이를 만들어요. 하지만 INTEGER 값이 디스크에서 메모리로 읽혀 처리되자마자 가장 일반적인 데이터타입(8바이트 부호 있는 정수)으로 변환돼요. 그래서 대부분의 경우 "스토리지 클래스"는 "데이터타입"과 구별할 수 없고, 두 용어를 바꿔 쓸 수 있어요.
SQLite 버전 3 데이터베이스의 어떤 컬럼이든, INTEGER PRIMARY KEY 컬럼을 제외하고, 어떤 스토리지 클래스의 값이든 저장하는 데 사용할 수 있어요.
SQL 문의 모든 값은 SQL 문 텍스트에 내장된 리터럴이든 미리 컴파일된 SQL 문에 바인딩된 파라미터든, 암시적 스토리지 클래스를 가져요. 아래 설명하는 상황에서 데이터베이스 엔진은 쿼리 실행 중에 숫자 스토리지 클래스(INTEGER와 REAL)와 TEXT 사이에서 값을 변환할 수 있어요.
2.1. Boolean 데이터타입
SQLite는 별도의 Boolean 스토리지 클래스를 가지지 않아요. 대신 Boolean 값은 정수 0(false)과 1(true)로 저장돼요.
SQLite는 3.23.0 (2018-04-02)부터 "TRUE"와 "FALSE" 키워드를 인식하지만, 그 키워드들은 각각 정수 리터럴 1과 0의 대체 철자일 뿐이에요.
2.2. 날짜와 시간 데이터타입
SQLite는 날짜 및/또는 시간 저장을 위해 따로 마련된 스토리지 클래스를 가지지 않아요. 대신 SQLite의 내장 Date And Time Functions가 날짜와 시간을 TEXT, REAL, 또는 INTEGER 값으로 저장할 수 있어요:
- TEXT는 ISO8601 문자열("YYYY-MM-DD HH:MM:SS.SSS")로.
- REAL은 율리우스력 날짜 숫자(proleptic Gregorian calendar에 따라 기원전 4714년 11월 24일 그리니치 정오부터의 일수)로.
- INTEGER는 Unix Time(1970-01-01 00:00:00 UTC부터의 초 수)으로.
애플리케이션은 이 형식 중 어떤 것으로든 날짜와 시간을 저장하고, 내장 날짜/시간 함수로 형식 사이를 자유롭게 변환할 수 있어요.
3. 타입 친화도 (Type Affinity)
엄격한 타이핑을 사용하는 SQL 데이터베이스 엔진은 보통 값을 적절한 데이터타입으로 자동 변환하려고 해요. 다음을 고려해보세요:
CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);
엄격한 타입 데이터베이스는 삽입 전에 문자열 '123'을 정수 123으로, 정수 456을 문자열 '456'으로 변환할 거예요.
SQLite와 다른 데이터베이스 엔진 사이의 호환성을 최대화하고, 위 예제가 다른 SQL 데이터베이스 엔진에서처럼 SQLite에서도 동작하도록, SQLite는 컬럼에 "type affinity"라는 개념을 지원해요. 컬럼의 type affinity는 그 컬럼에 저장된 데이터에 대한 권장 타입이에요. 여기서 중요한 아이디어는 타입이 권장되는 것이지 필수는 아니라는 점이에요. 어떤 컬럼이든 여전히 어떤 타입의 데이터든 저장할 수 있어요. 다만 어떤 컬럼은 선택이 가능할 때 하나의 스토리지 클래스를 다른 것보다 선호할 뿐이에요. 컬럼의 선호 스토리지 클래스를 그 "affinity"라고 불러요.
SQLite 3 데이터베이스의 각 컬럼에는 다음 타입 affinity 중 하나가 할당돼요:
- TEXT
- NUMERIC
- INTEGER
- REAL
- BLOB
(역사적 참고: "BLOB" 타입 affinity는 예전에 "NONE"이라고 불렸어요. 하지만 그 용어는 "no affinity"와 혼동되기 쉬워서 이름이 바뀌었어요.)
TEXT affinity 컬럼은 NULL, TEXT, BLOB 스토리지 클래스를 사용해 모든 데이터를 저장해요. 숫자 데이터가 TEXT affinity 컬럼에 삽입되면 저장되기 전에 텍스트 형태로 변환돼요.
NUMERIC affinity 컬럼은 다섯 가지 스토리지 클래스를 모두 사용하는 값을 포함할 수 있어요. 텍스트 데이터가 NUMERIC 컬럼에 삽입될 때, 텍스트가 잘 형성된 정수 또는 실수 리터럴이면 그 텍스트의 스토리지 클래스는 (선호 순서대로) INTEGER 또는 REAL로 변환돼요. TEXT 값이 64비트 부호 있는 정수에 맞기엔 너무 큰 잘 형성된 정수 리터럴이면 REAL로 변환돼요. TEXT와 REAL 스토리지 클래스 사이의 변환에서 약 15.95개의 유효 소수 자릿수가 보존돼요. (변환 정확도는 부동소수점 값에 IEEE 754 binary64 또는 "double" 인코딩을 사용함으로써 제한돼요.) TEXT 값이 잘 형성된 정수나 실수 리터럴이 아니면 값은 TEXT로 저장돼요. 이 문단의 목적에서 16진수 정수 리터럴은 잘 형성된 것으로 간주되지 않고 TEXT로 저장돼요. (이것은 16진수 정수 리터럴이 SQLite에 처음 도입된 3.8.6 (2014-08-15) 이전 버전과의 역사적 호환성을 위해 이렇게 해요.) 정수로 정확히 표현될 수 있는 부동소수점 값이 NUMERIC affinity 컬럼에 삽입되면 그 값은 정수로 변환돼요. NULL이나 BLOB 값을 변환하려는 시도는 없어요.
문자열이 소수점 및/또는 지수 표기의 부동소수점 리터럴처럼 보일 수 있지만, 값이 정수로 표현될 수 있는 한 NUMERIC affinity는 그것을 정수로 변환해요. 따라서 '3.0e+5' 문자열은 NUMERIC affinity 컬럼에 부동소수점 값 300000.0이 아니라 정수 300000으로 저장돼요.
INTEGER affinity를 사용하는 컬럼은 NUMERIC affinity 컬럼과 동일하게 동작해요. INTEGER와 NUMERIC affinity의 차이는 CAST 표현식에서만 드러나요: "CAST(4.0 AS INT)"는 정수 4를 반환하지만 "CAST(4.0 AS NUMERIC)"은 값을 부동소수점 4.0으로 남겨요.
REAL affinity 컬럼은 정수 값을 부동소수점 표현으로 강제한다는 점만 빼고 NUMERIC affinity 컬럼처럼 동작해요. (내부 최적화로, 분수 부분이 없는 작은 부동소수점 값은 REAL affinity 컬럼에 저장될 때 공간을 덜 차지하도록 정수로 디스크에 기록되고, 값이 읽힐 때 자동으로 부동소수점으로 다시 변환돼요. 이 최적화는 SQL 레벨에서 완전히 보이지 않고 데이터베이스 파일의 원시 비트를 조사해야만 감지할 수 있어요.)
BLOB affinity 컬럼은 한 스토리지 클래스를 다른 것보다 선호하지 않고, 데이터를 한 스토리지 클래스에서 다른 것으로 강제 변환하려는 시도도 없어요.
3.1. 컬럼 affinity 결정
STRICT로 선언되지 않은 테이블의 경우, 컬럼의 affinity는 컬럼의 선언된 타입에 의해 다음 규칙에 따라 표시된 순서로 결정돼요:
- 선언된 타입이 "INT" 문자열을 포함하면 INTEGER affinity가 할당돼요.
- 컬럼의 선언된 타입이 "CHAR", "CLOB", 또는 "TEXT" 문자열 중 하나를 포함하면 그 컬럼은 TEXT affinity를 가져요. VARCHAR 타입은 "CHAR" 문자열을 포함하므로 TEXT affinity가 할당된다는 점에 주의해요.
- 컬럼의 선언된 타입이 "BLOB" 문자열을 포함하거나 타입이 지정되지 않았으면 컬럼은 BLOB affinity를 가져요.
- 컬럼의 선언된 타입이 "REAL", "FLOA", 또는 "DOUB" 문자열 중 하나를 포함하면 컬럼은 REAL affinity를 가져요.
- 그 외에는 affinity가 NUMERIC이에요.
컬럼 affinity 결정 규칙의 순서가 중요하다는 점에 주의해요. 선언된 타입이 "CHARINT"인 컬럼은 규칙 1과 2를 모두 충족하지만 첫 번째 규칙이 우선하므로 컬럼 affinity는 INTEGER가 돼요.
3.1.1. Affinity 이름 예제
다음 표는 더 전통적인 SQL 구현의 흔한 데이터타입 이름들이 이전 절의 다섯 규칙에 의해 어떻게 affinity로 변환되는지 보여줘요. 이 표는 SQLite가 받아들이는 데이터타입 이름의 작은 부분집합만 보여줘요. 타입 이름 뒤의 괄호 안 숫자 인자(예: "VARCHAR(255)")는 SQLite가 무시한다는 점에 주의해요. SQLite는 문자열, BLOB, 숫자 값의 길이에 어떤 길이 제한도(큰 전역 SQLITE_MAX_LENGTH 제한 외에) 부과하지 않아요.
| CREATE TABLE 문 또는 CAST 표현식의 예제 타입 이름 | 결과 affinity | affinity 결정에 사용된 규칙 |
|---|---|---|
| INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8 | INTEGER | 1 |
| CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT, CLOB | TEXT | 2 |
| BLOB, 데이터타입 미지정 | BLOB | 3 |
| REAL, DOUBLE, DOUBLE PRECISION, FLOAT | REAL | 4 |
| NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME | NUMERIC | 5 |
선언된 타입 "FLOATING POINT"는 "POINT" 끝의 "INT" 때문에 REAL affinity가 아니라 INTEGER affinity를 준다는 점에 주의해요. 그리고 선언된 타입 "STRING"은 TEXT가 아니라 NUMERIC affinity를 가져요.
3.2. 표현식의 affinity
모든 테이블 컬럼은 타입 affinity(BLOB, TEXT, INTEGER, REAL, NUMERIC 중 하나)를 가지지만, 표현식은 반드시 affinity를 가지지는 않아요.
표현식 affinity는 다음 규칙으로 결정돼요:
- IN 또는 NOT IN 연산자의 오른쪽 피연산자는, 피연산자가 리스트이면 affinity가 없고, 피연산자가 SELECT이면 결과 집합 표현식의 affinity와 같은 affinity를 가져요.
- 표현식이 실제 테이블(VIEW나 서브쿼리가 아닌)의 컬럼에 대한 단순 참조이면 표현식은 테이블 컬럼과 같은 affinity를 가져요.
- 컬럼 이름 주변의 괄호는 무시돼요. 따라서 X와 Y.Z가 컬럼 이름이면 (X)와 (Y.Z)도 컬럼 이름으로 간주되고 해당 컬럼의 affinity를 가져요.
- 컬럼 이름에 적용되는 연산자(no-op 단항 "+" 연산자 포함)는 컬럼 이름을 항상 affinity가 없는 표현식으로 변환해요. 따라서 X와 Y.Z가 컬럼 이름이어도 +X와 +Y.Z 표현식은 컬럼 이름이 아니고 affinity가 없어요.
- "CAST(expr AS type)" 형태의 표현식은 선언된 타입이 "type"인 컬럼과 같은 affinity를 가져요.
- COLLATE 연산자는 그것의 왼쪽 피연산자와 같은 affinity를 가져요.
- 그 외에는 표현식이 affinity가 없어요.
3.3. VIEW와 서브쿼리에 대한 컬럼 affinity
VIEW 또는 FROM-절 서브쿼리의 "컬럼"은 실제로는 VIEW나 서브쿼리를 구현하는 SELECT 문의 결과 집합에 있는 표현식들이에요. 따라서 VIEW나 서브쿼리 컬럼의 affinity는 위의 표현식 affinity 규칙으로 결정돼요. 예를 들어 생각해보세요:
CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
v1.x 컬럼의 affinity는 v1.x가 t1.b에 직접 매핑되므로 t1.b(TEXT)의 affinity와 같을 거예요. 하지만 v1.y와 v1.z 컬럼은 모두 affinity가 없어요. 그 컬럼들이 표현식 a+c와 42에 매핑되고, 표현식은 항상 affinity가 없기 때문이에요.
3.3.1. 복합 VIEW에 대한 컬럼 affinity
VIEW나 FROM-절 서브쿼리를 구현하는 SELECT 문이 복합 SELECT이면, VIEW나 서브쿼리의 각 컬럼 affinity는 복합을 구성하는 개별 SELECT 문 중 하나의 해당 결과 컬럼의 affinity가 돼요. 하지만 어느 SELECT 문이 affinity를 결정하는 데 사용될지는 결정적이지 않아요. 서로 다른 구성 SELECT 문이 쿼리 평가 중 서로 다른 시점에 affinity를 결정하는 데 사용될 수 있어요. 그 선택은 SQLite의 서로 다른 버전에 따라 달라질 수 있어요. 같은 SQLite 버전에서 한 쿼리와 다음 쿼리 사이에 바뀔 수 있어요. 같은 쿼리 안에서도 다른 시점에 달라질 수 있어요. 따라서 구성 서브쿼리에서 서로 다른 affinity를 가진 복합 SELECT 컬럼에 어떤 affinity가 사용될지 절대 확신할 수 없어요.
결과의 데이터타입에 신경 쓴다면 복합 SELECT에서 affinity를 섞는 것을 피하는 것이 모범 사례예요. 복합 SELECT에서 affinity를 섞으면 놀랍고 직관적이지 않은 결과를 낳을 수 있어요. 예를 들어 forum 게시글 02d7be94d7을 참고해요.
3.4. 컬럼 affinity 동작 예제
다음 SQL은 값이 테이블에 삽입될 때 SQLite가 컬럼 affinity로 타입 변환을 하는 방법을 보여줘요.
CREATE TABLE t1(
t TEXT, -- text affinity by rule 2
nu NUMERIC, -- numeric affinity by rule 5
i INTEGER, -- integer affinity by rule 1
r REAL, -- real affinity by rule 4
no BLOB -- no affinity by rule 3
);
-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text
-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|real
-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer
-- BLOBs are always stored as BLOBs regardless of column affinity.
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob
-- NULLs are also unaffected by affinity
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null
4. 비교 표현식
SQLite 버전 3은 "=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", "IS NOT"을 포함한 보통의 SQL 비교 연산자 집합을 가져요.
4.1. 정렬 순서
비교 결과는 피연산자의 스토리지 클래스에 따라 다음 규칙으로 결정돼요:
- 스토리지 클래스 NULL의 값은 다른 어떤 값(다른 NULL 스토리지 클래스 값 포함)보다 작은 것으로 간주돼요.
- INTEGER 또는 REAL 값은 어떤 TEXT나 BLOB 값보다 작아요. INTEGER 또는 REAL을 다른 INTEGER 또는 REAL과 비교하면 숫자 비교가 수행돼요.
- TEXT 값은 BLOB 값보다 작아요. 두 TEXT 값을 비교할 때는 적절한 collating sequence가 결과를 결정하는 데 사용돼요.
- 두 BLOB 값을 비교할 때 결과는
memcmp()로 결정돼요.
4.2. 비교 전 타입 변환
SQLite는 비교를 수행하기 전에 INTEGER, REAL, 그리고/또는 TEXT 스토리지 클래스 사이에서 값을 변환하려고 시도할 수 있어요. 비교 전에 변환이 시도되는지 여부는 피연산자의 타입 affinity에 따라 달라요.
"affinity를 적용한다"는 것은 변환이 필수 정보를 잃지 않는 경우에만 피연산자를 특정 스토리지 클래스로 변환하는 것을 의미해요. 숫자 값은 항상 TEXT로 변환될 수 있어요. TEXT 값은 텍스트 콘텐츠가 잘 형성된 정수나 실수 리터럴(단, 16진수 정수 리터럴은 아님)이면 숫자 값으로 변환될 수 있어요. BLOB 값은 이진 BLOB 콘텐츠를 현재 데이터베이스 인코딩의 텍스트 문자열로 단순히 해석해서 TEXT 값으로 변환돼요.
affinity는 비교 전에 비교 연산자의 피연산자에 다음 규칙에 따라 표시된 순서로 적용돼요:
- 한 피연산자가 INTEGER, REAL 또는 NUMERIC affinity를 갖고 다른 피연산자가 TEXT 또는 BLOB affinity를 갖거나 affinity가 없으면, 다른 피연산자에 NUMERIC affinity가 적용돼요.
- 한 피연산자가 TEXT affinity를 갖고 다른 것이 affinity가 없으면, 다른 피연산자에 TEXT affinity가 적용돼요.
- 그 외에는 affinity가 적용되지 않고 두 피연산자가 있는 그대로 비교돼요.
"a BETWEEN b AND c" 표현식은 각 비교에서 'a'에 다른 affinity가 적용된다는 뜻이라도 두 개의 별도 이진 비교 "a >= b AND a <= c"로 취급돼요. "x IN (SELECT y ...)" 형태의 비교에서 데이터타입 변환은 비교가 정말 "x=y"인 것처럼 처리돼요. "a IN (x, y, z, ...)" 표현식은 "a = +x OR a = +y OR a = +z OR ..."과 동등해요. 다시 말해 IN 연산자 오른쪽의 값들(이 예제의 "x", "y", "z" 값)은 우연히 컬럼 값이나 CAST 표현식이어도 affinity가 없는 것으로 간주돼요.
4.3. 비교 예제
CREATE TABLE t1(
a TEXT, -- text affinity
b NUMERIC, -- numeric affinity
c BLOB, -- no affinity
d -- no affinity
);
-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer
-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40, a < 60, a < 600 FROM t1;
0|1|1
-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1
-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right. Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur. All
-- values are compared numerically.
SELECT b < 40, b < 60, b < 600 FROM t1;
0|0|1
-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers. Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1
-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40, c < 60, c < 600 FROM t1;
0|0|0
-- No affinity conversions occur. Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1
-- No affinity conversions occur. Right-hand side values all have
-- storage class INTEGER which compare numerically with the INTEGER
-- values on the left.
SELECT d < 40, d < 60, d < 600 FROM t1;
0|0|1
-- No affinity conversions occur. INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d < '40', d < '60', d < '600' FROM t1;
1|1|1
비교를 교환해도 — "a<40" 형태의 표현식을 "40>a"로 바꿔도 — 예제의 모든 결과는 동일해요.
5. 연산자
수학 연산자(+, -, *, /, %, <<, >>, &, |)는 두 피연산자를 모두 숫자로 해석해요. STRING 또는 BLOB 피연산자는 자동으로 REAL 또는 INTEGER 값으로 변환돼요. STRING이나 BLOB이 실수처럼 보이면(소수점이나 지수가 있으면) 또는 값이 64비트 부호 있는 정수로 표현할 수 있는 범위를 벗어나면 REAL로 변환돼요. 그렇지 않으면 피연산자가 INTEGER로 변환돼요. 수학 피연산자의 암시적 타입 변환은 CAST to NUMERIC과 약간 달라요. 실수처럼 보이지만 분수 부분이 없는 문자열과 BLOB 값은 CAST to NUMERIC에서처럼 INTEGER로 변환되는 대신 REAL로 유지되기 때문이에요. STRING 또는 BLOB에서 REAL 또는 INTEGER로의 변환은 손실이 있고 되돌릴 수 없어도 수행돼요. 일부 수학 연산자(%, <<, >>, &, |)는 INTEGER 피연산자를 기대해요. 그 연산자들에 대해 REAL 피연산자는 CAST to INTEGER와 같은 방식으로 INTEGER로 변환돼요. <<, >>, &, | 연산자는 항상 INTEGER(또는 NULL) 결과를 반환하지만, % 연산자는 피연산자의 타입에 따라 INTEGER 또는 REAL(또는 NULL)을 반환해요. 수학 연산자의 NULL 피연산자는 NULL 결과를 내요. 수학 연산자에서 숫자처럼 보이지 않고 NULL도 아닌 피연산자는 0 또는 0.0으로 변환돼요. 0으로 나누기는 NULL 결과를 줘요.
6. 정렬, 그룹화 그리고 복합 SELECT
쿼리 결과가 ORDER BY 절로 정렬될 때, 스토리지 클래스 NULL의 값이 먼저 오고, 그다음 숫자 순서로 섞인 INTEGER와 REAL 값, 그다음 collating sequence 순서의 TEXT 값, 마지막으로 memcmp() 순서의 BLOB 값이 와요. 정렬 전에 스토리지 클래스 변환은 일어나지 않아요.
GROUP BY 절로 값을 그룹화할 때, 서로 다른 스토리지 클래스의 값은 구별되는 것으로 간주돼요. 숫자로 같으면 같게 간주되는 INTEGER와 REAL 값은 예외예요. GROUP BY 절의 결과로 어떤 값에도 affinity가 적용되지 않아요.
복합 SELECT 연산자 UNION, INTERSECT, EXCEPT는 값 사이의 암시적 비교를 수행해요. UNION, INTERSECT, EXCEPT와 연관된 암시적 비교에는 비교 피연산자에 affinity가 적용되지 않아요. 값은 있는 그대로 비교돼요.
7. Collating Sequence
SQLite가 두 문자열을 비교할 때, 어느 문자열이 더 큰지 또는 두 문자열이 같은지 결정하기 위해 collating sequence 또는 collating function(같은 것을 뜻하는 두 용어)을 사용해요. SQLite에는 BINARY, NOCASE, RTRIM 세 가지 내장 collating function이 있어요.
- BINARY — 텍스트 인코딩과 무관하게
memcmp()로 문자열 데이터를 비교해요. - NOCASE — 비교에
sqlite3_strnicmp()을 사용한다는 점만 빼고 binary와 비슷해요. 따라서 비교를 수행하기 전에 ASCII의 26개 대문자가 소문자로 접혀요. ASCII 문자만 대소문자 접힘이 된다는 점에 주의해요. SQLite는 필요한 테이블의 크기 때문에 완전한 UTF 대소문자 접힘을 시도하지 않아요. 또한 문자열의 U+0000 문자는 비교 목적에서 문자열 종결자로 간주돼요. - RTRIM — 후행 공백 문자를 무시한다는 점만 빼고 binary와 같아요.
애플리케이션은 sqlite3_create_collation() 인터페이스로 추가 collating function을 등록할 수 있어요.
Collating function은 문자열 값을 비교할 때만 중요해요. 숫자 값은 항상 숫자로 비교되고, BLOB는 항상 memcmp()로 바이트 단위로 비교돼요.
7.1. SQL에서 collating sequence 할당
모든 테이블의 모든 컬럼에는 연관된 collating function이 있어요. collating function이 명시적으로 정의되지 않으면 기본값은 BINARY로 돼요. 컬럼 정의의 COLLATE 절은 컬럼의 대체 collating function을 정의하는 데 사용돼요.
이진 비교 연산자(=, <, >, <=, >=, !=, IS, IS NOT)에 어떤 collating function을 사용할지 결정하는 규칙은 다음과 같아요:
- 두 피연산자 중 하나가 후위 COLLATE 연산자를 사용해 명시적 collating function 할당을 가지면, 왼쪽 피연산자의 collating function에 우선권을 주어 비교에 그 명시적 collating function을 사용해요.
- 두 피연산자 중 하나가 컬럼이면 그 컬럼의 collating function을 사용하되 왼쪽 피연산자에 우선권을 줘요. 앞 문장의 목적에서 하나 이상의 단항 "+" 연산자 및/또는 CAST 연산자가 앞에 붙은 컬럼 이름도 여전히 컬럼 이름으로 간주돼요.
- 그 외에는 BINARY collating function을 비교에 사용해요.
피연산자의 어떤 하위 표현식이라도 후위 COLLATE 연산자를 사용하면 그 비교 피연산자는 명시적 collating function 할당(위 규칙 1)을 가진 것으로 간주돼요. 따라서 COLLATE 연산자가 비교 표현식 어디든 사용되면, 그 연산자가 정의한 collating function이 그 표현식의 일부인 테이블 컬럼과 관계없이 문자열 비교에 사용돼요. 비교 어디에든 두 개 이상의 COLLATE 연산자 하위 표현식이 나타나면, COLLATE 연산자가 표현식에서 얼마나 깊이 중첩되어 있고 표현식이 어떻게 괄호로 묶여 있는지와 무관하게 가장 왼쪽의 명시적 collating function이 사용돼요.
"x BETWEEN y and z" 표현식은 논리적으로 두 비교 "x >= y AND x <= z"와 동등하고, collating function에 관해서도 두 개의 별도 비교인 것처럼 동작해요. "x IN (SELECT y ...)" 표현식은 collating sequence 결정 목적에서 "x = y" 표현식과 같은 방식으로 처리돼요. "x IN (y, z, ...)" 형태의 표현식에 사용되는 collating sequence는 x의 collating sequence예요. IN 연산자에 명시적 collating sequence가 필요하면 왼쪽 피연산자에 적용해야 해요: "x COLLATE nocase IN (y,z, ...)"처럼요.
SELECT 문의 일부인 ORDER BY 절의 항은 COLLATE 연산자를 사용해 collating sequence를 할당받을 수 있으며, 이 경우 지정된 collating function이 정렬에 사용돼요. 그렇지 않고 ORDER BY 절이 정렬하는 표현식이 컬럼이면 컬럼의 collating sequence가 정렬 순서를 결정하는 데 사용돼요. 표현식이 컬럼이 아니고 COLLATE 절이 없으면 BINARY collating sequence가 사용돼요.
7.2. Collation Sequence 예제
아래 예제들은 다양한 SQL 문이 수행할 수 있는 텍스트 비교의 결과를 결정하는 데 사용될 collating sequence를 식별해요. 숫자, blob, NULL 값의 경우 텍스트 비교가 필요하지 않아 collating sequence가 사용되지 않을 수 있다는 점에 주의해요.
CREATE TABLE t1(
x INTEGER PRIMARY KEY,
a, /* collating sequence BINARY */
b COLLATE BINARY, /* collating sequence BINARY */
c COLLATE RTRIM, /* collating sequence RTRIM */
d COLLATE NOCASE /* collating sequence NOCASE */
);
/* x a b c d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc', 'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC', 'abc');
/* Text comparison a=b is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3
/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4
/* Text comparison d=a is performed using the NOCASE collating sequence. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4
/* Text comparison a=d is performed using the BINARY collating sequence. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4
/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3
/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3
/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4
/* Grouping is performed using the BINARY collating sequence. 'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2
/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3
/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1
/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;
--result 2 4 3 1