問題描述
如果要將 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，是否可以讓它們一起工作?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！