後で、SQL Server 2008 とかの最新バージョンを調べますが、落とし穴になりかねないのでメモ。
1 2 3 4 5 | create table test1 ( id int , name varchar (10), num int ); |
として test1 テーブルを作成しておきます。
ここに次のようなデータを入れます。
1 2 3 4 | delete from test1 ; insert into test1 values (1, 'masuda' ,10); insert into test1 values (2, 'tomoaki' ,20); insert into test1 values (3, '100' ,30); |
このデータを検索します。
1 2 | select * from test1 where num = 10 ; |
この結果は予想通り、1行目が取得できます。
1 | 1 masuda 10 |
さて、num に文字列の’10’を指定したときはどうなるでしょうか?
1 2 | select * from test1 where num = '10' ; |
マッチングしないと思いきや、自動的に文字列型から数値型に変換されて、1行取得されます。
# Oracle の場合は変換エラーになったと思うのだけど、うろ覚えです。
1 | 1 masuda 10 |
逆に文字列型(varchar)に対して数値で比較してみると、
1 2 | select * from test1 where name = 100 ; |
これはエラーになります。
1 2 | サーバー : メッセージ 245、レベル 16、状態 1、行 1 構文エラー。varchar 値 'masuda ' から int データ型に変換できませんでした。 |
1行目の name 列の値「masuda」が int 型に変換できないためにエラーになっていますね。
となると、name 列の値が全て数値型になるようにしたら、どうなるのでしょうか?
1 2 3 4 | delete from test1 ; insert into test1 values (1, '100' ,10); insert into test1 values (2, '200' ,20); insert into test1 values (3, '300' ,30); |
というデータを入れた場合に、次の SQL を動かすと、
1 2 | select * from test1 where name = 100 ; |
実は、結果が取れるのですッ!!!
1 | 1 100 10 |
そんな訳で、SQL Server 2005 の SQL を書くときに、自動変換をあてにしたり、自動変換がされるような書き方をすると、はまる可能性があるよという話でした。
これは、.NET から扱うときに、SqlParamter オブジェクトの使い方が問題になってくるのですよね。これは別の記事に。
自動変換だと、1番でなく2番のように動くのでパフォーマンスに影響して困ります。
(1) select * from test1 where name=cast(100 as varchar);
(2) select * from test1 where cast(name as integer)=100;
SQL Server だけでなく、Oracle でも同様な処理なのは、ショックでした。
ああ、SQL Server 2008 R2 で試してみたのですが、取れますねぇ。
ああ、MySQL で試してみたのですが、取れますねぇ。
ひょっとして、これが RDB の基本の動作なのかも、思ったりします。
ただ、MySQL のほうが賢くて、name に ‘masuda’ を入れても、
select * from test1 where name = 100 ;
で、こけません。。。それならば、対称性を考えるとそっちのほうがいいかという感じもするけど。