金仓数据库：如何高效处理 SQL 错误 [0A000] 中的 nondeterministic collations 限制

1. 从一次深夜告警说起：我遇到的“神秘”错误

那天晚上，我正在处理一个常规的数据查询需求，系统突然给我弹了个告警。日志里赫然躺着一行刺眼的错误信息：SQL 错误 [0A000]: ERROR: nondeterministic collations are not supported for LIKE。说实话，第一眼看到“nondeterministic collations”这个词组，我也有点懵。这听起来像是数据库底层一个非常学术化的限制。我的查询很简单，就是一个LIKE '%某个关键词%'的模糊匹配，在别的数据库上跑得好好的，怎么到了金仓数据库（KingbaseES）这儿就报错了呢？

经过一番排查和学习，我才搞明白。这个错误的核心，其实在于“排序规则”（Collation）。你可以把排序规则理解为数据库处理文本比较、排序时的一套“字典”或“规则手册”。比如，字母“a”和“A”在比较时算不算一样？德语的“ß”和“ss”是不是等价？这些都由排序规则来定义。金仓数据库默认使用一种叫做“C”的排序规则，它是一种“确定性”的规则，简单粗暴，直接按字符的二进制编码进行比较，不玩任何花样。

而“非确定性排序规则”（Nondeterministic Collation），通常是指那些更智能、更贴近人类语言习惯的规则，比如基于Unicode标准（ICU库）的规则。这些规则能正确处理大小写不敏感、重音符号忽略、以及某些语言中字符的等价关系（比如前面说的德语例子）。问题就出在这里：金仓数据库的LIKE操作符在处理这类复杂的、非确定性的排序规则时，内部机制存在限制，无法准确判断某些字符的等价形式在模糊匹配中该如何处理，所以干脆就报错，不让这么用了。

这其实是一个典型的“高级功能”与“特定操作”兼容性冲突的问题。你的数据列可能因为迁移、继承默认设置或者为了支持多语言，使用了这种高级的排序规则，但当你用它进行最基础的LIKE模糊查询时，却触发了底层限制。别担心，这个问题并不罕见，而且有不止一种方法可以高效地解决它。下面我就把自己踩坑后总结的几种实战方案，以及背后的考量，详细分享给你。

2. 首选方案：查询时临时“切换规则”

当我第一次解决这个问题时，我选择的方法是在查询语句里动个小手术。这也是我最推荐给大多数人的首选方案，因为它改动最小、最安全、最即时。

具体怎么做呢？就是在你使用LIKE进行匹配的那个列名后面，加上一个COLLATE "C"的指令。这相当于在运行这一条查询的瞬间，告诉数据库：“别用这列原来的复杂排序规则了，临时改用简单直接的‘C’规则来处理这次比较。”

-- 假设你的原查询是这样报错的
SELECT * FROM 用户表 WHERE 用户名 LIKE '%张%';

-- 修改后，在列名后指定 COLLATE "C"
SELECT * FROM 用户表 WHERE 用户名 COLLATE "C" LIKE '%张%';

就这么简单的一行改动，错误通常就消失了。我把它理解为给查询戴上一副“特制眼镜”，这副眼镜（C规则）只看字符最原始的编码，不理会任何语言上的等价变换，因此完美避开了LIKE操作符的那个内部限制。

实测下来很稳，但这里有几个细节需要注意：

1. 比较行为的变化：COLLATE "C"是二进制比较，意味着它是大小写敏感的。‘Zhang’和‘zhang’会被视为不同的字符串。如果你的业务逻辑原本期望的是不区分大小写的模糊搜索，那这个改动可能会影响结果。你需要评估这是否符合你的需求。
2. 仅限当前查询：这个修改只影响这一条SQL语句的执行。数据库表里该列的原始排序规则没有任何变化，其他所有查询和操作都不受影响。这是它最大的优点——影响范围可控。
3. 性能：基本上没有额外的性能开销。数据库只是换了一种更简单的规则进行计算，对于单次查询来说，可以忽略不计。

所以，当你只是要快速修复一个报错的查询，并且能接受二进制比较的特性时，方法1是你的不二之选。它就像一把精准的手术刀，直击问题要害。

