MySQLのvarchar型をintegerにcastしながらorderとかするとどうなるか

1. 始めに
MySQLを使っていてとある問題に当たった。文字列と数字列が混在するカラムで、数字のみの比較を取りたくなった。比較というか厳密にはmax()を取りたかった。
varchar型のカラムなのでmax()をするとそのカラムの中で9から始まる列が最も大きなカラムとして認識されてしまった。
例えば、1234 と 99 という二つの数字(カラムはvarcharなのでMySQL的には文字列)があり、そのカラムに対してmax()を取ると99がmaxとして出てきてしまった。
これは想像の通り、1234の1という文字と99の9という文字では9の方が文字として大きいとして認識されてしまったからだ。
そこでintegerにcastしたときにどんな動作になるか試してみた。

2. temporary table の作成とテストデータ

テストは簡単だ。

create temporary table tmp (
id int primary key,
name varchar(100)
);

insert into tmp
values
(1, 'aaa'),
(2, 'bbb'),
(3, '123'),
(4, '12'),
(5, '23456789'),
(6, '9'),
(7, '9999aaaa'),
(8, 'abcd9999dddd');

select * from tmp
order by cast(name as unsigned);

こんな感じでやってみた。
結果はこう

1 aaa
2 bbb
8 abcd9999dddd
6 9
4 12
3 123
7 9999aaaa
5 23456789

こんな感じで数字の文字列をちゃんと整数として認識されている様子。
ちなみに

CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name)

CAST() および CONVERT() 関数はひとつのタイプの値をもって、他のタイプの値を生成します。

その type は次の値のどれかになりえます :

  • BINARY[(N)]
  • CHAR[(N)]
  • DATE
  • DATETIME
  • DECIMAL
  • SIGNED [INTEGER]
  • TIME
  • UNSIGNED [INTEGER]

MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.8 キャスト関数と演算子

というわけで、CAST関数(?)に渡されるcast先の型はintegerとか普段カラムの型として使われる名前では無く、cast用に用意された型名を使わなければならないらしい。注意されたし。

今回ではmaxで数字列を取る、ということが当初の目的だったのでそっちも取ってみた。

select max(cast(name as unsigned)) from tmp
order by cast(name as unsigned);
max(cast(name as unsigned))
23456789

という感じでうまく取れているようだった。

コメントを残す