TOP

データベースsqlite3 その５

データ型について

Posted on by takai
データ型について

sqlite は他の RDMS 、たとえば MySQL と違い、格納する値に対して、データ型が柔軟に決定されます。これは、動的型付けと言えるようなもので、javascript や python などのプログラミング言語などと同じような型付けといえます。厳密に型付けされたものと違い、 暗黙に型付けされてしまうので、その性質を理解していないと、思わぬトラブルを招くこともあります。ここでは、データ型について、実際にsqlite3 ドットプロンプト上でデータを格納して、確認してみることにします。

ストレージクラス

sqlite データベースに格納される実際のデータタイプをストレージクラスと呼んでおります。ちょっとややっこしいのですが、次節の解釈されるデータタイプと格納されるデータタイプが存在します。sqlite3 エンジンの中では、以下のような格納タイプに振り分けられるということのようです。

NULL

値は NULL 値です。値がない、空っぽであることを意味します。

INTEGER

値は符号付き整数で、値の大きさに応じて 1、2、3、4、6、または 8 バイトで格納されます。

REAL

値は浮動小数点値で、8 バイトの IEEE 浮動小数点数として格納されます。

TEXT

値はテキスト文字列で、データベースのエンコーディング (UTF-8、UTF-16BE、または UTF-16LE) を使用して保存されます。

BLOB

入力されたとおりに保存されます。

動的型付けとなる、型のアフィニティAffinity（親和性）

sqlite は列コンテナに対して、推奨するデータタイプを定義しております。「推奨する」というのは、そのほかのデータタイプでも受け入れられる場合があるというこ とを意味しております。これを「型アフィニティ」と呼ばれ、このことが「値に対して柔軟に型つけされる、動的型付けである。」と言われることになっており ます。

外部から投入されるいろいろなデータ型は sqlite3 エンジンの中で、以下のようなアフィニティとして解釈されます。

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

そして、他の sql データベースのデータ型はテーブルを定義する時に以下のような順序規則によって列アフィニティタイプが決定されます。（但し、STRICT 宣言されている文は除く。）

  1. 宣言された型に文字列「INT」が含まれている場合は、INTEGER アフィニティが割り当てられます。(INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIGINT INT2 INT8 etc.)
  2. 宣言された列の型に文字列「CHAR」、「CLOB」、または「TEXT」のいずれかが含まれている場合、その列は TEXT アフィニティを持ちます。たとえば「VARCHAR(255)」は255文字列を格納する列に対するデータ型で、TEXT アフィニティとして割り当てられますが、255文字列であるという内容は無視されます。(VARCHAR(255) NCHAR(55) TEXT CLOB etc.)
  3. 列の宣言された型に文字列「BLOB」が含まれている場合、または型が指定されていない場合、その列にはアフィニティ BLOB が割り当てられます。
  4. 列の宣言された型に文字列「REAL」、「FLOA」、または「DOUB」のいずれかが含まれている場合、その列には REALが割り当てられます。(DOUBLE FLOAT etc.)
  5. それ以外の場合、アフィニティは NUMERIC です。(NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME etc.)

以下は、int や text を含む適当につけたデータ型のテーブルを生成し、データを格納するsql文です。

sqlite> create table test_af
   ...> (i my_int, t my_text);
sqlite> insert into test_af
   ...> values(1, 'takai');
sqlite> .mode column
sqlite> select i, typeof(i), t, typeof(t) from test_af;
i  typeof(i)  t      typeof(t)
-  ---------  -----  ---------
1  integer    takai  text  -- my_int型 my_text型はそれぞれ integer textアフェニティとして解釈される

int や text という文字列を含むデータ型は、それぞれ integer text というストレージクラスとして格納されます。尚　typeof(列名)で、その列に格納された値のストレージクラスが表示されます。

型アフィニティの概念の中でストレージクラスが決定される

型アフィニティはテーブルの列に対して付与されるタイプであり、実際に格納される値に対しては、ストレージクラスのタイプとして格納されます。これは、アフィニティタイプそれぞれが、柔軟にストレージクラスを値に対して決めております。

  • CREATE TABLE ステートメントで宣言されたデータ型は、上記１から５の手順に沿ってアフィニティタイプが定義され、原則的に、その名称と同じストレージクラスに格納される。
  • 但し、その値がアフィニティタイプの名称と明らかに異なるストレージクラスの名称の値である場合は、合致するストレージクラスの名称として、データが適切に変換され、格納される。
  • NUMERIC アフィニティは、名称が合致したストレージクラスがないので、自ずと値から判断され、いずれかのストレージクラスに格納される。

データ各種を格納してストレージクラスを確認する

どういうデータがどのストレージクラスに格納されるかチェックしてみました。

確認用のテーブル test_type を以下のように作成します。

sqlite> create table test_type (non, n numeric, i integer, r real, t text, b blob);

