mysql字符串按数字排序、mysql字符串百分比排序

一、前言

在实际项目中，我们可能会遇到数据库中用字符串类型存储了数字、百分比等数据。若直接用varchar进行排序可能并不是我们想要的效果，以下整理一下字符串如何按数字进行排序。

Mysql提供了两种类型转换函数，可以将字符串转换为执行类型(如数字类型)。相关内容参考Mysql convert函数、 Mysql cast函数

示例数据：

CREATE TABLE `test` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
  `number` varchar(10)  DEFAULT NULL COMMENT '数字',
  `percent` varchar(10)  DEFAULT NULL COMMENT '百分比'
) ENGINE=InnoDB AUTO_INCREMENT=1;

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

select * from test  order by `number` desc;

可以发现数字最大的却排在最下面。

二、mysql字符串按数字排序

对上面的示例中的number字段进行排序

# 方法一：将number字段转换为float类型。 需mysql版本8.0.17及以上
select * from test order by cast(number as float) desc
select * from test order by convert(number, float) desc

# 方法二：将number字段转换为double类型。 需mysql版本8.0.17及以上
select * from test order by cast(number as double) desc
select * from test order by convert(number, double) desc

# 方法三：将number字段转换为decimal类型
select * from test order by cast(number as decimal(10,2)) desc
select * from test order by convert(number, decimal(10,2)) desc

排序结果如下：

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

# 将number字段转换为整数类型
select * from test order by cast(number as SIGNED) desc
select * from test order by convert(number, SIGNED) desc

三、mysql字符串百分比排序

对上面的示例中的percent字段进行排序

# 方法一：将number字段转换为decimal类型
-- 经测试后发现转换成decimal的时候默认会把%去掉，不受影响，若有影响可使用下面的方式，先把%去掉
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

在上面的sql中：

• REPLACE(percent, '%', '') ：移除百分比字符串中的百分号。
• CAST(... AS DECIMAL(10,2)) “”将结果转换为DECIMAL类型，其中10是总位数（包括小数点），2是小数位数。你可以根据需要调整这些值。

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

1.如下，按文件名中的数字进行排序

让我们先看几个 file_name的示例值：

• 中文_1.mp4
• 中文_12.mp4
• 中文_2.mp4
• 中文_10.mp4

默认按file_name升序排序如下：

数据准备

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

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

使用 REGEXP_SUBSTR 函数提取并排序
在MySQL 8.0及以上版本中，我们可以使用 REGEXP_SUBSTR() 函数来提取字符串中的数字部分。这个函数允许我们使用正则表达式来指定我们想要匹配的模式。在这个例子中，我们使用正则表达式 \d+ 来匹配一个或多个数字。

以下是完整的SQL查询，用于实现按数字排序：

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)：将提取出的字符串转换成无符号整数，以便按数字进行排序。

排序结果如下：