やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3構文　table（create/drop）

SQLite3構文　table（create/drop）

SQLite3

　テーブルの生成・削除。

成果物

一覧

create table

0.sql

create table T(
  A integer, -- 整数
  B real,    -- 不動小数
  C numeric, -- 数値
  D text,    -- 文字列
  E blob     -- バイナリ
);
select sql from sqlite_master;

create temp table

　スキーマ名tempにテーブルを作成する。

temp

1_temp.sql

create temp table T(
  A integer, -- 整数
  B real,    -- 不動小数
  C numeric, -- 数値
  D text,    -- 文字列
  E blob     -- バイナリ
);
.echo on
.databases
select sql from      sqlite_master;
select sql from main.sqlite_master;
select sql from temp.sqlite_master;
.echo off

temporary

1_temporary.sql

create temporary table T(
  A integer, -- 整数
  B real,    -- 不動小数
  C numeric, -- 数値
  D text,    -- 文字列
  E blob     -- バイナリ
);
.echo on
.databases
select sql from      sqlite_master;
select sql from main.sqlite_master;
select sql from temp.sqlite_master;
.echo off

create table if not exists

2.sql

create table               T(A integer);
create table if not exists T(A integer);
select sql from sqlite_master;

CREATE TABLE T(A integer)

　エラーにならない。

エラーになる

　if not existsせずに同名のテーブルをcreateしようとするとエラー。

2_error.sql

create table T(A integer);
create table T(A integer);

Error: near line 2: table T already exists

create table 名 as select ...

3.sql

.echo on
create table T as select 1, 0.1, 'A', x'FF';
select sql from sqlite_master;
.headers on
select * from T;
.echo off

create table T as select 1, 0.1, 'A', x'FF';
select sql from sqlite_master;
CREATE TABLE T("1","0.1","'A'","x'FF'")
.headers on
select * from T;
1|0.1|'A'|x'FF'
1|0.1|A|�
.echo off

　型はアフィニティによって決まる。よくわからん。

create table (...) without rowid

4.sql

create table T(
  A integer primary key
) without rowid;
select sql from sqlite_master;

　SQLite3固有。without rowidにするとクラスタ化インデックスになる。これは指定した主キーをソートして、その順に取り出すときに高速化する。つまり主キーが必須になる。

主キーの自動挿入不可

4_0.sql

create table T(
  A integer primary key,
  B text
) without rowid;
insert into T(B) values('A');
select * from T;

Error: near line 5: NOT NULL constraint failed: T.A

クラスタ化インデックス

　クラスタ化インデックスは辞書順にソートすることに意義がある。よって主キー値はランダムな数値でなく、ユーザが考慮して値をセットすべき。だからエラーが出るのは妥当。なのだと思う。たぶん。

　だが、残念なお知らせがあった。without rowidテーブルの1レコードあたりのサイズは、データベースページのサイズの約1/20未満にする必要があるという。

　試しに私の環境でデフォルトのページサイズを確認してみた。

pragma page_size;

4096

　設定は以下のようにする。

pragma page_size = 4096;

　ただし、ページサイズは512〜65536の間で、かつ2のn乗にする必要がある。

　たとえばページサイズ4KiBのときは4096B/20=204.8Bで約204Byteまで。1列でなく1行のサイズが、だ。少なすぎる。使い物にならない。

　SQLite3がデフォルトで使用する文字セットはUTF-8である。UTF-8で日本語を使ったとき、ほとんどの場合は1文字は3Byteである。ただし4,6,8Byteになる場合もある。204/3=68文字。なんと、1行あたり68文字分のデータしか入れられない。つぶやきさえ入らない。しかも主キーも合わせたサイズ。

　どんなときに有効か？

　たとえば.param initドットコマンドで作成されるsqlite_parametersテーブルはwithout rowidである。これはkeyとvalue列のみ。1行あたり少ないデータになるだろうし、主キーであるkeyを辞書順にソートするのも一定の価値がありそう。こういうときに使うのだろう。

　あとはマジックナンバーに名前を付けるときとか？　C言語の#defineプリプロセッサ的な。

drop table

5.sql

create table T(A integer);
drop table T;
select sql from sqlite_master;





　何も出ない。テーブルは削除された。






drop table if exists



6.sql



create table T(A integer);
drop table T;
drop table if exists T;
select sql from sqlite_master;











　何も出ない。テーブルは削除された。エラーも出ない。



エラーになる



　存在しないテーブルを削除しようとするとエラーになる確認。



6.sql



create table T(A integer);
drop table T;
drop table T;
select sql from sqlite_master;







Error: near line 3: no such table: T





　削除するときはif existsをつけたほうがいい。



他



　テーブル作成において、他にも関係するものが多数ある。






所感



　次回以降、他の要素についてやる。



対象環境






$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux





前回まで