Edited at

freetds + SQLServer で絵文字をSELECT, INSERTする

More than 1 year has passed since last update.


今回起こった現象について

こんな現象が起こったので、

・nvarcharのカラムに対して絵文字をINSERTするとエラーが出てないのにデータは登録されない。

・テーブルに登録済みの絵文字データをSELECTで取得してPHPで出力すると、??と表示され文字化けしてしまう。


実行環境

・PHP: 5.6.27

・CentOS: 6.8

・freetds: v0.91

・Microsoft SQL Azure(Azure上で動くSQL Server) 12.0.2000.8

・データベースの照合順序

Japanese_XJIS_100_CI_AI

・テーブル

SQL Serverは下記のテーブル"Comments"にデータ登録、データ取得の操作を実行する。

・ID int PK

・Text nvarchar(1024)


データを取得する時

カラム"Text"のデータをバイナリデータとして取得する。

SELECT

ID,
CONVERT(varbinary(MAX), Text)
FROM
Comments

次にPHP側で、バイナリデータをUTF-16LE→UTF-8に変換する処理を実行する。

foreach ($result as $row)

{
// 文字コードを"UTF-16LE"から"UTF-8"の変換
$text = mb_convert_encoding($row['Text'], 'UTF-8', 'UTF-16LE');
}


データを登録する時

はじめにPHPで文字列をbinary文字列に変換する。

mb_convert_encodingで、UTF-8からUTF-16LEに変換をかけることで、4バイト文字のデータを保持する。

$text = mb_convert_encoding($text, 'UTF-16LE','UTF-8');

$text = bin2hex($text);
$text = '0x' . $text;

実際にデータを登録する時は下記のSQLを実行する。

カラム"Text"はvarchar型だけど、varbinaryのデータをINSERTすると

INSERT

Comments(ID, Text)
VALUES
 (1, CONVERT(varbinary(MAX), N'{$textのバイナリ文字列}', 1));