最近、変わった案件で MySQL → SQLite というのがあって調べたので、まとめてみました。

SQLite3の特徴

• ファイルベースのDB
  • DBはファイル単位で作成されるので、インストール不要
  • ファイルのコピーで、データのバックアップが可能
  • サービスの起動が不要（ファイルにアクセスできればOK）
  • ファイルサイズ（DBサイズ）は、最大128TiB（ファイルシステムに依存）
  • 自動でROWIDが振られる
• データ型は5つのみ
• 正確にはRDBMSではなく、トランザクション分離レベルをサポートしていない
  （同時実行制御とSQLite3が詳しいです）
• ユーザやアクセス権限の概念がない（パスワード等データを保護する仕組みがない）
• 一部、使えないSQL構文（演算子や句）がある
• マルチスレッドが苦手なので、同時接続数が多いアプリケーションには向かない

また、SQLite3は「型親和性」の概念が導入されています。
これは、SQLite3が持っている5つのデータ型に合わせる形で値を格納するための概念です。

• TEXT（文字列）
• INTEGER（整数）
• REAL（浮動小数点）
• BLOB（バイナリ）
• NULL

さらに、SQLite3はスキーマ定義のデータ型とは関係なく値を保存できます。
※スキーマ定義に沿って自動変換される場合はありますが
（後述の「型親和性」をご参照ください）

なお、（主観ですが）RDBMSを名乗っている製品でもエラーにならずに格納するDBもあるので、これだけでSQLite3がダメということにはならないと思っています。
ただ、SQLite3の場合はスキーマとデータの型が大胆に異なることがあるので、アプリケーション側でのバリデーションは必須です。

SQLite3のデータ型

SQLite3には

• 値のデータ型
• カラムのデータ型
  の2つの考え方があります。

通常のRDBMSは、カラムのデータ型のみを意識すればよいのですが、後述する「型の親和性」により若干複雑になっています。

データ型

• 格納される値のデータ型
  実際の値の型
• カラムのデータ型
  スキーマ（列）に定義するデータ型

格納される値のデータ型

公式サイトが参考になります。

base on Datatypes In SQLite Version 3

1. Datatypes In SQLite
SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

2. Storage Classes and Datatypes

• NULL. The value is a NULL value.
• INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
• REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
• TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
• BLOB. The value is a blob of data, stored exactly as it was input.

2.1. Boolean Datatype
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

2.2. Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

1. SQLiteのデータ型
SQLiteは、より一般的な動的型システムを使用します。SQLiteでは、値のデータ型はコンテナではなく、値自体に関連付けられます。

2. ストレージクラスとデータ型

• NULL。値はNULL値です。
• INTEGER。値は符号付き整数で、値の大きさに応じて1,2,3,4,6、または8バイトに格納されます。
• REAL。値は浮動小数点値で、8バイトのIEEE浮動小数点数として格納されます。
• TEXT。値は、データベースエンコーディング（UTF-8、UTF-16BEまたはUTF-16LE）を使用して格納されたテキスト文字列です。
• BLOB。値はデータの塊であり、入力されたとおりに保存されます。

2.1. ブールデータ型
SQLiteには別のブールストレージクラスはありません。代わりに、ブール値は整数0（偽）と1（真）として格納されます。

2.2. 日付と時刻のデータ型
SQLiteには、日付や時刻を格納するためのストレージクラスはありません。代わりに、SQLite の組み込みの日付と時刻関数は、日付と時刻をTEXT、REAL、またはINTEGERの値として保存することができます：

• ISO8601文字列（ "YYYY-MM-DD HH：MM：SS.SSS"）としてのTEXT。
• REALはユリウス暦の日数であり、紀元前4714年11月24日のグリニッジ暦正午から紀元前4時から翌日にかけての日数である。
• Unix TimeとしてのINTEGERは、1970-01-01 00:00:00 UTC以降の秒数です。

アプリケーションでは、日付と時刻をこれらの形式で格納し、組み込みの日付と時刻関数を使用して自由に形式を変換することができます。

表にすると以下のようになります。

データ型	詳細
TEXT	テキスト。UTF-8, UTF-16BE, UTF-16-LEのいずれかで格納
INTEGER	符号付整数。自動選択か、1, 2, 4, 8バイトで格納 整数型プライマリーキーは常に8バイト整数
REAL	浮動小数点数。8バイトで格納
BLOB	Binary Large OBject。入力データをそのまま格納
NULL	NULL値

カラムのデータ型

型の親和性は、該当カラムに格納されるデータの推奨タイプ（型）です。型が推奨されているだけで、他のRDBMSのように必須ではありません。

型の親和性

以下にSQLite3に対応するデータ型の例を記載します。

主なデータ型	SQLite3 データ型	特記事項
CHARACTER VARYING CHARACTER NCHAR NVARCHAR TEXT CLOB	TEXT	数値型データはTEXT型に変換
NUMERIC DECIMAL BOOLEAN DATE DATETIME	INTEGER REAL	INTEGER型またはREAL型に変換 成功すればそのデータ型で格納、失敗すればTEXT型のまま格納
INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED INTEGER	INTEGER	整数として表せるREAL型やTEXT型の値が格納された場合、INTEGER型に変換して格納
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	浮動小数点として表せる値が格納された場合REAL型に変換
BLOB SQLite3に存在しないデータ型	BLOB （NONE）	変換を強要しない （そのまま格納）

※型の親和性により、SQLite3に格納された値の型は保証されません