SQLite3のデータ型を考える場合には格納される値そのもののデータ型と、値を格納するカラムのデータ型を分けて考える必要があります。
SQLite3で利用可能なデータ型
多くのデータベースでは、テーブルを定義する際にカラムごとにデータ型を指定する必要があります。その為、値を入力する時にはすでにカラム毎に格納できる値が決められています。
SQLite3ではテーブルを定義する時にカラム毎にデータ型を指定することは必須ではありません。
データ型を指定しなかったカラムには色々な型の値が格納されることがあります。
カラムにデータ型をあらかじめ指定していなかった場合、カラムに格納された値がどんなデータ型なのかは区別していて「NULL」「INTEGER」「REAL」「TEXT」「BLOB」の5種類のデータ型に分類します。
値のデータ型
| データ型 | 意味 |
|---|---|
| NULL | NULL値 |
| INTEGER | 符号付整数 1, 2, 3, 4, 6, or 8 バイトで格納 |
| REAL | 浮動小数点数。8バイトで格納 |
| TEXT | テキスト。UTF-8, UTF-16BE or UTF-16-LEのいずれかで格納 |
| BLOB | Binary Large OBject。入力データをそのまま格納 |
カラムのデータ型
カラムにデータ型を指定した場合でも、どのようなデータ型の値を格納することができます。
しかしカラムのデータ型と値のデータ型の組み合わせによっては値のデータ型を変換した上で格納します。
TEXT型のカラム
- INTEGERやREALのデータ型が格納された場合、TEXT型に変換されてから格納されます。
NUMERIC型のカラム
- TEXT型の値が格納された場合、INTEGER型または REAL型に変換を試みます。
- 成功すればそのデータ型で格納されます
- 失敗すればTEXT型のまま格納されます。
INTEGER型のカラム
- 整数として表せる REAL型の値(例えば34.0など)、又は同じ形式のTEXT型の値が格納された場合、INTEGER型に変換して格納します。
REAL型のカラム
- INTEGERの値が格納された場合、REAL型に変換して格納します。
NONE型のカラム
- 変換は行われません。
カラムに格納された値のデータ型の確認
カラムにデータ型を指定した場合と指定しなかった場合でそれぞれテーブルを作成し、そこに色々なデータを格納したらどのようなデータ型として格納されるのかについて確認してみます。
なお格納された値は最終的に「NULL」「INTEGER」「REAL」「TEXT」「BLOB」の5種類のデータ型に分類されます。
カラムにデータ型を指定しなかった場合
テスト用に以下のようにテーブルを作成します。
CREATE TABLE test(val01, val02);
テーブルにデータを３件格納します。
INSERT INTO test VALUES(null, 48);
INSERT INTO test VALUES(3.14, 'Tokyo');
INSERT INTO test VALUES('Good Morning', 1.2e-2);
格納したデータを取得してみます。
SELECT * FROM test;
val01 val02
---------- ----------
48
3.14 Tokyo
Good Morni 0.012
次は、値のデータ型も合わせて取得してみます。
SELECT val01, TYPEOF(val01), val02, TYPEOF(val02) FROM test;
val01 TYPEOF(val01) val02 TYPEOF(val02)
---------- ------------- ---------- -------------
null 48 integer
3.14 real Tokyo text
Good Morni text 0.012 real
nullはNULL型、整数はINTEGER型、文字列はTEXT型、浮動小数点はREAL型と、追加された値に応じて格納された値のデータ型が設定されていることが確認できます。
カラムにデータ型を指定した場合
先ほどはカラムにデータ型を指定しないでテーブルを作成しました。
今度は色々なデータ型をカラムに設定した時に値がどのように格納されるのかを確認してみます。
例として次のようなテーブルを作成しました。テーブルにはカラムが2つあり、1つ目のカラムにはTEXT型、2つ目のカラムにはNUMERIC型を設定してあります。
CREATE TABLE test2(val1 TEXT, val2 NUMERIC);
テーブルにデータを６件格納します。
INSERT INTO test2 VALUES('Summer', 'Summer');
INSERT INTO test2 VALUES(48, 48);
INSERT INTO test2 VALUES('72', '72');
INSERT INTO test2 VALUES(39.24, 39.24);
INSERT INTO test2 VALUES('0.17', '0.17');
INSERT INTO test2 VALUES(null, null);
格納したデータとデータ型を取得してみます。
SELECT val1, TYPEOF(val1), val2, TYPEOF(val2) FROM test2;
val1 TYPEOF(val1) val2 TYPEOF(val2)
---------- ------------ ---------- ------------
Summer text Summer text
48 text 48 integer
72 text 72 integer
39.24 text 39.24 real
0.17 text 0.17 real
null null
TEXT型を設定したカラムの場合
- TEXT型の値・・・そのまま格納されます。
- INTEGER型やREAL型の値・・・TEXT型に変換されて格納されます。
- NULL型の値・・・変換されずにそのまま格納されます。
NUMERIC 型を設定したカラムの場合
- TEXT型の値・・・INTEGER型またはREAL型に変換を試みます。
- 成功すれば、変換された値で格納されます。
- 失敗すれば、TEXT型のまま格納されます。
- NULL型の値・・・変換されずにそのまま格納されます。
文字列のエスケープ処理
SQLite3でテーブルに文字列の値を格納する時にはシングルクオーテーションで囲って記述します。
文字列の中にシングルクオーテーションが含まれている場合には、エスケープ処理が必要となります。ここでは文字列に対してのエスケープ処理について書いていきます。
SQLite3で文字列の値を記述する場合は次のように値をシングルクォーテーションで囲んで記述します。
'文字列'
ダブルクォーテーションで囲んだ場合は識別子となります。
文字列を記述すべき箇所に識別子を記述すると文字列のように扱われるためダブルクォーテーションで囲んでも文字列として処理されているように見えますが、文字列はシングルクォーテーションで囲んで下さい。
テストを行うために次のようなテーブルを作成しました。
CREATE TABLE test3(val TEXT);
格納するデータとして次のようなシングルクオーテーションが含まれる文字列を考えてみます。
I'm a student.
実際に格納してみます。
INSERT INTO test3('I'm a student.');
...>
この文字列をテーブルに格納するために次のように実行するとエラーにはならないのですが SQL文が完結していないため入力を待っている状態となります。どこからどこまでが文字列なのか分からないためです。
シングルクオーテーションが含まれる文字列の場合には、文字列の中のシングルクオーテーション(')の前にシングルクオーテーション(')を記述してエスケープ処理を行います。
INSERT INTO test3 VALUES('I''m a student.');
今度は無事値を格納することができました。確認の為、テーブルの値を取得してみます。
SELECT * FROM test3;
I'm a student.
格納されている値はエスケープ処理をする前の文字列だけが格納されていることが確認できました。このように文字列の中にシングルクオーテーションが含まれる場合にはエスケープ処理を行うことを忘れないようにしましょう。
本記事目次ページ
[【データベース】SQLite3・JDBCまとめ](https://qiita.com/tsweblabo/items/8fa21f3e9e30885be034