PHP、ORM、MSSQL 和 Unicode，是否可以让它们一起工作? [英] PHP, ORM, MSSQL and Unicode, is it possible to make these work together?
问题描述
如果要将 unicode 数据保存到 MSSQL，则需要保存到列类型 nvarchar(等)中，并且必须在 SQL STRING LITERALS 前加上大写 N.如果您使用的是准备好的语句，则不需要前缀与 N 的值.
If you are saving unicode data to MSSQL you need to save into a column type nvarchar (et. al) and you must prefix SQL STRING LITERALS with capital N. If you are using prepared statements, then you do not need to prefix values with N.
插入 tbl (col) 值 (N'hello')
insert into tbl (col) values (N'hello')
如果您使用的是像 ZF DB 这样的 ORM，那么您将使用 PDO 连接到 MSSQL(或者您正在 Windows 上部署，在这种情况下，您使用的是 SQLSRV 或 PDO_SQLSRV，一切都会正常运行，而我的问题没有没有意义了).
If you are using an ORM like ZF DB, then you are using PDO to conect to MSSQL (or you are deploying on Windows, in which case you're using SQLSRV or PDO_SQLSRV and everything will work and my question doesn't make sense anymore).
如果您在 Linux 上使用连接到 mssql(sybase、dblib 等)的任何 PDO 变体，那么您将无法获得真正的准备好的语句，只能进行模拟.
If you are using any PDO variant that connects to mssql (sybase, dblib, etc) on Linux then you do not get real prepared statements, only emulated.
如果您在线路级别模拟了准备好的语句，则 SQL 将完全仅用字符串文字写出，这意味着您必须在任何潜在的 unicode 值前加上 N.
If you have emulated prepared statements, at the wire level, the SQL is completely written out with only string literals and this means that you must prefix any potential unicode value with N.
- 是否可以使用 ZF 自动在任何字符值前加上 N?
- 是否有任何 ORM/TableGateway 库允许这种值操作?
- 有什么方法可以从 Linux 启用服务器端准备好的语句?
- 是否有人真正从 Linux 代码运行生产 PHP 并针对 MSSQL 运行它?(如果是，您如何处理 Unicode?)
我可以让 unicode 从 Linux 保存到 MSSQL，但是将 FreeTDS 协议升级到 7/8 似乎破坏了所有框架/库/orms，因为绝对需要改变带有 N 前缀的普通 SQL.我不确定 Internet 上声称升级到协议版本 7/8 解决了他们的 unicode 问题的任何人实际上除了以不可移植的方式手写每个 SQL 语句之外，还做了什么.看来，继续使用 Freetds 4.2 是处理 Unicode/UTF-8 和 mssql 的最佳方式.
I can get unicode to save to MSSQL from Linux, but upgrading FreeTDS protocol to 7/8 seems to break all frameworks/libraries/orms because of the absolute requirement to alter what would be normal SQL with the N prefix. I'm not sure how anyone on the Internet who claims that upgrading to protocol version 7/8 fixed their unicode problems actually does anything besides hand write every SQL statement in a non-portable way. It seems that staying on Freetds 4.2 is the best way to deal with Unicode/UTF-8 and mssql.
推荐答案
更新: 驱动程序不再处于预览状态.MS为现在发布的版本提供了官方说明:https://www.microsoft.com/en-us/sql-server/developer-get-started/php-ubuntu
Update: The driver is no longer in preview. MS has provided official instructions for the now released version: https://www.microsoft.com/en-us/sql-server/developer-get-started/php-ubuntu
以下说明现已过时，因为 MS 已取消预览驱动程序下载.
The instructions below are now out of date as MS has pulled the preview driver download.
嗯，有微软提供的 ODBC 驱动程序.这应该在这方面提供适当的行为.有关我如何测试其行为(初步方式)的信息，请参阅博文末尾.它已针对 Azure SQL 数据库 V12 进行了测试.
Well, there is the ODBC driver provided by Microsoft. That should provide proper behavior in this regard. See the end of the post for how I tested its behavior (in a preliminary manner). It was tested against an Azure SQL Database V12.
如何在 Ubuntu 16.04 上安装 Microsoft SQL ODBC 驱动程序
这是在基于 Canonical 提供的 Ubuntu 16.04 Azure 映像的全新 Ubuntu 16.04 Azure 实例上进行测试的.登录后，我使用sudo -i切换到root用户，然后:
This was tested on the fresh Ubuntu 16.04 Azure instance that was based on the Ubuntu 16.04 Azure image provided by Canonical. After logging in, I switched to the root user using sudo -i, then:
apt-get update
apt-get -y install atool make build-essential libc6 libkrb5-3 libgss3 e2fsprogs openssl equivs
wget https://download.microsoft.com/download/2/E/5/2E58F097-805C-4AB8-9FC6-71288AB4409D/msodbcsql-13.0.0.0.tar.gz
atool -x msodbcsql-13.0.0.0.tar.gz
rm msodbcsql-13.0.0.0.tar.gz
pushd msodbcsql-13.0.0.0/
./build_dm.sh --accept-warning | tee build_dm_result.txt
command=$(cat build_dm_result.txt | grep "Run the command" | cut -d"'" -f2)
rm build_dm_result.txt
sh -c "$command"
popd
echo "/usr/lib64" > /etc/ld.so.conf.d/microsoft-lib64.conf
ldconfig
pushd msodbcsql-13.0.0.0/
./install.sh install --accept-license
测试
将以下命令中的服务器和凭据替换为您自己的.
Replace the server and the credentials in the following command with your own.
sqlcmd -S somedatabase.database.windows.net -U someuser -P somepassword
此时您应该能够发出 SQL 命令.好的，让我们用 php 让它工作.
You should be able to issue SQL commands at this point. Okay, let's get it working with php.
与 php 一起使用
我们必须确保未安装 libodbc1 包并且不会安装它，因为 php 会使用它而不是我们自定义编译的包，这会导致编码问题.
We must make sure the libodbc1 package is not installed and that it will not get installed, as that would used by php instead of our custom complied one, and that would lead to encoding issues.
cat > libodbc1<<EOL
Section: misc
Priority: optional
Standards-Version: 3.9.2
Package: libodbc1
Version: 9999
Description: fake pkg, so that we satisfy the dependency of php7-odbc, so that we can keep our custom built libodbc
EOL
equivs-build libodbc1
dpkg -i libodbc1_9999_all.deb
rm libodbc1
rm libodbc1_9999_all.deb
apt-get install php7.0-odbc php7.0-cli
此时，您应该可以将其用作 ODBC 驱动程序.
At this point, you should have it available as an ODBC Driver.
测试其行为
用UTF-8编码创建一个php文件test.php，内容如下.将连接字符串中的服务器、数据库和凭据替换为您自己的.
Create a php file, test.php with UTF-8 encoding, and with the following content. Replace the server, the database and the credentials in the connection string with your own.
<?php
$pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=tcp:somedatabase.database.windows.net,1433;Database=somedatabase;Uid=someuser@somedatabase;Pwd=somepassword;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;');
$str = 'Árvíztűrő tükörfúrógép, and... 你好，世界';
$pdo->prepare("DROP TABLE test")->execute();
$pdo->prepare("CREATE TABLE test(a NVARCHAR(MAX))")->execute();
$stmt = $pdo->prepare("INSERT INTO test VALUES(?)");
$stmt->bindParam(1, $str);
$stmt->execute();
$stmt = $pdo->prepare("SELECT * FROM test");
$stmt->execute();
$data = $stmt->fetchall();
var_dump($data[0][0]==$str); //Returns true
$stmt = $pdo->prepare("SELECT * FROM test WHERE a=?");
$stmt->bindParam(1, $str);
$stmt->execute();
$data = $stmt->fetchall();
var_dump($data[0][0]==$str); //Returns true
使用 php -f test.php 运行它表明我们得到了没有任何损坏的字符串.此外，该字符串从 SQL Server Management Studio 看起来也不错.我在 Azure 门户的 Performance Insight 页面上观察到以下查询:(@P1 nvarchar(max))INSERT INTO test VALUES(@P1)，所以显然使用了准备好的语句，所以我假设它可以处理你的(和我的)场景.
Running this with php -f test.php shows that we get back the string without any corruption. Also, the string looks good from SQL Server Management Studio too. I observed following query on the Performance Insight page of the Azure Portal: (@P1 nvarchar(max))INSERT INTO test VALUES(@P1), so prepared statements were obviously used, so I assume that it could handle your (and my) scenario.
(这篇文章在尝试让它工作时有很大帮助:http://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/ 谢谢鲍里斯！)
(This post was of great help while trying to get this to work: http://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/ Thanks boris!)
这篇关于PHP、ORM、MSSQL 和 Unicode，是否可以让它们一起工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
