sqlite3 데이터 타입

아래 내용은 sqlite 의 공식 문서인 Datatypes In SQLite Version 3를 간단히 인용 및 정리한 것이다. 정확한 내용은 본문보다는 해당 문서 참고를 권장한다.

1. Datatypes In SQLite

In SQLite, the datatype of a value is associated with the value itself, not with its container.

sqlite는 SQL이지만 데이터 타입은 동적 타입을 사용한다.

2. Storage Classes and Datatypes

Storage class 는 다섯 가지가 있다.

• NULL
• INTEGER. signed 1,2,3,4,5,6,8 byte number.
• REAL: floating point value. 8 byte.
• TEXT
• BLOB: blob of data

A storage class is more general than a datatype. (...) And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably.

Storage class 는 데이터 타입보다 더 일반적인 개념이다. (...) 대부분의 경우, storage class 와 데이터 타입은 구분할 수 없다. 두 용어는 혼용될수 있다.

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

INTEGER PRIMARY KEY 컬럼을 제외한 모든 컬럼은, 어떤 storage class 의 값이라도 저장할 수 있다.

2.1. Boolean Datatype

Boolean values are stored as integers 0 (false) and 1 (true).

2.2. Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values

일시(날짜 및 시간)을 위한 storage class 는 따로 없고, 해당 값을 TEXT, REAL, INTEGER 로 저장할 수 있게 도와주는 함수들이 존재한다.

3. Type Affinity

The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required.

Type affinity 는 특정 컬럼에 저장하도록 권장하는 데이터 타입을 말한다.

아래와 같은 다섯 가지 Type affinity 가 존재함

• TEXT: 숫자 타입(INTEGER, REAL)은 TEXT 로 변환되어 저장된다. 나머지 storage class 는 그대로 저장된다.
• NUMERIC: 문자열은 가능하면 REAL 혹은 INTEGER 로 변환되어 저장되고, 가능하지 않다면 TEXT 로 저장된다. 숫자도 (실수라고 해도) INTEGER 로 변환 가능하다면 (변환해도 잃는 값이 없다면) 변환된다. 나머지 storage class 는 그대로 저장된다.
• INTEGER: CAST expression 을 제외하고 NUMERIC 과 동일하다.
• REAL: INTERGER 를 REAL 로 변환하여 저장함. 나머지는 NUMERIC 과 동일하다.
• BLOB: 별다른 타입 변환 없이 그대로 저장된다.

3.1. Determination Of Column Affinity

1. If the declared type contains the string "INT" then it is assigned INTEGER affinity.
2. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.
4. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
5. Otherwise, the affinity is NUMERIC.

CREATE TABLE 문 등으로 컬럼의 타입 선언 시

1. 컬럼의 선언된 타입 이름에 "INT" 가 들어가면 INTEGER affinity
2. 선언된 타입 이름에 "CHAR", "CLOB", "TEXT"가 들어가면 TEXT affinity
3. 이름에 "BLOB"가 들어가거나 타입이 지정되지 않으면 BLOB affinity
4. 이름에 "REAL", "FLOA", "DOUB"가 들어가면 REAL affinity
5. 위 조건에 모두 걸리지 않으면, NUMERIC affinity

가 지정된다.

3.1.1. Affinity Name Examples

해당 문서의 해당 항목 참고

3.2. Affinity Of Expression

Every table column has a type affinity (one of BLOB, TEXT, INTEGER, REAL, or NUMERIC) but expressions do not necessarily have an affinity.

모든 테이블 컬럼은 type affinity 를 갖지만, 표현식(Expression)은 type affinity 가 필수적이지 않다.

• The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list and has the same affinity as the affinity of the result set expression if the operand is a SELECT.

IN/NOT IN 문의 오른쪽 피연산자는 리스트면서 피연산자가 SELECT 인 결과 표현식의 affinity 와 같은 affinity 를 갖는다면 affinity 가 없다. (무슨 말인지 모르겠다..)

• When an expression is a simple reference to a column of a real table (not a VIEW or subquery) then the expression has the same affinity as the table column.
  • Parentheses around the column name are ignored.
  • Any operators applied to column names, including the no-op unary "+" operator, convert the column name into an expression which always has no affinity.

표현식이 테이블의 컬럼에 대한 레퍼런스라면, affinity 는 해당 컬럼과 동일하다.

• affinity 판단 시 괄호는 무시된다.
• 연산자를 사용했다면, 해당 표현식은 무조건 affinity 가 없다.

An expression of the form "CAST(expr AS type)" has an affinity that is the same as a column with a declared type of "type".

"CAST(expr AS type)" 형식의 표현식은 "type" affinity 를 갖는다.

A COLLATE operator has the same affinity as its left-hand side operand.

COLLATE 연산자는 왼쪽 피연산자와 같은 affinity 를 갖는다.

Otherwise, an expression has no affinity.

여기까지 해당사항이 없으면, 해당 표현식에 affinity 는 없다.

3.3. Column Affinity For Views And Subqueries

3.4. Column Affinity Behavior Example

해당 문서의 해당 항목 참고

4. Comparison Expressions

"=", "==", "<", "<=", ">", ">=", "!=", "", "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT"

4.1. Sort Order

정렬 시 순서는 아래처럼 적용된다.

