MySQL 排序時指定字元集排序方式的案例

看到有人發問

https://www.facebook.com/groups/taiwanmysqlusergroup/permalink/1852399401574463/

建立測試資料

create table t0416 (
  id int not null auto_increment primary key
, col varchar(12) not null
);

insert into t0416 (col) values
('凃世元'),('王宏嘉'),('王怡文'),
('王信雲'),('方珮馨'),('王偲宇'),
('王曾信'),('永同裕'),('田藍惇');

不指定時

select *
  from t0416
 order by convert(col using big5);

+----+-----------+
| id | col       |
+----+-----------+
|  1 | 凃世元    |
|  2 | 王宏嘉    |
|  3 | 王怡文    |
|  4 | 王信雲    |
|  5 | 方珮馨    |
|  6 | 王偲宇    |
|  7 | 王曾信    |
|  8 | 永同裕    |
|  9 | 田藍惇    |
+----+-----------+

big5 字元集在MySQL中有支援的排序方式?

SHOW COLLATION WHERE Charset = 'big5';

+-----------------+---------+----+---------+----------+---------+---------------+
| Collation       | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-----------------+---------+----+---------+----------+---------+---------------+
| big5_bin        | big5    | 84 |         | Yes      |       1 | PAD SPACE     |
| big5_chinese_ci | big5    |  1 | Yes     | Yes      |       1 | PAD SPACE     |
+-----------------+---------+----+---------+----------+---------+---------------+

Order by 時指定排序方式

select *
  from t0416
 order by convert(col using big5) collate big5_bin;

+----+-----------+
| id | col       |
+----+-----------+
|  1 | 凃世元    |
|  5 | 方珮馨    |
|  2 | 王宏嘉    |
|  3 | 王怡文    |
|  4 | 王信雲    |
|  7 | 王曾信    |
|  6 | 王偲宇    |
|  8 | 永同裕    |
|  9 | 田藍惇    |
+----+-----------+



select *
  from t0416
 order by convert(col using big5) collate big5_chinese_ci;

+----+-----------+
| id | col       |
+----+-----------+
|  1 | 凃世元    |
|  2 | 王宏嘉    |
|  3 | 王怡文    |
|  4 | 王信雲    |
|  5 | 方珮馨    |
|  6 | 王偲宇    |
|  7 | 王曾信    |
|  8 | 永同裕    |
|  9 | 田藍惇    |
+----+-----------+