如何在MSSQL中存储和检索扩展ASCII字符

Question

我很惊讶地发现，在搜索时无法找到这个问题的简单明了的答案。

我有一个使用PHP的Web应用程序，接受用户输入。由于应用程序的性质，用户经常使用扩展的ASCII字符（也称为“ALT代码”）。

我目前遇到的具体问题是ALT代码26，它是一个右箭头（→）。这将与其他文本一起存储在同一字段中（例如，'this→that'）。

我的列类型是NVARCHAR。

以下是我的尝试：

1. 我尝试不进行任何转换，只是正常插入值，但该值会被存储为thisâ??that。

2. 我尝试在PHP中将值转换为UCS-2，使用iconv('UTF-8', 'UCS-2', $value)，但我收到一个错误，说Unclosed quotation mark after the character string 't'.。查询最终看起来像这样：UPDATE myTable SET myColumn = 'this�!that'。

3. 我尝试做上述转换，然后在引号之前添加N，但我收到相同的错误消息。查询看起来像这样：UPDATE myTable SET myColumn = N'this�!that'。

4. 我尝试删除UCS-2转换，只是在引号之前添加N，查询再次正常工作，但该值存储为thisâ that。

5. 我尝试在PHP中使用utf8_decode($value)，但然后箭头只被替换为问号。

那么有人能回答这个（看起来简单的）问题吗？如何将此值存储在我的数据库中，并像最初输入的那样检索它？

我正在使用PHP 5.5和MSSQL 2012。如果涉及驱动程序/操作系统版本的任何问题，则是通过FreeTDS连接的Linux服务器。无法更改此设置。

- Travesty3

---

你确定 SQL Server 中存储的值是不正确的吗？SSMS 并不总是能够很好地显示扩展字符集中的字符。你可以检查该字符的实际 Unicode 值。看起来可能需要在 PHP 方面做一些工作，以使其对扩展字符集有效。 - Sean Lange

为了回答这个问题：“我已经尝试在PHP中使用utf8_decode($value)，但箭头被替换成了问号。”，这可能是因为ISO-8859-1字符集中没有该值的表示。utf8_decode将字符串转换为ISO-8859-1字符集。 - georaldc

@SeanLange：当从数据库中检索值以在应用程序中显示时，它会显示如我所提到的（损坏）。 - Travesty3

我会查看数据库并查看实际存储的值。您可以使用UNICODE函数获取有问题的字符的代码。 - Sean Lange

@georaldc：我尝试添加了那个，结果一样。 - Travesty3

Answer 1

你可以尝试对输入进行base64编码，PHP的base64_encode()和base64_decode()很容易处理，并且它应该能够处理用户输入的任何内容。

（编辑：显然，你也可以在SQL Server端进行base64编码。我认为这不是它应该负责的事情，但这是一种选择。）

- John Jones

---

好主意！我刚刚测试了一下，看起来它按预期工作。有点遗憾的是，在存储之前和之后添加这些步骤，但这是目前为止唯一有效的方法。谢谢！顺便说一句，16小时内我无法授予赏金。我想其他人也有16个小时来提出更好的解决方案！ - Travesty3

Answer 2

似乎你的freetds.conf有误。你需要TDS协议版本>= 7.0才能支持unicode。点击此处了解更多细节。

编辑你的freetds.conf:

[global]
# TDS protocol version
tds version = 7.4
client charset = UTF-8

同时确保正确配置PHP：

ini_set('mssql.charset', 'UTF-8');

- Christian Gollhardt

---

好的，tds version 应该是 7.4，因为 OP 正在访问一个 SQL Server 2012 实例。你提供的文档中说：“为获得最佳结果，请使用服务器支持的最高版本的协议。” - Lars Gyrup Brink Nielsen

来自同一链接的@LayZee：4.2 仍适用于所有产品，但受到其限制。 使用ASCI而不是UTF-8就是一种限制。仅仅使用新产品并不意味着你不能使用旧协议。 - Christian Gollhardt

我同意。我只是想指出在文档中7.4是可能且推荐的。 - Lars Gyrup Brink Nielsen

我的freetds.conf版本指定为7.1。我尝试按照上面问题评论中的建议执行ini_set('mssql.charset', 'UTF-8')。不幸的是，这些对我来说并没有解决问题。 - Travesty3

Answer 3

接受的答案似乎能够完成任务；是的，您可以将其编码为base64，然后再解码回来，但是所有使用该远程数据库的应用程序都应更改并支持字段为base64编码。我的想法是，如果有一个远程MS SQL Server数据库，可能会有其他应用程序（或应用程序）使用它，因此该应用程序还必须更改以支持纯文本和base64编码。您还需要处理纯文本和base64转换文本。

我搜索了一下，并找到了如何使用MS SQL命令和PHP将UNICODE文本发送到MS SQL Server以将UNICODE字节转换为HEX数字的方法。

如果您查看mssql_fetch_array的PHP文档http://php.net/manual/ru/function.mssql-fetch-array.php#80076的注释，您会看到一个相当不错的解决方案，将文本转换为UNICODE HEX值，然后像这样直接将该HEX数据发送到MS SQL Server：

将Unicode文本转换为HEX数据

// sending data to database 
$utf8 = 'Δοκιμή με unicode → Test with Unicode';  // some Greek text for example
$ucs2 = iconv('UTF-8', 'UCS-2LE', $utf8); 

// converting UCS-2 string into "binary" hexadecimal form 
$arr = unpack('H*hex', $ucs2); 
$hex = "0x{$arr['hex']}"; 

// IMPORTANT! 
// please note that value must be passed without apostrophes 
// it should be "... values(0x0123456789ABCEF) ...", not "... values('0x0123456789ABCEF') ..." 
mssql_query("INSERT INTO mytable (myfield) VALUES ({$hex})", $link);

现在所有的文本都以UNICODE格式正确地存储到NVARCHAR数据库字段中，这就是您需要做的一切，以便将其作为纯文本发送和存储而不进行编码。

要检索该文本，您需要请求MS SQL Server以以下方式发送回UNICODE编码的文本：

从MS SQL Server检索Unicode文本

// retrieving data from database 
// IMPORTANT! 
// please note that "varbinary" expects number of bytes 
// in this example it must be 200 (bytes), while size of field is 100 (UCS-2 chars) 

// myfield is of 50 length, so I set VARBINARY to 100
$result = mssql_query("SELECT CONVERT(VARBINARY(100), myfield) AS myfield FROM mytable", $link); 

while (($row = mssql_fetch_array($result, MSSQL_BOTH))) 
{ 
    // we get data in UCS-2 
    // I use UTF-8 in my project, so I encode it back 
    echo '1. '.iconv('UCS-2LE', 'UTF-8', $row['myfield'])).PHP_EOL; 
    // or you can even use mb_convert_encoding to convert from UCS-2LE to UTF-8
    echo '2. '.mb_convert_encoding($row['myfield'], 'UTF-8', 'UCS-2LE').PHP_EOL;
} 

插入后的包含UNICODE数据的MS SQL表格

使用PHP页面显示值的输出结果

我不确定你是否能够访问我的测试页面，但你可以尝试查看实时结果： http://dbg.deve.wiznet.gr/php56/mssql/test1.php

- Christos Lytras