MYSQLで「テキスト数字」を正しくソートできるようにする

OBATA

3年前

データベースを読み出しする場合、抽出条件・ソート指定は当たり前のように行います。
今回ソートが「思っていたのと違う」動きになっていました。

それは数字ソートなのですが、１～1000の数字があれば普通は数字順に並びます。

ただ、MYSQLでテキスト項目内での数字は数字順にソートされません。
今回は、この現象を何とかする方法について調べました。

忘備録として記録いたします。

検証に準備した内容は次の通りです

以下のテーブルを作成して検証しました。

テキストエリアでの数字ソートなので、データ型（text）が一つあればOKです。

次のようなSQL文でソートしてみたところ、このような表示になりました。

SELECT * FROM `sort_test` ORDER BY text_no asc;

２より100のほうが小さいと判断されてしまいました。

原因はなんだろうと調べてみたところ、意外なことが要因でした。

どういうことかというと、
テキストエリアでは、半角数字であっても「文字コード」でソートされるということです。

数字を文字コードで表すと次のようになります。

数字順ならば10が一番最後ですが、文字コード順の場合は異なります。
数字10は文字コード0x3130のため、文字コードソートの場合2（0x32）よりも前になります。

数字だとピンとこないですが、アルファベット順だとわかりやすいです。
例えば次のような文字列の場合どのような並びになるか想像がつきますでしょうか？

文字列ソートすると、BBが一番後ろになります。
なぜならば最初の一文字が「B」で「A」よりも大きいためです。
文字列ソートに、文字数は影響しません。

そのため、textエリアに半角数字を入れても数字順にソートされないのです。

int型など数字エリアの場合、正しく数字ソートできます。
しかし、textエリアの場合はかならず文字列ソートになります。

text型なので、文字として判断されます。
ある意味正論です。

では、text型で数字ソートする方法はないのでしょうか?

答えは「あります。」

次のようにソートの記述をすればtext型のエリアで数字順に並べることができます。

text型のエリアで数字順にソートする記述がこちらです。

SELECT * FROM `sort_test` ORDER BY cast(text_no as SIGNED) asc;

この内容で表示すると、text型であっても、数字順にソートされます。

プログラムをされている方ならば、馴染みのある「キャスト」と呼ばれる手法を使って、text型を数字順に並べました。

記述の　 cast(text_no as SIGNED) 　部分です。

SIGNEDは数値化を意味します。
text型のエリアを数値として扱うという指定です。

これにより、文字ではなく数値として扱われるようになります。

専門的な話ですが、プログラムで型の違う変数同士で代入を行う場合にも、キャストを使用します。
それと同様のことがMYSQLのSQL文でもできるということを学びました。