Oracle 11.2如何查询表文本列中包含非UTF-8无效字符的记录

筛选含无效字符的记录 #

你之前的方法失效，是因为看到的ó这类字符是客户端字符集配置不匹配导致的显示乱码，并非数据库中实际存储的原始字符序列，所以直接匹配字符串无法命中目标记录。可选用以下两种成熟方案实现300万级数据全量校验：

方法1：使用UTL_I18N内置包校验（性能最优） #

Oracle 11.2内置的UTL_I18N.IS_VALID_CHARACTER函数可直接判断字符是否符合指定字符集规范，返回1为合法，返回0为非法，性能足以支撑大表全量扫描：

SELECT * 
FROM <你的表名>
WHERE UTL_I18N.IS_VALID_CHARACTER(<你的文本字段名>, 'UTF8') = 0;

方法2：使用字符集转换对比（无包权限时可用） #

如果当前账号没有调用UTL_I18N包的权限，可以利用字符集转换的特性：非法UTF-8字符在转换过程中会被自动替换为默认替换符，对比转换前后的字段内容是否一致即可识别无效记录：

SELECT *
FROM <你的表名>
WHERE <你的文本字段名> != CONVERT(<你的文本字段名>, 'UTF8', 'UTF8');

批量修复无效字符 #

基础修复方案 #

直接通过转换逻辑批量替换非法字符，无需逐个匹配乱码内容：

UPDATE <你的表名>
SET <你的文本字段名> = CONVERT(<你的文本字段名>, 'UTF8', 'UTF8')
WHERE UTL_I18N.IS_VALID_CHARACTER(<你的文本字段名>, 'UTF8') = 0;
COMMIT;

如果确认乱码是因为导入时字符集配置错误（比如将Latin1编码的内容以UTF-8编码存入数据库），可以调整转换逻辑还原正确内容：

UPDATE <你的表名>
SET <你的文本字段名> = CONVERT(<你的文本字段名>, 'UTF8', 'WE8ISO8859P1')
WHERE <筛选条件>;
COMMIT;

生产环境分批次更新方案 #

300万条全表更新如果直接执行可能导致锁表时间过长，建议用PL/SQL分批次提交，每次处理1万条：

DECLARE
  CURSOR cur_err_records IS
    SELECT ROWID AS record_rowid
    FROM <你的表名>
    WHERE UTL_I18N.IS_VALID_CHARACTER(<你的文本字段名>, 'UTF8') = 0;
  TYPE rowid_list IS TABLE OF ROWID INDEX BY PLS_INTEGER;
  l_rowids rowid_list;
BEGIN
  OPEN cur_err_records;
  LOOP
    FETCH cur_err_records BULK COLLECT INTO l_rowids LIMIT 10000;
    EXIT WHEN l_rowids.COUNT = 0;
    
    FORALL i IN 1..l_rowids.COUNT
      UPDATE <你的表名>
      SET <你的文本字段名> = CONVERT(<你的文本字段名>, 'UTF8', 'UTF8')
      WHERE ROWID = l_rowids(i);
    COMMIT;
  END LOOP;
  CLOSE cur_err_records;
END;
/

注意事项 #

• 执行全表更新操作前请先备份整表或筛选出的错误记录，避免数据丢失
• 如果需要自定义非法字符的替换值（比如替换为空格），可以在CONVERT转换后再通过REPLACE替换默认替换符即可

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