SQLite使用(二)&&データ型
4623 ワード
1.概要
よく知られているデータベース・エンジンの大部分は静的データ型を採用しています.すなわち、カラム定義のタイプは値の格納を定義し、値はカラムの定義を厳格に満たす必要があります.同じカラムのすべての値の格納方法は同じです.たとえば、カラムタイプがintであることを定義し、そのカラムに「abc」を入力できません.SQLiteのデータ型は動的タイプを採用しており、カラム定義では値を決定できないストレージであり、値のストレージは値そのもので決定されるため、SQLiteでは同じカラムに複数のストレージ方式がある.
2.データ型とストレージクラス
SQLiteは、値のストレージに基づいて、次のストレージタイプに分類されます.
ストレージカテゴリ
意味
NULL
この値がNULLであることを示します
INTEGER
シンボル整数値があり、値の大きさに応じて記憶に占めるバイト数を制御し、1,2,3,4,6 or 8バイトである可能性がある
REAL
浮動小数点値は、8バイトのストレージを採用し、実質的にdoubleストレージを採用し、有効数字は15ビットである.
TEXT
テキスト文字列、記憶に使用する符号化方式はUTF-8、UTF-16 BE、UTF-16 LEであり、デフォルトでは最大1 Gバイトを記憶できる
BLOB
バイナリデータを格納し、デフォルトでは最大1 Gバイトを格納できます.
SQLiteでは、記憶分類とデータ型は完全に等価ではありません.例えば、INTEGER記憶カテゴリには6種類の異なる長さのIntegerデータ型が含まれていてもよいが、これらのINTEGERデータがメモリに読み込まれると、SQLiteはすべて8バイトのシンボル整数型と見なします.したがって、SQLiteについては、同じフィールドタイプであるため、そのフィールドには継続型のデータを格納することができ、値の格納タイプが同じであっても、下位ストレージが占有する空間は値に関連している.例えば、INTEGERが1バイト、INTEGERが8バイトを占有する場合がある.
(1).ブールデータ型:
SQLiteは、専用のブールストレージタイプを提供していません.代わりに、ストレージ整数1はtrueを表し、0はfalseを表します.
(2).日付と時刻のデータ型:
SQLiteは、ブールタイプと同様に、専用の日時格納タイプを提供しないで、TEXT、REAL、INTEGERタイプのそれぞれ異なるフォーマットでこのタイプを表します.
TEXT: "YYYY-MM-DD HH:MM:SS.SSS"
REAL:Julian日付形式で格納
INTEGER:Unix時間形式でデータ値を保存します.つまり、1970-01-01 00:00:00から現在の時間までの秒数です.
SQLiteはtypeof関数を提供し、ユーザーはこの関数に基づいて所定の値の記憶タイプを決定することができる.
3.類型親縁性
SQLiteと他のデータベース・エンジン間のデータ型互換性を最大化するために、SQLiteは「型親縁性(Type Affinity)」という概念を提案した.テーブルフィールドが宣言された後、SQLiteはフィールドによって宣言されたタイプに基づいて親縁タイプを選択し、データが挿入されると、親縁タイプが値として優先的に格納されます.親縁タイプが一致しないか、現在のデータを親縁タイプに変換できない限り、SQLiteは値に適した他のタイプを考慮して格納されます.SQLiteの現在のバージョンでは、次の5つの親縁タイプがサポートされています.
親のタイプ
説明
TEXT
数値型データは、挿入する前にテキスト形式に変換してからターゲットフィールドに挿入する必要があります.
NUMERIC
親がNUMERICのフィールドにテキストデータが挿入されると、変換操作によってデータ情報が失われ、完全に可逆的になることがない場合、SQLiteはそのテキストデータをINTEGERまたはREALタイプのデータに変換し、変換に失敗した場合もSQLiteはTEXT方式でデータを格納する.NULLまたはBLOBタイプの新しいデータの場合、SQLiteは変換せずにNULLまたはBLOBでデータを直接保存します.さらに、浮動小数点フォーマットの定数テキスト(3000.0など)については、数値情報を失うことなくINTEGERに変換できる場合、SQLiteはINTEGERの格納方法に変換します.
INTEGER
親縁タイプがINTEGERのフィールドの場合、そのルールはNUMERICに等しく、CAST式を実行するときに唯一の違いがあります.
REAL
そのルールは基本的にNUMERICと同等であり、唯一の違いは「3000.0」のようなテキストデータをINTEGERストレージに変換しないことである.
NONE
何の変換もせずに、そのデータが属するデータ型で直接格納します.
3.1.フィールドの親関係のルール
フィールドの親縁性は、このフィールドが宣言時に定義されるタイプに基づいて決定され、具体的なルールは以下のリストを参照することができる.フィールドタイプが両方の親縁性に合致する場合、前のルールが先に機能するリストの順序に注意してください. 1). タイプ文字列にINTが含まれている場合、このフィールドの親のタイプはINTEGERです. 2). タイプ文字列に「CHAR」、「CLOB」または「TEXT」が含まれている場合、このフィールドの親縁タイプはVARCHARなどのTEXTです. 3). タイプ文字列に「BLOB」が含まれている場合、このフィールドの親のタイプはNONEです. 4). タイプ文字列に「REAL」、「FLOA」または「DOUB」が含まれている場合、このフィールドの親縁タイプはREALである. 5). 残りの場合、フィールドの親縁タイプはNUMERICです.3.2.具体例
宣言タイプ
親のタイプ
ルールの適用
INTINTEGERTINYINTSMALLINTMEDIUMINTBIGINTUNSIGNED BIG INTINT2INT8
INTEGER
1
CHARACTER(20)VARCHAR(255)VARYING CHARACTER(255)NCHAR(55)NATIVE CHARACTER(70)NVARCHAR(100)TEXTCLOB
TEXT
2
BLOB
NONE
3
REALDOUBLEDOUBLE PRECISIONFLOAT
REAL
4
NUMERICDECIMAL(10,5)BOOLEANDATEDATETIME
NUMERIC
5
4.比較と並べ替え
SQLite 3でサポートされる比較式は、"=","=","<","<=",">",">=","!=","<>","IN","NOT IN","BETWEEN","IS"and"IS NOT"である.データの比較結果は、主にオペランドの格納方法に依存し、そのルールは次のとおりです.
1). 記憶方式はNULLの値が他の記憶タイプの値より小さい.
2). 記憶方式はINTEGERとREALの値がTEXTまたはBLOBタイプの値より小さく、同じINTEGERまたはREALであれば数値ルールに基づいて比較する.
3). 記憶方式はTEXTの値がBLOBタイプの値より小さい.
4). 2つのBLOBタイプの値を比較すると、その結果はCランタイム関数memcmp()の結果となります.
5). 同じTEXTの場合、SQLiteは特定の比較ルールで判断し、3つの比較ルールをサポートします.
校正規則
意味
binary
比較文字列はmemcmp()を採用し、文字列がどの符号化であっても
nocase
binaryと似ていますが、大文字と小文字は無視されます.
rtrim
binaryと似ていますが、文字列の末尾のスペースは無視されます.
次のように、テーブル文を作成して、指定した列に校正規則を指定します.
5.参考文書
https://www.sqlite.org/datatype3.html
http://www.cnblogs.com/stephen-liu74/archive/2012/01/18/2325258.html
よく知られているデータベース・エンジンの大部分は静的データ型を採用しています.すなわち、カラム定義のタイプは値の格納を定義し、値はカラムの定義を厳格に満たす必要があります.同じカラムのすべての値の格納方法は同じです.たとえば、カラムタイプがintであることを定義し、そのカラムに「abc」を入力できません.SQLiteのデータ型は動的タイプを採用しており、カラム定義では値を決定できないストレージであり、値のストレージは値そのもので決定されるため、SQLiteでは同じカラムに複数のストレージ方式がある.
2.データ型とストレージクラス
SQLiteは、値のストレージに基づいて、次のストレージタイプに分類されます.
ストレージカテゴリ
意味
NULL
この値がNULLであることを示します
INTEGER
シンボル整数値があり、値の大きさに応じて記憶に占めるバイト数を制御し、1,2,3,4,6 or 8バイトである可能性がある
REAL
浮動小数点値は、8バイトのストレージを採用し、実質的にdoubleストレージを採用し、有効数字は15ビットである.
TEXT
テキスト文字列、記憶に使用する符号化方式はUTF-8、UTF-16 BE、UTF-16 LEであり、デフォルトでは最大1 Gバイトを記憶できる
BLOB
バイナリデータを格納し、デフォルトでは最大1 Gバイトを格納できます.
SQLiteでは、記憶分類とデータ型は完全に等価ではありません.例えば、INTEGER記憶カテゴリには6種類の異なる長さのIntegerデータ型が含まれていてもよいが、これらのINTEGERデータがメモリに読み込まれると、SQLiteはすべて8バイトのシンボル整数型と見なします.したがって、SQLiteについては、同じフィールドタイプであるため、そのフィールドには継続型のデータを格納することができ、値の格納タイプが同じであっても、下位ストレージが占有する空間は値に関連している.例えば、INTEGERが1バイト、INTEGERが8バイトを占有する場合がある.
(1).ブールデータ型:
SQLiteは、専用のブールストレージタイプを提供していません.代わりに、ストレージ整数1はtrueを表し、0はfalseを表します.
(2).日付と時刻のデータ型:
SQLiteは、ブールタイプと同様に、専用の日時格納タイプを提供しないで、TEXT、REAL、INTEGERタイプのそれぞれ異なるフォーマットでこのタイプを表します.
TEXT: "YYYY-MM-DD HH:MM:SS.SSS"
REAL:Julian日付形式で格納
INTEGER:Unix時間形式でデータ値を保存します.つまり、1970-01-01 00:00:00から現在の時間までの秒数です.
SQLiteはtypeof関数を提供し、ユーザーはこの関数に基づいて所定の値の記憶タイプを決定することができる.
3.類型親縁性
SQLiteと他のデータベース・エンジン間のデータ型互換性を最大化するために、SQLiteは「型親縁性(Type Affinity)」という概念を提案した.テーブルフィールドが宣言された後、SQLiteはフィールドによって宣言されたタイプに基づいて親縁タイプを選択し、データが挿入されると、親縁タイプが値として優先的に格納されます.親縁タイプが一致しないか、現在のデータを親縁タイプに変換できない限り、SQLiteは値に適した他のタイプを考慮して格納されます.SQLiteの現在のバージョンでは、次の5つの親縁タイプがサポートされています.
親のタイプ
説明
TEXT
数値型データは、挿入する前にテキスト形式に変換してからターゲットフィールドに挿入する必要があります.
NUMERIC
親がNUMERICのフィールドにテキストデータが挿入されると、変換操作によってデータ情報が失われ、完全に可逆的になることがない場合、SQLiteはそのテキストデータをINTEGERまたはREALタイプのデータに変換し、変換に失敗した場合もSQLiteはTEXT方式でデータを格納する.NULLまたはBLOBタイプの新しいデータの場合、SQLiteは変換せずにNULLまたはBLOBでデータを直接保存します.さらに、浮動小数点フォーマットの定数テキスト(3000.0など)については、数値情報を失うことなくINTEGERに変換できる場合、SQLiteはINTEGERの格納方法に変換します.
INTEGER
親縁タイプがINTEGERのフィールドの場合、そのルールはNUMERICに等しく、CAST式を実行するときに唯一の違いがあります.
REAL
そのルールは基本的にNUMERICと同等であり、唯一の違いは「3000.0」のようなテキストデータをINTEGERストレージに変換しないことである.
NONE
何の変換もせずに、そのデータが属するデータ型で直接格納します.
3.1.フィールドの親関係のルール
フィールドの親縁性は、このフィールドが宣言時に定義されるタイプに基づいて決定され、具体的なルールは以下のリストを参照することができる.フィールドタイプが両方の親縁性に合致する場合、前のルールが先に機能するリストの順序に注意してください. 1). タイプ文字列にINTが含まれている場合、このフィールドの親のタイプはINTEGERです. 2). タイプ文字列に「CHAR」、「CLOB」または「TEXT」が含まれている場合、このフィールドの親縁タイプはVARCHARなどのTEXTです. 3). タイプ文字列に「BLOB」が含まれている場合、このフィールドの親のタイプはNONEです. 4). タイプ文字列に「REAL」、「FLOA」または「DOUB」が含まれている場合、このフィールドの親縁タイプはREALである. 5). 残りの場合、フィールドの親縁タイプはNUMERICです.3.2.具体例
宣言タイプ
親のタイプ
ルールの適用
INTINTEGERTINYINTSMALLINTMEDIUMINTBIGINTUNSIGNED BIG INTINT2INT8
INTEGER
1
CHARACTER(20)VARCHAR(255)VARYING CHARACTER(255)NCHAR(55)NATIVE CHARACTER(70)NVARCHAR(100)TEXTCLOB
TEXT
2
BLOB
NONE
3
REALDOUBLEDOUBLE PRECISIONFLOAT
REAL
4
NUMERICDECIMAL(10,5)BOOLEANDATEDATETIME
NUMERIC
5
4.比較と並べ替え
SQLite 3でサポートされる比較式は、"=","=","<","<=",">",">=","!=","<>","IN","NOT IN","BETWEEN","IS"and"IS NOT"である.データの比較結果は、主にオペランドの格納方法に依存し、そのルールは次のとおりです.
1). 記憶方式はNULLの値が他の記憶タイプの値より小さい.
2). 記憶方式はINTEGERとREALの値がTEXTまたはBLOBタイプの値より小さく、同じINTEGERまたはREALであれば数値ルールに基づいて比較する.
3). 記憶方式はTEXTの値がBLOBタイプの値より小さい.
4). 2つのBLOBタイプの値を比較すると、その結果はCランタイム関数memcmp()の結果となります.
5). 同じTEXTの場合、SQLiteは特定の比較ルールで判断し、3つの比較ルールをサポートします.
校正規則
意味
binary
比較文字列はmemcmp()を採用し、文字列がどの符号化であっても
nocase
binaryと似ていますが、大文字と小文字は無視されます.
rtrim
binaryと似ていますが、文字列の末尾のスペースは無視されます.
次のように、テーブル文を作成して、指定した列に校正規則を指定します.
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 */
);5.参考文書
https://www.sqlite.org/datatype3.html
http://www.cnblogs.com/stephen-liu74/archive/2012/01/18/2325258.html