【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種類のデータ型に分類されます。

### カラムにデータ型を指定しなかった場合
テスト用に以下のようにテーブルを作成します。

```sql
CREATE TABLE test(val01, val02);
```
テーブルにデータを３件格納します。

```sql
INSERT INTO test VALUES(null, 48);
INSERT INTO test VALUES(3.14, 'Tokyo');
INSERT INTO test VALUES('Good Morning', 1.2e-2);
```

格納したデータを取得してみます。

```sql
SELECT * FROM test;
```
```sql
val01       val02     
----------  ----------
            48        
3.14        Tokyo     
Good Morni  0.012     
```

次は、値のデータ型も合わせて取得してみます。

```sql
SELECT val01, TYPEOF(val01), val02, TYPEOF(val02) FROM test;
```
```sql
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型を設定してあります。

```sql
CREATE TABLE test2(val1 TEXT, val2 NUMERIC);
```

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

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

格納したデータとデータ型を取得してみます。

```sql
SELECT val1, TYPEOF(val1), val2, TYPEOF(val2) FROM test2;
```
```sql
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で文字列の値を記述する場合は次のように値をシングルクォーテーションで囲んで記述します。

```sql
'文字列'
```

ダブルクォーテーションで囲んだ場合は識別子となります。
文字列を記述すべき箇所に識別子を記述すると文字列のように扱われるためダブルクォーテーションで囲んでも文字列として処理されているように見えますが、文字列はシングルクォーテーションで囲んで下さい。

テストを行うために次のようなテーブルを作成しました。

```sql
CREATE TABLE test3(val TEXT);
```

格納するデータとして次のようなシングルクオーテーションが含まれる文字列を考えてみます。

```
I'm a student.
```
実際に格納してみます。

```sql
INSERT INTO test3('I'm a student.');
...> 
```

この文字列をテーブルに格納するために次のように実行するとエラーにはならないのですが SQL文が完結していないため入力を待っている状態となります。どこからどこまでが文字列なのか分からないためです。

シングルクオーテーションが含まれる文字列の場合には、文字列の中のシングルクオーテーション(')の前にシングルクオーテーション(')を記述してエスケープ処理を行います。

```sql
INSERT INTO test3 VALUES('I''m a student.');
```

今度は無事値を格納することができました。確認の為、テーブルの値を取得してみます。

```sql
SELECT * FROM test3;
```
```sql:実行結果
I'm a student.
```

格納されている値はエスケープ処理をする前の文字列だけが格納されていることが確認できました。このように文字列の中にシングルクオーテーションが含まれる場合にはエスケープ処理を行うことを忘れないようにしましょう。

---

### 本記事目次ページ
[【データベース】SQLite3・JDBCまとめ](https://qiita.com/tsweblabo/items/8fa21f3e9e30885be034

### 参考サイト
- [DBOnline 初心者でもわかりやすいサイトSQLite入門](https://www.dbonline.jp/sqlite/)