在特殊字符（不可见字符，如ASCII 0、控制字符、非法字符等）上，Oracle非常宽松，基本上可以写入任何数据，包括不符合编码规则的数据。而PostgreSQL则不然，必须符合该字符集的编码，比如在UTF8字符集下，输入的字符编码比如符合UTF8编码。 **Oracle** 字符集为GBK，创建数据表，插入特殊字符（0x00），没有问题 ``` TEST-orcl@DESKTOP-V430TU3>drop table t_0x00 purge; Table dropped. TEST-orcl@DESKTOP-V430TU3>create table t_0x00(id int,c1 varchar2(200),c2 blob); Table created. TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(1,chr(0),null); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(2,'c1'||chr(0),null); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(3,chr(0)||'c1',null); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(4,'c1'||chr(0)||'c1',null); 1 row created. TEST-orcl@DESKTOP-V430TU3>insert into t_0x00 values(5,'c1'||chr(0)||'c1',to_blob(HEXTORAW('550055'))); 1 row created. TEST-orcl@DESKTOP-V430TU3> TEST-orcl@DESKTOP-V430TU3>select * from t_0x00 where c1 like '%'||chr(0)||'%'; ID C1 C2 ---------- -------------------- -------------------- 1 2 c1 3 c1 4 c1 c1 5 c1 c1 550055 TEST-orcl@DESKTOP-V430TU3> ``` **PostgreSQL** 字符集为UTF8，创建数据表，插入特殊字符（0x00），无法插入 ``` [local:/data/run/pg12]:5120 pg12@testdb=# \encoding UTF8 [local:/data/run/pg12]:5120 pg12@testdb=# drop table t_0x00; insert into t_0x00 values(4,'c1'||E'\x00'||'c1',null); insert into t_0x00 values(5,'c1'||E'\x00'||'c1','\x550055'::bytea);DROP TABLE [local:/data/run/pg12]:5120 pg12@testdb=# create table t_0x00(id int,c1 varchar(200),c2 bytea); CREATE TABLE [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(1,E'\x00',null); ERROR: invalid byte sequence for encoding "UTF8": 0x00 [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(1,chr(0),null); ERROR: null character not permitted [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(2,null,bytea '\x00'); INSERT 0 1 [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(3,E'\x00'||'c1',null); ERROR: invalid byte sequence for encoding "UTF8": 0x00 [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(4,'c1'||E'\x00'||'c1',null); ERROR: invalid byte sequence for encoding "UTF8": 0x00 [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_0x00 values(5,'c1'||E'\x00'||'c1','\x550055'::bytea); ERROR: invalid byte sequence for encoding "UTF8": 0x00 [local:/data/run/pg12]:5120 pg12@testdb=# select * from t_0x00 where c1 like '%'||chr(0)||'%'; ERROR: null character not permitted [local:/data/run/pg12]:5120 pg12@testdb=# ``` 在Oracle -> PG的迁移过程中，这些特殊字符是需要考虑的。 **参考资料** N/A