[SQLITE] 자료형

출처 : http://blog.daum.net/sukee/17162407

저장형과 컬럼 친화성

SQLite에는 저장형(Storage Class)와 컬럼 친화성(Column Affinity)이 있습니다.

저장형은 각 값에 붙여지는 태그이며, 정렬이나 비교와 같은 연산을 실행할 때 사용됩니다. 컬럼 친화성은 다른 데이터베이스와 호환을 위해 제공됩니다. 이미 오라클이나 MySQL과 같은 데이터베이스에 경험이 있는 분들에게는 이 부분이 많이 헷갈릴 수 있습니다. 

저장형

저장형(Storage Class)은 각 값에 붙는 타입입니다. 다시 한 번 강조하자면, 각 값에 붙는 타입입니다. 오라클이나 MySQL 같은 경우에는 타입이 각 컬럼에 부여됩니다. 예를 들어, MySQL에서 테이블을 만들 때, 어떤 컬럼의 타입을 정수로 결정하면, 이후로 그 테이블의 그 컬럼에는 정수만이 들어갈 수 있습니다. 다시 말해, 정수가 아니면 안 되는 거죠. SQLite는 이와 같지 않습니다.

다음은 SQLite의 다섯 저장형입니다:

NULL:    값이 NULL인 경우.

INTEGER: 부호 있는 정수. 상황에 따라 1, 2, 3, 4, 6, 8 바이트가 됨.

REAL:    부동소수점. IEEE 부동소수 규격에 따른 8바이트.

TEXT:    문자(열). 기본적으로 UTF-8으로 저장되며 UTF-16 BE나 UTF-16 LE를 사용할 수 있음.

BLOB:    이진 데이터. 외부에서 전달된 데이터를 그대로 저장됨.

위 저장형은 다음 절에서 설명하는 컬럼 친화성과 함께 봐야 이해가 되므로, 저장형은 SQLite에서 타입을 의미한다는 정도만 알고 일단 패스!

컬럼 친화성

먼저, 우리에게 익숙한 다음 쿼리로 시작해 보겠습니다.

sqlite> CREATE TABLE tbl_test (One INTEGER, Two INTEGER);

위 쿼리로 두 개의 컬럼(One, Two)이 있는 테이블(tbl_test)을 하나 만들었습니다. 두 컬럼 모두 정수형이기 때문에, 위 테이블에는 정수만이 들어갈 수 있습니다. 그렇죠? 그러나 SQLite는 다릅니다! 다음으로 직접 확인해 보죠.

sqlite> INSERT INTO tbl_test VALUES (123, 456);
sqlite> INSERT INTO tbl_test VALUES (123, 456.7);
sqlite> INSERT INTO tbl_test VALUES (123, 456.0);
sqlite> INSERT INTO tbl_test VALUES (123, '456');
sqlite> INSERT INTO tbl_test VALUES (123, '456two');
sqlite> INSERT INTO tbl_test VALUES (123, 'ant');
sqlite> INSERT INTO tbl_test VALUES (123, ant);

위 실행중에서 가장 마지막 것만 오류가 발생하고 나머지는 실행됩니다. 즉 값이 테이블에 저장됩니다. 다음처럼 직접 확인해 보세요.

sqlite> SELECT * FROM tbl_test;

위에서 테이블을 만들 때, INTEGER란 키워드를 사용했는데 이는 컬럼 친화성을 지정하는 키워드 중에 하나였습니다. SQLite에는 모두 다섯 개의 컬럼 친화성이 있습니다: TEXT, NUMERIC, INTEGER, REAL, NONE.

자~ 여기서 맥(?)을 함 짚고 넘어 가겠습니다: 테이블을 생성하는 쿼리문이 동일하기 때문에, 여러분은 위의 INTEGER가 컬럼 타입이라고 생각하게 된 겁니다. SQLite에서는 컬럼 타입(Column Type)이 아니라 컬럼 친화성(Column Affinity)으로 사용되고 있습니다. 컬럼 타입은 딱 하나를 결정하는 것이고, 컬럼 친화성은 어떤 우선 순위를 취할지 결정하는 겁니다. 어떤 우선 순위라?