型アフィニティ各種に null を格納したときのストレージクラス

sqlite> insert into test_type values(
   ...> null, null, null, null, null, null);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n  typeof(n)  i  typeof(i)  r  typeof(r)  t  typeof(t)  b  typeof(b)
---  -----------  -  ---------  -  ---------  -  ---------  -  ---------  -  ---------
     null            null          null          null          null          null

すべてが NULL ストレージクラスに格納される

型アフィニティ各種に 0 を格納したときのストレージクラス

sqlite> select max(rowid) from test_type;
max(rowid)
----------
2
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type
   ...> where rowid = 2;
non  typeof(non)  n  typeof(n)  i  typeof(i)  r    typeof(r)  t  typeof(t)  b  typeof(b)
---  -----------  -  ---------  -  ---------  ---  ---------  -  ---------  -  ---------
0    integer      0  integer    0  integer    0.0  real       0  text       0  integer

real は 浮動小数点 、text は文字列に変換される。

型アフィニティ各種に 123 を格納したときのストレージクラス

sqlite> insert into test_type values(
   ...> 123, 123, 123, 123, 123, 123);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type
   ...> where rowid = 3;
non  typeof(non)  n    typeof(n)  i    typeof(i)  r      typeof(r)  t    typeof(t)  b    typeof(b)
---  -----------  ---  ---------  ---  ---------  -----  ---------  ---  ---------  ---  ---------
123  integer      123  integer    123  integer    123.0  real       123  text       123  integer

real は 浮動小数点 、text は文字列に変換される。

型アフィニティ各種に -123 を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> -123, -123, -123, -123, -123, -123);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non   typeof(non)  n     typeof(n)  i     typeof(i)  r       typeof(r)  t     typeof(t)  b     typeof(b)
----  -----------  ----  ---------  ----  ---------  ------  ---------  ----  ---------  ----  ---------
-123  integer      -123  integer    -123  integer    -123.0  real       -123  text       -123  integer

real は 浮動小数点 、text は文字列に変換される。

型アフィニティ各種に 0123 を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> 0123, 0123, 0123, 0123, 0123, 0123);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n    typeof(n)  i    typeof(i)  r      typeof(r)  t    typeof(t)  b    typeof(b)
---  -----------  ---  ---------  ---  ---------  -----  ---------  ---  ---------  ---  ---------
123  integer      123  integer    123  integer    123.0  real       123  text       123  integer

0123 は妥当な数値 123 に変換されてから、real は 浮動小数点 、text は文字列に変換される。

型アフィニティ各種に 0.12 を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> 0.12, 0.12, 0.12, 0.12, 0.12, 0.12);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non   typeof(non)  n     typeof(n)  i     typeof(i)  r     typeof(r)  t     typeof(t)  b     typeof(b)
----  -----------  ----  ---------  ----  ---------  ----  ---------  ----  ---------  ----  ---------
0.12  real         0.12  real       0.12  real       0.12  real       0.12  text       0.12  real

text アフィニティは text として変換される以外は、すべてreal として格納される。

型アフィニティ各種に 0.123e4 を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> 1.23e4, 1.23e4, 1.23e4, 1.23e4, 1.23e4, 1.23e4);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non      typeof(non)  n      typeof(n)  i      typeof(i)  r        typeof(r)  t        typeof(t)  b        typeof(b)
-------  -----------  -----  ---------  -----  ---------  -------  ---------  -------  ---------  -------  ---------
12300.0  real         12300  integer    12300  integer    12300.0  real       12300.0  text       12300.0  real

実数 real に変換され、そののち、numeric integer は整数に変換される。text は文字列に変換される。0.123E4 も同様である。

型アフィニティ各種に 0xff を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> 0xff, 0xff, 0xff, 0xff, 0xff, 0xff);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n    typeof(n)  i    typeof(i)  r      typeof(r)  t    typeof(t)  b    typeof(b)
---  -----------  ---  ---------  ---  ---------  -----  ---------  ---  ---------  ---  ---------
255  integer      255  integer    255  integer    255.0  real       255  text       255  integer

10進数に変換される。real は 浮動小数点 、text は文字列として格納される。

型アフィニティ各種に ‘123’ を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> '123', '123', '123', '123', '123', '123');
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n    typeof(n)  i    typeof(i)  r      typeof(r)  t    typeof(t)  b    typeof(b)
---  -----------  ---  ---------  ---  ---------  -----  ---------  ---  ---------  ---  ---------
123  text         123  integer    123  integer    123.0  real       123  text       123  text

numeric integer は 整数として格納される。real は浮動小数値として格納される。

型アフィニティ各種に “123” を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> "123", "123", "123", "123", "123", "123");
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n    typeof(n)  i    typeof(i)  r      typeof(r)  t    typeof(t)  b    typeof(b)
---  -----------  ---  ---------  ---  ---------  -----  ---------  ---  ---------  ---  ---------
123  text         123  integer    123  integer    123.0  real       123  text       123  text

