varchar類型的字段存儲純數字的排序
如果表字段的類型為varchar型，但是里面存儲的是純數字，怎么實現按照數字的大小來排序？
下面的方法對于mysql和oracle都實用
order by 字段+0
order by 字段*1
等等都可以實現
mysql> create table tn (id varchar(30),name varchar(50));
mysql> show create table tn;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| tn | CREATE TABLE `tn` (
`id` varchar(30) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> insert into tn values (1,'a');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tn values (10,'b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tn values (11,'bb');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tn values (2,'bb');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tn values (5,'c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tn values (3,'f');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tn values (6,'g');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tn;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 10 | b |
| 11 | bb |
| 2 | bb |
| 5 | c |
| 3 | f |
| 6 | g |
+------+------+
使用order by語句進行排序
mysql> select * from tn order by id desc;
+------+------+
| id | name |
+------+------+
| 6 | g |
| 5 | c |
| 3 | f |
| 2 | bb |
| 11 | bb |
| 10 | b |
| 1 | a |
+------+------+
7 rows in set (0.00 sec)
貌似是不起作用，因為id列的值存的是字符串類型的
解決：
使用字段+0解決
mysql> select * from tn order by
id+0 desc;
+------+------+
| id | name |
+------+------+
| 11 | bb |
| 10 | b |
| 6 | g |
| 5 | c |
| 3 | f |
| 2 | bb |
| 1 | a |
+------+------+
7 rows in set (0.00 sec)
或者使用 字段*1解決
mysql> select * from tn order by
id*1 desc;
+------+------+
| id | name |
+------+------+
| 11 | bb |
| 10 | b |
| 6 | g |
| 5 | c |
| 3 | f |
| 2 | bb |
| 1 | a |
+------+------+
7 rows in set (0.00 sec)
