SQLiteでStrictテーブルを推奨する

要するに：SQLiteでStrictテーブルを推奨するのは、データ型に関するいくつかの問題、例えば数値列にテキストを入れるといったことを回避できるからです。 SQLiteには、私が過小評価されていると思う機能があります。それはStrictテーブルです。Strictテーブルは、厳格な型付けを強制し、整数列にテキストを入れるといった間違いを防ぐのに役立ちます。私はそれが好きで、この投稿はその使用を促進するために書きました！ Strictテーブルを作成するには、定義の最後にSTRICTを追加します。このようになります。 -CREATE TABLE people (name TEXT); +CREATE TABLE people (name TEXT) STRICT; これだけです！しかし、それは何をするのでしょうか？ Strictテーブルの利点 一般的に、Strictテーブルは他のSQLエンジンと同様に、厳格な型を強制するのに役立ちます。 挿入/更新時の型不一致を防ぐ 最も重要なのは、Strictテーブルは間違った型の値を列に挿入するのを防ぐことです。例えば、SQLiteは通常、INTEGER列にテキストを入れることを許可しますが、Strictテーブルでは許可しません。 -- 非Strictテーブルでは、どこにでも何でも入れられます。 CREATE TABLE people_nonstrict (age INTEGER); INSERT INTO people_nonstrict (age) VALUES ('garbage'); -- => 問題なく動作します -- Strictテーブルはそれを許可しないので、私はそれを好みます。 CREATE TABLE people_strict (age INTEGER) STRICT; INSERT INTO people_strict (age) VALUES ('garbage'); -- => エラー: INTEGER列にTEXT値を格納できません 個人的には、整数列にテキストを入れようとしたり、その逆をしたりするのは間違いだと思います。SQLiteにこのエラーを犯させたくありません！ 同じ検証はUPDATEにも適用されます。 注目すべきは、値が損失なく変換できる場合、それは引き続き受け入れられるということです。例えば、文字列 '123' は整数に完全に変換できるため、許可されます。これらの2行は、Strictテーブルであっても同等です。 INSERT INTO people_strict (age) VALUES ('123'); INSERT INTO people_strict (age) VALUES (123); テーブル作成時の不正な列型を防ぐ デフォルトでは、不正な型の列を作成できます。例えば、これらのすべては有効なSQLiteデータ型ではありませんが、受け入れられます。 -- SQLiteはこれらの型をサポートしていませんが、これらはすべて受け入れられます。 CREATE TABLE tbl (name GARBAGE); CREATE TABLE tbl (name DATETIME); CREATE TABLE tbl (name JSON); CREATE TABLE tbl (name UUID); CREATE TABLE tbl (name BLOBB); これらは開発者が意図したものではないと思います。これらのいくつかはタイプミスであり、いくつかはSQLiteがサポートするデータ型についての誤解であり、いくつかはひどい間違いです。 これらのいずれかのステートメントにSTRICTを追加すると、エラーになります。私の意見では、それは正しい動作です！ -- これらすべてがエラーになりますが、私はそれを好みます。 CREATE TABLE tbl (name GARBAGE) STRICT; CREATE TABLE tbl (name DATETIME) STRICT; CREATE TABLE tbl (name JSON) STRICT; CREATE TABLE tbl (name UUID) STRICT; CREATE TABLE tbl (name BLOBB) STRICT; INT、INTEGER、REAL、TEXT、BLOB、ANYのみが許可されます。 Strictテーブルは列型も要求するため、CREATE TABLE tbl (name) のようなことはできません。 ANYで柔軟性を維持 それでも列に柔軟性が必要な場合は、ANYデータ型を使用できます。名前が示すように、それはStrictテーブルであっても、すべてを許可します。 CREATE TABLE tbl (value ANY) STRICT; -- 列がANYなので、これらはすべて有効です： INSERT INTO tbl (value) VALUES (123); INSERT INTO tbl (value) VALUES ('text'); INSERT INTO tbl (value) VALUES (12.34); INSERT INTO tbl (value) VALUES (X'8647'); これに使う場面は見つかっていませんが、あなたは見つけるかもしれません！ Strictテーブルの欠点 Strictテーブルを好みますが、いくつかの欠点を共有しなければなりません。すべてがより良いわけではありません！ 既存のテーブルをStrict化できない 最初からStrict性を使用するのが最善だと思いますが、常に可能とは限りません。 残念ながら、テーブルをALTERしてStrictにすることはできないと思います。データを非StrictテーブルからStrictなテーブルにコピーする必要があると思います。以下のようなものです。 -- 1. 同じスキーマで新しいStrictテーブルを作成します。 CREATE TABLE new_people (name TEXT) STRICT; -- 2. データをコピーします（型が間違っていると危険です！）。 INSERT INTO new_people SELECT * FROM people; -- 3. 古いテーブルを置き換えます。 DROP TABLE people; ALTER TABLE new_people RENAME TO people; 注意：非Strictテーブルに無効なデータが含まれている場合、これはトリッキーになる可能性があります！例えば、古いデータに誤って整数列にテキストが含まれている場合、移行中にエラーが発生します。データをクリーンアップするか、キャストする必要があるでしょう。 コードベースのルールとして、すべての新しいテーブルをStrictにすることができます。それは有用かもしれません。少なくともあなたのテーブルの一部は有効です！しかし、それはまた、テーブル全体で検証が一貫していないことを意味する可能性があり、それはすべてのテーブルで弱い検証を持つよりも驚くべきことかもしれません。これがあなたに適しているかどうかを決定するのはあなた次第です。 SQLite開発者は私に同意しない SQLiteには「柔軟な型付けの利点」というページ全体があり、SQLiteの柔軟な動作は実際には良いと主張しています。 静的対動的の論争に踏み込むのはためらわれますが、ほとんどの場合、私は同意しません。私は個人的に、予期しないデータ型が微妙な頭痛の種を引き起こした多くのバグに遭遇しました。私はこれらの間違いが大きく爆発する方がずっと良いです。しかし、SQLiteの開発者はStrictテーブルに対する私の好みを共有していないようだということは注目に値します！ 彼らは、純粋なキーバリューストア」や、さまざまな型の「雑多な属性を格納する場所」など、柔軟なテーブルのいくつかの良い用途を挙げています。また、メチャクチャなCSVを直接インポートしていて、データを失いたくない場合など、無効なデータを保持したい場合もあると述べています。私はまだStrictテーブルを好みますが、非Strictテーブルにはいくつかの合理的なケースがあることを認めます。 （SQLiteソースコードには、非Strictテーブルを「レガシー」と呼ぶコメントも少なくとも1つありますが、私は公式ドキュメントよりもそれを信頼しません。） SQLite 3.37.0+のみ SQLiteはバージョン3.37.0（2021年11月リリース）でStrictテーブルを導入しました。古いバージョンのSQLiteを使用している場合、Strictテーブルは使用できません。 古いバージョンのSQLiteはStrictテーブルを含むデータベースを読み込めないことに注意してください。例えば、最新バージョンのSQLiteでStrictテーブルを作成し、その後SQLite 3.36.0（Strictテーブルが追加される前）でそのデータベースを読み込もうとすると、Strictテーブルが既にデータベース内にあってもエラーが発生します。 パフォーマンスの可能性？ Strictテーブルは、少し余分な作業を行う必要があるため、理論的には遅くなります。例えば、挿入または更新時にデータ型をチェックします。 しかし、実際には、これは問題ではないと思います。私は100列のテーブルに数百万行を挿入するハッキーなスクリプトを書きましたが、私が試した複数のマシンで明らかな違いはありませんでした。ディスク上のファイルサイズも同じでした。徹底的にテストしたわけではないので、何か見落としたことがあるかもしれませんが、Strictテーブルがパフォーマンスの問題を引き起こすとは思いません。 実際、SQLiteの列アフィニティと意図せず一致しないようにすることで、パフォーマンスが向上すると期待できるかもしれません。しかし、これもテストしていません。 結論：Strictテーブルが好きです！ 個人的には、Strictテーブルの長所は短所を上回ると思います。 私は一般的に、型が厳密に強制されることを好みます。それは間違いのクラスを潰し、良いデータ整合性を強制するのに役立ちます。それらは万能薬ではありませんが、通常は追加が簡単で、大きな効果があります。 もしあなたが過小評価されていると思うSQLiteの機能があれば、教えてください。