‘123’ と同じ

型アフィニティ各種に abc を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> abc, null, null, null, null, null);
Error: no such column: abc
sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> null, abc, null, null, null, null);
Error: no such column: abc
sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> null, null, abc, null, null, null);
Error: no such column: abc
sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> null, null, null, abc, null, null);
Error: no such column: abc
sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> null, null, null, null, abc, null);
Error: no such column: abc
sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> null, null, null, null, null, abc);
Error: no such column: abc   -- 文字列はそのままでは格納できない

文字列 は「’」 または 「”」で囲まなければ、認識しない。

型アフィニティ各種に ‘abc’ を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> 'abc', 'abc', 'abc', 'abc', 'abc', 'abc');
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n    typeof(n)  i    typeof(i)  r    typeof(r)  t    typeof(t)  b    typeof(b)
---  -----------  ---  ---------  ---  ---------  ---  ---------  ---  ---------  ---  ---------
abc  text         abc  text       abc  text       abc  text       abc  text       abc  text

すべて、text として格納される。”abc” の場合も同様である。

型アフィニティ各種に true を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> true, true, true, true, true, true);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n  typeof(n)  i  typeof(i)  r    typeof(r)  t  typeof(t)  b  typeof(b)
---  -----------  -  ---------  -  ---------  ---  ---------  -  ---------  -  ---------
1    integer      1  integer    1  integer    1.0  real       1  text       1  integer

数値 1 に変換される。real では浮動小数点値、text では文字列として格納される。

型アフィニティ各種に false を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> false, false, false, false, false, false);
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n  typeof(n)  i  typeof(i)  r    typeof(r)  t  typeof(t)  b  typeof(b)
---  -----------  -  ---------  -  ---------  ---  ---------  -  ---------  -  ---------
0    integer      0  integer    0  integer    0.0  real       0  text       0  integer

数値０に変換される。real では浮動小数点値、text では文字列として格納される。

型アフィニティ各種に undefined を格納したときのストレージクラス

sqlite> insert into test_type values(
   ...> undefined, null, null, null, null, null);
Error: no such column: undefined

undefined としては格納されない。

型アフィニティ各種に ‘I’m’ ‘I\’m’ ‘I”m’ “I’m” を格納したときのストレージクラス

sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> 'I'm', 'I'm', 'I'm', 'I'm', 'I'm', 'I'm');
Error: near "m": syntax error    -- 「'」は禁則文字
sqlite> insert into test_type values(
   ...> 'I\'m', 'I\'m', 'I\'m', 'I\'m', 'I\'m', 'I\'m');
Error: near "m": syntax error   -- 「\」ではエスケープできない
sqlite> insert into test_type values(
   ...> 'I''m', 'I''m', 'I''m', 'I''m', 'I''m', 'I''m'); --「'」がエスケープ文字になっている
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n    typeof(n)  i    typeof(i)  r    typeof(r)  t    typeof(t)  b    typeof(b)
---  -----------  ---  ---------  ---  ---------  ---  ---------  ---  ---------  ---  ---------
I'm  text         I'm  text       I'm  text       I'm  text       I'm  text       I'm  text
sqlite> delete from test_type;
sqlite> insert into test_type values(
   ...> "I'm", "I'm", "I'm", "I'm", "I'm", "I'm"); -- 「"」で文字列を囲むことでも良い
sqlite> select non, typeof(non), n, typeof(n), i, typeof(i), r, typeof(r), t, typeof(t), b, typeof(b) from test_type;
non  typeof(non)  n    typeof(n)  i    typeof(i)  r    typeof(r)  t    typeof(t)  b    typeof(b)
---  -----------  ---  ---------  ---  ---------  ---  ---------  ---  ---------  ---  ---------
I'm  text         I'm  text       I'm  text       I'm  text       I'm  text       I'm  text

文字列の中の「’」 は禁則文字です。エスケープするには 「’」でエスケープ、 または「”」で文字列を囲む必要があります。

“STRICT” テーブル オプション

sqlite3では型アフェニティによって柔軟にデータ型が決まります。ところが、バージョン 3.37.0 (2021-11-27) の時点以降、データ型定義に厳格なテーブルを生成できるようになりました。CREATE ステートメントで、”STRICT” テーブル オプション キーワードを末尾の “)” の後に追加すると、厳密な型指定規則が適用されることになります。

使用できるデータ型

  • INT
  • INTEGER
  • REAL
  • TEXT
  • BLOB
  • ANY