3. 根治方案：修改表或列的排序规则

如果经过评估，你发现业务中很多查询都需要用到LIKE，并且都期望使用简单的“C”规则，那么临时在每个查询里加COLLATE就显得有点繁琐了。这时，你可以考虑“根治”方案：直接修改表或列的默认排序规则。这相当于把“特制眼镜”焊在了数据上，以后所有操作默认都透过它来看。

这里有两种粒度：

### 3.1 修改表的默认排序规则

这条SQL语句会改变表的“默认”排序规则，主要影响的是以后新增的列。

ALTER TABLE 用户表 COLLATE "C";

我实测过，这个操作非常轻量。它基本上只修改了数据库系统表里的一条元数据记录，告诉系统：“这张表以后新加的列，如果没有特别说明，就用‘C’规则。”它不会去动表里已经存在的任何一行的数据，也不会重建任何索引。所以执行速度很快，对在线业务影响极小（主要是在执行瞬间需要一个高级别的锁）。但请注意，它不会解决现有列的问题。如果报错的是表里一个已经存在的列，这个操作帮不上忙。

### 3.2 修改特定列的排序规则

这才是真正“根治”现有列问题的操作。

-- 假设‘用户名’列是 text 类型
ALTER TABLE 用户表 ALTER COLUMN 用户名 TYPE text COLLATE "C";

注意，这是一个“高危操作”！ 我称之为高危，不是因为它复杂，而是因为它背后的影响。这条语句执行时，数据库实际上做了一件大事：它为了改变这个列的排序规则属性，会重新创建这个列。这意味着：

1. 数据重写：表里每一行这个列的数据，都会被读取、转换（虽然字符内容不变，但属性变了）、再写回去。对于大表，这会产生巨大的磁盘I/O。
2. 索引重建：所有建立在用户名列上的索引，都会因为列被重建而失效，需要全部重新建立。这又是一轮密集的CPU和I/O消耗。
3. 锁表：在整个操作期间，表通常会被加上最高级别的锁（ACCESS EXCLUSIVE），其他所有试图访问这张表的操作（包括简单的SELECT）都会被阻塞。

我曾在测试环境对一个几百GB的表做过这个操作，系统负载瞬间飙升，操作持续了数十分钟。所以，如果你决定走这条路，务必做好以下准备：

• 评估影响：先用SELECT pg_size_pretty(pg_total_relation_size('用户表'));看看表有多大。
• 选择窗口：一定要在业务低峰期、甚至计划停机时间进行操作。
• 备份：操作前务必进行数据备份。
• 监控：操作时密切监控数据库的CPU、内存、磁盘I/O和锁等待情况。

对于小型或中型表，直接操作或许可以接受。但对于大型或核心业务表，我强烈建议你继续往下看，我们有更优雅的“无损”迁移方案。

4. 另辟蹊径：用正则表达式或其他操作符替代LIKE

有时候，我们不一定非要跟LIKE和排序规则死磕。金仓数据库（继承自PostgreSQL）提供了非常强大的正则表达式功能，完全可以实现甚至超越LIKE的模糊匹配能力，而且不受这个排序规则错误的限制。

### 4.1 使用 ~ 操作符进行正则匹配

~是PostgreSQL/Kingbase中用于正则表达式匹配的操作符。如果你的模糊查询需求可以用正则来描述，用它就对了。

-- 等价于 WHERE 用户名 LIKE '%张%'
SELECT * FROM 用户表 WHERE 用户名 ~ '张';

-- 更强大的例子：查找以‘张’开头，后面跟着任意两个字符的名字
SELECT * FROM 用户表 WHERE 用户名 ~ '^张..';

用正则表达式最大的好处就是灵活和强大，并且完美规避了collation错误。但缺点是对不熟悉正则的开发同学有点门槛，而且正则表达式的性能通常不如简单的LIKE，特别是在表数据量极大且没有合适索引的情况下。复杂的正则可能会让查询变慢。

### 4.2 使用 SIMILAR TO 操作符

这是一个折中的选择，语法更像LIKE（也使用%和_通配符），但底层是正则引擎。

SELECT * FROM 用户表 WHERE 用户名 SIMILAR TO '%张%';

