SQLite의 특이한 점, 주의점, 함정

SQLite의 특이한 점, 주의점, 함정 (Quirks, Caveats, and Gotchas In SQLite)

이 문서는 SQLite를 사용할 때 흔히 마주치는 특이한 동작들과 주의해야 할 함정들을 설명해요. SQLite는 다른 SQL 데이터베이스와 다르게 동작하는 부분이 많아요.

출처: 문서

본문

1. 개요 (Overview)

SQLite는 의도적으로 다른 SQL 데이터베이스와 다르게 동작하는 몇 가지 특징을 가져요. 어떤 차이는 설계상의 선택이고, 어떤 것은 오래된 호환성 때문에 발생한 것이에요. 이 문서는 그런 차이점들을 명확히 알려 주어, 다른 데이터베이스에 익숙한 개발자들이 SQLite를 쓸 때 혼란을 덜도록 돕는 것이 목적이에요.

2. SQLite는 임베디드형이며 클라이언트-서버형이 아님

SQLite는 서버 프로세스를 두지 않는 임베디드형(embedded) 데이터베이스예요. 별도의 데이터베이스 서버나 연결 문자열, 관리자 계정이 없어요. 데이터베이스는 그냥 파일 하나이며, 응용 프로그램이 라이브러리로 직접 접근해요. 이 때문에 설치·배포가 간단하지만, 클라이언트-서버 모델에서 기대하던 기능(권한 제어, 네트워크 접근 등)은 다르게 동작해요.

3. 유연한 데이터 타입 (Flexible Typing)

SQLite는 데이터 타입에 있어 유연해요. 데이터 타입은 필수가 아니라 권고(advisory)에 가까워요.

일부 논평자들은 SQLite가 "약한 타입"(weakly typed)이고 다른 SQL 데이터베이스가 "강한 타입"(strongly typed)이라고 말해요. 우리는 이 용어가 부정확하고 심지어 비하적이라고 생각해요. SQLite는 "유연한 타입"(flexibly typed)이고, 다른 SQL 엔진은 "엄격한 타입"(rigidly typed)이라고 말하는 것을 선호해요.

SQLite의 타입 시스템에 대한 자세한 논의는 SQLite의 데이터 타입 문서를 참고하세요.

핵심은 SQLite가 데이터베이스에 넣는 데이터의 타입에 대해 매우 관대하다는 것이에요. 예를 들어 열의 데이터 타입이 "INTEGER"인데 응용 프로그램이 문자열을 넣으면, SQLite는 다른 모든 SQL 엔진처럼 먼저 그 문자열을 정수로 변환하려고 해요. 따라서 '1234'를 INTEGER 열에 넣으면 정수 1234로 변환되어 저장돼요. 하지만 'wxyz' 같은 숫자가 아닌 문자열을 INTEGER 열에 넣으면, 다른 SQL 데이터베이스와 달리 SQLite는 오류를 내지 않아요. 대신 그 문자열 값을 그대로 저장해요.

마찬가지로 2000자 문자열을 VARCHAR(50) 열에 저장할 수 있어요. 다른 SQL 구현은 오류를 내거나 문자열을 잘라내겠지만, SQLite는 정보 손실이나 불평 없이 전체 2000자 문자열을 저장해요.

이것이 문제를 야기하는 경우는 개발자가 SQLite로 초기 코딩을 해서 응용 프로그램을 동작시킨 뒤, 배포를 위해 PostgreSQL이나 SQL Server 같은 다른 데이터베이스로 전환하려 할 때예요. 응용 프로그램이 처음에 SQLite의 유연한 타입을 활용했다면, 데이터 타입에 더 엄격한 다른 데이터베이스로 옮기면 실패할 거예요.

