如何让DuckDB实现不区分大小写的文本搜索？

以下几种方法可以解决DuckDB默认文本搜索区分大小写的问题：

• 统一大小写转换匹配
  和PostgreSQL的通用方案一致，通过lower()函数将字段值和搜索条件都转换为小写后匹配：

  SELECT * FROM bar WHERE lower(foo) = lower('My name');
  

  注意：如果foo列存在索引，这种写法无法利用索引，适合小数据集或无性能要求的场景。

• 设置不区分大小写的排序规则
  DuckDB支持为列指定NOCASE排序规则，这是最接近MySQL全局配置调整效果的方案，设置后列的所有相等比较都会自动忽略大小写：

  • 创建表时指定列规则：

    CREATE TABLE bar (foo VARCHAR COLLATE NOCASE);
    

  • 修改现有列的规则：

    ALTER TABLE bar ALTER COLUMN foo SET DATA TYPE VARCHAR COLLATE NOCASE;
    

  设置完成后，直接执行原查询即可实现不区分大小写匹配，且能正常利用列上的索引，性能更优。

• 使用ILIKE操作符（模糊搜索场景）
  如果是模糊搜索需求，可使用ILIKE替代LIKE，它默认支持不区分大小写匹配：

  -- 完全匹配
  SELECT * FROM bar WHERE foo ILIKE 'My name';
  -- 包含指定文本的模糊匹配
  SELECT * FROM bar WHERE foo ILIKE '%name%';
  

目前DuckDB暂未提供类似PostgreSQLcitext的专用不区分大小写文本类型，但上述方案已能覆盖绝大多数不区分大小写搜索的需求。

内容的提问来源于stack exchange，提问作者Morris de Oryx