它能解决报错问题，但性能和正则表达式~类似，也需要谨慎使用。

### 4.3 使用 ILIKE 操作符（不区分大小写的LIKE）

这是一个非常容易被忽略的简单方案！如果你的报错场景中，只是想做不区分大小写的模糊匹配，那么直接把LIKE换成ILIKE试试看。

-- 将报错的 LIKE 直接替换为 ILIKE
SELECT * FROM 用户表 WHERE 用户名 ILIKE '%zhang%';

ILIKE是数据库原生支持的不区分大小写匹配操作符。在我遇到的不少案例里，用户原本就是想忽略大小写，结果用了LIKE触发了复杂排序规则问题。换成ILIKE后，不仅错误消失，语义也更准确了。这是最轻量级的解决方案之一，前提是你的需求确实是不区分大小写。

### 4.4 其他字符串函数

对于一些简单的包含性检查，不一定非要用模式匹配。

-- 检查是否包含子串
SELECT * FROM 用户表 WHERE POSITION('张' IN 用户名) > 0;

-- 检查是否以某字符串结尾
SELECT * FROM 用户表 WHERE RIGHT(用户名, LENGTH('三')) = '三';

这些函数通常也能正常工作，但功能比较单一，不如LIKE或正则通用。

5. 高级场景：拥抱全文检索

如果你的搜索需求不仅仅是简单的模式匹配，而是涉及中文分词、语义搜索、相关性排序等，那么是时候考虑更专业的工具了——全文检索。金仓数据库内置了强大的全文检索功能，基于文本搜索向量（tsvector）和查询（tsquery），这完全是另一套体系，从根本上绕开了排序规则的问题。

举个例子，你想在文章内容里搜索“数据库管理”：

-- 1. 创建全文检索索引（假设‘内容’是text类型列）
CREATE INDEX 文章_内容_全文索引 ON 文章表 USING GIN (to_tsvector('chinese', 内容));

-- 2. 使用全文检索进行查询
SELECT * FROM 文章表 WHERE to_tsvector('chinese', 内容) @@ to_tsquery('数据库 & 管理');

这里的关键是to_tsvector('chinese', 内容)，它会把中文内容按词汇进行分词，转换成搜索向量。@@是全文检索匹配操作符。to_tsquery('数据库 & 管理')表示要搜索同时包含“数据库”和“管理”的文档。

全文检索的优势非常明显：

• 无排序规则困扰：处理的是分词后的词元，与字符级的排序规则无关。
• 支持中文分词：这是LIKE做不到的，LIKE‘%管理%’是找不到“数据库管理”这个词的。
• 效率高：配合GIN索引，在海量文本中搜索关键词的速度远超LIKE。
• 功能强大：支持与、或、非逻辑，以及词干提取、同义词、排名等高级功能。

当然，它的缺点是配置相对复杂，需要理解分词器、词典等概念，并且改变了搜索的语义（从字符匹配变为词汇匹配）。但对于真正的文本搜索场景，投资学习全文检索是绝对值得的。

6. 大表实战：安全无忧的“零停机”修改方案

回到最棘手的场景：你的表很大（比如几百GB甚至上TB），业务又不能停，但你又确实需要永久修改某个列的排序规则来一劳永逸。直接ALTER COLUMN的风险太高，怎么办？我在生产环境实践过一套 “影子表+切换” 的方案，可以实现近乎零停机的平滑迁移。

核心思路：不在原表上直接动手术，而是创建一张结构符合要求的新表，把数据慢慢迁移过去，最后在瞬间完成新旧表的“身份互换”。

### 6.1 操作步骤详解

-- 步骤1：创建一张和原表结构一模一样的新表，并直接指定好目标排序规则
CREATE TABLE 用户表_新 (LIKE 用户表 INCLUDING ALL);
ALTER TABLE 用户表_新 ALTER COLUMN 用户名 TYPE text COLLATE "C";
-- 注意：INCLUDING ALL 会复制索引、约束等，但排序规则可能需要单独调整