• NULL < not NULL
• INTEGER/REAL < TEXT < BLOB
• BLOB 끼리의 비교에는 memcmp() 사용

4.2. Type Conversion Prior To Comparison

• If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand.
• If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand.
• Otherwise, no affinity is applied and both operands are compared as is.

• 피연산자 하나가 INTEGER/REAL/NUMERIC 이고 다른 피연산자가 TEXT/BLOB/affinity없음 이면, 숫자 유형이 다른 피연산자 유형으로 변환된다.
• 피연산자 하나가 TEXT 이고 다른 피연산자의 affinity 가 없으면 TEXT 가 다른 피연산자 유형으로 적용된다. (?? affinity 가 없는 걸로 적용된다고? 3.1. 항목을 보면 affinity 가 없는 경우는 없는 것 같은데, BLOB 를 말하는 건가?)
• 그렇지 않으면, 두 피연산자는 affinity 변환 없이 있는 그대로 비교된다.

4.3. Comparison Example

해당 문서의 해당 항목 참고

CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
    d            -- no affinity
);

5. Operators

+, -, *, /, %, <<, >>, &, and |

The implied type conversion of mathematical operands is slightly different from CAST to NUMERIC in that string and BLOB values that look like real numbers but have no fractional part are kept as REAL instead of being converted into INTEGER as they would be for CAST to NUMERIC.

수학적 피연산자의 암시적 타입 변환은 CAST to NUMERIC 문서에 나온 것과는 약간 다르다. 문자열이나 BLOB 값이 REAL 형식이면서 소숫점 아래 값이 없을 경우, INTEGER 로 자동 변환하는 것이 아니라 REAL 로 유지한다.

The conversion from STRING or BLOB into REAL or INTEGER is performed even if it is lossy and irreversible.

수학적 피연산자로 사용된 문자열이나 BLOB 값은 무조건 REAL/INTEGER 로 변환된다. 심지어는 변환으로 인해 값의 손실이 생기더라도.

Some mathematical operators (%, <<, >>, &, and |) expect INTEGER operands. For those operators, REAL operands are converted into INTEGER in the same way as a CAST to INTEGER.

어떤 연산자들은 피연산자로 INTEGER 값만을 사용한다. 이런 연산자들은 만약 REAL 이 피연산자로 사용되면, 값의 손실이 생기더라도 이 값을 INTEGER 로 변환한다.

6. Sorting, Grouping and Compound SELECTs

When query results are sorted by an ORDER BY clause, values with storage class NULL come first, followed by INTEGER and REAL values interspersed in numeric order, followed by TEXT values in collating sequence order, and finally BLOB values in memcmp() order.

쿼리 결과가 ORDER BY 문으로 정렬될 경우, 아래 순서대로 값이 나온다.

1. NULL 값이 먼저 나옴. 그 뒤를 따라서
2. INTEGER/REAL 값들이 숫자 순서에 따라 정렬됨. 그 뒤를 따라서
3. TEXT 값들이 collaing sequence 순서에 따라 정렬됨. 그 뒤를 따라서
4. BLOB 값이 memcmp() 순서에 따라 정렬됨

When grouping values with the GROUP BY clause values with different storage classes are considered distinct, except for INTEGER and REAL values which are considered equal if they are numerically equal.

쿼리 결과를 GROUP BY 절에 의해 묶을 경우, storage class 가 다른 값은 별개의 값으로 판단된다. 단, INTEGER/REAL 의 경우 같은 숫자값이라면 같은 값으로 판단된다.

The compound SELECT operators UNION, INTERSECT and EXCEPT perform implicit comparisons between values. No affinity is applied to comparison operands for the implicit comparisons associated with UNION, INTERSECT, or EXCEPT - the values are compared as is.

UNION, INTERSEC, EXCEPT 문을 사용할 때 각 값 간에 암시적 비교가 실시된다. 이 때 변환은 일어나지 않고 값들은 있는 그대로 비교된다.

7. Collating Sequences

When SQLite compares two strings, it uses a collating sequence or collating function (two words for the same thing) to determine which string is greater or if the two strings are equal. SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM.

두 문자열을 비교할 때, collating sequence 혹은 collating function 을 사용한다. sqlite 는 세 개의 내장 collating function 을 제공한다.

• BINARY: 문자열 비교에 memcmp() 사용
• NOCASE: 문자열 비교에 sqlite3_strnicmp() 사용
• RTRIM: BINARY 와 동일하나 앞뒤 공백 문자는 제거한 뒤 비교

7.1. Assigning Collating Sequences from SQL

1. If either operand has an explicit collating function assignment using the postfix COLLATE operator, then the explicit collating function is used for comparison, with precedence to the collating function of the left operand.

만약 두 피연산자가 COLLATE 문을 사용해 collating function 이 지정되어 있다면, 두 값의 비교를 위해 이 명시적 collating function 이 사용된다. 왼쪽 피연산자의 collating function 가 우선권을 가진다.

2. If either operand is a column, then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence, a column name preceded by one or more unary "+" operators and/or CAST operators is still considered a column name.

3. Otherwise, the BINARY collating function is used for comparison.

그렇지 않으면 BINARY collating function 이 사용된다.

7.2. Collating Sequence Example

해당 문서의 해당 항목 참고