MySQL 如何按照保存为VARCHAR格式的数字进行排序，其中一些数字具有类似于085、090等的前导0

以下是语法-

select *from yourTableName order by yourColumnName*1,yourColumnName;

让我们先创建一个表-

mysql> create table DemoTable
(
   Value varchar(100)
);
Query OK, 0 rows affected (0.62 sec)

使用插入命令向表中插入一些记录-

mysql> insert into DemoTable values('90');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('86');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('45');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('85');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('085');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('090');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('045');
Query OK, 1 row affected (0.10 sec)

使用select语句从表中显示所有记录：

mysql> select *from DemoTable;

这将产生以下输出-

+-------+
| Value |
+-------+
|    90 |
|    86 |
|    45 |
|    85 |
|   085 |
|   090 |
|   045 |
+-------+
7 rows in set (0.00 sec)

以下是按保存为VARCHAR类型数字排序的查询，其中一些数字具有前导0-

mysql> select *from DemoTable order by Value*1,Value;

这将产生以下输出-

+-------+
| Value |
+-------+
|   045 |
|    45 |
|   085 |
|    85 |
|    86 |
|   090 |
|    90 |
+-------+
7 rows in set (0.00 sec)

阅读更多：MySQL 教程