建表如下:
+----+------+--------+
| id | name | pinyin |
+----+------+--------+
| 1 | 李 | li |
| 2 | 王 | wang |
| 3 | 张 | zhang |
| 4 | 刘 | liu |
+----+------+--------+
表中字段的编码格式如下:
mysql>show full columns from order_by_demo;+--------+-------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+-------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id | int | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| name | varchar(10) | utf8_bin | YES | | NULL | | select,insert,update,references | |
| pinyin | varchar(10) | utf8_bin | YES | | NULL | | select,insert,update,references | |
+--------+-------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
3 rows in set
可以看到name和pinyin列都是utf8编码。
以name列进行升序排序:
mysql> SELECT *FROM order_by_demo ORDER BY name;+----+------+--------+
| id | name | pinyin |
+----+------+--------+
| 4 | 刘 | liu |
| 3 | 张 | zhang |
| 1 | 李 | li |
| 2 | 王 | wang |
+----+------+--------+
4 rows in set
name列并没有以汉语拼音顺序进行升序,因为utf8并不支持拼音的排序,可以如下处理:
mysql> SELECT *FROM order_by_demo ORDER BY CONVERT(name USING GBK);+----+------+--------+
| id | name | pinyin |
+----+------+--------+
| 1 | 李 | li |
| 4 | 刘 | liu |
| 2 | 王 | wang |
| 3 | 张 | zhang |
+----+------+--------+
4 rows in set
将想要按拼音排序的字段进行转码,转为GBK,再进行排序即可达到效果。