유연한 타입은 SQLite의 기능이지 버그가 아니에요. 유연한 타입은 자유에 관한 것이에요. 그럼에도 이 기능이 데이터 타입 규칙에 더 엄격한 다른 데이터베이스에 익숙한 개발자들에게 혼란을 주기도 하는 것을 인지해요. 돌이켜 보면, SQLite가 그냥 ANY 데이터 타입을 구현해서 유연한 타입을 쓰고 싶을 때 개발자가 명시적으로 선언하게 했더라면, 유연한 타입을 기본값으로 만든 것보다 덜 혼란스러웠을지도 몰라요. 엄격한 타입을 기대하는 사람들을 위해 SQLite 3.37.0(2021-11-27)부터 STRICT 테이블 옵션이 도입됐어요. 이 테이블은 다른 SQL 엔진의 필수 데이터 타입 제약을 적용하거나, 명시적 ANY 타입으로 SQLite의 유연한 타입을 유지할 수 있어요.

3.1. 별도의 BOOLEAN 데이터 타입이 없음

대부분의 다른 SQL 구현과 달리 SQLite에는 별도의 BOOLEAN 데이터 타입이 없어요. 대신 TRUE와 FALSE가 (보통) 각각 정수 1과 0으로 표현돼요. 이 때문에 큰 문제는 없는 것 같아요(이에 대한 불만은 거의 없으니까요). 하지만 알아 두는 것이 중요해요.

SQLite 3.23.0(2018-04-02)부터 TRUE와 FALSE 키워드를 정수 1과 0의 별명으로 인식해요. 이는 다른 SQL 구현과의 호환성을 높여 줘요. 하지만 하위 호환성을 위해, TRUE나 FALSE라는 이름의 열이 있으면 그 키워드는 BOOLEAN 리터럴이 아니라 그 열을 가리키는 식별자로 취급돼요.

3.2. 별도의 DATETIME 데이터 타입이 없음

SQLite에는 DATETIME 데이터 타입이 없어요. 대신 날짜와 시간을 다음 방식 중 하나로 저장할 수 있어요.

• ISO-8601 형식의 TEXT 문자열. 예: '2018-04-02 12:13:46'
• 1970년 이후의 초(secatnds)를 나타내는 INTEGER("unix time"이라고도 함)
• 분수 율리우스 날짜인 REAL 값

SQLite의 내장 날짜·시간 함수는 위 모든 형식의 날짜/시간을 이해하고, 그들 사이를 자유롭게 변환할 수 있어요. 어떤 형식을 사용할지는 전적으로 응용 프로그램에 달려 있어요.

3.3. 데이터 타입은 선택적임

SQLite는 데이터 타입에 대해 유연하고 관대하기 때문에, 지정된 데이터 타입이 전혀 없는 테이블 열을 만들 수 있어요. 예를 들어:

CREATE TABLE t1(a,b,c,d);

테이블 "t1"에는 특정 데이터 타입이 할당되지 않은 "a", "b", "c", "d" 네 개의 열이 있어요. 그 열들 중 아무 곳에나 원하는 것을 저장할 수 있어요.

4. 외래 키 강제(default) 꺼져 있음

SQLite는 오래전부터 외래 키 제약을 파싱했지만, 실제로 그 제약을 강제하는 기능은 훨씬 나중인 3.6.19(2009-10-14)에서 추가됐어요. 외래 키 제약 강제가 추가될 무렵에는 이미 셀 수 없이 많은 데이터베이스가 외래 키 제약을 포함한 채 유통되고 있었고, 그중 일부는 올바르지 않았어요. 레거시 데이터베이스를 깨뜨리지 않기 위해 SQLite에서는 외래 키 제약 강제가 기본적으로 꺼져 있어요.

응용 프로그램은 실행 시 PRAGMA foreign_keys 문으로 외래 키 강제를 켤 수 있어요. 또는 컴파일 시 -DSQLITE_DEFAULT_FOREIGN_KEYS=1 옵션으로 활성화할 수 있어요.

5. PRIMARY KEY에 NULL이 들어갈 수 있음

SQLite 테이블의 PRIMARY KEY는 보통 그냥 UNIQUE 제약이에요. 역사적인 실수 때문에 PRIMARY KEY의 열 값에 NULL이 허용돼요. 이것은 버그지만, 문제가 발견될 때쯤에는 이미 그 버그에 의존하는 데이터베이스가 너무 많아서 그 버그를 계속 지원하기로 결정했어요. PRIMARY KEY의 각 열에 NOT NULL 제약을 추가하면 이 문제를 우회할 수 있어요.

