SqlServe到PG迁移错误：无效的编码序列"UTF8": 0x00-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

环境：

sqlserver2008 R2 （winXP）

postgresql9.3.4 （win7-64bit）

1.通过java像PostgreSQL提交批量 insert（或者普通insert或者执行copy）：

错误：

java.sql.BatchUpdateException: 批次处理 被中止，呼叫 getNextException 以取得原因。

解决：在catch模块打印出getNextException的内容，就能知道具体的原因是什么了。

catch (ClassNotFoundException | SQLException ex)
{
       System.out.println("Error: " + ex.getMessage());
       ex.printStackTrace(System.out);
       if (ex instanceof BatchUpdateException)
       {
             BatchUpdateException bex = (BatchUpdateException) ex;
             bex.getNextException().printStackTrace(System.out);
       }
}

2.getNextException抓到的具体错误信息是：org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00

org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
 at JDBCTest.ConnTest.main(ConnTest.java:154)

3.经查看sqlsever和PG的字符编码:

sql server的字符集编码是GBK（对于sqlserver查看encoding很复杂，我看参考了这个方法http://bbs.csdn.net/topics/190034619）

PostgreSQL的字符集编码是UTF8（对于PG来说很容易查看encoding，看一下属性中的encoding就可以）

这样看来两个数据库的字符集编码不同，但是PG的服务器端不支持字符集GBK，也就是创建数据库时不能指定encoding=GBK，PG中可替代GBK的字符集EUC_CN也无法使用。只有客户端可以支持GBK，但该设置只在一个连接中有效，故在插入数据之前执行 ppstmt =conn.prepareStatement("set client_encoding=GBK;");，此时又产生了如下错误：

org.postgresql.util.PSQLException: The server's client_encoding parameter was changed to GBK. The JDBC driver requires client_encoding to be UTF8 for correct operation.
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1966)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:365)
	at JDBCTest.ConnTest.main(ConnTest.java:56)

这样看来，sqlserver和PG的字符集没法实现完全统一。

4.重新回到invalid byte sequence for encoding "UTF8": 0x00。

我在postgresql的社区邮件列表里找到了这个问题：http://postgresql.1045698.n5.nabble.com/invalid-byte-sequence-for-encoding-quot-UTF8-quot-0x00-td2172080.html

http://postgresql.1045698.n5.nabble.com/supporting-0x00-from-client-Unicode-JDBC-td1969492.html#a1969493

通过邮件列表中提到的内容以及个人多次测试报错表的字段，发现导致问题的原因就是字符类型字段值中含有空字符\0，这是一个结束符。（Unicode 查询工具 http://www.scarfboy.com/coding/unicode-tool?s=U%2B2323）。

5. 问题重现：

在sqlserver中：

create table test_varchar3 (id int,name varchar(23));
insert into test_varchar3  values (1,'ddd'+CHAR(0)+'aaa');
insert into test_varchar3  values (2,'ddd'+CHAR(0));
insert into test_varchar3  values (3,CHAR(0)+'aaa');
insert into test_varchar3  values (4,'aaa\0');---注意此种的\0仅仅是字符串并不是空字符'\0'
select * from test_varchar3;

将该表从sqlserver迁移到pg：

通过jdbc取到的(其中空字符无法表示，此处用空格替代)：

id,name,name_length

1,ddd aaa,length=7

2,ddd ,length =4

3, aaa,length=4

4,aaa\0,length=5

错误重现：invalid byte sequence for encoding "UTF8": 0x00。

解决方法：在向pg中copy或者insert数据提交之前，现将其中的字符类型字段值中的空字符\0取掉即可成功执行。

比如：str.replaceAll("\0", ""); 或者str.replace("\0", "");

6.扩展问题：

sqlserver中字符类型字段值，当插入的字符值中带有空字符的时候，sqlserver客户端显示时会去掉空字符\0之后的字符，但是在查询中要查到该条数据还需要匹配\0之后的内容：

create table test_varchar3 (id int,name varchar(23));
insert into test_varchar3  values (1,'ddd'+CHAR(0)+'aaa');
insert into test_varchar3  values (2,'ddd'+CHAR(0));
insert into test_varchar3  values (3,CHAR(0)+'aaa');
insert into test_varchar3  values (4,'aaa\0');
select * from test_varchar3;

select * from test_varchar3 where name='ddd';
id | name
-------------
2  |  ddd 
select * from test_varchar3 where name='ddd'+CHAR(0);
id | name
-------------
2  |  ddd 
select * from test_varchar3 where name='ddd'+CHAR(0)+'aaa';
id | name
-------------
1  |  ddd 
select * from test_varchar3 where name=CHAR(0);
id | name
------------

select * from test_varchar3 where name=CHAR(0)+'aaa';
id | name
-------------
3  |

关于如何从sqlsever数据库中去掉子赋值中的空值结束符，可以参考（待补充）：

http://stackoverflow.com/questions/3533320/sql-server-remove-end-string-character-0-from-data

所以说尽管空值终止符'\0'是空的不显示的，但是他会对字符类型字段值及其查询等造成影响，所以使用sqlsever最好不要向字符类型字段中插入带有空值终止符'\0'的字符或字符串。

关于空值终止符'\0'或者其他控制符的详细介绍，请参考MS SQL Server官方文档：

http://msdn.microsoft.com/en-us/library/ms191485.aspx。

转载于:https://my.oschina.net/liuyuanyuangogo/blog/306793