以类似Excel的方式在MySQL中排序varchar列

那是因为 my_column+0 等于所有字符串(0)。

ORDER BY my_column+0, my_column

mysql> SELECT a FROM test ORDER BY a+0, a;
+-------+
| a     |
+-------+
| NULL  |
| alan  |
| bob   |
| carl  |
| david |
| 1     |
| 2     |
| 3     |
| 3.5   |
| 10    |
| 11    |
| 12    |
+-------+
12 rows in set (0.00 sec)

如果你严格要求数字在字符串之上,这里有一个解决方案 :

mysql> SELECT a FROM test ORDER BY (a = CONCAT('', 0+a)) DESC, a+0, a;
+-------+
| a     |
+-------+
| 1     |
| 2     |
| 3     |
| 3.5   |
| 10    |
| 11    |
| 12    |
| alan  |
| bob   |
| carl  |
| david |
| NULL  |
+-------+
12 rows in set (0.00 sec)

这是有效的:

SELECT a FROM test ORDER BY a IS NULL OR a='', a<>'0' AND a=0, a+0, a;

不过,我们欢迎任何更有效/更优雅的解决方案。