우리는 테이블을 만들고 거기에 데이터를 저장합니다; 레코드를 기본 단위로 저장합니다. 위 테이블 예에서, 오라클이나 MySQL은 레코드를 저장할 때마다 컬럼의 값들이 항상 정수입니다. 레코드가 추가되더라도 이건 변하지 않고 고정된 거죠. SQLite에서는 동일한 컬럼일지라도 각 레코드마다 다른 타입을 가질 수 있습니다.

아래 쿼리는 위에서 사용했던 것과 동일한데, 각 쿼리의 끝에 저장형을 보여주고 있습니다.(컬럼 친화성이랑 헷갈리지 마세요)

sqlite> INSERT INTO tbl_test VALUES (123, 456);       --(INTEGER, INTEGER)
sqlite> INSERT INTO tbl_test VALUES (123, 456.7);     --(INTEGER, REAL)
sqlite> INSERT INTO tbl_test VALUES (123, 456.0);     --(INTEGER, INTEGER)
sqlite> INSERT INTO tbl_test VALUES (123, '456');     --(INTEGER, INTEGER)
sqlite> INSERT INTO tbl_test VALUES (123, '456two');  --(INTEGER, TEXT) 
sqlite> INSERT INTO tbl_test VALUES (123, 'ant');     --(INTEGER, TEXT) 
sqlite> INSERT INTO tbl_test VALUES (123, ant);       --ERROR

위 쿼리에서 눈여겨 볼 부분은 두 번째 컬럼(컬럼 Two, 컬럼 친화성 INTEGER)입니다. 먼저 첫 번째 쿼리에서, 정수 값(456)이 입력되었으므로 저장형은 INTEGER가 됩니다.(INTEGER란 키워드는 컬럼 친화성과 저장형에 모두 있으므로 헷갈리지 마세요) 두 번째 쿼리에서는 정수가 아닙니다(456.7). SQLite는, 컬럼 친화성이 INTEGER이므로, 먼저 이 값이 정수가 될 수 있는지 확인합니다. 그런데 정수가 될 수가 없기 때문에 차선으로 REAL 저장형을 선택한 것입니다. 세 번째 쿼리에서도 정수로 변환 가능한지 확인하는데, 이번에는 가능합니다. 때문에 INTEGER 저장형을 부여합니다. 네 번째도 세번째에서와 마찬가지로 정수로 변환 가능하므로 INTEGER 저장형을 부여했습니다. 그 다음 값('456two')은 어떻습니까? 이 값은 정수가 될 수도 없고 부동 소수가 될 수도 없습니다. 그 다음으로 TEXT로 변환 가능하기 때문에 TEXT 저장형을 부여했습니다. 그 다음 여섯 번째도 마찬가지입니다. 마지막 쿼리에서의 값(ant)은 정수도 부동 소수도 될 수가 없기 때문에 텍스트가 되야 하는데, 작은 따옴표가 없기 때문에 에러를 발생합니다. 컬럼 친화성에 상관없이 작은 따옴표를 사용하지 않는 값의 경우에는 숫자만이 가능합니다.

음, 여기서 말한 컬럼 친화성이란 입력되는 값에 따라 어떤 타입을 우선해서 선택할까를 의미합니다. 그럼 다음 번에는 다섯 개 컬럼 친화성의 우선 순위에 대해서 알아 보도록 하겠습니다.

자동 증가 Autoincrement

자동 증가는 보통 테이블의 키로 사용됩니다. 그래서 유일한 값을 갖는게 중요하지요. 다른 테이블에서 외래키(foreign key)로 사용될 때도 필요하구요. 다음 처럼 간단합니다.

CREATE TABLE tbl_test (
  RowID INTEGER PRIMARY KEY AUTOINCREMENT,
  Name  TEXT,
  Age   INTEGER
);

**참고  http://qkrgns208.tistory.com/119?srchid=BR1http://qkrgns208.tistory.com/119