小巧、快速、可靠。
任選三項。
SQLite 中的資料類型
► 目錄

1. SQLite 中的資料類型

大多數 SQL 資料庫引擎（就我們所知，除了 SQLite 之外的所有 SQL 資料庫引擎）都使用靜態、嚴格的類型。在靜態類型中，值的資料類型由其容器決定，也就是儲存該值的特定欄位。

SQLite 使用更通用的動態類型系統。在 SQLite 中，值的資料類型與值本身關聯，而不是與其容器關聯。SQLite 的動態類型系統與其他資料庫引擎更常見的靜態類型系統向後相容，也就是說，在靜態類型資料庫上運作的 SQL 語法在 SQLite 中運作方式相同。然而，SQLite 中的動態類型允許它執行傳統嚴格類型資料庫中無法執行的事項。 彈性類型是 SQLite 的功能，而不是錯誤。

更新：自 3.37.0 版（2021-11-27）起，SQLite 提供 STRICT 表格，針對偏好此類型的開發人員進行嚴格的類型強制執行。

2. 儲存類別與資料類型

儲存在 SQLite 資料庫（或由資料庫引擎處理）的每個值都具有下列儲存類別之一

儲存類別比資料類型更為廣泛。例如，INTEGER 儲存類別包含 7 種不同長度的整數資料類型。 這會在磁碟上產生差異。 但一旦 INTEGER 值從磁碟讀取到記憶體進行處理，就會轉換為最通用的資料類型（8 個位元組的有號整數）。因此，在大部分情況下，「儲存類別」與「資料類型」並無區別，這兩個術語可以互換使用。

SQLite 版本 3 資料庫中的任何欄位（INTEGER PRIMARY KEY 欄位除外）都可以用來儲存任何儲存類別的值。

SQL 陳述式中的所有值，無論是嵌入在 SQL 陳述式文字中的文字，或是繫結到 預編譯 SQL 陳述式 的 參數，都具有內含的儲存類別。在以下所述的情況下，資料庫引擎可能會在查詢執行期間將值在數字儲存類別（INTEGER 和 REAL）與 TEXT 之間轉換。

2.1. 布林資料類型

SQLite 沒有單獨的布林儲存類別。相反地，布林值儲存為整數 0（false）和 1（true）。

SQLite 辨識關鍵字「TRUE」和「FALSE」，自 3.23.0 版本（2018-04-02）開始，但這些關鍵字實際上只是整數文字 1 和 0 的替代拼寫。

2.2. 日期和時間資料類型

SQLite 沒有儲存類別專門用於儲存日期和/或時間。相反地，SQLite 內建的 日期和時間函數 能夠將日期和時間儲存為 TEXT、REAL 或 INTEGER 值

應用程式可以選擇以任何這些格式儲存日期和時間，並使用內建的 日期和時間函數 在格式之間自由轉換。

3. 型別親和性

使用嚴格型別的 SQL 資料庫引擎通常會嘗試自動將值轉換為適當的資料類型。考慮這一點 

CREATE TABLE t1(a INT, b VARCHAR(10));
INSERT INTO t1(a,b) VALUES('123',456);

嚴格型別的資料庫會在插入之前將字串「123」轉換為整數 123，將整數 456 轉換為字串「456」。

為了最大化 SQLite 與其他資料庫引擎之間的相容性，且為了讓上述範例在 SQLite 上運作時，如同在其他 SQL 資料庫引擎上運作一般，SQLite 支援欄位上的「類型相容性」概念。欄位的類型相容性是建議儲存在該欄位中的資料類型。此處的重要概念是類型為建議的，而非必要的。任何欄位仍可儲存任何類型的資料。只是在給定選項的情況下，有些欄位會偏好使用一種儲存類別，而非另一種。欄位的偏好儲存類別稱為其「相容性」。

SQLite 3 資料庫中的每個欄位都指定下列類型相容性之一

（歷史備註：「BLOB」類型相容性過去稱為「NONE」。但此術語容易與「無相容性」混淆，因此更名。）

具有 TEXT 相容性的欄位使用 NULL、TEXT 或 BLOB 儲存類別儲存所有資料。如果將數值資料插入具有 TEXT 相容性的欄位，則在儲存前會將其轉換為文字形式。

