問題描述

我正在使用 DomDocument 用 PHP 生成 XML 文件，我需要處理亞洲字符.我正在使用 pdo_mssql 驅(qū)動(dòng)程序從 MSSQL2008 服務(wù)器中提取數(shù)據(jù)，并對(duì) XML 屬性值應(yīng)用 utf8_encode().只要沒有特殊字符，一切正常.

I'm generating a XML file with PHP using DomDocument and I need to handle asian characters. I'm pulling data from the MSSQL2008 server using the pdo_mssql driver and I apply utf8_encode() on the XML attribute values. Everything works fine as long as there's no special characters.

服務(wù)器是 MS SQL Server 2008 SP3

The server is MS SQL Server 2008 SP3

數(shù)據(jù)庫(kù)、表和列的排序規(guī)則都是SQL_Latin1_General_CP1_CI_AS

The database, table and column collation are all SQL_Latin1_General_CP1_CI_AS

我使用的是 PHP 5.2.17

I'm using PHP 5.2.17

這是我的 PDO 對(duì)象:

Here's my PDO object:

$pdo = new PDO("mssql:host=MyServer,1433;dbname=MyDatabase", user123, password123);

我的查詢是一個(gè)基本的 SELECT.

My query is a basic SELECT.

我知道將特殊字符存儲(chǔ)到 SQL_Latin1_General_CP1_CI_AS 列中并不是很好，但理想情況下，讓它在不更改的情況下工作會(huì)很好，因?yàn)槠渌?PHP 程序已經(jīng)使用該列并且它工作正常.在 SQL Server Management Studio 中，我可以正確地看到亞洲字符.

I know storing special characters into SQL_Latin1_General_CP1_CI_AS columns isn't great, but ideally it would be nice to make it work without changing it, because other non-PHP programs already use that column and it works fine. In SQL Server Management Studio I can see the asian characters correctly.

考慮到以上所有細(xì)節(jié)，我應(yīng)該如何處理數(shù)據(jù)?

Considering all the details above, how should I process the data?

推薦答案

我找到了解決方法，所以希望這對(duì)某人有所幫助.

I found how to solve it, so hopefully this will be helpful to someone.

首先，SQL_Latin1_General_CP1_CI_AS 是 CP-1252 和 UTF-8 的奇怪組合.基本字符是 CP-1252，所以這就是為什么我所要做的就是 UTF-8 并且一切正常.亞洲和其他 UTF-8 字符以 2 個(gè)字節(jié)編碼，php pdo_mssql 驅(qū)動(dòng)程序似乎討厭不同長(zhǎng)度的字符，因此它似乎對(duì) varchar(而不是 nvarchar)執(zhí)行 CAST，然后所有 2 個(gè)字節(jié)字符都變成問號(hào)('?').

First, SQL_Latin1_General_CP1_CI_AS is a strange mix of CP-1252 and UTF-8. The basic characters are CP-1252, so this is why all I had to do was UTF-8 and everything worked. The asian and other UTF-8 characters are encoded on 2 bytes and the php pdo_mssql driver seems to hate varying length characters so it seems to do a CAST to varchar (instead of nvarchar) and then all the 2 byte characters become question marks ('?').

我通過將其轉(zhuǎn)換為二進(jìn)制文件來(lái)修復(fù)它，然后使用 php 重建文本:

I fixed it by casting it to binary and then I rebuild the text with php:

SELECT CAST(MY_COLUMN AS VARBINARY(MAX)) FROM MY_TABLE;

在 php 中:

//Binary to hexadecimal
$hex = bin2hex($bin);

//And then from hex to string
$str = "";
for ($i=0;$i<strlen($hex) -1;$i+=2)
{
    $str .= chr(hexdec($hex[$i].$hex[$i+1]));
}
//And then from UCS-2LE/SQL_Latin1_General_CP1_CI_AS (that's the column format in the DB) to UTF-8
$str = iconv('UCS-2LE', 'UTF-8', $str);

這篇關(guān)于將 SQL_Latin1_General_CP1_CI_AS 編碼為 UTF-8的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！