使用 PHP FreeTDS 扩展从 MSSQL 服务器获取带有 UTF-8 字符集的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13377812/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting data with UTF-8 charset from MSSQL server using PHP FreeTDS extension
提问by Marius Grigaitis
I can't seem to get data from MSSQL encoded as UTF-8 using FreeTDS extension.
我似乎无法使用 FreeTDS 扩展从 MSSQL 中获取编码为 UTF-8 的数据。
Connecting:
连接：
ini_set('mssql.charset', 'UTF-8');
$this->_resource = mssql_connect($config['servername'], $config['username'], $config['password']);
I have no ability to use any other extension.
我无法使用任何其他扩展。
I've tried creating ~/.freetds.conf
我试过创建 ~/.freetds.conf
[global]
client charset = UTF-8
I've tried passing parameters to php:
我试过将参数传递给 php：
php -d mssql.charset="UTF-8" index.php
Data is still not in UTF-8.
数据仍然不是 UTF-8。
php -i
php -i
mssql
MSSQL Support => enabled
Active Persistent Links => 0
Active Links => 0
Library version => FreeTDS
Directive => Local Value => Master Value
mssql.allow_persistent => On => On
mssql.batchsize => 0 => 0
mssql.charset => no value => no value
mssql.compatability_mode => Off => Off
mssql.connect_timeout => 5 => 5
mssql.datetimeconvert => On => On
mssql.max_links => Unlimited => Unlimited
mssql.max_persistent => Unlimited => Unlimited
Ideas?
想法？
采纳答案by Louis Huppenbauer
MSSQL and UTF-8 are quite a pain in the ... sometimes. I had to convert it manually. The problem: MSSQL doesn't actually know and support UTF-8.
MSSQL 和 UTF-8 在...有时是相当痛苦的。我不得不手动转换它。问题：MSSQL 实际上并不知道和支持 UTF-8。
Convert from database value to UTF-8:
从数据库值转换为 UTF-8：
mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8' ? $value : mb_convert_encoding($value, 'UTF-8');
Converting from UTF-8 to database value:
从 UTF-8 转换为数据库值：
mb_convert_encoding($value, 'UCS-2LE', mb_detect_encoding($value, mb_detect_order(), true));
Fortunately I was using Doctrine so all I had was to create a custom StringType implementation.
幸运的是，我使用了 Doctrine，所以我所要做的就是创建一个自定义的 StringType 实现。
回答by mschraudolph
I had a similar problem and tried all settings i could find on the web - in vain.
我遇到了类似的问题，并尝试了我可以在网上找到的所有设置 - 徒劳无功。
In my case the problem was the configuration of FreeTDS itself. On Linux the file is /etc/freetds/freetds.conf
就我而言，问题是 FreeTDS 本身的配置。在 Linux 上，该文件是 /etc/freetds/freetds.conf
I had to change the Version to 7.0 (maybe other numbers work, too. i just tried 7.0)
我不得不将版本更改为 7.0（也许其他数字也有效。我刚尝试了 7.0）
[global]
# TDS protocol version
tds version = 7.0
After this, the driver seemed to accept changes of the charset like.
在此之后，驱动程序似乎接受了字符集的更改。
ini_set('mssql.charset', 'UTF-8');
Btw: the change immediately is in effect, no need to restart anything afterwards
顺便说一句：更改立即生效，之后无需重新启动任何东西
回答by Moe Far
If you use freeTDS, you should change below lines on /etc/freetds/freetds.conf:
如果您使用 freeTDS，您应该更改以下几行/etc/freetds/freetds.conf：
[global]
# TDS protocol version
tds version = 4.2
To this:
对此：
[global]
# TDS protocol version
tds version = 8.0
;tds version = 4.2
and finally add this line:
最后添加这一行：
# set charset
client charset = UTF-8
** clinet charset is in global [scope]
** clinet 字符集在全局 [范围]
In your queries, you should use N character. like this:
在您的查询中，您应该使用 N 字符。像这样：
$query = "INSERT INTO dbo.SMSOutbox (StationID, Dest, Text) VALUES ";
$query .= '(';
$query .= "'" . $this->stationId . "', ";
$query .= "'" . $this->destination . "', ";
$query .= "N'" . $this->text . "'";
$query .= ')';
回答by Magnus Johansson
You can also solve this issue by adding CharacterSet UTF-8 in the $connectionInfo before connecting to the DB.
您还可以通过在连接到数据库之前在 $connectionInfo 中添加 CharacterSet UTF-8 来解决此问题。
$serverName = "MyServer";
$connectionInfo = array( "Database"=>"AdventureWorks", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
Worked fine NO additional encoding needed.
工作正常不需要额外的编码。
回答by Sina
I had this problem and it solved by adding this line to my php script before connecting to MSSQL Server:
我遇到了这个问题，它通过在连接到 MSSQL Server 之前将此行添加到我的 php 脚本中解决了：
ini_set('mssql.charset', 'UTF-8');
回答by jjwdesign
It seem version 7.0 or great is required. iconv() also seems to work well, but is tedious.
似乎需要 7.0 或更高版本。iconv() 似乎也运行良好，但很乏味。
$query = $this->db->query($sql);
$result = $query->fetchAll(PDO::FETCH_OBJ);
foreach ($result as $row) {
foreach (get_object_vars($row) as $key => $value) {
$row->$key = (mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8')
? $value : iconv('iso-8859-1', 'utf-8', $value);
}
$results[] = $row;
}
回答by bstory
You should change your TDS version based on what version of SQL server your using. Check out the installation guide for details.
您应该根据您使用的 SQL 服务器版本更改 TDS 版本。有关详细信息，请查看安装指南。
回答by Kordik
I used same as above but windows 1250, so:
我使用与上面相同但 Windows 1250，所以：
$query = $this->db->query($sql);
$result = $query->fetchAll(PDO::FETCH_OBJ);
foreach ($result as $row) {
foreach (get_object_vars($row) as $key => $value) {
$row->$key = (mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8')
? $value : iconv('windows-1250', 'utf-8', $value);
}
$results[] = $row;
}
And then it worked, but I use polish characters
然后它起作用了，但我使用波兰语字符
