none
SQL Server へ格納しているデータの文字コードについて RRS feed

  • 質問

  • お世話になります。

    SQL Serverへデータを格納する際の文字コードについてお教えいただきたく思います。

    SQL Serverのバージョンは日本語版2008です。

    ※Webで調べると、日本語版のSQL Serverは文字コードSJISでデータを格納するという情報がありましたが、

     確認のために質問させていただいております。

    PerlのCGIを用いたWebアプリケーションがあります。

    ブラウザからの入力データ(日本語)を元にPerlがSQLを作成してSQL Serverへクエリを実行し、

    ブラウザからの入力データをSQL Serverへ格納しています。格納先はvarchar型もしくはtext型カラムです。

    Perlでは日本語をUTF-8で扱っています。

    InsertのSQLに記載するデータもUTF-8です。

    この場合、SQL Server(varchar型、text型のカラム)へはSJISでデータが格納されるのでしょうか。

    もしそうであれば、SQL ServerがUTF-8の文字列をSJISに変換して格納するということでしょうか。

    以上、何卒よろしくお願い致します。

    2014年4月21日 10:57

回答

  • あぁ、なるほど、一つ合点が行きました。

    「TDS で Unicode を使う」というのは、TDS のプロトコル上でテキストとして扱われている個所に関してのみです。
    TDS でクライアントからサーバーへ送信する SQL はテキスト扱いなので Unicode ですが、サーバーからクライアントへ送信するデータはバイナリ扱いなので Unicode とは限りません(型と照合順序に依ります)。
    TDS のドキュメントの冒頭で「Unicode を使う」と書かれているので、特に補足せずそのまま引用してしまいましたが、上記のような前提込みで理解してください。
    そのため、前述のような仕組みになります。
    TDS 全体の話になってしまうと公式リファレンス(前述の MSDN ライブラリ)や実装例(FreeTDS)を確認して頂く方が間違いありませんし、分量もここで収まる話では無くなってしまいますので、かなり省いて記述している点はご理解ください。


    MCITP(Database Developer/Database Administrator)

    • 回答としてマーク k-k2 2014年5月1日 5:12
    2014年4月30日 5:39