예외:

• INTEGER PRIMARY KEY 열의 값은 항상 NULL이 아닌 정수여야 해요. INTEGER PRIMARY KEY는 ROWID의 별명이기 때문이에요. INTEGER PRIMARY KEY 열에 NULL을 넣으면 SQLite가 자동으로 NULL을 고유한 정수로 변환해요.
• WITHOUT ROWID와 STRICT 기능은 이 버그가 발견된 뒤에 추가됐으므로, WITHOUT ROWID와 STRICT 테이블은 올바르게 동작해요: PRIMARY KEY에 NULL을 허용하지 않아요.

6. 집계 쿼리에 GROUP BY 절에 없는 비집계 결과 열이 포함될 수 있음

대부분의 SQL 구현에서 집계 쿼리의 출력 열은 집계 함수 또는 GROUP BY 절에 이름이 있는 열만 참조할 수 있어요. 집계 쿼리에서 일반 열을 참조하는 것은 타당하지 않은데, 각 출력 행이 입력 테이블(들)의 두 개 이상의 행으로 구성될 수 있기 때문이에요.

SQLite는 이 제한을 강제하지 않아요. 집계 쿼리의 출력 열은 GROUP BY 절에 없는 열을 포함한 임의의 표현식일 수 있어요. 이 기능에는 두 가지 용도가 있어요.

• SQLite에서는 (우리가 아는 다른 어떤 SQL 구현에서도 없지만) 집계 쿼리가 단일 min() 또는 max() 함수를 포함하면, 출력에 사용되는 열의 값은 min() 또는 max() 값이 달성된 행에서 가져와요. 두 개 이상의 행이 같은 min()/max() 값을 가지면 열 값은 그 행들 중 하나에서 임의로 선택돼요.

  예를 들어 가장 높은 급여를 받는 직원을 찾으려면:

  SELECT max(salary), first_name, last_name FROM employee;
  

  위 쿼리에서 first_name과 last_name 열의 값은 max(salary) 조건을 충족한 행에 해당해요.

• 쿼리가 집계 함수를 전혀 포함하지 않으면, GROUP BY 절을 DISTINCT ON 절의 대체로 추가할 수 있어요. 다시 말해 GROUP BY 값의 각각의 구별되는 집합에 대해 행 하나만 표시되도록 출력 행이 필터링돼요. GROUP BY 열에 대해 같은 값 집합을 가진 출력 행이 두 개 이상이면 그중 하나가 임의로 선택돼요. (SQLite는 DISTINCT를 지원하지만 DISTINCT ON은 지원하지 않으며, 그 기능은 GROUP BY로 제공돼요.)

7. SQLite는 기본적으로 완전한 유니코드 대소문자 변환을 하지 않음

SQLite는 모든 유니코드 문자의 대문자/소문자 구분을 알지 못해요. upper()와 lower() 같은 SQL 함수는 ASCII 문자에서만 동작해요. 여기에는 두 가지 이유가 있어요.

• 지금은 안정적이지만, SQLite가 처음 설계될 당시 유니코드 대소문자 변환 규칙은 여전히 변화 중이었어요. 즉 각 유니코드 릴리스마다 동작이 바뀌어 응용 프로그램을 방해하고 인덱스를 손상시킬 수 있었어요.
• 완전하고 적절한 유니코드 대소문자 변환에 필요한 테이블이 SQLite 라이브러리 전체보다 커요.

SQLite를 -DSQLITE_ENABLE_ICU 옵션으로 컴파일하고 International Components for Unicode 라이브러리와 링크하면 완전한 유니코드 대소문자 변환이 지원돼요.

8. 큰따옴표 문자열 리터럴이 허용됨

SQL 표준은 식별자에 큰따옴표, 문자열 리터럴에 작은따옴표를 요구해요. 예를 들어:

• "this is a legal SQL column name"
• 'this is an SQL string literal'

SQLite는 위 둘 다 받아들여요. 하지만 SQLite가 처음 설계될 당시 가장 널리 쓰이던 RDBMS 중 하나였던 MySQL 3.x와의 호환성을 위해, SQLite는 큰따옴표 문자열이 유효한 식별자와 일치하지 않으면 문자열 리터럴로도 해석해요.

