DuckDB的字符串 collate用法

文章开始前推荐2个学习环境: 

1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》

2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、应用等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

标签

PostgreSQL , DuckDB , collate , nocase , noaccent , nonfc , icu

背景

https://duckdb.org/docs/sql/expressions/collations

默认情况下DuckDB支持三种collate限定用法: noCASE, noACCENT, noNFC

• nocase 忽略大小写

• noaccent 忽略西方各国字符集的口音差异

• nonfc 忽略NFC

安装icu插件后, 支持地域属性. 未安装icu时, 估计类似PostgreSQL的collate=c, 采用binary的对比.

用法举例：

设置默认collate:

PRAGMA default_collation=NOCASE;  
  SELECT 'hello'='HeLlo';  
-- true

忽略口音

CREATE TABLE names(name VARCHAR COLLATE NOACCENT);  
INSERT INTO names VALUES ('hännes');  
SELECT name FROM names WHERE name='hannes';  
-- hännes  
    SELECT 'hello' = 'hëllo';  
-- false  
SELECT 'hello' COLLATE NOACCENT = 'hëllo';  
-- true

两个参与对比的字符串collate必须一致:

SELECT name FROM names WHERE name='hannes' COLLATE NOCASE;  
-- ERROR: Cannot combine types with different collation!  
  CREATE TABLE other_names(name VARCHAR COLLATE NOCASE);  
INSERT INTO other_names VALUES ('HÄNNES');  
  SELECT * FROM names, other_names WHERE names.name=other_names.name;  
-- ERROR: Cannot combine types with different collation!  
  -- need to manually overwrite the collation!  
  SELECT * FROM names, other_names WHERE names.name COLLATE NOACCENT.NOCASE=other_names.name COLLATE NOACCENT.NOCASE;  
-- hännes|HÄNNES  
      D CREATE TABLE other_names(name VARCHAR COLLATE NOCASE);  
D INSERT INTO other_names VALUES ('HÄNNES');  
D select * from other_names;  
┌────────┐  
│  name  │  
├────────┤  
│ HÄNNES │  
└────────┘  
D select * from other_names where name collate noaccent = 'hannes' collate noaccent;

忽略大小写的例子:

SELECT 'hello'='hElLO';  
-- false  
SELECT 'hello' COLLATE NOCASE='hElLO';  
-- true

使用icu, 支持不同地域的字符排序顺序.

D select * from (values ('重庆'),('刘德华'),('刘少奇'),('张学友'),('黎明'),('郭富城')) as t(n) order by n;  
┌────────┐  
│   n    │  
├────────┤  
│ 刘少奇 │  
│ 刘德华 │  
│ 张学友 │  
│ 郭富城 │  
│ 重庆   │  
│ 黎明   │  
└────────┘  
  D install icu;  
D load icu;  
  D select * from (values ('重庆'),('刘德华'),('刘少奇'),('张学友'),('黎明'),('郭富城')) as t(n ) order by n collate zh_CN;  
┌────────┐  
│   n    │  
├────────┤  
│ 重庆   │  
│ 郭富城 │  
│ 黎明   │  
│ 刘德华 │  
│ 刘少奇 │  
│ 张学友 │  
└────────┘  
    create table tn (id int, info text collate zh_CN);  
insert into tn (id,info) values (1,'重庆'),(2,'刘德华'),(3,'刘少奇'),(4,'张学友'),(5,'黎明'),(6,'郭富城');  
select * from tn order by info;  
  ┌────┬────────┐  
│ id │  info  │  
├────┼────────┤  
│ 1  │ 重庆   │  
│ 6  │ 郭富城 │  
│ 5  │ 黎明   │  
│ 2  │ 刘德华 │  
│ 3  │ 刘少奇 │  
│ 4  │ 张学友 │  
└────┴────────┘  
D select * from tn order by info collate en_US;  
┌────┬────────┐  
│ id │  info  │  
├────┼────────┤  
│ 3  │ 刘少奇 │  
│ 2  │ 刘德华 │  
│ 4  │ 张学友 │  
│ 6  │ 郭富城 │  
│ 1  │ 重庆   │  
│ 5  │ 黎明   │  
└────┴────────┘

查询支持哪些collate?

PRAGMA collations;  
SELECT * FROM pragma_collations();  
-- [af, am, ar, as, az, be, bg, bn, bo, bs, bs, ca, ceb, chr, cs, cy, da, de, de_AT, dsb, dz, ee, el, en, en_US, en_US, eo, es, et, fa, fa_AF, fi, fil, fo, fr, fr_CA, ga, gl, gu, ha, haw, he, he_IL, hi, hr, hsb, hu, hy, id, id_ID, ig, is, it, ja, ka, kk, kl, km, kn, ko, kok, ku, ky, lb, lkt, ln, lo, lt, lv, mk, ml, mn, mr, ms, mt, my, nb, nb_NO, ne, nl, nn, om, or, pa, pa, pa_IN, pl, ps, pt, ro, ru, se, si, sk, sl, smn, sq, sr, sr, sr_BA, sr_ME, sr_RS, sr, sr_BA, sr_RS, sv, sw, ta, te, th, tk, to, tr, ug, uk, ur, uz, vi, wae, wo, xh, yi, yo, zh, zh, zh_CN, zh_SG, zh, zh_HK, zh_MO, zh_TW, zu]

nfc相关参考:
https://duckdb.org/docs/sql/functions/char

https://www.postgresql.org/docs/current/functions-string.html

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.