フリーランス チャレンジ!!

読者です 読者をやめる 読者になる 読者になる

フリーランス チャレンジ!!

週休4日制に俺はなる!

照合順序に気をつけろ!!SQL Serverをデフォルトで使うと大文字、小文字を区別しないぞ!

f:id:ksakae1216:20170517231937j:plain

 みなさん、コウタロウです!!

 

今日はタイトルについて。

 

 

照合順序

長いこと、ITの業界にいるんだけど、照合順序ってまったく知りませんでした。

 

先日、テスト担当者から小文字で検索してもデータが表示できると報告を受けたんです。

 

テーブルには大文字で"ABC"と登録してあったんですが、小文字の"abc"でも検索できたとのこと。

 

 

そんなはずはない!!

 

正直、「ちゃんとテストやってんのか?」と疑いながら確認したら本当に小文字で検索できた?

 

ビックリしてネットで調べると、照合順序という聞いたことのない言葉が。

 

詳細はMSDNのページを参照して下さい。

https://blogs.msdn.microsoft.com/jpsql/2016/07/26/1-3/

 

ザックリ説明すると、文字の順序を示すSQL照合順序と文字を区別するWindows照合順序の2つがあり、Windows照合順序の設定で大文字と小文字が区別されずデータが表示されました。

 

SQL照合順序

例えば、"あ"、"い"、"う"、"え"、"お"であればこの順序で並ぶのは誰でもわかりますがMSDNのページにも例である通り、"朝"と"海"はどちらが大きいのか?

これを決めているのが照合順序です。

 

もし、"朝"と"海"の順序がなければ、Selectの度に順番が変わってしまいます。

 

Windows照合順序

今回のメインはこちらの照合順序。

 

設定は下記ページに詳しく紹介されてます。

SQL Server の照合順序 - マイクロソフト系技術情報 Wiki

 

f:id:ksakae1216:20170516233043p:plain

CIだと"a"と"A"は同じです。

AIだと"は"、"ぱ"、"ば"は同じです。

KIだと"は"と"ハ"は同じです。

WIだと"A"(半角)と"A"(全角)は同じです。

 

SQL Serverのデフォルト設定

デフォルト設定は、"Japanese_CI_AS"です。 

 

つまり、デフォルト設定だと小文字と大文字は区別しない。

 濁点、半濁点は区別するです。

 

どうすればいい?

データベースの設定をSqlserver ManagementStudio変更することができます。

http://blog.engineer-memo.com/2012/10/21/%E7%85%A7%E5%90%88%E9%A0%86%E5%BA%8F%E3%81%AE%E5%A4%89%E6%9B%B4%E6%99%82%E3%81%AE%E5%86%85%E9%83%A8%E5%8B%95%E4%BD%9C%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/

 

ただ、ここで注意があります。

データベースの設定を変更しても、それまでに作成しているテーブルは元の設定のままです。

※設定変更後に作成されたテーブルは新しい設定でテーブルが作成されます。

 

すでに作成したテーブルの設定を変更するにはALTER TABLE文で全カラムを変更すればOKです。

 

変更のポイントとして

①文字列(VARCHAR、CHAR)のみの変更でOK(数値、日付項目は変更の必要なし)

②その項目がプライマリキーの場合は、最初にプライマリキーを削除してから設定変更し、変更後にプライマリキーを再設定。

 

とはいえ、全テーブルの文字列項目を調べて、プライマリキーを削除して、設定変更して、プライマリキーを再設定するのって大変ですよね。

 

サンプルとして私が作成したSQLを以下に記します。

この3つのSQLをそれぞれ流すと、「プライマリキー削除」、「設定変更」、「プライマリキー再設定」のSQL文が表示されます。

表示されたSQLを順に実行すれば設定変更できます。

※'TEST_%'は適宜修正して下さい。

 この例は、テーブル名が"TEST_"で始まるテーブルが対象となっています。

gist1b7f1de46df8376bd51a96c9171d696e

 

gist076bd694d60a93df76e6de7f1ceffed2

 

gist87bd938f3d771714ce2f42134e1de520

 

最後に

いかがですか?

もし、デフォルト設定でSQL Serverをすでに作成してしまい、困っている人はぜひこのSQLで再設定して下さい。

 

ちなみに、変更する時は、事前にローカル環境などでテストしてから検証環境や、本番環境に適用して下さい。

 

ただ、最初に照合順序を設定すれば 、こんな面倒なことをしなくてもいいんですけどね。