漢数字はソートしても順番に並ばない
前回の記事「MariaDB(MySQL)の照合順序の話」で少し触れた漢数字のお話です。
結論から言うと漢数字は数字順に並ばないし、そもそも漢字コードでソートすべきではない、ということなんですけど、それだけだと話が終わってしまうし、いまいち納得されない方もいるかもしれないし、なんとかしろ!と上司から無茶ぶりされているプログラマーさんとかも普通に存在しそうなのでもう少しつっこんだ話をします。
漢数字が綺麗に並ばないのは何もDBだけの話ではない
例えば、身近なところでエクスプローラーなんかもフォルダ名(ファイル名でも可)に漢数字を付けるとこんなふうに並びます。
前回テストした際、Unicodeでは「一七三九二五八六四」、cp932では「一九五三四七二八六」でしたのでエクスプローラーではcp932をベースに並び替えているんだなということがわかります。
こういうテストデータだとピンとこないかも知れませんが、お仕事上、営業部ごとに共有フォルダなんかを作っているケースだとこんなふうに並びます。
- 第一営業部
- 第三営業部
- 第二営業部
これを見て、バグだ!Windowsはおかしい!とお偉いさんが騒ぎ出すと、説明するのがなかなかに面倒なんですよね…。
たまたま漢数字が1桁だけで、一、二、三だから違和感を覚えるのであって、これが壱、弐、参だったらどうでしょう。あるいは第十二営業部とか第三十一営業部まであって文字数が違ったらどうでしょう。そこまで例を出せば、漢字を数字にあてはめて並び替えることの難しさがわかって頂けるかと思います。
エクスプローラーの並び順のふしぎ
漢数字のゼロである「〇」は文字コード的には「一(いち)」よりも小さいけれど、半角英数字よりは大きいハズなのですが、先述の例では半角数字の「0」と「1」の間に漢数字のゼロが入ってきます。これは内部的に0に変換されている予感がひしひしとします。
また、漢数字とは関係ありませんが、文字コードという意味では半角数字の1,2,3,20,100という並びもおかしい。
文字列として並び替えるなら、1,100,2,20,3となるはずですよね。実際、MySQL等のデータベースでソートした場合にはそうなります。
実はWindows XPよりも前のエクスプローラーだとMySQLと同じように文字列として判定され、1,100,2,20,3という並びになっていました。XP以降は数字名の付いたファイルやフォルダは数値として扱うようになったんですね。
ただ、そのために違和感を覚えるようになったケースもありまして、例えばフォルダ名に日付を付けていた場合です。
- 201811
- 20181001
- 20181002
こんなふうに年月日(8桁)と年月(6桁)が混在すると数字としては 201811 < 20181001 と判定されるので、10月よりも11月のほうが上にきてしまう、なんて現象が起こります。まぁ桁数そろえろって話ですよね(;´∀`)
一応、昔のエクスプローラーのように文字列として判断してソートする方式にも変更できるようですが、レジストリを変更しなければならないのであまり手軽ではありません。
MariaDB(MySQL)でも確認してみる
まず基本パターンの確認です。
■SQL文
create table customer (name varchar(255), address1 varchar(255), address2 varchar(255));
insert into customer values ('鈴木一郎', '千代田', '一丁目');
insert into customer values ('鈴木二郎', '千代田', '二丁目');
insert into customer values ('鈴木三郎', '千代田', '三丁目');
select * from customer order by address2;
■実行結果
鈴木一郎 | 千代田 | 一丁目 |
鈴木三郎 | 千代田 | 三丁目 |
鈴木二郎 | 千代田 | 二丁目 |
はい、1→3→2の順で並びます。utf8mb4_binでもcp932_binでも同じです。
こういうケースでは丁目のマスターテーブルを用意して、そっちにコードIDと並び順を持つのが王道でしょう。
マスターテーブルを用意する王道パターン
■SQL文
create table customer (name varchar(255), address1 varchar(255), address2 varchar(2));
insert into customer values ('鈴木一郎', '千代田', '01');
insert into customer values ('鈴木二郎', '千代田', '02');
insert into customer values ('鈴木三郎', '千代田', '03');
insert into customer values ('鈴木四郎', '千代田', '04');
insert into customer values ('鈴木五郎', '千代田', '05');
insert into customer values ('鈴木四十二郎', '千代田', '42');
create table chome (id varchar(2), name varchar(255), sort_no int);
insert into chome values ('01', '一丁目', 1);
insert into chome values ('02', '二丁目', 2);
insert into chome values ('03', '三丁目', 3);
insert into chome values ('04', '四丁目', 4);
insert into chome values ('05', '五丁目', 5);
insert into chome values ('42', '四十二丁目', 42);
select customer.name,customer.address1,chome.name
from customer left join chome on customer.address2=chome.id
order by chome.sort_no;
■実行結果
鈴木一郎 | 千代田 | 一丁目 |
鈴木二郎 | 千代田 | 二丁目 |
鈴木三郎 | 千代田 | 三丁目 |
鈴木四郎 | 千代田 | 四丁目 |
鈴木五郎 | 千代田 | 五丁目 |
鈴木四十二郎 | 千代田 | 四十二丁目 |
バッチグー!(古)
つっこみどころ満載だと思いますが、ひとつひとつ説明します。
chomeテーブルに並び順いる?IDでソートすれば良くない?
例が丁目なのでそうも思えますが、たとえばこれが部署名や役職名だったらどうでしょう。
特に役職ではよくあるケースなのですが、「01:社長 02:部長 03:課長」とコードを割り振った後に、新しく「04:次長」が追加され、IDでソートしたら課長より次長のほうが下に表示されてしまう…なんてケース。笑い話ですが、現場では真顔です…(;´Д`)
なのでマスターテーブルにはIDとは別に並び順(sort_no)の項目を追加するのが基本だと思います。
特に最近はWebアプリが主流ですからマスターテーブルのIDはint型でauto_incrementなんてケースも多いでしょうしね。
(URLの引数で渡すときに数値のほうが楽だから)
突然、四丁目と五丁目を追加したのはなんで?
文字列で見たとき、「四十二」が「四」と「五」の間に入りませんよ~というのをわかりやすくするためです。
そもそも四十二丁目って……?
なんかググったら丁目の最大値は現在42らしいんですよ。「北海道帯広市西19条南42丁目」という住所が存在するようです。びっくりですね。
でも、ぶっちゃけ面倒くさくない?
それな。
丁目マスターを用意するということは、ユーザーが入力する住所欄には丁目のプルダウンが表示されて、開いてみると「一丁目」~「四十二丁目」までずら~っと並んでゲンナリする絵面が思い浮かびます。
本当によくあるケースなので、ほとんどユーザー側も飼いなら…ごほん…受け入れているのかも知れませんが、住所入力するのに
- 郵便番号を入力
※システムによっては4桁/3桁でテキストボックスが別れていたり、あるいは7桁入力になっているけれどハイフンの入力を強制されたり、逆に入力するように指示されたり、そんなんシステム側で修正しろや!とはっky…ごほん。なんでもありません。 - 都道府県プルダウン
- 市区町村プルダウン
- 丁目プルダウン
- 番地テキストボックス
みたいに並んでいると見ただけでゲンナリします。
まぁ、大抵は郵便番号入力した時点で丁目まで自動入力されたり、[検索]ボタンを押下したタイミングで自動入力されるような仕組みになってきているとは思いますが。
このへんはどこまで厳格に入力させるか、ユーザー側を配慮して多少適当な入力でもヨシとするかは設計上悩ましいところでしょう。
でも…
既に稼働中で既存テーブルを変更できない場合
そう、大抵はもう稼働中のシステムにおいて、ちょろっと顧客一覧表的なのを見たお偉いさんが、「んー、ちみちみ。この住所の並び順はなんだね?」なんて思いつきレベルの無茶ぶりがきて頭を悩ませるものなのです。そんなのに対応しなければならないのがプログラマー。
がんばれプログラマー。
厳密に完璧に、それこそ四十二丁目まで対応しようとすると正直かったるいです。なので、とりあえず目立つ部分(一~九丁目)だけ対応すりゃいいんじゃね、というケース。
ストアドファンクションで解決する
例えば、こんなストアドファンクションはどうでしょう。
■ストアドファンクションの例
delimiter //
create function kanji2num(kanji varchar(255))
returns varchar(255)
begin
set kanji=replace(kanji,'一','1');
set kanji=replace(kanji,'二','2');
set kanji=replace(kanji,'三','3');
set kanji=replace(kanji,'四','4');
set kanji=replace(kanji,'五','5');
set kanji=replace(kanji,'六','6');
set kanji=replace(kanji,'七','7');
set kanji=replace(kanji,'八','8');
set kanji=replace(kanji,'九','9');
set kanji=replace(kanji,'〇','0');
return kanji;
end;
//
delimiter ;
えらい単純ですが、渡された漢数字をアラビア数字に変換します。ただし、四十二とか四百二とかはダメ。そこまで対応すると面倒なのであくまで一桁だけ。
ストアドファンクションはorder by句にそのまま記述できるため、こんなふうに使えます。
■SQL文
create table customer (name varchar(255), address1 varchar(255), address2 varchar(255));
insert into customer values ('鈴木一郎', '千代田', '一丁目');
insert into customer values ('鈴木二郎', '千代田', '二丁目');
insert into customer values ('鈴木三郎', '千代田', '三丁目');
insert into customer values ('鈴木四郎', '千代田', '四丁目');
insert into customer values ('鈴木五郎', '千代田', '五丁目');
insert into customer values ('鈴木四十二郎', '千代田', '四十二丁目');
select *,kanji2num(address2) from customer order by kanji2num(address2);
■実行結果
鈴木一郎 | 千代田 | 一丁目 | 1丁目 |
鈴木二郎 | 千代田 | 二丁目 | 2丁目 |
鈴木三郎 | 千代田 | 三丁目 | 3丁目 |
鈴木四郎 | 千代田 | 四丁目 | 4丁目 |
鈴木四十二郎 | 千代田 | 四十二丁目 | 4十2丁目 |
鈴木五郎 | 千代田 | 五丁目 | 5丁目 |
四十二丁目は対応できていませんが、なんとなく道筋が見えてきませんか?
四十二丁目を42丁目に変換したとしても文字列のままでは4丁目と5丁目の間に入ってしまいます。なのでいっそのこと「丁目」という文字もとっぱらってint型として扱ってしまうのも良いでしょう。
10以上については書き方が色々あって、十丁目という記述だったり、一〇丁目という記述だったりする可能性もあるわけで、本気で対応しだすと大変めんどうくさい。四十二丁目も四二丁目という書き方の可能性も考えなければいけません。
このストアドファンクションでゴリ押しパターンはお客様の無茶ぶりや、SEのテーブル設計ミスがわかっているのに今は修正できない!という緊急事態で使われることが多いと思います。
なのでぼくだったら、厳密に十や〇の対応なんかほおりなげて1~42までのパターンを複数ずら~っとコピペしてとりあえず済ませてしまいそうです。
まとめ
前回に引き続き、誰得なのかわからない漢数字のお話でした。
- 漢数字が数字順に並ばないのは仕様。DBの問題でもOSの問題でもない。
- そもそも漢字の文字コードでソートするのが無茶なのでマスターテーブルを用意すべき。
- やむにやまれぬ事情で再設計できない場合はストアドファンクションで逃げるのも手。
- 事態が落ち着いてからソート用フィールドを追加しよう。
どこかの誰かの役に立つとは思えないけれど、最近記憶力の低下が著しいので個人的なメモ帳としては役立ちそうだなぁとひそかに思っています(*ノェノ)