MySQL列按字母数字排序，忽略最高值

Question

在MySQL中，我有一个名为custom_meta的表，其中我获得的值如

id   meta_value
1    USNEWYORK01
2    USNEWYORK02
3    USNEWYORK03
4    USNEWYORK04
5    USNEWYORK05
6    USNEWYORK06
7    USNEWYORK07
8    USNEWYORK10
9    USNEWYORK14
10   USNEWYORK16
11   USNEWYORK20
12   USNEWYORK21
13   USNEWYORK32
14   USNEWYORK45
15   USNEWYORK56
16   USNEWYORK78
17   USNEWYORK68
18   USNEWYORK69
19   USNEWYORK80
20   USNEWYORK90
21   USNEWYORK99
22   USNEWYORK100
23   USNEWYORK45
24   USNEWYORK101

现在我想获得的meta_value与最高的数字。在表格中你可以看到最高的是USNEWYORK101。为了得到它，我输入了这样的查询

SELECT meta_value from custom_meta ORDER BY meta_value DESC LIMIT 1

但它总是得到USNEWYORK99。我也试过使用CAST，但这也不起作用。

谁能帮我把这个做完?如有任何建议和建议，我将不胜感激。

谢谢。

Answer 1

SELECT id,
meta_value,
Cast(Substring(meta_value, 10, Length(meta_value)) AS UNSIGNED)
FROM   custom_meta
ORDER  BY 3 DESC
LIMIT  1; 

Answer 2

对于较新版本的MySQL，您可以使用REGEXP_SUBSTR()函数，但由于您使用的是5.7，我们可以使用其他方式。

假设你总是在你的值的开始有USNEWYORK，你可以摆脱它，只留下之后使用SUBSTRING()函数。然后需要执行CAST()将文本转换为数值，以便排序正常工作。

示例数据

create table custom_meta(meta_value varchar(255));
insert into custom_meta(meta_value) values
('USNEWYORK01'),('USNEWYORK02'),('USNEWYORK45'),('USNEWYORK99'),('USNEWYORK101');

<<p>解决方案/strong>

select
meta_value
from
custom_meta
order by
cast(substring(meta_value, 10) as unsigned) desc
limit 1

meta_value 
USNEWYORK101 

Answer 3

将其替换并强制转换为int:

1. 如果前缀是FİXED到USNEWYORK:

---

select * from test 
order by  CAST(REPLACE(meta_value, "USNEWYORK", "") AS SIGNED) desc;

---

2. IF前缀不固定，可以是任何东西，mysqldb 8.0

---

select * from test 
order by  CAST(REGEXP_REPLACE(meta_value, '[^0-9]', '') AS SIGNED) desc;

---

3. IF Prefix不固定，可以是任何值，mysqldb 5.7你可以创建下面解释的函数，并像这样使用它:

---

select * from test 
order by  CAST(STRIP_NON_DIGIT(meta_value) AS SIGNED) desc;