MySQL中的带字母数字排序

假设您在表中有一个包含字符串值且数字在右侧的VARCHAR列。例如：

John1023
Carol9871
David9098

现在，考虑您想要按整个列中的这些右侧数字进行排序。为此，请使用ORDER BY RIGHT。

让我们首先创建一张表 –

mysql> create table DemoTable757 (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ClientId varchar(100)
);
Query OK, 0 rows affected (0.53 sec)

使用插入命令将一些记录插入表中 –

mysql> insert into DemoTable757(ClientId) values('John1023');
Query OK, 1 row affected (0.41 sec)
mysql> insert into DemoTable757(ClientId) values('Carol9871');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable757(ClientId) values('David9098');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable757(ClientId) values('Adam9989');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable757(ClientId) values('Bob9789');
Query OK, 1 row affected (0.20 sec)

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

mysql> select *from DemoTable757;

这将产生以下输出 –

+----+-----------+
| Id | ClientId  |
+----+-----------+
|  1 | John1023  |
|  2 | Carol9871 |
|  3 | David9098 |
|  4 | Adam9989  |
|  5 | Bob9789   |
+----+-----------+
5 rows in set (0.00 sec)

以下是MySQL中带有字母数字顺序的查询 –

mysql> select Id,ClientId from DemoTable757 order by right(ClientId,4);

这将产生以下输出 –

+----+-----------+
| Id | ClientId  |
+----+-----------+
|  1 | John1023  |
|  3 | David9098 |
|  5 | Bob9789   |
|  2 | Carol9871 |
|  4 | Adam9989  |
+----+-----------+
5 rows in set (0.00 sec)