【MySQL】【SQL】MySQLで日本語のソートがうまくいかない

SQL データベース

今の職場でちょっとした珍現象が発生して、調べたのでメモ。

珍現象というのは
日本語のソート処理を実装していたのだが、どうもうまくいかないことがわかった。
自分のローカルで対応策を考える。

実行環境

MySQL 5.6
ユーザー：root

調査用にサンプルDBとテーブルを用意

CREATE DATABASE exsample_db DEFAULT CHARACTER SET utf8;

SHOW DATABASES;

USE exsample_db;

CREATE TABLE exsample_table (title VARCHAR(64)) ENGINE=InnoDB;

DESC exsample_table;

INSERT INTO exsample_table VALUES ("オービーシー");
INSERT INTO exsample_table VALUES ("ａｂｃ");
INSERT INTO exsample_table VALUES ("えーびーしー");
INSERT INTO exsample_table VALUES ("！ビックリマーク");
INSERT INTO exsample_table VALUES ("ＡＢＣ");
INSERT INTO exsample_table VALUES ("豆腐");
INSERT INTO exsample_table VALUES ("てすとです。");
INSERT INTO exsample_table VALUES ("エービーシー");
INSERT INTO exsample_table VALUES ("鉄筋コンクリート");
INSERT INTO exsample_table VALUES ("BCA");
INSERT INTO exsample_table VALUES ("鉄道");
INSERT INTO exsample_table VALUES ("ABC");
INSERT INTO exsample_table VALUES ("（ただし、鍋に限る）");
INSERT INTO exsample_table VALUES ("テストです。");
INSERT INTO exsample_table VALUES ("aBc");
INSERT INTO exsample_table VALUES ("おーびーしー");

調査開始

例えば、以下の様にデータが格納されている。

title
オービーシー
ａｂｃ
えーびーしー
！ビックリマーク
ＡＢＣ
豆腐
てすとです。
エービーシー
鉄筋コンクリート
BCA
鉄道
ABC
（ただし、鍋に限る）
テストです。
aBc
おーびーしー

これを降順でソートすると以下のようになってしまう。

SELECT * FROM exsample_table ORDER BY title DESC;
title
ａｂｃ
ＡＢＣ
（ただし、鍋に限る）
！ビックリマーク
鉄道
鉄筋コンクリート
豆腐
てすとです。
テストです。
おーびーしー
オービーシー
えーびーしー
エービーシー
BCA
ABC
aBc

パッと見わかりにくいが、ひらがなとカタカナが交互になってしまっている。
さらに漢字を見てみると「とうふ」が「てっきんこんくりーと」より下になってしまっている。
想定では「と」なので「てつどう」より上でなければならないのに。

調査した結果

日本語の場合、マルチバイトとなってしまい
コンピュータは「とうふ」が「てっきんこんくりーと」ということが判断できない。

結局、「漢字」の文字コードで判断してしまう。

ひらがなとカタカナの対策

以下のページを参考にSQLを発行すれば良いみたい。

いろいろ開発メモ: MySQL 日本語（マルチバイト）文字をソート（ORDER BY)する　UTF-8

SELECT * FROM exsample_table ORDER BY CAST(title AS CHAR) DESC;
title
ａｂｃ
ＡＢＣ
（ただし、鍋に限る）
！ビックリマーク
鉄道
鉄筋コンクリート
豆腐
テストです。
オービーシー
エービーシー
てすとです。
おーびーしー
えーびーしー
BCA
ABC
aBc

また、DBの設定がうまくいっていれば
ひらがなとカタカナのソートはうまくいく模様。なぜ・・・？

根本的な対策

DB設計時に「ふりがな」カラムを追加して、ふりがなでソートしましょう。

その他、後始末

DROP TABLE exsample_table;

DROP DATABASE exsample_db;