이 잘못된 기능 때문에, 철자가 틀린 큰따옴표 식별자가 오류를 생성하는 대신 문자열 리터럴로 해석돼요. 또한 SQL 언어에 새로 온 개발자들이, 올바른 작은따옴표 문자열 리터럴 형식을 배워야 하는데 큰따옴표 문자열 리터럴을 쓰는 나쁜 습관에 빠지게 유인해요.

돌이켜 보면 MySQL 3.x 구문을 받아들이게 하는 시도를 하지 말았어야 하고, 큰따옴표 문자열 리터럴을 절대 허용하지 말았어야 해요. 하지만 큰따옴표 문자열 리터럴을 사용하는 응용 프로그램이 셀 수 없이 많아서, 레거시를 깨뜨리지 않기 위해 그 기능을 계속 지원해요.

SQLite 3.27.0(2019-02-07)부터 큰따옴표 문자열 리터럴을 사용하면 오류 로그로 경고 메시지가 보내져요.

SQLite 3.29.0(2019-07-10)부터 큰따옴표 문자열 리터럴 사용은 sqlite3_db_config()에 SQLITE_DBCONFIG_DQS_DDL과 SQLITE_DBCONFIG_DQS_DML 동작을 사용해 실행 시 비활성화할 수 있어요. 기본 설정은 컴파일 시 -DSQLITE_DQS=N 옵션으로 바꿀 수 있어요. 응용 프로그램 개발자는 이 잘못된 기능을 기본적으로 끄기 위해 -DSQLITE_DQS=0으로 컴파일할 것을 권장해요. 불가능하다면 다음과 같은 C 코드로 개별 데이터베이스 연결에 대해 큰따옴표 문자열 리터럴을 비활성화하세요.

sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DDL, 0, (void*)0);
sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DML, 0, (void*)0);

또는 큰따옴표 문자열 리터럴이 기본적으로 비활성화되어 있지만 일부 역사적 데이터베이스 연결에서 선택적으로 켜야 한다면, 위와 같은 C 코드에서 세 번째 매개변수를 0에서 1로 바꿔서 할 수 있어요.

SQLite 3.41.0(2023-02-21)부터 CLI에서는 SQLITE_DBCONFIG_DQS_DDL과 SQLITE_DBCONFIG_DQS_DML이 기본적으로 비활성화돼 있어요. 원한다면 ".dbconfig" 점(dot) 명령으로 레거시 동작을 다시 활성화할 수 있어요.

9. 키워드가 식별자로 자주 사용될 수 있음

SQL 언어는 키워드가 풍부해요. 대부분의 SQL 구현은 큰따옴표로 감싸지 않는 한 키워드를 식별자(테이블이나 열 이름)로 사용하는 것을 허용하지 않아요. 하지만 SQLite는 더 유연해요. 많은 키워드가, 그것들이 식별자로 의도된 것이 분명한 문맥에서 사용되는 한, 인용 없이도 식별자로 사용될 수 있어요.

예를 들어 다음 문장은 SQLite에서 유효해요:

CREATE TABLE union(true INT, with BOOLEAN);

같은 SQL 문장은 키워드 "union", "true", "with"를 식별자로 사용했기 때문에 우리가 아는 모든 다른 SQL 구현에서는 실패할 거예요.

키워드를 식별자로 사용하는 능력은 하위 호환성을 높여 줘요. 새 키워드가 추가되어도, 우연히 그 키워드를 테이블이나 열 이름으로 쓰는 레거시 스키마가 계속 동작해요. 하지만 키워드를 식별자로 사용하는 능력은 때때로 놀라운 결과를 낳아요. 예를 들어:

CREATE TRIGGER AFTER INSERT ON tableX BEGIN
  INSERT INTO tableY(b) VALUES(new.a);
END;

이전 문장으로 생성된 트리거의 이름은 "AFTER"이고 "BEFORE" 트리거예요. "AFTER" 토큰은 키워드 대신 식별자로 사용됐는데, 문장을 파싱하는 유일한 방법이기 때문이에요. 또 다른 예:

