PostgreSQL ODBC驱动(psqlODBC)的字符编码转换详解

使用ODBC访问PostgreSQL的时候，客户端和数据库的字符编码很可能会不一致，这时就需要进行字符编码转码。大多数场合，ODBC驱动(psqlODBC)和PostgreSQL后台可以很好地处理字符编码转码，不需要用户操心。但是如果设置不当，也可能会产生乱码或性能问题。所以有必要了解一下使用psqlODBC时字符编码是如何处理的。

1. ANSI ODBC 驱动 or Unicode ODBC 驱动?

和大多数ODBC驱动一样，psqlODBC有ANSI和Unicode 两个驱动,那么他们有什么区别呢?
ANSI ODBC驱动提供的API是ASNI接口，比如：

RETCODE SQL_API
SQLConnect(HDBC ConnectionHandle,
  SQLCHAR *ServerName, SQLSMALLINT NameLength1,
  SQLCHAR *UserName, SQLSMALLINT NameLength2,
  SQLCHAR *Authentication, SQLSMALLINT NameLength3)

API的所有字符串参数都使用ANSI编码存放。ANSI究竟是哪种编码依赖于应用程序的运行环境。比如中文Windows下可能是GB2312。


Unicode ODBC驱动提供的API是Unicode接口，比如：

RETCODE  SQL_API SQLConnectW(HDBC ConnectionHandle,
           SQLWCHAR *ServerName, SQLSMALLINT NameLength1,
           SQLWCHAR *UserName, SQLSMALLINT NameLength2,
           SQLWCHAR *Authentication, SQLSMALLINT NameLength3)

API的所有字符串参数都使用Unicode编码存放。在Windows上意味着UTF16，有些环境下既支持UTF16也支持UTF8（比如[6.参考]中列出的产品）。

那么我们应该使用哪个驱动呢？
答案是两个都可以。
当应用程序的ODBC驱动的接口(ANSI/Unicode)不一致时,由介于应用程序和ODBC驱动之间的ODBC Manager负责ANSI和Unicode间的转换。具体如下:


1）ANSI应用程序->ANSI ODBC驱动
应用程序和ODBC间不发生编码转换

2）ANSI应用程序->Unicode ODBC驱动
ODBC Manager负责将ANSI应用程序的输入从ANSI转成Unicode,并把Unicode ODBC驱动的输出从Unicode转成ANSI。

3）Unicode应用程序->Unicode ODBC驱动
应用程序和ODBC间不发生编码转换

4）Unicode应用程序->ANSI ODBC驱动
ODBC Manager负责将Unicode应用程序的输入从Unicode转成ANSI,并把Unicode ODBC驱动的输出从ANSI转成Unicode。

除了API中显式的字符串参数，绑参时使用的C字符数据类型也分为ANSI和Unicode两个版本，即SQL_C_CHAR和SQL_C_WCHAR。ANSI应用程序绑定SQL_C_CHAR并期望返回值也是SQL_C_CHAR类型的。类似的，大多数Unicode应用程序绑定SQL_C_WCHAR并期望返回值也是SQL_C_WCHAR类型的。
ANSI ODBC驱动只支持SQL_C_CHAR；然而，ODBC 3.5兼容的Unicode ODBC驱动必须能同时支持SQL_C_CHAR和SQL_C_WCHAR。当Unicode应用程序使用ANSI ODBC驱动时，因为ANSI ODBC驱动不支持SQL_C_WCHAR，所以由ODBC Manager负责SQL_C_CHAR和SQL_C_WCHAR之间的转换。

由此可见，最好根据应用程序的类型是ANSI的还是Unicode的，选择与之一致的ODBC驱动。这样可以减少应用程序和ODBC驱动间不必要的编码转换。

2. 与数据库间的编码转换

PostgreSQL数据库的编码在创建数据库的时候就固定下来了，对中文而言可能是EUC_CN或UTF8。客户端和数据库通信的时候，ODBC驱动会通过client_encoding告诉服务端自己的编码，如果这个编码与数据库的编码不一致，由服务端负责编码转换。

对Unicode ODBC驱动，它会固定把client_encoding设置为UTF8;对于ANSI ODBC驱动，它会根据区域自动判断编码，并设置client_encoding。

当ANSI程序使用ANSI ODBC驱动时，用户也可通过以下方式设置client_encoding，以此改变ODBC的输入和输出的字符的编码。
1）PGCLIENTENCODING环境变量
2）在ODBC 数据源的连接属性中设置set client_encoding to 'UTF8'

当Unicode程序使用ANSI ODBC驱动时，不要设置client_encoding。因为ODBC Manager不认识client_encoding，它只认应用程序当前区域对应的编码。如果设置的client_encoding和区域对应的编码不一致会导致ODBC Manager实施错误的转码，从而导致乱码。(如果设置的client_encoding和区域对应的编码一致，也就没有必要设置client_encoding了，因为这就是psqlODBC默认的行为)

