読者です 読者をやめる 読者になる 読者になる

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

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

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

実行環境

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 ("abc");
INSERT INTO exsample_table VALUES ("えーびーしー");
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 ("エービーシー");
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
オービーシー
abc
えーびーしー
!ビックリマーク
ABC
豆腐
てすとです。
エービーシー
鉄筋コンクリート
BCA
鉄道
ABC
(ただし、鍋に限る)
テストです。
aBc
おーびーしー

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

SELECT * FROM exsample_table ORDER BY title DESC;
title
abc
ABC
(ただし、鍋に限る)
!ビックリマーク
鉄道
鉄筋コンクリート
豆腐
てすとです。
テストです。
おーびーしー
オービーシー
えーびーしー
エービーシー
BCA
ABC
aBc

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

調査した結果

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

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

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

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

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

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

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

根本的な対策

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

その他、後始末

DROP TABLE exsample_table;

DROP DATABASE exsample_db;