mysql 字符串特殊字符_在MySQL中从具有字符串值(字符串,数字和特殊字符)的列中获取特定记录...
为此,您可以使用ORDER BY CAST()。让我们看一个例子-mysql> create table DemoTable2006
(
UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
UserCode varchar(20)
);
使用插入命令在表中插入一些记录-mysql> insert into DemoTable2006(UserCode) values('John_12');
mysql> insert into DemoTable2006(UserCode) values('John_34');
mysql> insert into DemoTable2006(UserCode) values('John_56');
mysql> insert into DemoTable2006(UserCode) values('Chris_101');
mysql> insert into DemoTable2006(UserCode) values('Chris_103');
mysql> insert into DemoTable2006(UserCode) values('Chris_106');
使用select语句显示表中的所有记录-mysql> select * from DemoTable2006;
这将产生以下输出-+--------+-----------+
| UserId | UserCode |
+--------+-----------+
| 1 | John_12 |
| 2 | John_34 |
| 3 | John_56 |
| 4 | Chris_101 |
| 5 | Chris_103 |
| 6 | Chris_106 |
+--------+-----------+
6 rows in set (0.00 sec)
这是从列中获取特定记录的查询-mysql> select * from DemoTable2006
where UserCode like 'John%'
order by cast(substring(UserCode from 7) as signed) desc
limit 1;
这将产生以下输出-+--------+----------+
| UserId | UserCode |
+--------+----------+
| 3 | John_56 |
+--------+----------+
1 row in set (0.00 sec)