CREATE TABLE tableZ(INTEGER PRIMARY KEY);

tableZ 테이블에는 "INTEGER"라는 단일 열이 있어요. 그 열에는 데이터 타입이 지정되어 있지 않지만 PRIMARY KEY예요. 데이터 타입이 없으므로 그 열은 테이블의 INTEGER PRIMARY KEY가 아니에요. "INTEGER" 토큰은 데이터 타입 키워드가 아니라 열 이름의 식별자로 사용된 거예요.

10. 의심스러운 SQL이 오류나 경고 없이 허용됨

SQLite의 원래 구현은 부분적으로 "받아들이는 데 관대하라"고 말하는 Postel의 법칙을 따르고자 했어요. 이것은 예전에는 좋은 설계로 여겨졌어요—시스템이 지저분한 입력을 받아 과도하게 불평하지 않고 최선을 다하는 방식이요. 최근에는 오류를 더 쉽게 찾기 위해 받아들이는 데 엄격한 소프트웨어를 선호하는 추세가 됐어요.

이제 SQLite의 유연하고 관대한 설계 선택을 활용하는 응용 프로그램이 수백만 개나 있어요. 이런 레거시 응용 프로그램을 깨뜨리지 않고 SQLite를 현재 선호되는 엄격하고 교조적인 동작으로 바꿀 수는 없어요.

11. AUTOINCREMENT는 MySQL과 다르게 동작함

SQLite의 AUTOINCREMENT 기능은 MySQL에서보다 다르게 동작해요. 이는 처음에 MySQL에서 SQL을 배운 뒤 SQLite를 사용하기 시작해 두 시스템이 동일하게 동작할 것이라 기대하는 사람들에게 종종 혼란을 일으켜요.

SQLite에서 AUTOINCREMENT가 무엇을 하고 무엇을 하지 않는지에 대한 자세한 지침은 SQLite AUTOINCREMENT 문서를 참고하세요.

12. 텍스트 문자열에 NUL 문자가 허용됨

NUL 문자(ASCII 코드 0x00 및 유니코드 \u0000)는 SQLite의 문자열 중간에 나타날 수 있어요. 이는 예상치 못한 동작을 일으킬 수 있어요. 자세한 내용은 "문자열의 NUL 문자" 문서를 참고하세요.

13. SQLite는 정수 리터럴과 텍스트 리터럴을 구분함

SQLite는 다음 쿼리가 거짓을 반환한다고 말해요:

SELECT 1='1';

정수는 문자열이 아니기 때문이에요. SQLite의 창시자조차 이유를 이해하지 못하는 어떤 이유로, 다른 모든 주요 SQL 데이터베이스 엔진은 이것이 참이라고 말해요.

14. SQLite는 쉼표 조인의 우선순위를 다르게 처리함

SQLite는 모든 조인 연산자에 동일한 우선순위를 부여하고 왼쪽에서 오른쪽으로 처리해요. 하지만 이것은 정확히 옳지는 않아요. 쉼표 조인은 다른 모든 조인 연산자보다 우선순위가 낮아야 해요. 즉 다음과 같은 FROM 절은:

... FROM a, b RIGHT JOIN c, d ...

다음처럼 파싱되어야 해요:

JOIN
  JOIN
    D
    RIGHT JOIN
      A
      B
    C

하지만 SQLite는 대신 FROM 절을 이렇게 파싱해요:

JOIN
  RIGHT JOIN
    D
    JOIN
      C
      A
    B

이 문제는 같은 FROM 절에서 RIGHT OUTER JOIN이나 FULL OUTER JOIN을 쉼표 조인과 함께 사용할 때만 결과에 차이를 만들 수 있는데, 실제로는 드물게 발생해요. 그리고 FROM 절에 괄호를 사용하면 쉽게 해결할 수 있어요:

... FROM a, (b RIGHT JOIN c), d ...

더 알아보기 (Learn more)

• SQLite의 데이터 타입
• STRICT 테이블
• AUTOINCREMENT
• SQLite FAQ