あいつの日誌β

働きながら旅しています。

MySQL: 全角、半角が混在するカラムをソートする

全角、半角が混在するカラムを並べ替えるっていうタスクが来たのでソートする方法をかんがえた

テスト用スクリプト

CREATE TABLE test_user (
  `id`   int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(12) NOT NULL COMMENT 'ユーザー名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='テスト';

INSERT INTO test_user (name) VALUES ('タナカ'),('アキヤマ'),('アントキノイノキ'),('アントキモイノキ');

SELECT * FROM test_user ORDER BY name ASC;
SELECT * FROM test_user ORDER BY name COLLATE utf8_unicode_ci ASC;
SELECT * FROM test_user ORDER BY name COLLATE utf8_general_ci ASC;

DROP TABLE test_user;

実行結果

期待した結果ではない。先に全角が並べられる

mysql> SELECT * FROM test_user ORDER BY name ASC;
+----------+--------------------------+
| id       | name                     |
+----------+--------------------------+
| 82795544 | アントキノイノキ         |
| 82795542 | タナカ                   |
| 82795543 | アキヤマ                     |
| 82795545 | アントキモイノキ                 |
+----------+--------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM test_user ORDER BY name COLLATE utf8_general_ci ASC;
+----------+--------------------------+
| id       | name                     |
+----------+--------------------------+
| 82795544 | アントキノイノキ         |
| 82795542 | タナカ                   |
| 82795543 | アキヤマ                     |
| 82795545 | アントキモイノキ                 |
+----------+--------------------------+
4 rows in set (0.00 sec)

こちらは期待した通り

mysql> SELECT * FROM test_user ORDER BY name COLLATE utf8_unicode_ci ASC;
+----------+--------------------------+
| id       | name                     |
+----------+--------------------------+
| 82795543 | アキヤマ                     |
| 82795544 | アントキノイノキ         |
| 82795545 | アントキモイノキ                 |
| 82795542 | タナカ                   |
+----------+--------------------------+
4 rows in set (0.00 sec)

まとめ

データを全角、あるいは半角に統一するようにしたほうがいいと思いますが、まあこういう回避策もあるっていう事で。