やってみる

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

📌 ？
トップ > SQLite3 > SQLite3構文　列制約（collate）

SQLite3構文　列制約（collate）

SQLite3

　照合シーケンス。比較方法の指定。デフォルトはbinary。正確には制約ではない。

成果物

情報源

照合シーケンス

組込

collater 概要
binary テキストエンコーディングのmemcmpで比較する
nocase binary同様だがアルファベット26字の大文字を小文字にしてから比較する
rtrim binary同様だが末尾のスペース文字を無視する

　rtrimだと末尾のスペースが多かろうが少なかろうが、insertした順（たぶんrowid順？）になると思われる。binary,nocaseは末尾スペース数の大小によりソートされる。そもそも挿入時にデータをtrim()したほうがいいのでは？

　組込でない照合シーケンスを使う方法は以下。

テーブル生成

0.sql

create table T(
  A int collate binary,
  B int collate nocase,
  C int collate rtrim
);
select sql from sqlite_master;

　エラーなく実行されたことを確認。

CREATE TABLE T(
  A int collate binary,
  B int collate nocase,
  C int collate rtrim
)

binary

1_binary.sql

create table T(A text collate binary);
insert into T values('A');
insert into T values('a');
insert into T values('B');
select A from T;
select A from T order by A asc;
select A from T order by A desc;

select A from T;
A
a
B
select A from T order by A asc;
A
B
a
select A from T order by A desc;
a
B
A

nocase

1_nocase.sql

.print ===== A,a =====
create table T(A text collate nocase);
insert into T values('A');
insert into T values('a');
insert into T values('B');
.echo on
select A from T;
select A from T order by A asc;
select A from T order by A desc;
.echo off
drop table T;
.print ===== a,A =====
create table T(A text collate nocase);
insert into T values('a');
insert into T values('A');
insert into T values('B');
.echo on
select A from T;
select A from T order by A asc;
select A from T order by A desc;
.echo off

===== A,a =====
select A from T;
A
a
B
select A from T order by A asc;
A
a
B
select A from T order by A desc;
B
A
a
===== a,A =====
select A from T;
a
A
B
select A from T order by A asc;
a
A
B
select A from T order by A desc;
B
a
A

　nocaseはAとaを同値とみなす。同値のときはおそらく内部値rowidを第2キーとしてソートするのだろう。rowidは挿入タイミングでインクリメントされる。つまり挿入タイミングによってはA,aにもなるし、a,Aにもなるということ。

rtrim

.print ===== RTRIM =====
create table T(id integer primary key, A text collate rtrim);
insert into T(A) values('A');
insert into T(A) values('a');
insert into T(A) values('B');
.echo on
insert into T(A) values('C');
insert into T(A) values('C  ');
insert into T(A) values('C ');
select * from T;
select * from T order by A asc;
select * from T order by A desc;
.echo off

.print ===== BINARY =====
create table U(id integer primary key, A text collate binary);
insert into U(A) values('A');
insert into U(A) values('a');
insert into U(A) values('B');
insert into U(A) values('C');
insert into U(A) values('C  ');
insert into U(A) values('C ');
.echo on
select * from U;
select * from U order by A asc;
select * from U order by A desc;
.echo off

===== RTRIM =====
insert into T(A) values('C');
insert into T(A) values('C  ');
insert into T(A) values('C ');
select * from T;
1|A
2|a
3|B
4|C
5|C  
6|C 
select * from T order by A asc;
1|A
3|B
4|C
5|C  
6|C 
2|a
select * from T order by A desc;
2|a
4|C
5|C  
6|C 
3|B
1|A
.echo off
===== BINARY =====
select * from U;
1|A
2|a
3|B
4|C
5|C  
6|C 
select * from U order by A asc;
1|A
3|B
4|C
6|C 
5|C  
2|a
select * from U order by A desc;
2|a
5|C  
6|C 
4|C
3|B
1|A
.echo off

　rtrimのときは同じCが必ず4,5,6の順になっている。ところがbinaryのときはCが昇順では4,6,5で、降順では5,6,4だったりする。

　rtrimは末尾スペースを無視した上、同値ならrowid順にソートしているのだろう。対してbinaryは末尾スペースが少ない順、多い順にソートされている。

比較式

select 'A' = 'a';
select 'A' = 'a' collate binary;
select 'A' = 'a' collate nocase;
select 'A' = 'A ';
select 'A' = 'A ' collate rtrim;

select 'A' = 'a';
0
select 'A' = 'a' collate binary;
0
select 'A' = 'a' collate nocase;
1
select 'A' = 'A ';
0
select 'A' = 'A ' collate rtrim;
1
比較方法 collate
大文字小文字を無視 collate nocase
末尾スペースを無視 collate rtrim

　両方は出来ないと思う。大文字小文字を無視しつつ末尾スペースも無視する方法はないのだろう。

試しにANDで繋いでもエラーになった。

select 'A' = 'a ' collate nocase and rtrim;

Error: near line 7: no such column: rtrim

select 'A' = 'a ' collate nocase and collate rtrim;

Error: near line 8: near "collate": syntax error

対象環境

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

前回まで