MySQL字符串排序实战：数字、百分比和文件名排序技巧

MySQL字符串排序实战：数字、百分比和文件名排序技巧

引用

CSDN

1.

https://blog.csdn.net/weixin_49114503/article/details/141053164

在数据库开发中，我们经常会遇到字符串类型存储了数字、百分比等数据的情况。如果直接使用varchar进行排序，往往无法得到预期的结果。本文将介绍如何在MySQL中对字符串进行数字排序、百分比排序以及从字符串中提取数字排序。

一、MySQL字符串按数字排序

假设我们有一张表test，其中number字段存储了数字字符串：

CREATE TABLE `test` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
  `number` varchar(10)  DEFAULT NULL COMMENT '数字'
);

insert into test(`number`) values
('89.01'),
('89.10'),
('100.01');

如果我们直接按照number字段进行排序：

select * from test  order by `number` desc;

结果可能不符合预期：

这是因为字符串排序是按照字符的ASCII码进行的。为了解决这个问题，我们可以使用CAST或CONVERT函数将字符串转换为数字类型：

方法一：转换为浮点数

select * from test order by cast(number as float) desc;
select * from test order by convert(number, float) desc;

方法二：转换为双精度数

select * from test order by cast(number as double) desc;
select * from test order by convert(number, double) desc;

方法三：转换为十进制数

select * from test order by cast(number as decimal(10,2)) desc;
select * from test order by convert(number, decimal(10,2)) desc;

如果字段中全是整数无小数点，也可以使用下面的方式进行排序：

select * from test order by cast(number as SIGNED) desc;
select * from test order by convert(number, SIGNED) desc;

二、MySQL字符串百分比排序

假设我们有一张表test，其中percent字段存储了百分比字符串：

CREATE TABLE `test` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
  `percent` varchar(10)  DEFAULT NULL COMMENT '百分比'
);

insert into test(percent) values
('89.01%'),
('89.10%'),
('100.01%');

我们可以使用以下方法进行排序：

方法一：直接转换为十进制数

select * from test order by cast(percent as decimal(10,2)) desc;
select * from test order by convert(percent , decimal(10,2)) desc;

方法二：先移除百分号再转换

select * from test order by cast(REPLACE(percent, '%', '') as decimal(10,2)) desc;
select * from test order by convert(REPLACE(percent, '%', '') , decimal(10,2)) desc;

三、从字符串中提取数字并排序

假设我们有一张表test1，其中file_name字段存储了文件名：

CREATE TABLE `test1` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
  `file_name` varchar(10)  DEFAULT NULL COMMENT '文件名'
);

insert into test1(file_name) values
('中文_1.txt'),
('中文_2.txt'),
('中文_10.txt'),
('中文_12.txt');

我们可以使用REGEXP_SUBSTR函数提取数字部分并排序：

SELECT *
FROM test1
WHERE file_name LIKE '%中文%'
ORDER BY CAST(REGEXP_SUBSTR(file_name , '\\d+') AS UNSIGNED);

这条SQL语句做了以下几件事：

• WHERE original_name LIKE '%中文%'：筛选出所有文件名包含“中文”的记录。
• REGEXP_SUBSTR(original_name, '\\d+')：从 original_name 中提取第一组连续的数字。
• CAST(... AS UNSIGNED)：将提取出的字符串转换成无符号整数，以便按数字进行排序。

排序结果如下：