SQLite无法实现不区分大小写搜索问题求助

环境与配置 #

• 连接初始化代码：

  new SQLiteAsyncConnection(_dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache));
  

• 未配置任何影响LIKE大小写敏感性的PRAGMA
• 表定义（已添加[Collation("NOCASE")]并重建表）：

  [Table($"{nameof(Customer)}s")]
  public class Customer
  {
      [PrimaryKey, AutoIncrement]
      public long CustomerId { get; set; }
      [Collation("NOCASE")]
      public string? BusinessName { get; set; }
      [Collation("NOCASE")]
      public string? Suburb { get; set; }
      // ... 其他字段
  }
  

问题现象 #

数据中有9条Suburb为全大写"BRUNSWICK HEADS"的记录，但出现以下异常情况：

• Linq查询：搜索词与列值全大写时返回9条结果，混合大小写搜索时返回0条，说明搜索仍大小写敏感
• 参数化SQL查询：无论使用=还是LIKE操作符，均返回0条结果；但直接写死值的SQL查询可正常返回结果

当前临时方案 #

将搜索词和列值都转为小写后查询，但该方案无法利用列索引，性能较差

解决方案 #

1. 验证列排序规则是否生效

先通过SQLite工具（如sqlite3命令行）检查表结构，确认列是否正确应用了NOCASE排序规则：

PRAGMA table_info(Customers);

查看Suburb和BusinessName列的collation字段是否为NOCASE。如果未生效，手动修改列的排序规则：

ALTER TABLE Customers MODIFY COLUMN Suburb TEXT COLLATE NOCASE;
ALTER TABLE Customers MODIFY COLUMN BusinessName TEXT COLLATE NOCASE;

修改后需重建相关索引。

2. 修复参数化查询失效问题

参数化查询未应用排序规则时，可在SQL语句中显式指定COLLATE NOCASE：

SELECT * FROM Customers WHERE Suburb LIKE ? COLLATE NOCASE;

Linq查询则通过Collate方法显式声明：

var results = await _connection.Table<Customer>()
    .Where(c => c.Suburb.Collate("NOCASE") == searchTerm)
    .ToListAsync();

3. 全局配置LIKE大小写敏感性

SQLite默认LIKE操作是大小写敏感的，可通过会话级PRAGMA修改：

await _connection.ExecuteAsync("PRAGMA case_sensitive_like = FALSE;");

注意该设置仅对当前连接会话有效，每次重新连接都需要执行。

4. 创建带NOCASE规则的索引

如果要让索引支持不区分大小写查询，需创建带排序规则的索引：

CREATE INDEX idx_Customers_Suburb ON Customers(Suburb COLLATE NOCASE);
CREATE INDEX idx_Customers_BusinessName ON Customers(BusinessName COLLATE NOCASE);

这样后续的不区分大小写查询就能正常利用索引提升性能。

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