【ポイント解説】知っておきたいSQLiteの仕様と注意事項

当ページのリンクには広告が含まれています。

SQLiteは様々なプラットフォームと開発言語で利用が可能な、軽量且つ高速な組み込み型データベースです。

Androidの標準データベースにも採用され、機械学習やAIプログラミングで注目を浴びているPythonにおいても標準搭載されるなど、幅広く活用されています。

具体的なプログラミング方法は別の記事にゆだねるとして、ここではSQLiteの概念について解説したいと思います。

これを知らないと開発でツボにハマったり、バグに悩まされること間違いなしなので、是非最後まで読んでいただければと思います。

SQLiteのコンセプト

大量データを管理する方法としてOracleやPostgreSQLなどのデータベースが思い浮かびます。

PostgreSQLやMySQLは全ての機能を無償で利用できますし、OracleやSQLServerは扱えるデータ一量などに制限はあるものの、無償版が提供されています。

しかし、インストールが面倒であったり、ユーザーや接続権限など多くの設定が必要だったりと、それなりに専門知識が要求されます。

もっとお手軽に、もっと気軽に、「自分のローカルPCの中だけで動かせれば十分」「自分しかアクセスしないので、セキュリティは簡単に接続できる」というパーソナルな用途に特化したデータベース、それがSQLiteです。

後で詳しく説明しますが、SQLiteのデータベースは単一ファイルで構成されています。

つまり、１つのファイルの中に、テーブル、ビュー、インデックス、トリガ、他が格納されています。

また、データベースシステムではなくデータベースエンジンとして位置づけされており、各開発言語毎に用意されたライブラリを使って制御が行われます。

従って、他のデータベースの様にデータベース自身をインストールする必要はありません。

SQLiteの制限

他のデータベースにはあるのにSQLiteでは出来ない制限事項として、次のものがあります。

制限事項	補足
複数ユーザー又は複数アプリからの同時アクセス	更新には対応していませんが、参照なら可能です。
ストアドプロシージャの作成	ストアドプロシージャの作成が出来ません
列名の変更(RENAME COLUMN)	ALTER TABLE RENAME COUMN が使えないのでテーブルを作り直す必要があります。
日付／時間の計算	日付／時刻のデータ型が存在しないため関数は用意されていません。但し、'2021-06-01 10:05:03' というフォーマットの文字列を日付／時刻として扱い、差分を計算する関数は用意されています

データベースの構造

OracleやSqlServer、PostgreSql、MySqlなど、一般的なデータベースは複数のファイルで構成されています。

これは、複数のユーザーが同時に接続する事を前提に、ユーザー毎に利用できる機能を制限したり、同時にデータを書き込んだ時や、書き込み途中でトラブルが発生した際にデータの不整合が起きないようにするための様々な機能が盛り込まれているためです。

一方SQLite は単一のファイルで構成されているところが大きく異なります。

通常のデータベースは別のサーバ（又はPC)にデータを移動させたり、コピーする際、単純にファイルをコピーするだけでは動かない事が多いのですが、SQLite は１つのファイルを単純にコピーするだけで済んでしまいます。

以下は一般的なデータベースとSQLiteデータベースのファイル構成イメージです。

データベースのファイル構成は製品によって全て異なりますので、あくまでもイメージとして捉えてください。

データファイルの中には、実際のデータがテーブル形式で保存されるわけですが、実はテーブル名やカラム名など、テーブルを作成した際の情報がデータとして保存されています。

例えば、sqlite_master　というテーブルの中には、データベースファイルに含まれる全テーブル名とCreate文が格納されています。

例えばデータベースにテーブルが存在するかを確認したり、テーブルの一覧を取得する場合は、通常のデータを検索するのと同じ方法で、このテーブルから情報を取得する必要があります。

SQLiteで使えるデータ型

SQLite で利用できる型は以下の5種類であり、格納されるデータはこの5種類のいずれかに分類されます。

型	解説
NULL	NULL型
REAL	浮動小数点
INTEGER	整数
TEXT	任意の文字列
BLOB	任意のバイナリデータ

カラムの型として指定できるのは4種類

テーブル作成において、カラムの型として指定できるのは、REAL、INTEGER、TEXT、BLOBの4種類になります。

テーブル作成時、一般的なデータベースで使われる NUMERIC、VARCHAR、DATE、DOUBLE等の指定も受付てはくれますが、内部では上記４つに分類・管理されます。

使用上の注意点

それほど多くはありませんが、他のデータベースと同じ感覚で利用してしまうと、思わぬバグを発生させてしまいます。

特にここで紹介する内容は、知らないとついつやってしまいがちなので、今のうちに念頭に入れておきましょう。

カラムの型を指定しないと何でも入ってしまう

SQLiteの特長として、型を指定しないでテーブルを作成することも可能です。

この場合、テキストや数字、浮動小数点など、なんでも格納可能になります。

一見TEXT型とよく似ていますが、TEXT型は数字を文字に変換して格納するのに対し、型が指定されていないとデータから型を勝手に判断してしまいます。

例えば、123.5 なら浮動小数点と解釈されてREAL型へ、123 なら整数型と解釈されて INTEGER型へ変換され、データと一緒に型情報も格納されてしまいます。

これは、1つのカラムに複数の型が存在するという意味になるため、問題を引き起こす場合があります。

例えば、REAL型を想定したカラムがあったとして、そこから実数型(double、float など）の配列にデータを格納するような場合、10レコード目まではREALでデータを取得できても、１１レコード目で急にINTEGER型やTEXT型の値が返ってくるという可能性があります。

厳密に型を指定する必要のある言語（C、C#、Javaなど）の場合は、この時点でエラーになりますが、実際にデータを取得してエラーになるまでは気づくことが出来ません。

型を指定しないと、集計関数が誤動作する

SUMやMAX、MIN等の集計関数を使う場合、エラーにはならずに意図と違う結果が返されてしまいます。

この場合、CAST（カラム名 as 型名）で型変換してあげれば良いのですが、これを忘れると誤った答えをあたかも正しい答えとして返されてしまうため、間違いが発見しづらくなります。

トランザクションが無いとInsertが極めて遅い

これはパフォーマンスに関する注意点です。

データをInsertする場合、Oracleなど他のデータベースでは、トランザクションを使わなくてもインサート速度が低下することはありません。

しかし、SQLiteの場合はトランザクションを掛けないと、速度が極めて遅くなります。

これは大げさではなく、100倍以上速度が遅くなりますから、大量のInsert 文を発行する場合は、必ずトランザクションをONにしてから実行して下さい。

まとめ

今回はSQLiteについて、プラットフォームや開発言語に依らない部分で、SQLiteのコンセプトや構造、そして利用上の注意点について説明しました。

気軽に使える反面、仕様が緩くてバグが混入し易い部分もありますが、そこさえ気を付けていれば非常に強力です。

正直言って、同一スペックのPCで利用する場合、OracleやPostgreSQLなどの業務用データベースより高速に動作します。

本格的なSQLにも対応しているので、他のデータベースと同様に複数のテーブルを結合して複雑な集計を行わせることも可能です。

数万～数千万件のデータをローカルPCで扱いたくなったら、是非SQLiteをご検討下さい。