Help us understand the problem. What is going on with this article?

【SQLite3】SQLite3のデータ型

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);

テーブルにデータを3件格納します。

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);

テーブルにデータを6件格納します。

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

参考サイト

tsweblabo
はじめまして。t's Web Laboと申します。 昨年新しい環境での生活が始まり、多くの方のサポートのおかげでようやく環境が整ってきました。 とはいえ知識や技術面の足りない部分が多く、日々勉強の必要性を感じています。 プログラミング関連で興味を持ったこと、勉強したことを備忘録の意味も込めてQiitaで発信していきます。 2020.06.03
https://tsweblabo.github.io/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away