具有 NUMERIC 相關性的欄位可能包含使用所有五個儲存類別的值。當文字資料插入 NUMERIC 欄位時，如果文字是格式良好的整數或實數文字，文字的儲存類別會轉換為 INTEGER 或 REAL（依喜好順序）。如果 TEXT 值是格式良好的整數文字，但太大而無法放入 64 位元有號整數中，它會轉換為 REAL。對於 TEXT 和 REAL 儲存類別之間的轉換，只會保留數字的前 15 個有效小數位。如果 TEXT 值不是格式良好的整數或實數文字，則該值會儲存為 TEXT。就本段落而言，十六進位整數文字不被視為格式良好，並儲存為 TEXT。（這是為了與 SQLite 在 版本 3.8.6 2014-08-15 之前所發布的版本相容，當時十六進位整數文字首次導入 SQLite。）如果可以準確表示為整數的浮點值插入具有 NUMERIC 相關性的欄位中，該值會轉換成整數。不會嘗試轉換 NULL 或 BLOB 值。

字串可能看起來像具有小數點和/或指數符號的浮點文字，但只要該值可以表示為整數，NUMERIC 相關性就會將其轉換為整數。因此，字串 '3.0e+5' 會儲存在具有 NUMERIC 相關性的欄位中，作為整數 300000，而不是浮點值 300000.0。

使用 INTEGER 相關性的欄位行為與具有 NUMERIC 相關性的欄位相同。INTEGER 和 NUMERIC 相關性之間的差異只會在 CAST 表達式 中顯現：「CAST(4.0 AS INT)」表達式會傳回整數 4，而「CAST(4.0 AS NUMERIC)」會將值保留為浮點數 4.0。

具有 REAL 相容性的欄位行為類似於具有 NUMERIC 相容性的欄位，但它會強制整數值轉換成浮點數表示法。（作為內部最佳化，沒有小數部分且儲存在具有 REAL 相容性的欄位中的小浮點數值會寫入磁碟作為整數，以減少空間，並在讀取值時自動轉換回浮點數。此最佳化在 SQL 層級完全不可見，只能透過檢查資料庫檔案的原始位元來偵測。）

具有 BLOB 相容性的欄位不會偏好一個儲存類別，也不會嘗試將資料從一個儲存類別轉換到另一個儲存類別。

3.1. 欄位相容性的決定

對於未宣告為 STRICT 的表格，欄位的相容性由欄位的宣告類型決定，依據下列規則按順序執行

  1. 如果宣告類型包含字串「INT」，則會指定為 INTEGER 相容性。

  2. 如果欄位的宣告類型包含任何字串「CHAR」、「CLOB」或「TEXT」，則該欄位具有 TEXT 相容性。請注意，類型 VARCHAR 包含字串「CHAR」，因此指定為 TEXT 相容性。

  3. 如果欄位的宣告類型包含字串「BLOB」，或未指定類型，則欄位具有 BLOB 相容性。

  4. 如果欄位的宣告類型包含任何字串「REAL」、「FLOA」或「DOUB」，則欄位具有 REAL 相容性。

  5. 否則，相容性為 NUMERIC。

請注意，決定欄位相容性的規則順序很重要。宣告類型為「CHARINT」的欄位將符合規則 1 和 2，但第一個規則優先，因此欄位相容性將為 INTEGER。

3.1.1. 相容性名稱範例

下表顯示前一節的五個規則如何將來自較傳統 SQL 實作的許多常見資料類型名稱轉換為相容性。此表僅顯示 SQLite 會接受的資料類型名稱的一小部分。請注意，類型名稱後面的括號中的數字引數（例如：「VARCHAR(255)」）會被 SQLite 忽略 - SQLite 沒有對字串、BLOB 或數字值的長度施加任何長度限制（除了大型全域 SQLITE_MAX_LENGTH 限制）。

範例類型名稱從
CREATE TABLE 陳述
或 CAST 運算式 		結果關聯性 用於決定關聯性的規則
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」類型會產生 INTEGER 關聯性，而不是 REAL 關聯性，這是因為「POINT」結尾的「INT」。而宣告的「STRING」類型具有 NUMERIC 關聯性，而不是 TEXT。

3.2. 運算式的關聯性

每個表格欄位都有類型關聯性（BLOB、TEXT、INTEGER、REAL 或 NUMERIC 之一），但運算式不一定具有關聯性。