型指定規則

  1. すべての列定義で、その列のデータ型を指定する必要があります。
  2. 現時点で、上記以外のデータ型以外は、指定することが出来ません。
  3. ANY 以外のデータ型で列に挿入されるデータは、NULL (列に NOT NULL 制約がない場合) または指定された型のいずれかである必要があります。
  4. ANY 型の列では型強制は発生しません。あらゆる種類のデータを受け入れることができます。但し、従来のような、自動型変換は行われず、その値とデータ型は挿入されたとおりに保持されます。
---- version 3.34.0
takai@takai-pc ~/db
$ sqlite3
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table member(code integer primary key, name text) strict;
Error: near "strict": syntax error  -- strict モードは使えない

sqlite> create table member(code integer primary key, name text);
sqlite> insert into member values(100, 'takai');
sqlite> insert into member values(null, 'ishida');
sqlite> insert into member values(null, 999);
sqlite> select code, typeof(code), name, typeof(name) from member;
100|integer|takai|text
101|integer|ishida|text
102|integer|999|text -- 定義どおりのデータ型

sqlite> insert into member values('unknown', 'unknown');
Error: datatype mismatch  -- データ型が指定されていて、挿入データが自動型変換できなければエラーとなる

sqlite> create table member2(code integer, name text primary key);
sqlite> insert into member2 values(100, 'A123');
sqlite> insert into member2 values(100, 0123);
sqlite> select code, typeof(code), name, typeof(name) from member4;
100|integer|A123|text
100|integer|123|text  -- 0123 --> 123 --> '123' に解釈される


sqlite> insert into member2 values(null, 'B123');
sqlite> select code, typeof(code), name, typeof(name) from member2 where name='B123';
|null|B123|text  -- null は そのままデータ型に関係なく格納される。


sqlite> insert into member2 values(0.11, 'C123');
sqlite> select code, typeof(code), name, typeof(name) from member2 where name='C123';
0.11|real|C123|text -- integer型定義のcode列にreal型となる0.11が格納できる

-- version 3.45.1
C:\Users\takai\sqlite-tools-win-x64-3450100>sqlite3
SQLite version 3.45.1 2024-01-30 16:01:20 (UTF-16 console I/O)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table member(code integer primary key, name text) strict; -- strict モード
sqlite> insert into member values(100, 'takai');
sqlite> insert into member values(null, 'ishida');
sqlite> insert into member values(null, 999);

sqlite> select code, typeof(code), name, typeof(name) from member;
100|integer|takai|text
101|integer|ishida|text
102|integer|999|text  -- 定義どおりのデータ型

sqlite> insert into member values('unknown', 'unknown');
Runtime error: datatype mismatch (20)  -- strictではない場合と同じでエラーになる


-- version 3.34.0
sqlite> create table member3(code primary key, name text);  -- code データ型は無指定
sqlite> insert into member3 values('1234', 'takai');
sqlite> select code, typeof(code), name, typeof(name) from member3;
1234|text|takai|text  -- code列 '1234' は text 型として認識される

sqlite> create table member4(code any primary key, name text); -- code データ型はany
sqlite> insert into member4 values('1234', 'takai');
sqlite> select code, typeof(code), name, typeof(name) from member4;
1234|integer|takai|text  -- '1234'はinteger 型に型変換される


sqlite> insert into member4 values(0.111, 'simada');
sqlite> select code, typeof(code), name, typeof(name) from member4;
1234|integer|takai|text
A234|text|yamada|text
0.111|real|simada|text  -- code列はreal型も受け入れる



-- version 3.45.1
sqlite> create table member3(code any primary key, name text) strict; -- code データ型はany
sqlite> insert into member3 values('1234', 'takai');
sqlite> select code, typeof(code), name, typeof(name) from member3;
1234|text|takai|text  -- 数字文字列'1234'は数値に型変換しない


sqlite> insert into member3 values('A123', 'yamada');
sqlite> select code, typeof(code), name, typeof(name) from member3;
1234|text|takai|text
A123|text|yamada|text
sqlite> insert into member3 values(0.111, 'simada');
sqlite> select code, typeof(code), name, typeof(name) from member3;
1234|text|takai|text
A123|text|yamada|text
0.111|real|simada|text  -- code列はreal型もstrictなしと同様に受け入れることができる

データ型を指定しなければいけないこと、定義どおりの型に格納されること、ANY型へ格納されるデータは型変換しないこと、以外はstrictなしモードと変わらないようです。

関連タグ

売上管理システム・テーブル作成
【データベースsqlite3 その１２】
売上管理システム・テーブル作成
テーブルの結合
【データベースsqlite3 その６】
テーブルの結合
特徴的な仕様
【データベースsqlite3 その１０】
ROWID・時間に関する処理
管理作業
【データベースsqlite3 その１１】
コマンドライン操作と管理作業
Basic＿SQL
【データベースsqlite3 その４】
データベース操作言語sqlについて
外部キー制約
【データベースsqlite3 その８】
外部キー制約について

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。