PHP + SQL Server - 如何为连接设置字符集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1322421/
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
PHP + SQL Server - How to set charset for connection?
提问by Maurice
I'm trying to store some data in a SQL Server database through php.
我正在尝试通过 php 在 SQL Server 数据库中存储一些数据。
Problem is that special chars aren't converted properly. My app's charset is iso-8859-1 and the one used by the server is windows-1252.
问题是特殊字符没有正确转换。我的应用程序的字符集是 iso-8859-1，服务器使用的字符集是 windows-1252。
Converting the data manually before inserting doesn't help, there seems to be some conversion going on.
在插入之前手动转换数据无济于事，似乎正在进行一些转换。
Running the SQL query 'set char_convert off' doesn't help either.
运行 SQL 查询 'set char_convert off' 也无济于事。
Anyone have any idea how I can get this to work?
任何人都知道我怎样才能让它发挥作用？
EDIT:I have tried ini_set('mssql.charset', 'windows-1252'); as well, but no result with that one either.
编辑：我试过 ini_set('mssql.charset', 'windows-1252'); 同样，但也没有结果。
采纳答案by Mano Kovacs
Client charset is necessary but not sufficient:
客户端字符集是必要的，但还不够：
ini_set('mssql.charset', 'UTF-8');
I searched for two days how to insert UTF-8 data (from web forms) into MSSQL 2008 through PHP. I read everywhere that you can't, you need to convert to UCS2 first (like cypher's solution recommends). On Windows SQLSRV said to be a good solution, which I couldn't try, since I am developing on Mac OSX.
我搜索了两天如何通过 PHP 将 UTF-8 数据（来自网络表单）插入到 MSSQL 2008 中。我到处都读到你不能，你需要先转换到 UCS2（就像 cypher 的解决方案推荐的那样）。在 Windows 上 SQLSRV 据说是一个很好的解决方案，我无法尝试，因为我是在 Mac OSX 上开发的。
However, FreeTDS manual (what PHP mssql uses on OSX) says to add a letter "N" before the opening quote:
但是，FreeTDS 手册（PHP mssql 在 OSX 上使用的内容）说在开头引用前添加一个字母“N”：
mssql_query("INSERT INTO table (nvarcharField) VALUES (N'?áú??á球最大的采购批发平台')", +xon);
According to this discussion, N character tells the server to convert to Unicode. https://softwareengineering.stackexchange.com/questions/155859/why-do-we-need-to-put-n-before-strings-in-microsoft-sql-server
根据这个讨论，N 字符告诉服务器转换为 Unicode。 https://softwareengineering.stackexchange.com/questions/155859/why-do-we-need-to-put-n-before-strings-in-microsoft-sql-server
回答by Matt Setter
I suggest looking at the following points:
我建议看以下几点：
- Ensure that the columns that you're storing the information in are ncharor nvarcharas char and nvarchar don't support UCS-2(SQLServer doesn't store in UTF-8 format btw)
- If you're connecting with the mssql library/extensionfor PHP, run:
ini_set('mssql.charset', 'utf-8');as there's no function with a charset argument (connect, queryetc) - Ensure that your browsers charset is also set to UTF-8
- 确保您存储信息的列是nchar或nvarchar作为 char 并且 nvarchar 不支持UCS-2（顺便说一句，SQLServer 不以 UTF-8 格式存储）
- 如果您要连接PHP的mssql 库/扩展，请运行：
ini_set('mssql.charset', 'utf-8');因为没有带有字符集参数的函数（连接、查询等） - 确保您的浏览器字符集也设置为UTF-8
回答by Jaime Neto
I had the same problem and ini_set('mssql.charset', 'utf-8')did not work for me.
However, it worked in uppercase:
我有同样的问题，ini_set('mssql.charset', 'utf-8')对我不起作用。但是，它以大写形式工作：
ini_set('mssql.charset', 'UTF-8');
回答by chugadie
If you are using TDS protocol version 7 or above, ALL communications over the wire are converted to UCS2. The server will convert from UCS2 into whatever the table or column collation is set to, unless the column is nvarchar or ntext. You canstore UTF-8 into regular varchar or text, you just have to use a TDS protocol version lower than 7, like 6.0 or 4.2. The only drawback with this method is that you cannot query any nvarchar, ntext, or sys.* tables (I think you also can't do any CAST()ing) - as the server refuses to send anything that might possibly be converted to UTF-8 to any client using protocol version lower than 7.
如果您使用的是 TDS 协议版本 7 或更高版本，则所有通过线路的通信都将转换为 UCS2。服务器将从 UCS2 转换为表或列排序规则设置的任何内容，除非列是 nvarchar 或 ntext。您可以将 UTF-8 存储到常规 varchar 或文本中，您只需要使用低于 7 的 TDS 协议版本，例如 6.0 或 4.2。这种方法的唯一缺点是你不能查询任何 nvarchar、ntext 或 sys.* 表（我认为你也不能做任何 CAST()ing）——因为服务器拒绝发送任何可能被转换为UTF-8 到任何使用低于 7 的协议版本的客户端。
It is not possible to avoid converting character sets when using TDS protocol version 7 or higher (roughly equivalent to MSSQL 2005 or newer).
使用 TDS 协议版本 7 或更高版本（大致相当于 MSSQL 2005 或更新版本）时，无法避免转换字符集。
回答by Attila Fulop
If ini_set('mssql.charset', 'UTF-8');doesn't help AND you don't have root access to modify the system wide freetds.conffile, here's what you can do:
如果ini_set('mssql.charset', 'UTF-8');没有帮助，并且您没有 root 访问权限来修改系统范围的freetds.conf文件，您可以执行以下操作：
1.Set up /your/local/freetds.conffile:
1.设置 /your/local/freetds.conf文件：
[sqlservername]
host=192.168.0.56
port=1433
tds version=7.0
client charset=UTF-8
2.Make sure your connection DSN is using the servername, not the IP:
2.确保您的连接 DSN 使用的是服务器名，而不是 IP：
'dsn' => 'dblib:host=sqlservername;dbname=yourdb
3.Make FreeTDS to use your local freetds.conf file as an unprivileged user from php script via env variables:
3.使 FreeTDS 以非特权用户身份使用本地 freetds.conf 文件，通过 env 变量从 php 脚本：
putenv('FREETDSCONF=/your/local/freetds.conf');
回答by Donovan P
In my case, It worked after I added the "CharacterSet" parameters into sqlsrv_connect()connection's option.
就我而言，它在我将“CharacterSet”参数添加到sqlsrv_connect()连接选项中后起作用。
$connectionInfo = array(
"Database"=>$DBNAME,
"ConnectionPooling"=>0,
"CharacterSet"=>"UTF-8"
);
$LAST_CONNECTION = sqlsrv_connect($DBSERVER, $connectionInfo);
See documentation here : https://docs.microsoft.com/en-us/sql/connect/php/connection-options?view=sql-server-2017
请参阅此处的文档：https: //docs.microsoft.com/en-us/sql/connect/php/connection-options?view=sql-server-2017
回答by Bud Damyanov
I did not notice someone to mention another way of converting results from MSSQL database. The good old iconv()function:
我没有注意到有人提到从 MSSQL 数据库转换结果的另一种方法。好的旧iconv()功能：
iconv (string $in_charset, string $out_charset, string $str): string;
In my case everything else failed to provide meaningful conversion, except this one when getting the results. Of course, this is done inside the loop of parsing the results of the query - from CP1251 to UTF-8:
在我的情况下，其他一切都未能提供有意义的转换，除了在获得结果时的这一转换。当然，这是在解析查询结果的循环内完成的——从 CP1251 到 UTF-8：
foreach ($records as $row=>$col) {
$array[$row]['StatusName'] = iconv ('CP1251', 'UTF-8' , $records[$row]['StatusName']);
}
Ugly, but it works.
丑陋，但它的工作原理。
回答by bohdanbobrowski
For me editing this file:
/etc/freetds/freetds.conf
...and changing/setting 'tds version' parameter to '7.0' helped. Edit your freetds.conf and try to change this parameter for your server configuration (or global).
It will work even without apache restart.
对我来说编辑这个文件：
/etc/freetds/freetds.conf
...并将'tds version'参数更改/设置为'7.0'有帮助。编辑您的 freetds.conf 并尝试为您的服务器配置（或全局）更改此参数。
即使没有 apache 重启，它也能工作。
回答by Jacob Hume
I've had luck in a similar situation (using a PDO ODBD connection) using the following code to convert the encoding before printing output:
我在类似的情况下（使用 PDO ODBD 连接）很幸运，使用以下代码在打印输出之前转换编码：
$data = mb_convert_encoding($data, 'ISO-8859-1', 'windows-1252');
I had to manually set the source encoding, because it was erroneously being reported as 'ISO-8859-1' by mb_detect_encoding().
我不得不手动设置源编码，因为它被错误地报告为“ISO-8859-1” mb_detect_encoding()。
My data was also being stored in the database by another application, so I might be in a unique situation, although I hope it helps!
我的数据也被另一个应用程序存储在数据库中，所以我可能处于一种独特的情况，尽管我希望它有所帮助！
回答by Sandro Gr?i?i?
Can't you just convert your tables to your application encoding? Or use utf-8 in both?
您不能将您的表格转换为您的应用程序编码吗？或者两者都使用utf-8？
I don't know whether MSSQL supports table-level encodings, though.
不过，我不知道 MSSQL 是否支持表级编码。
Also, try the MB (multibyte) string functions, if the above fails.
另外，如果上述方法失败，请尝试 MB（多字节）字符串函数。