当使用Unicode ODBC驱动时，不管是Unicode的应用程序还是ANSI的应用程序，对client_encoding的设置都会被psqlODBC无视，它会固定使用UTF8。也就是说对Unicode psqlODBC驱动而言，UTF8是唯一可用的client_encoding。

3. 一个案例

某个程序原来是ANSI的，支持GB码。现在想支持多种语言，但是又不想改为Unicode，因为那样对程序的改动太大，因此想继续使用ANSI程序，但通过设置使用UTF8编码。

于是架构变成这样:
ANSI APP(UTF8) + Unicode psqlODBC + PostgreSQL(UTF8)

这样做表面上好像数据插入和读取都是对的。但是，打开MyLog后会发现其实是错的。
内部发生编码转码如下:
数据插入的流程
1) 应用程序调用ODBC的INSERT语句：UTF8
2) ODBC Manager进行的编码转码：GB2312->UTF16
3) Unicode psqlODBC进行的编码转码： UTF16->UTF8
4) 数据库接收到的数据：乱码

上面2)和3)的编码转码产生了乱码。但是碰巧转码过程中没有发生转码失败，数据库以为接收到的是另外的UTF8编码的字符，问题被暂时掩盖。

数据查询的流程
1) 数据库发送过来的数据：乱码
2) Unicode psqlODBC进行的编码转码： UTF8->GB2312
   ANSI应用程序期待的返回数据是SQL_C_CHAR型的，所以Unicode psqlODBC会将输出数据转成ANSI(SJIS)编码。
3) 应用程序取得的数据：UTF8

神奇的是最后读出来的数据是对的。
这是由于经过GB2312->UTF16->UTF8->GB2312这一轮转码，负负得正，应用程序最后取到的数据又恢复了原样。这给人一种一切OK的假象，其实是错的很离谱。因为，第一，数据库里存的数据已经是乱码了，用其他客户端访问的时候就会看出来；第二，上面转码的过程只是碰巧这些字符的UTF8编码也在GB2312的编码范围内，即没有发生转码错误，并且转码后的SQL也没有发生字符边界变化导致SQL语法错误的问题(比如字符串常量最后的单引号和前面汉字被结合成了一个字符)。


正确的做法应该是：
ANSI APP(UTF8) + ANSI psqlODBC(set client_encoding to 'UTF8') + PostgreSQL(UTF8)
这里必须要设置client_encoding为UTF8，否则会出现乱码。

这样设置后整个处理过程不会发生编码转码，具体如下:
数据插入的流程
1) 应用程序调用ODBC的INSERT语句：UTF8
2) ODBC Manager进行的编码转码：不转换
3) ANSI psqlODBC进行的编码转码： 不转换
4) 数据库接收到的数据：UTF8

数据查询的流程
1) 数据库发送过来的数据：UTF8
2) ANSI psqlODBC进行的编码转码： 不转换
3) 应用程序取得的数据：UTF8

 

4. 结论

1)Unicode应用程序
对于Unicode应用程序推荐使用Unicode psqlODBC，并且应用程序使用SQL_C_WCHAR和Unicode编码。

2)ANSI应用程序
对于ANSI应用程序推荐使用ANSI psqlODBC,并设置client_encoding为应用程序所使用的编码。如果应用程序使用的编码就是默认的区域编码可省略client_encoding的设置。字符类型使用SQL_C_CHAR。

 

5. 其他

1）打开MyLog的方法
通过ODBC数据源管理工具打开MyLog，或直接在连接字符串中设置。

比如：
"ODBC;DRIVER={PostgreSQL};DSN=PostgreSQL30;Debug=1;ConnSettings=set client_encoding to 'UTF8'" 

MyLog日志文件名为mylog_xxxx.log。用户数据源的日志文件输出到用户目录，系统数据源的日志文件输出到C:盘根目录。

2) 设置client_encoding的方法
在ODBC 数据源定义工具中设置set client_encoding to 'XXXX'
Datasource->Page 2->Connect Settings

或直接在连接字符串中设置，比如:
"ODBC;DRIVER={PostgreSQL};DSN=PostgreSQL30;ConnSettings=set client_encoding to 'UTF8'" 

不能使用 set client_encoding = 'UTF8' ，因为psqlODBC不认这种形式，即使连接时zhen发给服务器了，之后执行SQL前，psqlODBC还会再设一次。

6. 参考

https://www.progress.com/products/datadirect-connect/odbc-drivers/odbc-developer-center/odbc-tutorials/understanding-unicode-and-odbc-data-access/the-driver-manager-and-unicode-encoding-on-unix