sql server cast函数_「SQL优化」varchar字符串按照int整型排序的实现方案及性能对比

「SQL小贴士」Mysql将varchar字符串按照int整型排序的实现方案和性能对比

前言

在设计表的时候将用户的“会员卡号”创建为varchar类型，实际上用户的卡号是有规则的数字类型。这样的设计在使用过程中并没有出现问题，但是最近要根据“会员开好”进行排序的时候发现排序的结果有问题，具体信息如下：

SQL示例：select * from user order by member_no desc limit 100;查询结果：+-----------+| member_no |+-----------+| 9000      | | 8000      | | 7000      | | 60000     | | 50000     | | 4000      | | 3000      | | 2000      | | 1000      | +-----------+

通过查询的结果发现并不是我们想想中根据数字的大小按照倒叙展示，由此发现SQL中varchar类型要进行格式化为数字类型才能达到目标。

方案一：手动转换类型

手动转换类型常用做法有两种：

SQL示例1：select * from user order by member_no+0 desc limit 100; // 补0

SQL示例2：select * from user order by member_no*1 desc limit 100; // 乘1

经测试可以达到预期效果。

方案二：函数CAST/CONVERT

SQL示例1：select * from user order by CAST(member_no as SIGNED) desc desc limit 100; // CAST

SQL示例2：select * from user order by CONVERT(member_no,SIGNED) desc limit 100; // CONVERT

经测试可以达到预期效果。

性能对比

那这几种方法的性能有无差别呢？我来测试一下.....

第一步：准备数据

如上图所示，准备了字段20+，数量600万+的测试数据。

第二步：依次执行SQL

+-----------++-----------+| member_no |+-----------+| 9000      | +-----------+

由此可见函数的写法要比手动转换的写法耗时要少，两种函数(CAST、CONVERT)耗时接近。