在MySQL中按数字顺序对varchar字段进行排序

Question

我有一个类型为varchar的字段number。尽管它是varchar类型，但它存储具有可选前导零的整数值。排序按字典顺序排列（"42"在"9"之前）。如何按数字值排序（使"9"在"42"之前）？

目前我使用以下查询：

SELECT * FROM table ORDER BY number ASC

- wow

---

“number”是一个字符串字段，对吗？因为这是按字典顺序排序的。 - moinudin

你只需要将列类型更改为int并使用zerofill（如果要保留00100）即可。 - ajreal

@ajreal，使用zerofill（非标准）没有任何好处，因为这会在提取该字段时添加更多的前导零。所以在这种情况下不会有帮助。 - Mayank Jain

Answer 1

试一试这个

SELECT * FROM table_name ORDER BY CAST(field_name as SIGNED INTEGER) ASC

- bobs

---

对于大型数据库，这种方式是否高效？ - moinudin

正如paxdiable所回答的那样，这将会严重影响性能。 - James John McGuire 'Jahmic'

尽管它存在性能问题，但仍然能够工作。总比没有好。 - Krishnadas PC

Answer 2

其实我发现了一些有趣的东西：

SELECT * FROM mytable ORDER BY LPAD(LOWER(mycol), 10,0) DESC

这可以让您按以下方式排序字段：

1
2
3
10
A
A1
B2
10A
111

- workdreamer

---

+1，因为这个解决方案只能完美地解决字母数字字符。如果您的数据量非常大，性能如何？如果您的数据有10位或更多位数字，增加LPAD长度超过10是明智的选择。 - Lizesh Shakya

Answer 3

有几种方法可以做到这一点：

1. 将它们存储为数字值而不是字符串。你已经排除了这个选项，因为你想保留像 00100 这样的带前导零的字符串。
2. 将字符串强制转换为数字进行排序。这样可以工作，但请注意对于相当大的数据库来说，这会对性能造成很大的影响。每行函数无法很好地扩展。
3. 添加第三列，该列是字符串的数字等效项，并在其上进行索引。然后使用一个 insert/update 触发器，在字符串列更改时确保它正确设置。

由于绝大多数数据库的读取远远多于写入，上述第三种选项将计算成本（在 insert/update 时完成）分摊到所有选择中。您的选择将非常快，因为它们使用数字列进行排序（并且没有每行函数）。

您的插入和更新将会变慢，但这是您要付出的代价，老实说，这个代价非常值得。

触发器的使用维护了表的 ACID 属性，因为两列保持同步。而且众所周知，通常您可以在大多数性能优化中以空间换时间。

我们在许多情况下使用了这个 "技巧"，例如将原始姓氏的小写版本（而不是使用类似 tolower 的东西）与其一起存储，标识字符串的长度以找到所有具有 7 个字符的用户（而不是使用 len），等等。

请记住，为了性能，从第三范式回退是可以接受的，前提是您理解并减轻了其后果。

- paxdiablo

Answer 4

我刚学会的一个技巧。在 varchar 字段的排序语句中添加 '+0'：

SELECT * FROM table ORDER BY number+0 ASC

我现在看到了上面的答案。我想知道这是否是将字段和整数进行类型转换。我还没有进行性能比较。工作得很好。

- alexii

---

哦，没错。这也是一个旧的Perl技巧。我有另一种排序varchar字段以获得此类结果的方法，现在我会被诱惑用这个相当混乱但易于实现的WTDI来替换它。 - Sue Spence

如果列的数据类型是字符串，并且值只包含数字，则返回+1。如果值既包含数字又包含字母，则会失败。 - Lizesh Shakya

Answer 5

SELECT * FROM table ORDER BY number + 0

- shantanuo

---

这样数字就会转换为整数。好主意！ - Aamol

我还使用了+0.0来获取浮点数，以正确排序我的版本字段。谢谢！ - Lorna Mitchell

如果列数据类型为字符串并且值仅为数字，则返回+1。如果值为数字和字母，则会失败。 - Lizesh Shakya

Answer 6

对于类似Er353、ER 280、ER 30、ER36这样的值表，使用默认排序会得到以下结果： ER280 ER30 ER353 ER36

SELECT fieldname, SUBSTRING(fieldname, 1, 2) AS bcd, 
CONVERT(SUBSTRING(fieldname, 3, 9), UNSIGNED INTEGER) AS num 
FROM table_name
ORDER BY bcd, num;

结果将按照以下顺序呈现： ER30 ER36 ER280 ER353

- ChAnDu353

Answer 7

您可以使用以下SQL查询根据您的要求获取订单。

SELECT * FROM mytable ORDER BY ABS(mycol)

- Rabesh Lal Shrestha

Answer 8

给定一个包含VARCHAR的username列，其内容如下：

username1
username10
username100

可以这样做：

SELECT username,
CONVERT(REPLACE(username, 'username', ''), UNSIGNED INTEGER) AS N
FROM users u
WHERE username LIKE 'username%'
ORDER BY N;

它不便宜，但能胜任工作。

- minusf

Answer 9

SELECT * FROM table ORDER BY number ASC

应该显示你想要显示的内容...看起来你正在按id或number进行排序，但目前未定义为整数。

- Or Weinberger

---

你将 “number” 字段设置为 int 类型还是 varchar 类型了吗？ - Or Weinberger

@wow 为什么是 varchar？你能改变它吗，还是已经太晚了？ - moinudin

Answer 10

另一种选项是将数字保持在顶部，然后按字母顺序排序。

IF(name + 0, name + 0, 9999999), name

- Михаил Юдин

---

目前你的回答不够清晰，请编辑并添加更多细节，以帮助其他人理解它如何回答问题。你可以在帮助中心找到有关如何编写好答案的更多信息。 - Community