针对你遇到的问题——存储大小写敏感文本的SQL表，需要高效实现不区分大小写的多关键词子串查询，同时避免LIKE '%xxx%'带来的全表扫描，我整理了几个实用的优化方案，帮你解决痛点：

一、优先使用全文索引（Full-Text Indexing） #

这是处理子串/模糊查询最高效的方案，几乎所有主流数据库都支持全文索引，它专门为文本搜索设计，能快速定位包含指定关键词的记录，且天然支持不区分大小写（可通过配置分词器实现）。

举个SQLite的例子（其他数据库类似，比如MySQL的FULLTEXT、PostgreSQL的tsvector）：

-- 1. 创建带全文索引的虚拟表（如果是现有表，可改用FTS5的外部表关联）
CREATE VIRTUAL TABLE T_fts USING FTS5(c);
-- 2. 同步数据到全文表
INSERT INTO T_fts SELECT c FROM T;
-- 3. 不区分大小写的多关键词查询
SELECT * FROM T_fts WHERE T_fts MATCH 'a OR b';

全文索引会自动处理大小写问题，查询效率远超带通配符的LIKE，而且支持复杂的搜索语法（比如逻辑运算、短语搜索）。

二、利用数据库特定的模糊匹配索引扩展 #

如果你的数据库支持，比如PostgreSQL的pg_trgm扩展，可以创建 trigram 索引，这种索引能为字符串的三元组片段建立索引，即使查询带前置通配符的LIKE，也能利用索引快速定位。

PostgreSQL的实现示例：

-- 1. 启用pg_trgm扩展
CREATE EXTENSION pg_trgm;
-- 2. 创建trigram索引（支持不区分大小写的ILIKE）
CREATE INDEX idx_c_trgm ON T USING gin (c gin_trgm_ops);
-- 3. 不区分大小写的子串查询
SELECT * FROM T WHERE c ILIKE '%a%' AND c ILIKE '%b%';

trigram索引的性能虽然略逊于全文索引，但胜在无需修改表结构，适合现有系统的快速改造。

三、修正COLLATE的使用方式（解决你之前的无效示例问题） #

你之前的instr(c, 'a') COLLATE NOCASE写法无效，原因是COLLATE NOCASE被应用在了instr返回的数字结果上，完全没作用。正确的做法是让字符串比较本身使用不区分大小写的规则：

方案A：用LIKE结合COLLATE #

如果表的列没有默认NOCASE collation，可以直接在查询中指定：

SELECT * FROM T WHERE c LIKE '%a%' COLLATE NOCASE;

如果列已经设置了COLLATE NOCASE（比如你示例中的表），那么LIKE会默认继承该规则，直接写WHERE c LIKE '%a%'就会不区分大小写。

方案B：表达式索引配合lower()（备选方案） #

如果你不得不使用lower()，可以创建表达式索引来避免每次查询都计算lower(c)：

-- 创建lower(c)的表达式索引
CREATE INDEX idx_lower_c ON T(lower(c));
-- 查询时用小写关键词匹配
SELECT * FROM T WHERE lower(c) LIKE '%a%' AND lower(c) LIKE '%b%';

虽然带前置通配符的LIKE依然无法走B-tree索引的范围扫描，但表达式索引会存储lower(c)的结果，查询时只需扫描索引数据（比扫描原表的文本数据更快）。

为什么你的示例无效？ #

你写的：

SELECT * FROM T WHERE instr(c, 'a') COLLATE NOCASE;

这里instr(c, 'a')返回的是数字（找到的位置，没找到返回0），COLLATE NOCASE对数字没有意义，所以这个查询等价于WHERE instr(c, 'a') != 0，而instr本身是大小写敏感的，自然查不到"b"这条记录。正确的写法应该是用LIKE或者把字符串转成统一大小写后再用instr：

SELECT * FROM T WHERE instr(lower(c), 'a') > 0;

内容的提问来源于stack exchange，提问作者thisIsTheFoxe