-- 步骤2：分批迁移数据
-- 这是关键，分批进行可以避免一次性占用过多资源和产生长事务。
INSERT INTO 用户表_新
SELECT * FROM 用户表
WHERE id BETWEEN 1 AND 100000; -- 假设id是主键，第一次迁移前10万条
-- 然后继续迁移 100001-200000，以此类推，直到全部迁移完毕。
-- 可以使用脚本或工具（如金仓的etl工具）来循环控制。

-- 在迁移过程中，原表‘用户表’仍然正常对外提供服务。
-- 如果有新的数据写入，需要在每次分批迁移后，同步一下增量数据（根据更新时间戳或日志）。

-- 步骤3：准备切换（在业务最低谷的短暂窗口进行）
-- 首先，确保所有数据都已迁移，并处理完最后的增量。
-- 然后，在一个数据库事务中完成原子切换：
BEGIN;
-- 获取排它锁，阻塞极短时间
LOCK TABLE 用户表 IN ACCESS EXCLUSIVE MODE;
-- 将原表改名
ALTER TABLE 用户表 RENAME TO 用户表_旧;
-- 将新表改名为原表名
ALTER TABLE 用户表_新 RENAME TO 用户表;
COMMIT;

-- 步骤4：后续清理
-- 观察新表‘用户表’运行稳定后，可以择机删除备份的旧表。
-- DROP TABLE 用户表_旧;

### 6.2 方案优势与注意事项

这个方案的最大好处是将一个长时间的高风险锁表操作，转化为了一个仅在最后切换瞬间需要的、极短时间的锁。数据迁移过程可以在业务高峰期之外慢慢进行，对线上服务影响极小。

需要注意的坑：

• 外键约束：如果原表有被其他表外键引用，或者在迁移前创建了新表，新表的外键关系需要妥善处理。可以在创建新表时不包含外键（EXCLUDING CONSTRAINTS），在切换后再添加。
• 序列和触发器：LIKE ... INCLUDING ALL可能会复制序列的关联，需要检查。
• 并发和增量：迁移过程中，如果原表有大量写操作，捕获并同步增量数据会变得复杂。可能需要借助更专业的CDC（变更数据捕获）工具，或者选择在写操作极少的时段快速完成全量迁移。
• 权限：新表需要重新授予原表相关的角色权限。

对于超大型表，金仓数据库可能还提供了一些内置的重定义工具（类似Oracle的DBMS_REDEFINITION），可以自动管理这些过程，建议查阅最新官方文档。

7. 决策指南：如何选择最适合你的方法？

分享了这么多方案，你可能有点选择困难。别急，我画个简单的决策树，帮你快速定位：

1. 你的需求是否只是不区分大小写的匹配？

   • 是 → 直接尝试 方法4.3：使用 ILIKE。这是最简单的试金石。
   • 否 → 进入下一步。

2. 你只是要临时修复一个或几个特定查询吗？

   • 是 → 优先选择 方法2：查询时加 COLLATE "C"。快、稳、影响小。
   • 否 → 进入下一步。

3. 你的搜索需求是否是针对大段文本、需要分词和相关性排名？

   • 是 → 认真考虑 方法5：全文检索。这是面向未来的升级方案。
   • 否 → 进入下一步。

4. 你是否愿意且能够接受正则表达式的语法和性能特性？

   • 是 → 可以使用 方法4.1：正则表达式 ~ 作为通用替代。
   • 否 → 进入下一步。

5. 你是否希望永久性解决表中某列的所有LIKE问题？

   • 是 → 评估表大小。
     • 小表（如<10GB）：可以考虑 方法3.2：直接ALTER COLUMN，在维护窗口操作。
     • 中大型表（>10GB）：必须采用 方法6：影子表迁移方案，确保业务连续性。
   • 否 → 回到方法2，继续使用临时方案。

最后，也是最重要的经验：无论选择哪种方案，在生产环境执行前，一定要在测试环境充分验证！特别是修改表结构或迁移数据的操作，要模拟真实的数据量和并发，观察对应用的影响。处理数据库问题，谨慎永远是第一美德。金仓的这个错误提示虽然看起来有点晦涩，但只要我们理解了排序规则这个核心概念，就能找到多条解决问题的路径。希望我的这些实战经验，能帮你下次遇到类似问题时，从容不迫，快速搞定。