Mysql中，结合CONVERT函数和ROWNUM自定义变量自增，来将Varchar类型的数字进行排序

MySQL中int类型的字段排序的话是这样的：1,2,3,4,5,6,7,8,9,10

当字段类型为Varchar时，排序是这样的：1,10,2,3,4,5,6,7,8,9

那如何将Varchar类型排序变得和int一样呢？

话不多说，直接举例：

建表test

create table test
(
    id           varchar(100) PRIMARY KEY,        
    name         varchar(100)
)

数据

insert into test(id,name) values
('1','小一'),
('2','小二'),
('3','小三'),
('4','小四'),
('5','小五'),
('6','小六'),
('7','小七'),
('8','小八'),
('9','小九'),
('10','小十')

我们根据id进行排序查询：

select * from test order by id asc

效果如下：

我们可以发现，1的后面本来应该是2的，结果现在却是10

我们会使用两种函数来将10送到它该去的地方了

第一种：使用CONVERT函数，将id的varchar类型的数据，转为指定int类型的值

select * from test order by CONVERT(id,SIGNED) asc

第二种：使用CONVERT+ROWNUM函数，使用自定义变量自增，专门自定义一个排序字段

-- @rownum 为自定义变量
-- @rownum := 0 确定初始值
-- @rownum := @rownum +1 步长(每次+1)
select (@rowNum := @rowNum + 1) AS num,p2.* from (select * from test order by CONVERT(id,SIGNED) asc) p2,(SELECT (@rowNum := 0)) p1

查询完效果如下

想要了解CONVERT函数和ROWNUM函数想下接着看

CONVERT函数

CONVERT(expr,type), CONVERT(expr USING transcoding_name)
convert函数接受任意类型的数据，然后转为指定类型的值
第一个是导入字段名（类型），第二个值为想要转换的类型

CONVERT函数的type主要有以下几种：

• DATE

生成一个DATE类型数据

• DATETIME

生成一个DATETIME类型数据

• DECIMAL[(M[,D])]

生成一个DECIMAL类型数据，如果可选的M，D被指定，分别表示数据的最大值和最小精度

• DOUBLE

生成一个DOUBLE类型数据，mysql 8.0.17后可用

• FLOAT[(p)]

如果p没有指定，生成一个FLOAT类型数据，
如果0 <= < p <= 24，生成FLOAT
如果25 <= p <= 53，生成DOUBLE
如果p<0 或者 p>53，就会产生错误

• JSON

生成一个JSON类型的值

• NCHAR[(N)]

和CHAR类型，不过字符串使用自然排序，
和CHAR不一样的是，NCHAR不允许指定尾部字符信息

• REAL

生成一个REAL类型数据，如果开启了REAL_AS_FLOAT模式，实际类型就是FLOAT,否则就是DOUBLE

• SIGNED [INTEGER]

生成一个有符号整型

• TIME

生成一个TIME类型数据

• UNSIGNED [INTEGER]

生成一个无符号整型

ROWNUM自定义变量自增函数

通常使用方法是：通过定义自定义变量@rownum来保存表中的数据。通过赋值语句
@rownum:=@rownum + 1 来累加达到递增行号的需求