SQLite バージョン 2 は、すべてのカラムの値を ASCII テキストで保存していました。 バージョン3は整数と浮動小数点数をよりコンパクトな形式で保存する能力を持つように拡張されました。 さらに、BLOB データを格納する能力も持ちます。
SQLite データベースに格納された（またはデータベースエンジンによって操作された）各値は 以下のストレージクラスのうちいずれかを持ちます。
NULL . 値は NULL 値です。
INTEGER . 値は符号付き整数です。必要に応じて 1、2、3、4、6、8 バイトで格納されます。
REAL . 値は浮動小数値です。 8バイトの IEEE 浮動小数点数で格納されます。
TEXT . 値はテキスト文字列です。 データベースエンコーディング（UTF-8、UTF-16BE、UTF-16-LE）で 格納されます。
BLOB . 値はBLOBです。 入力そのままに格納されます。
バージョン 3 データベースの INTEGER PRIMARY KEY 以外のすべてのカラムにあらゆる型の値が 格納される可能性があり、この点についてはバージョン 2 と変わりありません。 この規則の例外については、「ストリクトアフィニティーモード」で説明します。
SQL ステートメントに組み込まれた定数かコンパイル済みの SQL ステートメントにバインドされた 値であるかにかかわらず SQLite に供給されるすべての値は、SQL ステートメントの実行前に ストレージクラスが割り当てられます。 以下で説明する状況下で、データベースエンジンが問い合わせの実行中に数値のストレージクラスと TEXT 間で値を変換する場合があります。
ストレージクラスは以下のように最初に割り当てられます。
SQL ステートメントの一部としてリテラルで指定された値は以下のストレージクラスが割り当てられます。 引用符か二重引用符で囲まれているなら TEXT。 小数点も指数部もなく引用符で囲まれていないなら INTEGER。 少数点や指数部を持ち引用符で囲まれていないなら REAL。 値が NULL であれば NULL。 ストレージクラスが BLOB のリテラルは X'ABCD' という表記法で指定します。
sqlite3_bind_* API を使って供給される値は、バインドされる固有の型と最もマッチする ストレージクラスが割り当てられます。 （たとえば、sqlite3_bind_blob() はストレージクラス BLOB で値をバインドします。）
SQL スカラー演算子によって生成される値のストレージクラスは、式の最も外側の演算子に 依存します。 ユーザー定義関数はあらゆるストレージクラスの値を返す可能性があります。 コンパイル時に式の結果のストレージクラスを決定するのは通常不可能です。
SQLite バージョン 3 において、値の型は、値が格納されるカラムや変数ではなく 値自身と関連付けられます。 （これはしばしば マニフェストタイピングと呼ばれます。） スタティックタイピングでより制約が多いシステムである他の多くの SQL データベース エンジンでは、型が値ではなくコンテナに関連付けられます。
他のデータベースエンジンとの互換性を最大とするため、SQLite は「タイプアフィニティ」 コンセプトをサポートします。 カラムのタイプアフィニティーは、そのカラムへ格納されるデータに推奨される推奨される型です。 ここで注目してほしいのは、必要とされるではなく推奨されるということです。 理論上、どのようなカラムにも、どのような型のデータでも格納できます。 若干のカラムにおいて他のストレージクラスも選択できるにかかわらず単一のストレージクラスと 親和性を持つ場合があります。 カラムの優先ストレージクラスを「アフィニティー」と呼びます。
SQLite 3 データベースの各カラムには以下のタイプアフィニティーのどれかが割り当て られます。
TEXT アフィニティーを持つカラムは、ストレージクラス NULL、TEXT、BLOB を使って すべてのデータを格納します。 数値データが TEXT アフィニティーを持つカラムに挿入される場合、テキスト形式に変換後 格納されます。
NUMERIC アフィニティーを持つカラムは、五つのストレージクラスすべてで値を格納できます。 テキストデータが NUMERIC カラムに挿入される場合、格納前に整数あるいは実数への変換を 試行します。 変換が成功した場合、INTENGER あるいは REAL ストレージクラスを使って値が格納されます。 変換が実行できない場合、TEXT ストレージクラスを使って値が格納されます。 NULL あるいは blob 値に対して変換が試みられることはありません。
INTEGER アフィニティーを持つカラムに浮動小数点部のない実数値（あるいはそういった ものに変換されるテキスト値）が挿入される場合、整数に変換後 NUMERIC アフィニティーを使って 格納されます。 それ以外は、NUMERIC アフィニティーを持つカラムと同様に動作します。
REAL アフィニティーを持つカラムは、整数値を浮動小数表記に強制します。 それ以外は、NUMERIC アフィニティーを持つカラムと同様に動作します。 （最適化として、スペースを節約するため整数値は整数としてディスクに格納し、テーブルから 値が読み込まれるときに浮動小数点に変換されます。）
NONE アフィニティーを持つカラムは、特定のストレージクラスと親和性を持ちません。 したがって、挿入される前に変換を試みることもありません。
カラムのタイプアフィニティーは以下の規則に従いカラムの宣言型によって決定されます。
データ型が文字列「INT」を含む場合、INTEGER アフィニティーが割り当てられます。
カラムのデータ型が文字列「CHAR」「CLOB」「TEXT」のいずれかを含む場合、カラムは TEXT アフィニティーを持ちます。 VARCHAR 型は文字列「CHAR」を含むので、TEXT アフィニティーが割り当てられることに 注意してください。
カラムのデータ型が文字列「BLOB」を含むかあるいはデータ型が指定されない場合、カラムは アフィニティー NONE を持ちます。
カラムのデータ型が文字列「REAL」「FLOA」「DOUB」のいずれかを含む場合、カラムは REAL アフィニティーを持ちます。
それ以外の場合、アフィニティーは NUMERIC です。
テーブルが「CREATE TABLE <table> AS SELECT...」ステートメントを使って作成された 場合、すべてのカラムはデータ型の指定を持たずアフィニティーも与えられません。
CREATE TABLE t1(
t TEXT,
nu NUMERIC,
i INTEGER,
no BLOB
);
-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, TEXT
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');
-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
SQLite バージョン 2 同様 バージョン 3 も、二つ組みの比較演算子「=」「<」「<=」 「>=」「!=」、包含関係をテストする「IN」、三つ組比較演算子「BETWEEN」機能を持ちます。
比較の結果は以下の規則に従い二つの値のストレージクラスの比較に依存します。
ストレージクラス NULL を持つ値は（ストレージクラス NULL を持つ別の値を含む）他の あらゆる値より小さい。
INTEGER または REAL 値は、あらゆる TEXT あるいは BLOB 値よりも小さい。 INTEGER あるいは REAL が、別の INTEGER あるいは REAL 値と比較される場合、数値の比較が 行われます。
TEXT 値は BLOB 値より小さい。 二つの TEXT 値が比較される場合、通常、C ライブラリ関数 memcmp() を結果の決定に使います。 しかしながら、下記の「ユーザー定義の照合順序」で説明するように、この動作はオーバーライド できます。
二つの BLOB 値が比較される場合、結果は常に memcmp() を使って決定されます。
SQLite は比較前に数値ストレージクラス（INTEGER と REAL）と TEXT 間で値の変換を試みる 場合があります。 これは、以下に列挙したケースで二つ組みの比較のためになされます。 下記の黒丸の場所で使われる用語「式」は、すべての SQL スカラ式あるいはカラム値を除く リテラルを意味します。 X と Y.Z がカラム名である場合、+X と +Y.Z が式とみなされることに注意してください。
カラム値が式の結果と比較される場合、比較前にカラムのアフィニティーが式の結果に 適用されます。
二つのカラムの値が比較されるとき、カラムの一つが INTEGER または REAL または NUMERIC のアフィニティーを持ち、他方がそうでない場合、NUMERIC 以外のカラムから抽出された ストレージクラス TEXT を持つ値は NUMERIC アフィニティーが適用されます。
二つの式の結果が比較される場合、変換は起こりません。 結果はそのまま比較されます。 文字列と数が比較される場合、常に数は文字列より小さい。
SQLite においては、式の評価に必要な比較のそれぞれで異なるアフィニティーが「a」に 適用されるとしても、式「a BETWEEN b AND c」と「a >= b AND a <= c」は等価です。
「a IN (SELECT b ....)」形式の式は、二つ組みの比較を上記で列挙した三種の規則で処理 します（たとえば「 a = b」と類似の方法で）。 たとえば、「b」がカラム値で「a」が式の場合、比較の前に「b」のアフィニティーが「a」に 適用されます。
SQLite は、式「a IN (x, y, z)」と「a = +x OR a = +y OR a = +z」を等価と扱います。 IN 演算子の右の値（このサンプルで「x」「y」「z」の値）は、カラム値であったとしても 式とみなされます。 IN 演算子の左の値がカラムの場合、そのカラムのアフィニティーが使われます。 値が式の場合、変換は起こりません。
CREATE TABLE t1(
a TEXT,
b NUMERIC,
c BLOB
);
-- Storage classes for the following row:
-- TEXT, REAL, TEXT
INSERT INTO t1 VALUES('500', '500', '500');
-- 60 and 40 are converted to '60' and '40' and values are compared as TEXT.
SELECT a < 60, a < 40 FROM t1;
1|0
-- Comparisons are numeric. No conversions are required.
SELECT b < 60, b < 600 FROM t1;
0|1
-- Both 60 and 600 (storage class NUMERIC) are less than '500'
-- (storage class TEXT).
SELECT c < 60, c < 600 FROM t1;
0|0
すべての数学演算子（連接演算子「||」以外すべての演算子）は、実行前にすべての オペランドに対して NUMERIC アフィニティーを適用します。 一つあるいは両方のオペランドが NUMERIC に変換できない場合、演算の結果は NULL です。
連接演算子では、TEXT アフィニティーが双方のオペランドに適用されます。 いずれかのオペランドが（NULL あるいはBLOB であるため）TEXT に変換できない場合、結合の 結果は NULL です。
When values 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 usually in memcmp() order, and finally BLOB values in memcmp() order. No storage class conversions occur before the sort.
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. No affinities are applied to any values as the result of a GROUP by clause.
The compound SELECT operators UNION, INTERSECT and EXCEPT perform implicit comparisons between values. Before these comparisons are performed an affinity may be applied to each value. The same affinity, if any, is applied to all values that may be returned in a single column of the compound SELECT result set. The affinity applied is the affinity of the column returned by the left most component SELECTs that has a column value (and not some other kind of expression) in that position. If for a given compound SELECT column none of the component SELECTs return a column value, no affinity is applied to the values from that column before they are compared.
The above sections describe the operation of the database engine in 'normal' affinity mode. SQLite version 3 will feature two other affinity modes, as follows:
Strict affinity mode. In this mode if a conversion between storage classes is ever required, the database engine returns an error and the current statement is rolled back.
No affinity mode. In this mode no conversions between storage classes are ever performed. Comparisons between values of different storage classes (except for INTEGER and REAL) are always false.
By default, when SQLite compares two text values, the result of the comparison is determined using memcmp(), regardless of the encoding of the string. SQLite v3 provides the ability for users to supply arbitrary comparison functions, known as user-defined "collation sequences" or "collating functions", to be used instead of memcmp().
Aside from the default collation sequence BINARY, implemented using memcmp(), SQLite features two extra built-in collation sequences intended for testing purposes, the NOCASE and RTRIM collations:
Each column of each table has a default collation type. If a collation type other than BINARY is required, a COLLATE clause is specified as part of the column definition to define it.
Whenever two text values are compared by SQLite, a collation sequence is used to determine the results of the comparison according to the following rules. Sections 3 and 5 of this document describe the circumstances under which such a comparison takes place.
For binary comparison operators (=, <, >, <= and >=) if either operand is a column, then the default collation type of the column determines the collation sequence to use for the comparison. If both operands are columns, then the collation type for the left operand determines the collation sequence used. If neither operand is a column, then the BINARY collation sequence is used. For the purposes of this paragraph, a column name preceded by one or more unary "+" operators is considered a column name.
The expression "x BETWEEN y and z" is equivalent to "x >= y AND x <= z". The expression "x IN (SELECT y ...)" is handled in the same way as the expression "x = y" for the purposes of determining the collation sequence to use. The collation sequence used for expressions of the form "x IN (y, z ...)" is the default collation type of x if x is a column, or BINARY otherwise.
An ORDER BY clause that is part of a SELECT statement may be assigned a collation sequence to be used for the sort operation explicitly. In this case the explicit collation sequence is always used. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the default collation type of the column is used to determine sort order. If the expression is not a column, then the BINARY collation sequence is used.
The examples below identify the collation sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collation sequence used, in the case of numeric, blob or NULL values.
CREATE TABLE t1(
a, -- default collation type BINARY
b COLLATE BINARY, -- default collation type BINARY
c COLLATE REVERSE, -- default collation type REVERSE
d COLLATE NOCASE -- default collation type NOCASE
);
-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = b) FROM t1;
-- Text comparison is performed using the NOCASE collation sequence.
SELECT (d = a) FROM t1;
-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = d) FROM t1;
-- Text comparison is performed using the REVERSE collation sequence.
SELECT ('abc' = c) FROM t1;
-- Text comparison is performed using the REVERSE collation sequence.
SELECT (c = 'abc') FROM t1;
-- Grouping is performed using the NOCASE collation sequence (i.e. values
-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;
-- Grouping is performed using the BINARY collation sequence.
SELECT count(*) GROUP BY (d || '') FROM t1;
-- Sorting is performed using the REVERSE collation sequence.
SELECT * FROM t1 ORDER BY c;
-- Sorting is performed using the BINARY collation sequence.
SELECT * FROM t1 ORDER BY (c || '');
-- Sorting is performed using the NOCASE collation sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;
This page last modified 2008/06/26
02:51:28 UTC