PHP/ODBC编码问题 [英] Php/ODBC encoding problem

问题描述

我使用ODBC从PHP连接到SQL Server. 在PHP中，我从SQL Server中读取了一些字符串(nvarchar列)数据，然后将其插入到mysql数据库中.当我尝试将此类值插入mysql数据库表时，出现此mysql错误:

I use ODBC to connect to SQL Server from PHP. In PHP I read some string (nvarchar column) data from SQL Server and then want to insert it to mysql database. When I try to insert such value to mysql database table I get this mysql error:

Incorrect string value: '\xB3\xB9ow...' for column 'name' at row 1

对于具有所有ASCII字符的字符串来说，一切都很好，当存在非ASCII字符(来自某些欧洲语言)时，就会出现问题.

For string with all ASCII characters everything is fine, the problem occurs when non-ASCII characters (from some European languages) exist.

因此，更笼统地说:MS SQL Server数据库中有一个Unicode字符串，可通过PHP通过ODBC检索.然后将其放入为MySQL数据库执行的sql插入查询(作为utf-8 varchar列的值).

So, in more general terms: there is a Unicode string in MS SQL Server database, which is retrieved by PHP trough ODBC. Then it is put in sql insert query (as value for utf-8 varchar column) which is executed for mysql database.

有人可以向我解释这种情况下在编码方面发生了什么吗?在哪一步可以进行字符编码转换?

Can someone explain to me what is happening in this situation in terms of encoding? At which step what character encoding convertions may take place?

我使用:PHP 5.2.5，MySQL5.0.45-community-nt，MS Sql Server 2005.

I use: PHP 5.2.5, MySQL5.0.45-community-nt, MS Sql Server 2005.

PHP必须在 Linux 平台上运行.

PHP have to run on Linux platform.

更新:当我在此字符串上调用utf8_encode($ s)并在mysql插入查询中使用该值时，不会发生错误，但是插入的字符串在mysql中无法正确显示数据库(这样utf8编码仅可用于强制执行正确的utf8字符串，但会丢失正确的字符).

UPDATE: The error doesn't occur when I call utf8_encode($s) on this string and use that value in mysql insert query, but then the inserted string doesn't display correctly in mysql database (so that utf8 encoding only worked for enforcing proper utf8 string, but it loses correct characters).

推荐答案

首先，您具有数据库的编码.然后，您将拥有ODBC客户端使用的编码.

First you have the encoding of the DB. Then you have the encoding used by the ODBC client.

在某些情况下，如果ODBC客户端连接的编码与数据库之一不匹配，则ODBC层将自动对您的数据进行转码.

If the encoding of your ODBC client connection does not match the one of the DB, the ODBC layer will automatically transcode your data, in some cases.

这里的技巧是强制ODBC客户端连接的编码.

The trick here is to force the encoding of the ODBC client connection.

对于所有UTF-8"设置:

For an "all UTF-8" setup :

$conn=odbc_connect(DB_DSN,DB_USR,DB_PWD);
odbc_exec($conn, "SET NAMES 'UTF8'");
odbc_exec($conn, "SET client_encoding='UTF-8'");

// processing here

这与PostgreSQL + Php 5.x完美配合. 确切 的语法和选项取决于数据库供应商.

This works perfectly with PostgreSQL + Php 5.x. The exact syntax and options depends on the DB vendor.

您可以在此处找到非常有用并清除MySql的其他信息: http://dev.mysql.com/doc/refman/5.0/fr/charset-connection.html

You can find very useful and clear additional info for MySql here : http://dev.mysql.com/doc/refman/5.0/fr/charset-connection.html

希望这会有所帮助.

这篇关于PHP/ODBC编码问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！