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

2021年10月12日

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

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

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

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

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

検証に使ったMYSQLのテーブル

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

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

このテーブルに次の内容を入力しました

ソートるすると次のような並びになりました。

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

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

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

原因は半角数字を文字コードでソートしていたため

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

具体的には次の通りです

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

• 半角数字1の文字コード・・・0x31
• 半角数字2の文字コード・・・0x32
• 半角数字10の文字コード・・・0x3130

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

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

• AA
• BB
• AAB

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

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

そもそもtextエリアで数字順ソートはできない

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

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

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

答えは「あります。」

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

text型で数字順にソートする記述

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

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

text型を数字順にできたのは「キャスト」と呼ばれる手法

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

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

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

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

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