中文，数字排序问题

[阿_布]

数据库表内容：
mysql> select * from test;
+--------+
| res |
+--------+
| 5555 |
| 9879 |
| 123456 |
| 6879 |
| 中文 |
| ABC |
| 456789 |
| 546824 |
+--------+
想得到的结果：
5555
6879
9879
123456
456789
546824
ABC
中文

请教高手用什么办法可以做到？谢谢！

[ACMAIN_CHM]

你的 show create table test;是什么？这个和字符集有很大关系。

[阿_布]

mysql> show create table test;
+-------+------------------------------------
----------------------+
| Table | Create Table
                      |
+-------+------------------------------------
----------------------+
| test  | CREATE TABLE `test` (
  `res` varchar(40) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------
----------------------+
1 row in set (0.00 sec)

[ACMAIN_CHM]

注意如果字符集不同，结果可能不一样！

mysql> set names 'gb2312';
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (res varchar(40))engine=myisam DEFAULT CHARSET=gb2312;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test values
    -> ('5555'),
    -> ('9879'),
    -> ('123456'),
    -> ('6879'),
    -> ('中文'),
    -> ('ABC'),
    -> ('456789'),
    -> ('546824');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from test;
+--------+
| res    |
+--------+
| 5555   |
| 9879   |
| 123456 |
| 6879   |
| 中文   |
| ABC    |
| 456789 |
| 546824 |
+--------+
8 rows in set (0.00 sec)

mysql> select * from test
    -> order by res+0=0, res+0;
+--------+
| res    |
+--------+
| 5555   |
| 6879   |
| 9879   |
| 123456 |
| 456789 |
| 546824 |
| 中文   |
| ABC    |
+--------+
8 rows in set (0.00 sec)

mysql>

[阿_布]

我的机器上可以，请问order by res+0=0, res+0是什么意思?

[ACMAIN_CHM]

mysql> select * from test
    -> order by res+0=0, res+0,res;
+--------+
| res    |
+--------+
| 5555   |
| 6879   |
| 9879   |
| 123456 |
| 456789 |
| 546824 |
| ABC    |
| 中文   |
+--------+
8 rows in set (0.00 sec)

mysql>

[阿_布]

谢谢！