SQLite 3 二つの値を比較し同じ場合NULLを返す(nullif())

概要

nullif()は引数に与えられた2つの値を比較し、 同じだった場合null、違った場合第一引数を返す。
iif()の亜種のような関数。

構文

nullif(value1, value2)
                

value1

二つの引数の値が異なる場合、この引数が返される。

value2

value1 と比較する値

実行例１

環境

• Windows 10
• SQLite ver 3.45.1

sqlite> .nullvalue [null]
sqlite> select nullif(1, 1);
[null]
sqlite> select nullif(1, 0);
1
sqlite> select nullif('sample1', 'sample1');
[null]
sqlite> select nullif('sample1', 'sample2');
sample1
sqlite> select nullif('1', 1);
1
sqlite> select nullif(true, true);
[null]
sqlite> select nullif(true, false);
1
                

参考URL

• 公式のnullif()に関するドキュメント
  https://www.sqlite.org/lang_corefunc.html#nullif

• SQLite 3 NULLの場合、別の値を返す(ifnull())
  https://sfnovicenotes.blogspot.com/2024/03/sqlite-3-nullifnull.html

SQLite 3 NULLの場合、別の値を返す(ifnull())

概要

値がNULLかどうかを判定して、NULLだった場合別の値を返すようにするには ifnull()関数を使う。 ifnull()はOracleのNVL()に相当する機能。

構文

ifnull(value1, value2)
                

value1

NULLかどうかを判定する対象。この値がNULLでなければ関数はこの値をそのまま返す。

value2

value1がNULLだった場合、この関数はこの値を返す。

実行例１

環境

• Windows 10
• SQLite ver 3.45.1

sqlite> .mode box
sqlite> -- 1.
sqlite> select ifnull(null, 'sample1') as result;
┌─────────┐
│ result  │
├─────────┤
│ sample1 │
└─────────┘

sqlite> -- 2.
sqlite> select ifnull(0, 'sample2') as result;
┌────────┐
│ result │
├────────┤
│ 0      │
└────────┘
sqlite> select ifnull('not null', 'sample3') as result;
┌──────────┐
│  result  │
├──────────┤
│ not null │
└──────────┘

sqlite> -- 3.
sqlite> .nullvalue [null]
sqlite> select ifnull(null, null) as result;
┌────────┐
│ result │
├────────┤
│ [null] │
└────────┘

                

1. 第一引数がnullだった場合、第二引数の値を返す。
2. 第一引数がnullでなければ、第一引数をそのまま返す。
3. 第一引数・第二引数ともにnullだった場合、nullを返す。

実行例２

users

id	last_login	created_at
0001	2024-02-01	2024-01-31
0002		2024-02-01
0003		2024-02-03

そのまま出力。

select
    id
    , last_login
    , created_at
from
    users
;
                    

結果

id	last_login	created_at
0001	2024-02-01	2024-01-31
0002		2024-02-01
0003		2024-02-03

last_login がNULLだった場合、「not logged in」を表示する。

select
    id
    , ifnull(last_login, 'not logged in') as last_login
from
    users
;
                    

結果

id	last_login
0001	2024-02-01
0002	not logged in
0003	not logged in

last_login がNULLだった場合、created_at を換わりに取得する。

select
    id
    , ifnull(last_login, created_at) as last_login
from
    users
;
                    

結果

id	last_login
0001	2024-02-01
0002	2024-02-01
0003	2024-02-03

参考URL

• 公式のifnull()に関するドキュメント
  https://www.sqlite.org/lang_corefunc.html#ifnull

SQLite 3 コマンドラインツール起動時に読み取り専用でデータベースを開く (-readonly)

概要

SQLiteにはGRANTやREVOKEといった権限を操作するコマンドはなく、 ユーザやアクセス権限はOSのファイルアクセス管理に準ずる。

• SQL Features That SQLite Does Not Implement

ただし、プログラミング言語用のライブラリやコマンドラインツールには データベースファイルを読み取り専用で開く手段が準備されていることがある。

このページでは、コマンドプロンプトやbashといったシェルから SQLite 3 のデータベースファイルを読み取り専用で開く方法を記載しておく。

コマンド

SQLite 3 のコマンドのオプションに「-readonly」を指定してデータベースを開くことで 読み取り専用で開くことが出来る。
読み取り専用で開くとinsert や update 実行時にエラーになる。

sqlite3 -readonly DB_NAME
                

実行例

環境

• Windows 10 64bit
• SQLite3 (3.43.1) Command-Line Shell

C:\temp>rem # 1.
C:\temp>sqlite3 -readonly test.db
SQLite version 3.45.1 2024-01-30 16:01:20 (UTF-16 console I/O)
Enter ".help" for usage hints.

sqlite> -- # 2.
sqlite> create table test_table (id, name);
Runtime error: attempt to write a readonly database (8)
sqlite>
                

• 読み取り専用でtest.dbを開く
• create文を実行しようとするとエラーになるのがわかる

SQLite 3 のコマンドラインツールを起動した後でも 「.open」コマンドを使用して読み取り専用でデータベースに繋ぎなおすことができる。

• SQLite 3 読み取り専用でデータベースを開く (.open --readonly)

参考URL

• SQL Features That SQLite Does Not Implement
  公式の未実装機能に関するドキュメント
  https://www.sqlite.org/omitted.html

SQLite3 指定した範囲からランダム値を取得する(random())

概要

SQLite3でランダム値を取得するにはrandom()関数を使用する。
範囲を指定するのであれば、指定したい範囲の「間隔」で割って、指定したい範囲の「最小値」を足せばいい。

構文

select abs(random()) % RANGE + MIN
                

• SQLite3 ランダム値を取得する(random())

実行例

指定した範囲内からランダム値を取得したい場合は剰余(割り算の余り)を使う。
例えば 0～4 の範囲からランダムに値を取得したい場合は 範囲 0,1,2,3,4 の個数 5 で割った余りを使う。

sqlite> select abs(random()) % 5;
1
sqlite> select abs(random()) % 5;
3
sqlite> select abs(random()) % 5;
3
sqlite> select abs(random()) % 5;
4
sqlite> select abs(random()) % 5;
2
sqlite> select abs(random()) % 5;
0
sqlite> select abs(random()) % 5;
2
                

1～5 の範囲からランダムに値を取得したい場合はこれに+1してやればいい。

sqlite> select abs(random()) % 5 + 1;
5
sqlite> select abs(random()) % 5 + 1;
1
sqlite> select abs(random()) % 5 + 1;
4
sqlite> select abs(random()) % 5 + 1;
1
sqlite> select abs(random()) % 5 + 1;
5
sqlite> select abs(random()) % 5 + 1;
2
sqlite> select abs(random()) % 5 + 1;
3
                

• SQLite3 数値の絶対値を求める (abs(X))

参考URL

• 公式のrandom()に関するドキュメント
  https://www.sqlite.org/lang_corefunc.html#random