SQL Serverにおける日本語文字列の取り扱いで躓いた話

公開日:2018-12-20
最終更新:2018-12-20

発生した問題

varchar型のカラムに日本語文字列を格納すると文字化けする

  • ASP.NETCore(とそれに付随するパッケージ、EFCore)の標準文字セットがUTF-8
  • SQLServerのvarchar型の文字セットがShift-JIS

この差によって文字化けが発生すると推測した。

検討した対応策

  1. SQLServerのデータ型をUTF-8対応にする
  2. EFCoreをShift-JISに対応させる

モデルに値を格納する直前にShift-JISに変換しても、SQLServerとの通信直前にUnicodeに変換されてしまうのか、EFCore以前の処理で対応することは不可能な雰囲気。

また、EFCoreはMicrosoft.AspNetCore.Appメタパッケージの一部で、ASP.NETCoreはクロスプラットフォームであるためか、そもそも対応文字セットとしてShift-JISを含まない。
Shift-JISに対応していないパッケージに含まれるライブラリをShift-JISに対応させることは不可能。

SQLServer側の設定で対処する方針で検討。

具体的な検討

対応策 備考
nvarcharにする 照合順序やエンコードを気にする必要がなくなる。一番簡単。
照合順序を変更する Azureでの動作に懸念材料有り。要調査。
データアクセス後、コンバートをかける 複数のencodeで変換を試したものの、上手いこと値が変換されなかった
EFCoreを使わず、生で接続処理から実装する 品質を担保できないのであまりやりたくないよね

調査結果

根本的な文字化けの原因

SQLServerのLocalDBにおける照合順序がSQL_Latin1_General_CP1_CI_ASになっていることが問題だった。
Databaseの照合順序をJapanese系に変更することで、varcharに日本語文字列を格納した際の文字化けは防ぐことができる。

LocalDBの照合順序を変更する

EFCoreのMigrationでクリエイトテーブル時に直接変更することは難しそうなので、テーブル作成後にSQLクエリを発行して変更する。
基本の変更クエリなどはこちらを参考。

以下Visual Studio 2017で操作することを想定。

  1. SQLServerオブジェクトエクスプローラーを開き、照合順序を変更したいDB上で右クリック、新しいクエリを選択する。
  2. 変更したいDBのフルパスを確認。
    SELECT DB_NAME()  
  3. 右クリック、Executeを選択でクエリ実行。
  4. 照合順序変更のクエリを記述して実行する。
    ALTER DATABASE [dbのフルパス] COLLATE Japanese_XJIS_100_CI_AI_WS  
  5. SQLServerオブジェクトエクスプローラーを更新し、照合順序を変更したDB上で右クリック、プロパティを選択。
  6. プロパティウィンドウ内の照合順序欄が変更した照合順序になっていれば成功。

Azureへの影響

本番環境やステージングでAzureを使うことも多いと思う。
Azureにおいても、自動生成されるDBインスタンスの照合順序はSQL_Latin1_General_CP1_CI_ASがデフォルト。
最初に確認したサイトではデータベースレベルの照合順序変更はサポートされていないとなっていたので、検討段階では要調査ということにしたが、2010年の情報だったのでいささか古すぎた。
現在はちゃんとデータベースレベルでの照合順序変更はサポートされているので、DBインスタンス生成後、テーブル作成前に照合順序を変更すればOK。(テーブル作成後でも変更は可能だが、手順が面倒になる)
変更方法はこちらが参考になる。

照合順序変更に関する補足

テーブルレベル、カラムレベルでの照合順序変更も可能。ただし、照合順序が異なるテーブル、カラム間でデータのコピーやテーブル結合をしようとすると、エラーが多発することになる。
照合順序にまで気を張って開発するのは骨が折れるので、照合順序変更はデータベースレベルで行うのがベターかと思われる。

照合順序変更の恒久性について

LocalDBを切断して再接続した場合でも、データベースの照合順序は変わっていなかった。
照合順序の変更は起動状態の間維持される一時的な変更ではなく、恒久的なものと考えられる。
なんらかの拍子で変更が初期化されたら別途原因と対策を調査する必要があるなぁと思っている。

まとめ

結局、調査序盤で確認したMSフォーラムの情報に答えが詰まっていた。
なお、ASP.NETCoreでSJISが扱えないという問題は、FileI/O機能の実装時などで問題になりそうなので留意しておくのもいいかもしれない。

記事が少しでもいいなと思ったらクラップを送ってみよう!
18
+1
@showyleeの技術ブログ

よく一緒に読まれている記事

0件のコメント

ブログ開設 or ログイン してコメントを送ってみよう
目次をみる

技術ブログをはじめよう

Qrunch(クランチ)は、ITエンジニアリングに携わる全ての人のための技術ブログプラットフォームです。

技術ブログを開設する

Qrunchでアウトプットをはじめよう

Qrunch(クランチ)は、ITエンジニアリングに携わる全ての人のための技術ブログプラットフォームです。

Markdownで書ける

ログ機能でアウトプットを加速

デザインのカスタマイズが可能

技術ブログ開設

ここから先はアカウント(ブログ)開設が必要です

英数字4文字以上
.qrunch.io
英数字6文字以上
ログインする