SQLite字符串含NUL字节时如何替换为'\0'并保留NUL后全部内容

问题背景 #

• 接手遗留SQLite数据库时，发现部分字符串字段混入NUL字节
• 修复目标：将字符串内的NUL字节替换为字面量\0，既标记原字符串曾包含NUL字节，又保证NUL字节后的所有内容可正常读取
• 已尝试的方案均未生效，核心原因是SQLite内置字符串处理函数默认将字符串识别为NUL终止格式，读取到第一个NUL字节就会终止，无法获取NUL后的内容：
  1. 初次尝试的截取拼接SQL：

iif(instr(Message, char(0)) = 0
     , Message
     , substr(Message, 1, instr(Message, char(0)))
       || "\0"
       || substr(Message, instr(Message, char(0))-length(Message)+1)
 )

2. 直接调用replace函数的SQL：

replace(Message, char(0), "\0")

• 已验证可通过LENGTH(CAST(Message AS BLOB))获取字段实际字节长度（库内仅使用UTF-8编码7位字符，该方法返回结果准确），但substr作用于字符串类型时，无论传入正向/负向起始位置，都无法读取NUL字节后的内容
• 直接剥离NUL字节的方案会丢失NUL后的所有内容，不符合需求，需要绕开SQLite字符串函数的NUL终止限制

可行实现方案 #

SQLite的BLOB类型按原始字节序列处理，不会因为遇到NUL字节截断，所有处理逻辑可以基于BLOB类型实现：

纯SQL递归处理方案 #

通过递归CTE逐字节遍历BLOB内容，遇到NUL字节（x'00'）就替换为字面量\0对应的字节序列（反斜杠x'5c'+字符0即x'30'），其余字节原样保留，处理完成后转回TEXT类型即可。
参考SQL如下：

WITH RECURSIVE
-- 筛选所有含NUL字节的记录，提前转BLOB获取总长度
source_data AS (
    SELECT 
        id, -- 替换为表实际主键字段名
        CAST(Message AS BLOB) AS msg_blob,
        LENGTH(CAST(Message AS BLOB)) AS total_len
    FROM your_table -- 替换为实际表名
    WHERE INSTR(CAST(Message AS BLOB), x'00') > 0
),
-- 逐字节递归替换
process(pos, row_id, current_result, remaining_blob) AS (
    SELECT 
        1 AS pos,
        id,
        CAST('' AS BLOB) AS current_result,
        msg_blob AS remaining_blob
    FROM source_data
    UNION ALL
    SELECT
        pos + 1,
        row_id,
        current_result || CASE 
            WHEN SUBSTR(remaining_blob, 1, 1) = x'00' THEN CAST('\0' AS BLOB)
            ELSE SUBSTR(remaining_blob, 1, 1)
        END,
        SUBSTR(remaining_blob, 2)
    FROM process
    WHERE LENGTH(remaining_blob) > 0
)
-- 关联原表更新处理后的值
UPDATE your_table
SET Message = CAST(p.current_result AS TEXT)
FROM process p
WHERE your_table.id = p.row_id
AND LENGTH(p.remaining_blob) = 0;

使用注意：

• 执行前务必备份原数据库，避免误操作导致数据损坏
• 将SQL中的id、your_table、Message替换为实际业务使用的主键名、表名、待修复字段名
• 该逻辑兼容全UTF-8字符集场景：UTF-8多字节字符的编码不会出现0x00字节，不会出现误替换或乱码问题

导出替换方案 #

如果不想使用递归SQL，也可以通过导出导入方式处理：

1. 通过sqlite3命令行客户端，以insert模式导出目标表数据，导出时BLOB值会以十六进制格式存储，不会被NUL截断
2. 使用sed等文本处理工具，将导出内容中对应原NUL字节的十六进制标记替换为\0对应的字节序列
3. 清空原表后导入处理完成的SQL文件即可

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