すべての返信

  • 一口に「SJIS」といっても、実装によって細かな違いがあったりしますが、それはさておき。

    ※Webで調べると、日本語版のSQL Serverは文字コードSJISでデータを格納するという情報がありましたが、

    前後の文章も確認しておきたいので、その参照元の具体的な URL を公開していただけないでしょうか。
    もしもリンクを含んだ投稿ができないようでしたら、該当ページにのサイト名や検索キーワードなどを教えて頂けると助かります。

    SJISでデータが格納されるのでしょうか。

    データベースを構築した際の『照合順序』に依存しますが、結果的には「コードページ 932」が設定されている場合が多いかと思います。(Unicode データについては、内部的にはUCS-2 のエンコード体系で扱われるそうです)

    照合順序」に関しては、この記事 この記事などを参照してみてください。

    格納先はvarchar型もしくはtext型カラムです。

    通信元が UTF-8 主体であることがわかっているのであれば、Unicode 用の型である nchar、nvarchar、ntext を使うという選択肢もあります。ただし、これらの型に文字列を渡す場合には、Nプレフィックスを付けることも忘れないようにしてください。忘れると文字化けの要因となりえます。

    SQL ServerがUTF-8の文字列をSJISに変換して格納するということでしょうか。

    SQL Server 自身の変換処理の他、OS本体の変換処理やミドルウェアなどで、変換処理が入ることもありますね。これらの処理は通常、自動的に行われますが、Perlに関しては不勉強のため、当方では情報を持ち合わせていません。

    2014年4月21日 12:47
  • どうも魔界の仮面弁士さんの投稿は質問に答えられていないように見受けられたので。

    格納先はvarchar型もしくはtext型カラムです。

    Perlでは日本語をUTF-8で扱っています。

    InsertのSQLに記載するデータもUTF-8です。

    この場合、SQL Server(varchar型、text型のカラム)へはSJISでデータが格納されるのでしょうか。

    もしそうであれば、SQL ServerがUTF-8の文字列をSJISに変換して格納するということでしょうか。

    SQL Server内で、varchar、text型はSJIS(1バイト文字orマルチバイト文字)で格納されていて、nvarchar、ntext型はUCS-2(ワイド文字)で格納されています。

    ただしこのことはアプリケーションには関係なく、SQL Server用のクライアントライブラリがどうなっているか、です。つまりPerlモジュールがどうなっているかであり、そこがUTF-8→SJIS変換してくれているのであればそれまでです。特にPerlはエンコーディングについていろいろな経緯がありややこしいのでPerlコミュニティに質問してみた方がいいと思います。

    2014年4月21日 22:45
  • 今から約10年ほど前に書いた記事ですが、参考になるかと思いましたので、私のブログに載せてみました。

    SQL ServerのNプレフィックス
    http://d.hatena.ne.jp/trapemiya/20140422


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2014年4月22日 1:11
  • 前回の回答で触れたように、データベース構築時の設定によっては、
    DB側が CP932(SJIS) 固定とは限らず、CP1252(Latin-1)などの
    可能性もありえなくは無いのですが、とりあえず今回は SJIS 系で
    設定されている前提のもと、どうしても文字化けが解消できない場合の
    最終手段」について紹介しておきます。

    今回必要になるとは思いませんが、一応参考情報として。


    たとえば Japanese_BIN 照合順序の SQL Server 上に、下記のテーブルが用意されているとします。

    CREATE TABLE TEST
    ( ID int IDENTITY (1000, 1) PRIMARY KEY
    , COL1 VARCHAR(10)
    , COL2 NVARCHAR(10))

    ここに、SQLで文字列をセットします。

    INSERT INTO TEST (COL1, COL2) VALUES('あいう', N'あいう')

    これは処理系が SJIS 系や Unicode 系を採用している場合、大抵の場合正常に動作するはずです。

    しかし、何らかの事情で、予期せぬ文字化けを生じてしまうような場合には、変換処理を自前で代替し、変換結果をバイナリリテラルで送出することで強引に回避する手法があります。具体的には、このような構文です。

    INSERT INTO TEST (COL1, COL2) VALUES(0x82A082A282A4, 0x423044304630)

    取り出す場合も、SELECT CAST(COL AS BINARY) FROM TEST などとして、文字列ではなくバイナリとして取り扱います。

    流石に、人間がそのまま読み書きするには無理のある表現ですし、決して手放しでお奨めできるような方法では無いのですが、これならば変換処理を自分で制御することが可能なため、SQL Server に文字化けしたデータが予期せず格納されてしまう可能性を排除することができます。

    この場合、フィールドを BINARY 型とする方法もありますが、あえて通常の文字列型としておくことで、普段は通常の 'あいう' 形式で取り扱い、どうしても化けてしまう時だけ、局所的に 0x82A082A282A4 形式で置き換えるという併用策も取ることができます。

    たとえば今回の場合、主となる Perl 側が UTF-8 とのことですが、
     UCS-2:「U+005C:\」「U+00A5:¥」
     CP932:「0x5C:\」
    の組み合わせや、
     UCS-2:「U+301C:〜」「U+305E:~」
     CP932:「0x8160:~」
    あるいは、数学記号ルートの
     UCS-2:「U+81E3:√」
     CP932:「0x8795:√」「0x81E3:√」
    などの相互変換時においては、前回紹介した KB170559 の問題などで相互変換に失敗する可能性があるわけですが、バイナリとして取り扱えば、SQL を取り扱う処理系の文字コードに囚われずに、確実に渡すことが可能です。

    -- VARCHAR 列に、CP932で二重定義されている2区のルート記号と13区のルート記号を区別してセットする
    INSERT INTO TEST (COL1) VALUES(0x8795)  -- NEC特殊文字
    INSERT INTO TEST (COL1) VALUES(0x81E3)  -- JIS X 0208
    
    -- CP932で三重定義されているBECAUSE記号∵(0x81E6, 0x879A, 0xFA5B)を区別してセットする
    INSERT INTO TEST (COL1) VALUES(0x81E6)  -- JIS X 0208
    INSERT INTO TEST (COL1) VALUES(0x879A)  -- NEC特殊文字
    INSERT INTO TEST (COL1) VALUES(0xFA5B)  -- IBM特殊非漢字
    
    -- NVARCHAR 列に、U+00A5 円記号と U+005C 逆斜線を区別してセットする
    INSERT INTO TEST (COL2) VALUES(0xA500)  -- U+005C
    INSERT INTO TEST (COL2) VALUES(0x5C00)  -- U+005C
    
    -- NVARCHAR 列に、U+301C 波ダッシュと U+FF5E 全角チルダを区別してセットする
    INSERT INTO TEST (COL2) VALUES(0x1C30)  -- U+301C
    INSERT INTO TEST (COL2) VALUES(0x5EFF)  -- U+FF5E
    2014年4月22日 2:35
  • 関連する話はほぼ全て出ていると思いますが、もう少し補足説明があった方が良いかなと思うところがあったので、横から失礼します。

    先ず何点か前提知識を。

    照合順序それぞれに対してコードページが決まっているので、照合順序が決まるとコードページが決まります。
    照合順序のコードページは COLLATIONPROPERTY で確認できます。
    http://technet.microsoft.com/ja-jp/library/ms190305(v=sql.100).aspx
    SELECT COLLATIONPROPERTY('Japanese_XJIS_100_CI_AS', 'CodePage')
    SQL Server ではコードページは直接設定できず、照合順序の設定を通して間接的に設定します。
    ですので、コードページの話と照合順序の話が混在することになります。

    照合順序はデータベースからカラム、あるいは COLLATE 句で演算ごとに設定できますが、データベースレベルでのみ設定していることがほとんどだと思います。
    この場合、各カラムや演算ではより上位(データベースレベルなど)の照合順序を継承しています。
    ですのでカラムに対して照合順序を明示的に設定していないとしても照合順序が暗黙的に決まっています。
    以下カラムの照合順序という表現をした場合には、明示ないし暗黙の照合順序と理解してください。
    実際、明示的に指定していない列でも sys.columns の該当の列に関する情報を確認すると collation_name 列に値が設定されているのが確認できます。

    SQL Server はバージョンによって対応している Unicode のバージョンが異なります。
    SQL Server 2008 で名前に「100」が含まれている照合順序を使用している場合は、Unicode 5.0 で処理されます。
    Unicode で文字化けの話をする場合は、厳密には Unicode のバージョンの考慮も必要ですが、煩雑であり今回の主題(SJIS 関連)からは外れますので、以下では割愛しています。

    SJISは色々ありますが、Microsoft の製品内部での話ですので、以下で出てくる SJIS は 全て Windows-31J (MS932) となります。
    今回のケースでは、SJIS の種類の違いの問題は発生しませんので、以下では割愛しています。

    text/ntext 型は既に非推奨ですので以下では言及しませんが、今回の話の範囲では text は varchar、ntext は nvarchar と同様です。

    SQL Server の内部処理は非公開の個所もありますので、以下では一部私の推測も含まれています。
    こればっかりは非公開である以上致し方ないところがありますので、その点あしからずご了承ください。

    以上を前提として、データの流れの順に整理を。

    今回は Web アプリケーションの側は UTF-8 で揃っているので割愛します。
    Perl 内部でどのようになっているかの影響は受けますが、今回はそこは本題では無いようですので、同様に割愛します。

    Web アプリケーションと SQL Server の間でのデータの通信は、ODBC ドライバなどが Tabular Data Stream(TDS) で通信を行うことになります。
    ここで TDS は Unicode(UCS-2) と定められています。
    http://msdn.microsoft.com/en-us/library/dd358013.aspx
    ですので、ODBC ドライバなどが SQL Server に送信する SQL は全て Unicode のデータです。
    実際、パケットキャプチャなどで確認すると Unicode であることが確認できます。
    ODBC ドライバがどのような変換を行っているかは ODBC ドライバ次第ですので、そちらの仕様を確認してください。
    FreeTDS といったライブラリを直接使用している場合は、その個所のプログラム次第です。

    SQL Server に SQL が届くと、SQL 内の文字列定数は必要に応じて文字コードの変換が行われます。
    N プレフィックスが付いている文字列定数は変換されずそのまま処理されます。
    N プレフィックスが付いていない文字列定数はデータベースの既定の照合順序に対応するコードページに変換されます。

    その後、処理の過程で関連するカラムの型や関数、照合順序(COLLATE 句)などに従って適宜型変換(Unicode と非 Unicode 間の変換)が行われます。
    例えば REPLACE 関数は引数の 1 つ以上が nvarchar の場合は nvarchar を返し、それ以外は varchar を返すという動作をします。
    あるいは varchar と nvarchar のデータの比較演算を行えば、型変換で型を揃えてから比較が行われます。

    特定のカラムへのデータの格納という点で考えると、カラムの型と照合順序に依ります。
    カラムの型が nvarchar/nchar という Unicode の型場合、データが Unicode であればそのまま、非 Unicode であれば Unicode に変換して格納されます。
    カラムの型が varchar/char のような非 Unicode 型の場合、データが Unicode であればカラムの照合順序に対応するコードページに変換して、非 Unicode であってもコードページが異なれば変換して、コードページが一致していれば変換せずに格納されます。

    細かい話は他にも色々ありますが、概要としては以上となります。
    ということで、そもそもの質問を再確認しておくと、以下のようになります。

    >この場合、SQL Server(varchar型、text型のカラム)へはSJISでデータが格納されるのでしょうか。
    概ね Yes となります。
    そのカラムの照合順序に応じたコードページで格納されるという方が厳密ですが、日本語のフォーラムでは日本語の照合順序を前提としても妥当かと思います。

    >もしそうであれば、SQL ServerがUTF-8の文字列をSJISに変換して格納するということでしょうか。
    概ね Yes となります。
    SQL Server が受け取った時点では UCS-2 ですので、UCS-2 から SJIS への変換という方が厳密ですが、UTF-8 と UCS-2 の違いはこの場合は瑣末かと思います。

    ・・・長いですね。
    このあたり、わんくま同盟さん辺りなら詳しい方がいらっしゃいそうなのですが、探した限りでは特にまとまった情報が見当たらなかったのでこの場を借りてまとめてみました。
    ただ、通常は Unicode 系でフロントからバックエンドまで統一すれば問題が起きませんので、それがお勧めです。
    実際、この辺りは知っていても何も仕事に結びつきません。。。

    MCITP(Database Developer/Database Administrator)

    2014年4月23日 1:28
  • ちなみに、UCS-2の文字コードページについてですが、「𠮟」(口偏に七)の様に存在しない文字も有ります。

    これらの文字は格納は出来ますが、WHERE条件が正しく動作しません。
    ※使った場合下記の様になります。

    全然関係無いデータが全て取得されています。

    この時の統計情報は下記です。

    述語欄を見て頂くと判る通り、「𠮟」が「??」となって認識出来ていません。
    ※他のUnicode文字は正しく処理されます。(「㊞」等)

    ・・・話しが逸れますが、他のコードページに対してUCS-2を選定した理由(優位性)ってあるのでしょうか。



    • 編集済み aviator__ 2014年4月23日 7:02 image調整
    2014年4月23日 6:58
  • >・・・話しが逸れますが、他のコードページに対してUCS-2を選定した理由(優位性)ってあるのでしょうか。
    TDS の話であれば、特に理由について明記された文書を見たことはありません。
    ただ、TDS が最初に定義されたのは Sybase の時代ですので、例えばサロゲートペアなどといった概念がまだありませんでした。
    ですので個人的には、その時点では文字列処理が簡潔に構築でき、且つ全ての文字を表現できる汎用性の高さがある、という 2 点から妥当と判断された、ということではないかと思います。

    Windows 上で動作するようになった SQL Server 4.21 であっても 1993 年、一方で Unicode で 16 ビットから拡張すると決めた Unicode 2.0 が 1996 年のようですので、まぁ歴史的理由ということだと思います。


    MCITP(Database Developer/Database Administrator)

    2014年4月23日 7:58
  • 丁寧にありがとうございます。
    せめてUTF-8とかになるといいなぁ。。。

    ってここに書く事じゃないですが。

    2014年4月23日 9:29
  • k-k2 さん、こんにちは。
    フォーラム オペレーターの星 睦美です。

    フォーラムのユーザーからの回答を確認いただいて、もし回答の内容にさらに質問したい場合には返信をお願いします。
    役立つ回答には投稿者から[回答としてマーク] いただけると今後もユーザー同士の情報交換がより活発になると思います。
    よろしくお願いします。

    フォーラム オペレーター 星 睦美 - MSDN Community Support

    2014年4月24日 1:55
  • 質問者です。みなさんありがとうございます。

    >引退エンジニアさんへ

    ご丁寧な回答誠にありがとうございます。

    SQL Serverへのデータ格納時には、WebアプリケーションからUTF8でODBC経由でSQL Serverへデータが送信され、

    SQL ServerがデータをSJISに変換して格納するということがわかりました。

    では、SELECT文によってSQL Serverからデータを抽出する際の動作はどのようになるのでしょうか。

    SELECT文を受け取ると、SQL ServerがSJISのデータをUTF8に変換してからODBC経由でWebアプリケーションにデータを

    渡すという動作になるのでしょうか。

    度々すみませんが、よろしくお願い致します。



    • 編集済み k-k2 2014年4月28日 4:56
    2014年4月28日 2:36
  • 初めに、何点か前提を。

    厳密には UTF8 ではないのですが、Unicode 系であることだけ分かれば十分なので、以下では Unicode と表記します。
    UTF8 や UTF16 や UCS-2 等々の総称と捉えてください。

    SQL Server の内部での動作は先に述べた処理と同様ですので、以下では SQL Server とODBC ドライバの通信のところからのみ触れます。

    とはいうものの、実はこの部分は単純です。
    最初に結論から言ってしまえば、文字コードの変換は行わず、そのままバイナリとして送信します。

    SELECT した結果データは ROW データとして送信されますが、この際の実データ部分(Data)は文字データでは無くバイナリ(バイト配列/TYPE_VARBYTE)で送信されます。
    http://msdn.microsoft.com/en-us/library/dd357254.aspx
    また、合わせて列定義(COLMETADATA)も送信されますが、ここに型情報(TYPE_INFO)が含まれています。
    http://msdn.microsoft.com/en-us/library/dd357363.aspx
    この型情報(TYPE_INFO)には照合順序の情報(COLLATION)が含まれており、これにより文字コードを知ることが出来ます。
    http://msdn.microsoft.com/en-us/library/dd358284.aspx
    このため、ODBC ドライバ側はバイト配列の文字コードを知ることができます。

    実際パケットキャプチャすると
    SELECT 'あ'
    では SJIS の照合順序(Japanese_CI_AS等)の環境では"82A0"のバイト配列が返ってきていることが確認できる一方、
    SELECT N'あ'
    では同様の環境でも"4230"(3042 がリトルエンディアンのため逆になっていると思われます)のバイト配列が返ってきていることができます。

    このデータを ODBC ドライバが受け取った後の処理は ODBC ドライバの実装次第です。

    このあたり、もしより詳細を知りたい場合は MS-TDS のドキュメントか、FreeTDS のソースコードを確認されるのが良いと思います・・・が、今のご時世、Front から DB まで全て Unicode で統一すれば何も問題が無いので、もはや趣味の領域ですかね・・・。


    MCITP(Database Developer/Database Administrator)

    2014年4月28日 5:35
  • 質問者です。

    引退エンジニアさん、ありがとうございます。

    つまり、SQL Serverへのデータ格納時(INSERT/UPDATE)とSQL Serverからのデータ抽出時(SELECT)では以下の

    動作になるという理解で正しいでしょうか。

    <SQL Serverへのデータ格納時(INSERT/UPDATE)>

    Webアプリケーション(Unicode)→ODBCドライバ(Unicode)→SQL Server(SJIS) ※SQL ServerがUnicodeからSJISに変換

    <SQL Serverからのデータ抽出時(SELECT)※'N'がなしの場合>

    SQL Server(SJIS)→ODBCドライバ(SJIS or Unicode)※→Webアプリケーション(SJIS or Unicode)

    ※ODBCドライバがSJISからUnicodeに変換するかどうかはODBCドライバの設定/仕様に依存する

    以上よろしくお願い致します。

    2014年4月28日 7:11
  • SJIS での char/varchar 型に限った話であれば間違ってはいないと思いますが、コンポーネント間の文字コードに着目した方が分かりやすいかと思います。
    ちょっと書き換えてみると以下のような感じになります。

    以下、Unicode で処理している Web アプリケーションと、char/varchar 型で SJIS の照合順序を使用した型、単純(=型変換を伴わない)な SELECT 文を前提とした場合です。

    <SQL Server へのデータ格納時(INSERT/UPDATE)>
    Web アプリケーション (Unicode)
     ↓ Unicode(厳密には Web アプリケーションの実装依存ですが、まぁ Unicode ですよね)
    ODBC ドライバ
     ↓ Unicode
    SQL Server ※適宜文字コードを変換
     ↓ SJIS で書き込み
    Storage (char/varchar 型 + SJIS の照合順序)

    <SQL Server からのデータ抽出時(SELECT)>
    Storage (char/varchar 型 + SJIS の照合順序)
     ↓ SJIS として読み込み
    SQL Server
     ↓ SJIS
    ODBC ドライバ
     ↓ 実装次第
    Web アプリケーション (Unicode)

    なんだか元々の質問からだんだん離れてきてしまっていますね・・・ちょっと妙な方向に私が誘導してしまいましたかね。


    MCITP(Database Developer/Database Administrator)

    2014年4月28日 7:56
  • 引退エンジニアさん

    質問者です。

    ご回答ありがとうございます。

    今、議題に挙がっている部分が私が最も知りたい部分です。

    申し訳ございませんが、一点確認させていただけますでしょうか。

    SQL ServerとODBCドライバ間の通信はTDSプロトコルで行うという認識です。

    TDSで扱うデータはUnicodeですので、SELECTした結果をODBCドライバに返す時もTDSを用いてデータはUnicodeであるかと

    思ったのですが、これは違うようですね。

    今までの情報をまとめると、以下になります。

    ・ODBCドライバからSQL Serverへデータを送信する際(INSERTやUPDATE)はTDSを使うので、データはUnicodeで送信される。

    ・ODBCドライバがSQL Serverからデータを受信する際(SELECTの結果を取得する時)はデータはSJISである。

     ※ODBCドライバがSELECTの結果を受信する時はTDSプロトコルを使わないのでしょうか。

      (データ送信の方向が異なると、プロトコルが異なる?)

    以上よろしくお願い致します。


    • 編集済み k-k2 2014年5月1日 5:10
    2014年4月30日 4:45
  • あぁ、なるほど、一つ合点が行きました。

    「TDS で Unicode を使う」というのは、TDS のプロトコル上でテキストとして扱われている個所に関してのみです。
    TDS でクライアントからサーバーへ送信する SQL はテキスト扱いなので Unicode ですが、サーバーからクライアントへ送信するデータはバイナリ扱いなので Unicode とは限りません(型と照合順序に依ります)。
    TDS のドキュメントの冒頭で「Unicode を使う」と書かれているので、特に補足せずそのまま引用してしまいましたが、上記のような前提込みで理解してください。
    そのため、前述のような仕組みになります。
    TDS 全体の話になってしまうと公式リファレンス(前述の MSDN ライブラリ)や実装例(FreeTDS)を確認して頂く方が間違いありませんし、分量もここで収まる話では無くなってしまいますので、かなり省いて記述している点はご理解ください。


    MCITP(Database Developer/Database Administrator)

    • 回答としてマーク k-k2 2014年5月1日 5:12
    2014年4月30日 5:39
  • 引退エンジニアさん

    質問者です。

    的確なご回答ありがとうございました。

    回答としてマークさせていただきます。

    今後ともよろしくお願い致します。

    2014年5月1日 5:11