DBでソートの対象となるカラムはデータ型に注意すべき

2007年09月28日 23:03

技術情報

当たり前なんだけど、意外とハマる可能性がある気がするのでメモ。

例えば、以下のようなテーブル（test）があるとします。

id	name
1	太郎
2	次郎
3	三郎
10	花子
11	良子

で、以下のようなSQLを実行した場合に、

SELECT id FROM test ORDER BY id;

結果が、必ずしも「1, 2, 3, 10, 11」の順にならない場合があります（！）

それは、idのデータ型が文字列型（textとかvarcharとか）の時です。
文字列型のカラムにソートをかけた場合、文字列の１文字目から順に大小を比較していきます。
なので、結果としては「1, 10, 11, 2, 3」の順になります。

また、同じテーブルでMAX関数を使った場合、

SELECT MAX(id) FROM test;

その結果が「11」にならず「3」になる事も同様の理由です。
ね？結構しでかしちゃいそうでしょ？

さて、上記ふまえてソートに使われそうなカラムにどのデータ型を使ったらいいか考えてみる。

普通に数値型（integerとか）でいいんじゃないか？

PrimaryKeyとかIDになりそうなユニークなカラムは数値型でいいと思う。桁が変わる事があるのでちょっと気持ち悪い気がするけど。

文字列型（textとかvarcharとか）を使う場合

間違っても文字列型の中に、数字っぽい値をいれてはいけない。
理由は冒頭で説明した通り、ソートしたりMAX値を取得した時に思った値が返ってこないから。

で、良くありそうなのが接頭字を付けるパターン。
「id001」とか「N0001」みたいな。
この場合気を付けなきゃいけないのは必ず桁を「0」で埋めて固定長にする事。
「id98, id99, id100」をソートすると「id100, id98, id99」になっちゃうから。
「id098, id099, id100」であれば思った通りの順番になる。

あとは、固定長といっても接頭字なしで「0」埋めしても良くない事が起こるかも。
「00001」「00002」「000003」・・・
なぜかというと、思わぬところで数値型に変換されちゃうかもしれないから。（JSとかで）
つまり、「"00001"」だったものがいつのまにか「1」になっちゃう事があるから。（詳細省略）