PHP，ORM，MSSQL和Unicode，是否可以使它们一起工作?

本文介绍了PHP，ORM，MSSQL和Unicode，是否可以使它们一起工作?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如果要将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之前加上任何char值?
是否存在任何允许这种值操作的ORM/TableGateway库?
是否可以通过Linux启用服务器端准备好的语句?
有人真的从Linux代码运行生产PHP并针对MSSQL运行它吗? (如果是，您如何处理Unicode?)

我可以将unicode从Linux保存到MSSQL，但是将FreeTDS协议升级到7/8似乎破坏了所有框架/库/规范，因为绝对需要更改带有N前缀的普通SQL.我不确定在互联网上声称升级到协议版本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/zh-cn/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已撤消预览驱动程序下载，因此以下说明已过时.

嗯，有Microsoft提供的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驱动程序

这已在新的Ubuntu 16.04 Azure实例上进行了测试，该实例基于Canonical提供的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，是否可以使它们一起工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！