Pentaho从MySQL同步数据到PostgreSQL报UTF-8无效字节序列0x00错误如何解决

该报错的核心原因和NULL值无关，是MySQL端char/varchar/text类字符串字段中存储了空字节字符（\0，十六进制编码为0x00），而PostgreSQL的UTF8编码规范明确禁止存储该字符，因此写入时触发校验失败。你此前尝试的NULL值处理、编码设置、懒转换都没有针对非法的0x00字符做处理，所以无法解决问题。

以下是适配3000万级大数据量的解决方案，按性能从高到低排序：

方案1：在MySQL表输入步骤直接处理（推荐） #

直接在表输入的查询SQL中，对所有字符串类型字段做0x00字符替换，在数据源侧直接清洗非法字符，不会增加Pentaho的处理负担，性能损耗最低：

SELECT
  非字符串字段直接保留,
  -- 对每个字符串字段做替换，0x00字符替换为空，也可以根据需求替换为NULL
  NULLIF(REPLACE(字符串字段名, CHAR(0), ''), '') AS 字符串字段名,
  其他字段...
FROM 你的表名

CHAR(0)是MySQL中代表0x00空字节的写法，REPLACE会直接删除所有该字符，NULLIF可以根据业务需求决定是否把替换后为空的内容转为NULL。

方案2：在Pentaho转换流程中加清洗步骤 #

如果不方便修改查询SQL，可以在「选择值」步骤前增加用户自定义Java表达式步骤，对所有字符串字段做空字节替换：

• 新增字段时选择和原字段相同的类型，表达式写为：
  字段名.replaceAll("\\u0000", "")
  如果需要将替换后为空的内容转为NULL，可以调整为：
  字段名.replaceAll("\\u0000", "").isEmpty() ? null : 字段名.replaceAll("\\u0000", "")

辅助定位方案（不确定哪个字段含非法字符时使用） #

可以在「PostgreSQL表输出」步骤上开启错误处理，将写入失败的行路由到临时文本文件或临时表中，先定位具体存在非法字符的字段，再针对性处理，不需要对所有字段做批量替换。

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