More than 5 years have passed since last update.

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

Last updated at 2018-03-27Posted at 2018-03-27

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

こんな現象が起こったので、
・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));