MYSQL varchar 类型排序错乱问题的解决

时间：2011-03-28 来源：confusing

今天项目遇到一个varchar数据类型排序的问题

原来的SQL语句如下：

mysql> SELECT sno FROM mytable WHERE sdate = '2011-03-15' ORDER BY sno;

显示结果

+------+
| sno  |
+------+
| 1    | 
| 10   | 
| 11   | 
| 12   | 
| 2    | 
| 3    | 
| 4    | 
| 5    | 
| 6    | 
| 7    | 
| 8    | 
| 9    | 
+------+
12 rows in set (0.00 sec)

问题原因：varchar类型只按照每一位进行排序所以造成排序结果不正确

解决：

mysql> SELECT sno FROM mytable WHERE sdate = '2011-03-15' ORDER BY sno+0;

解决后结果：

+------+
| sno  |
+------+
| 1    | 
| 2    | 
| 3    | 
| 4    | 
| 5    | 
| 6    | 
| 7    | 
| 8    | 
| 9    | 
| 10   | 
| 11   | 
| 12   | 
+------+
12 rows in set (0.00 sec)