開始前の雑談

主催の木村さんは、私が書いたこの記事で参考にした本の著者なので、わからなかったことについて質問して見ました。

qiita.com

おうちで学べるデータベースのきほん

作者: ミック,木村明治
出版社/メーカー: 翔泳社
発売日: 2015/02/13
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

Q:count,maxはソートを発生させるのか。

A:mysqlはソートが発生する oracle postgresはhashを使うはず。

何か一般的な話ではなく、結構そのRDBの実装依存の話になりそうです。実行計画を見て検証した記事を後日あげます。

誤字？操作 or 走査

19.1の最初の方にある

_を使ったテストは%を使ったテストよりも高速だ。理由は明らかで、パーサが文字列をパターンと比べるさい、次の文字に行く前にアンダースコアを1回操作するだけで済むからだ

ですが、操作ではなく走査ではないか？誤字か？と思われたが、原書を確認する限りoperationとなっていた。つまり誤字ではない。

同値性

19.3冒頭の

固定長文字列に対する同地生のテストでは、最初に２つの文字列の短い方の末尾を空白で埋め、それから比較を行う。従って、'Smith'と'Smith '(末尾に空白)は等しい。

に関して

=は実装依存。postgresは'test'＝'test 'はfalse。
ユニーク制約では空白が無視される('test'と'test 'は同一のものと見なされINSERTできない)
- これはユニーク制約に生成列で作成したlength(<unique対象の列>)を含めれば良いようです。

postgresqlでの例

create table t_word3(
  val char(255) unique
);

postgres=# insert into t_word values('test');
INSERT 0 1
postgres=# insert into t_word values('test ');
ERROR:  duplicate key value violates unique constraint "t_word3_val_key"
DETAIL:  Key (val)=(test                                                                                                                                                                                                                                                           ) already exists.

再現しました。 varcharではどうでしょうか？

CREATE TABLE t_word(
  val varchar(255) unique not null
)

postgres=# insert into t_word (val) values ('test');
INSERT 0 1
postgres=# insert into t_word (val) values ('test ');
INSERT 0 1
postgres=# select * from t_word;
  val  
-------
 test
 test 
(2 rows)

...postgresqlのvarcharは空白があってもuniq制約は発動しないようです。我らがmysqlではどうでしょうか

使うクエリです

CREATE TABLE test.t_word(
 val varchar(255) unique not null
);
insert into test.t_word (val) values ('test');
insert into test.t_word (val) values ('test ');

実行

mysql> CREATE TABLE test.t_word(
    ->   val varchar(255) unique not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test.t_word (val) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.t_word (val) values ('test ');
ERROR 1062 (23000): Duplicate entry 'test ' for key 'val'
mysql>

はいユニーク制約に引っかかってしまいました。生成列との複合ユニーク制約を作って見ます。

CREATE TABLE test.t_word2(
  val varchar(255) not null,
  len_val int(3) as (length(val)) ,
  unique(val,len_val)
);
insert into test.t_word2 (val) values ('test');
insert into test.t_word2 (val) values ('test ');

実行

mysql> CREATE TABLE test.t_word2(
    ->   val varchar(255) not null,
    ->   len_val int(3) as (length(val)) ,
    ->   unique(val,len_val)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test.t_word2 (val) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.t_word2 (val) values ('test ');
Query OK, 1 row affected (0.00 sec)

mysql> select concat('(',val,')') from test.t_word2;
+---------------------+
| concat('(',val,')') |
+---------------------+
| (test)              |
| (test )             |
+---------------------+
2 rows in set (0.01 sec)

'test'と'test 'を同じ列に格納可能なユニーク制約を設置することができました。

同じ問題で困っている人がいるだろうと検索して見たところ、varbinaryという型を使う手もあるそうです。

https://stackoverflow.com/questions/11714534/mysql-database-with-unique-fields-ignored-ending-spaces?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

単語の出現頻度の話

se式とlikeの合わせ技として単語の出現回数を数えるSQLが紹介されているが、不恰好なSQLになってしまっていると思う。書くならこんな感じが良いと思う https://takamints.hatenablog.jp/entry/2015/03/28/134728

正規表現

MySQLのrlikeは日本語に対応していない MySQLで日本語×正規表現を使おうとしている人は苦しい思いをしているようです。

オセロットソフトウェア

本中にオセロットソフトウェア社員が提案したテクニック的なものが出て来ますが、この会社はwindows用の標準SQLに可能な限り準拠したRDBを作っている会社らしい。 http://blog.kimuradb.com/?eid=636546

Betweenのsymmetric,asymmetric

between A and BのAとBはA<Bが成り立っていることが暗黙の了解になっているようだ。これを無視するならsymmetricという句をつける。無視しないオプション(デフォルトのもの)はassymmetricというがこちら明示してつけることも可能。

--symmetricのサンプル
postgres=# select 1 between symmetric 0 and 2;     
 t
 
postgres=# select 1 between symmetric 2 and 0;
 t

--asymmetricのサンプル
postgres=# select 1 between asymmetric 0 and 2;
 t

postgres=# select 1 between asymmetric 2 and 0;
 f

 --デフォルトの挙動
 postgres=# select 1 between 0 and 2;    
  t

 postgres=# select 1 between 2 and 0;
  f

overlaps述語

期間と期間が被ってるかを検査する術後です。postgresで使って見ました。

select (date '2018-05-01',date '2018-05-02') overlaps (date '2018-04-01',date '2018-05-02')
t

select (date '2019-05-01',date '2018-06-02') overlaps (date '2018-04-01',date '2018-05-02')
f

mysqlは地理のoverlapsはあるが、期間のoverlapsはない。