MySQL 如何在MySQL中按照保存为VARCHAR格式的数字进行排序

在MySQL中，按照数字排序是非常容易的，只需要使用ORDER BY子句即可。不过，如果数字被保存为VARCHAR格式，并且包含前导0，那么排序就会出现问题。例如，记录中存在以下几个数字：

如果按照VARCHAR类型进行排序，结果将会是这样的：

这显然不是我们所期望的结果，因为它根本不按数字顺序排序。那么，如何按照数字顺序排序呢？接下来我们来介绍一些方法。

阅读更多：MySQL 教程

去除前导0再排序

第一种方法是先将前导0去掉，再按照数字排序。这种方法非常简单，只需要使用MySQL内置的函数LTRIM就可以了。

例如，如果有以下记录：

SELECT id, number FROM mytable;
+----+--------+
| id | number |
+----+--------+
|  1 | 001    |
|  2 | 002    |
|  3 | 003    |
|  4 | 010    |
|  5 | 011    |
+----+--------+

我们可以使用以下SQL语句进行排序：

SELECT id, number
FROM mytable
ORDER BY CAST(LTRIM(number) AS UNSIGNED);

结果如下：

+----+--------+
| id | number |
+----+--------+
|  1 | 001    |
|  2 | 002    |
|  3 | 003    |
|  4 | 010    |
|  5 | 011    |
+----+--------+

这种方法的好处是非常简单，不需要使用任何复杂的算法，只需要使用MySQL中内置的函数即可。缺点是可能影响性能，因为需要将每个记录的数字都转换一遍。

对数字进行填充再排序

第二种方法是对数字进行填充，使它们都有相同的位数，然后再排序。例如，如果我们有以下数字：

我们可以将它们填充成相同的位数，例如3位，就变成了：

这样再按照VARCHAR类型进行排序，结果就是我们想要的数字顺序了。

在MySQL中，我们可以使用LPAD函数来填充数字。例如，如果我们有以下记录：

SELECT id, number FROM mytable;
+----+--------+
| id | number |
+----+--------+
|  1 | 001    |
|  2 | 002    |
|  3 | 003    |
|  4 | 010    |
|  5 | 011    |
+----+--------+

我们可以使用以下SQL语句进行排序：

SELECT id, number
FROM mytable
ORDER BY LPAD(number, 3, '0');

结果如下：

+----+--------+
| id | number |
+----+--------+
|  1 | 001    |
|  2 | 002    |
|  3 | 003    |
|  4 | 010    |
|  5 | 011    |
+----+--------+

这种方法的好处是性能比较好，因为只需要进行一次数字填充即可。缺点是需要事先知道数字的最大位数。

使用正则表达式进行排序

第三种方法是使用正则表达式进行排序，这种方法相对比较复杂，但是也比较强大。

在MySQL中，我们可以使用REGEXP_REPLACE函数来替换数字中的前导0。例如，如果我们有以下记录：

SELECT id, number FROM mytable;
+----+--------+
| id | number |
+----+--------+
|  1 | 001    |
|  2 | 002    |
|  3 | 003    |
|  4 | 010    |
|  5 | 011    |
+----+--------+

我们可以使用以下SQL语句进行排序：

SELECT id, number
FROM mytable
ORDER BY CAST(REGEXP_REPLACE(number, '^0+', '') AS UNSIGNED);

结果如下：

+----+--------+
| id | number |
+----+--------+
|  1 | 001    |
|  2 | 002    |
|  3 | 003    |
|  4 | 010    |
|  5 | 011    |
+----+--------+

这种方法的好处是可以灵活处理不同位数的数字，缺点是比较复杂，需要了解一定的正则表达式语法。

结论

在MySQL中按照保存为VARCHAR格式的数字进行排序，其中一些数字具有类似于085、090等的前导0，有很多方法可以解决。我们可以使用LTRIM函数去除前导0再排序，也可以使用LPAD函数对数字进行填充再排序，还可以使用正则表达式处理。选择哪种方法，需要根据具体情况决定。在实际使用中，应该根据数据量、性能要求、数据分布情况等因素进行综合考虑，选择最优的排序方法。