運算式關聯性由下列規則決定

3.3. 檢視和子查詢的欄位關聯性

VIEW 或 FROM 子句子查詢的「欄位」實際上是實作 VIEW 或子查詢的 SELECT 陳述式結果集中的表達式。因此，VIEW 或子查詢的欄位關聯性由上述表達式關聯性規則決定。考慮一個範例 

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 欄位的關聯性將與 t1.b (TEXT) 的關聯性相同，因為 v1.x 直接對應到 t1.b。但 v1.y 和 v1.z 欄位都沒有關聯性，因為這些欄位對應到表達式 a+c 和 42，而表達式永遠沒有關聯性。

3.3.1. 複合檢視的欄位關聯性

當實作 VIEW 或 FROM 子句子查詢的 SELECT 陳述式為 複合 SELECT 時，VIEW 或子查詢的每個欄位的關聯性將是組成複合陳述式的個別 SELECT 陳述式之一的對應結果欄位的關聯性。然而，無法確定哪個 SELECT 陳述式將用於決定關聯性。不同的組成 SELECT 陳述式可能會在查詢評估的不同時間用於決定關聯性。選擇可能會在 SQLite 的不同版本中有所不同。選擇可能會在 SQLite 的同一個版本中的一個查詢和下一個查詢之間改變。選擇可能會在同一個查詢中的不同時間有所不同。因此，您永遠無法確定具有組成子查詢中不同關聯性的複合 SELECT 的欄位將使用什麼關聯性。

如果您關心結果的資料類型，最佳做法是避免在複合 SELECT 中混合相似性。在複合 SELECT 中混合相似性可能會導致令人驚訝且不直觀的結果。例如，請參閱 論壇文章 02d7be94d7。

3.4. 欄位相似性行為範例

下列 SQL 展示 SQLite 如何在將值插入資料表時使用欄位相似性進行類型轉換。

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 具有常見的 SQL 比較運算子，包括「=」、「==」、「<」、「<=」、「>」、「>=」、「!=」、「」、「IN」、「NOT IN」、「BETWEEN」、「IS」和「IS NOT」，。

4.1. 排序順序

比較結果取決於運算元的儲存類別，依照下列規則

4.2. 比較前類型轉換

在執行比較之前，SQLite 可能會嘗試在 INTEGER、REAL 和/或 TEXT 儲存類別之間轉換值。是否在比較發生之前嘗試任何轉換取決於運算元的類型相似性。

「套用相似性」意指將運算元轉換為特定儲存類別，但前提是轉換不會遺失重要資訊。數值永遠可以轉換為 TEXT。如果文字內容是格式良好的整數或實數文字，則 TEXT 值可以轉換為數值，但不能是十六進位整數文字。BLOB 值會透過將二進位 BLOB 內容解釋為目前資料庫編碼中的文字字串，進而轉換為 TEXT 值。

相似性會套用於比較運算元的運算元，套用時機在比較之前，且會根據下列規則依序套用

運算式「a BETWEEN b AND c」會視為兩個獨立的二元比較「a >= b AND a <= c」，即使這表示在每個比較中會將不同的相似性套用於「a」。在「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 運算式。

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. 運算子

數學運算子（+、-、*、/、%、<<、>>、& 和 |）將兩個運算元都解釋為數字。字串或二進位大型物件運算元會自動轉換為 REAL 或 INTEGER 值。如果字串或二進位大型物件看起來像實數（如果它有小數點或指數）或如果值超出可以表示為 64 位元有號整數的範圍，則會轉換為 REAL。否則，運算元會轉換為 INTEGER。數學運算元的隱含類型轉換與 CAST to NUMERIC 略有不同，因為看起來像實數但沒有小數部分的字串和二進位大型物件值會保留為 REAL，而不是像 CAST to NUMERIC 那樣轉換為 INTEGER。即使有損失且不可逆，也會執行從字串或二進位大型物件到 REAL 或 INTEGER 的轉換。有些數學運算子（%、<<、>>、& 和 |）需要 INTEGER 運算元。對於這些運算子，REAL 運算元會以與 CAST to INTEGER 相同的方式轉換為 INTEGER。<<、>>、& 和 | 運算子總是傳回 INTEGER（或 NULL）結果，但 % 運算子會根據其運算元的類型傳回 INTEGER 或 REAL（或 NULL）。數學運算元上的 NULL 運算元會產生 NULL 結果。數學運算元上的運算元看起來完全不像數字且不為 NULL，則會轉換為 0 或 0.0。除以零會產生 NULL 結果。

6. 排序、分組和複合 SELECT

當查詢結果依 ORDER BY 子句排序時，儲存類別為 NULL 的值會先出現，接著是穿插以數字順序出現的 INTEGER 和 REAL 值，接著是按校對順序出現的 TEXT 值，最後是按 memcmp() 順序出現的 BLOB 值。排序前不會進行任何儲存類別轉換。

使用 GROUP BY 子句對值進行分組時，具有不同儲存類型的值被視為相異，但如果 INTEGER 和 REAL 值在數值上相等，則視為相等。GROUP BY 子句不會對任何值套用相似性。

複合 SELECT 營運子 UNION、INTERSECT 和 EXCEPT 會對值執行隱含比較。不會對 UNION、INTERSECT 或 EXCEPT 相關聯的隱含比較套用相似性比較運算元，而是依原樣比較值。

7. 校對順序

當 SQLite 比較兩個字串時，它會使用校對順序或校對函數（兩個術語表示相同意思）來判斷哪個字串較大或兩個字串是否相等。SQLite 有三個內建校對函數：BINARY、NOCASE 和 RTRIM。

應用程式可以使用 sqlite3_create_collation() 介面註冊其他校對函數。

校對函數僅在比較字串值時才重要。數值值總是根據數值進行比較，而 BLOB 則是始終使用 memcmp() 逐位元組比較。

7.1. 從 SQL 指定校對順序

每個表格的每一欄都有一個相關的校對函數。如果沒有明確定義校對函數，則校對函數預設為 BINARY。欄位定義 的 COLLATE 子句用於定義欄位的替代校對函數。

決定對二元比較運算子（=、<、>、<=、>=、!=、IS 和 IS NOT）使用哪個校對函數的規則如下

  1. 如果任一運算元使用後置 COLLATE 運算子 有明確的校對函數指定，則會使用明確的校對函數進行比較，優先於左運算元的校對函數。

  2. 如果任一運算元是欄位，則會使用該欄位的校對函數，優先於左運算元。就前一句的目的而言，一個或多個一元「+」運算子和/或 CAST 運算子之前的欄位名稱仍被視為欄位名稱。

  3. 否則，會使用 BINARY 校對函數進行比較。

如果運算元的任何子運算式使用後置 COLLATE 運算子，則比較運算元的運算元被視為有明確的校對函數指定（上述規則 1）。因此，如果在比較運算式中任何地方都使用 COLLATE 運算子，則由該運算子定義的校對函數用於字串比較，無論該運算式中可能有哪些表格欄位。如果在比較中任何地方都出現兩個或多個 COLLATE 運算子 子運算式，則會使用最左邊的明確校對函數，而不管 COLLATE 運算子在運算式中嵌套的深度如何，也不管運算式如何加上括號。

「x BETWEEN y and z」表達式在邏輯上等於兩個比較「x >= y AND x <= z」，並且在整理功能方面運作，就像兩個單獨的比較一樣。「x IN (SELECT y ...)」表達式在確定整理順序時，會以與「x = y」表達式相同的方式處理。用於「x IN (y, z, ...)」形式表達式的整理順序，就是 x 的整理順序。如果 IN 運算子需要明確的整理順序，則應將其套用於左運算元，如下所示：「x COLLATE nocase IN (y,z, ...)」。

SELECT 陳述式中 ORDER BY 子句的條款，可以使用COLLATE 運算子指定整理順序，在這種情況下，指定的整理功能會用於排序。否則，如果 ORDER BY 子句排序的表達式是欄位，則會使用欄位的整理順序來決定排序順序。如果表達式不是欄位，且沒有 COLLATE 子句，則會使用 BINARY 整理順序。

7.2. 整理順序範例

以下範例識別整理順序，這些順序會用於決定各種 SQL 陳述式可能執行的文字比較結果。請注意，在數字、blob 或 NULL 值的情況下，可能不需要文字比較，也不會使用整理順序。 

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

此頁面最後修改於 2022-04-27 09:17:51 UTC