[数据库]SQL的cast函数实际使用场景

NullPointerZZZ

于 2024-04-19 22:26:50 发布

阅读量657

点赞数 13

分类专栏：数据库文章标签：其他

本文链接：https://blog.csdn.net/nullPoninter/article/details/137979434

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

业务场景:

现在需要对用户钱包流水记录表 sysadmin_wallet_flow_record (以下简称:wf表)

进行批量新增数据 , wf表有几个重要字段 pro_money(操作前金额) 和 after_money (操作后金额)和remarks(流水记录备注)

新增完数据后需要在前端视图页面 ,能看到这些刚刚新增的钱包流水记录,大概如下:

而查询的列表的接口 queryWalletFlowRecordList接口则需要保证查询出来的记录是一个线性顺序

线性顺序的定义:

上1条记录的after_money的值 , 必须是下1条记录的pro_money的值

以及最后1条记录的after_money 必须是这个用户钱包总金额(total_money)的值

遇到的问题:

由于wf表不是本人设计的 ,并且之前的业务也不是我写的 , 而且最主要的 ,之前新增这个流水记录时 ,都是1条条新增的,所以每条数据的创建时间都是不一样的 ,这是正常的, 那么此时queryWalletFlowRecordList接口 , 只需要根据创建时间createTime排序就可以完成线性顺序排序的需求, 也就是查询主表的sql 只需要这样写:

order by created_time desc

但是我现在写的新的业务, 是批量新增这个钱包流水记录的, 所以1个用户一次性会新增多个记录, 也就导致每个记录的创建时间是一样的 ,结果这些数据加到数据库之后, 我再去看前端的列表 ,此时列表的顺序就不满足线性顺序的需求了因为所有的创建时间都是一样的 ,列表就是乱的.

可能到这里就有人想问了:

"就算创建时间一样的, 那么MySQL也可以根据表的原顺序返回数据列表呀? 所以你只要保证插入数据时顺序是对的就ok "

但我想说 , 这个说法不完全正确 , 我这边经过一些测试后得到的一个答案是这样的:

在SQL查询时如果指定了一个排序规则 ,例如created_time desc 但是2个数据的created_time又是完全一样的 , 那么此时MySQL返回的数据顺序 , 其实会受到其他因素(索引?字节大小?主键id?) 的影响, 这个没有规律, 也就是是说并不是完全按照表内数据的顺序返回

这点我也问过GPT 但它的回答只能做个小小小小...参考 , 毕竟它老墙头草了你说它错了它就和你道歉并且立马改口

这里留下一个小疑惑: 如果完全未指定排序规则呢? 那么会完全按照表中的顺序返回吗?

ok , 解决上面的问题之后 , 可能又有人想问了

"你把创建时间弄成不一样不就行了?"

我只能说不是不行而是要弄的话风险太大为什么呢因为之前的 createTime一直是自动填充的 , 并且填充有2个策略

1个是数据库表字段自己定义的, 如下:

 `created_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',

1个是mybatis-plus框架定义的, 如下:

@TableField(fill = FieldFill.INSERT)
private Date createdTime;

所以说buff属实是拉满了 , 然后这里我也测试过 , 我自己写入了创建时间 , 结果您猜怎么着? 嘿被覆盖了

但是 , 是哪个策略覆盖的, 我也不清楚因为这2个策略的优先级 , 以及具体详细的使用方式我不知道

但我估计大概率是DEFAULT CURRENT_TIMESTAMP(3) 这个函数覆盖的

原因:

mybatis-plus是最新的技术不可能不去对值做非空判断 , 就直接强行填充

而DEFAULT CURRENT_TIMESTAMP(3) 这个函数非常老,

一些新版的MySQL都不支持这个函数了

所以我觉得是DEFAULTCURRENT_TIMESTAMP(3) 覆盖的

所以说到这里 , 你应该明白了 , 如果我不想让他覆盖大概率要去修改表结构这玩意谁敢啊到时候别的业务出了问题我死都不知道怎么死

吐槽:

        为什么设计wf表时不设计一个编号字段!!!! 有了编号字段直接根据编号排序就好了

(编号直接用时间戳填充,每次时间戳保证让其不一样就行了可以+1毫秒)

        好吧即使没有编号字段为什么主键不用自增像这种钱包流水记录表 , 很明显要用自增类型的主键吧. 啊喂!!!

给出的解决方案:

第一种方法(弃用): 更改主键的填充类型为递增 (改源码)

弃用原因: 我不想死

第二种方法(弃用): 使自动填充时间变得无效 , 而是手动指定创建时间的值 (改源码,改表结构)

弃用原因: 我想活

第三种方法(弃用): 修改排序条件 : 当创建时间createTime相同 , 根据 after_money 降序排序

<--  也就是由 -->
order by created_time desc 
<--  改为 -->
order by created_time desc , after_money desc

说一说为什么我会想到这个解决方案 , 因为我这次新增的钱包流水记录全部是正向的(增加金额) 没有负向的(减少金额) 所以每次的after_money是最接近最后的总金额的 , 那么我根据它降序排序其实就能保证线性顺序

弃用原因:

对后续业务侵入, 完全是为了满足查询当前业务产生的钱包流水记录而生 , 后续其他业务可能因为这个排序条件 , 导致更难查询 , 因为后续其他业务 , 可能有负向的钱包流水记录产生 , 而此时after_money desc 完全没有任何合理的理由对其他业务解释为什么是要这样排序

第四种方法(采用): 修改排序条件 : 当创建时间createTime相同根据 remarks(流水记录备注) 字段降序排序

<--  也就是由 -->
order by created_time desc 
<--  改为 -->
order by created_time desc , remarks desc

首先 remarks没有在前端展示过 (起码我没看到QAQ)

其次 remarks 存储的值很多样:

如果是售后产生的记录它会存储 "售后订单编号：zzzz123456575"

如果是火车票产生的记录它会存储: "购买火车票"

如果是购买商品产生的记录它会存储:"商品订单编号：aaaa123456575"

那么我只需要在我的这个业务 , 构建钱包流水记录时为每个数据的remarks字段写入不同的时间戳 , 然后再把排序sql改为上面这个SQL

这样的话 , 我用remarks字段排序也没问题吧我觉得是说的过去的毕竟这个字段本来就是设计出来随便造的

但是, 但是 , 问题又来了 remarks 是 varchar类型的 !!!

提问:

如果varchar类型的字段存储的是纯数字那么怎么根据这个字段进行数字型的排序? 而不是根据ascll码 (如果remarks是varchar类型直接写 remarks desc , 其实是根据这个字符串首个字符的 ascll码进行排序 , 这是完全不能满足当前业务需求的 )

答案就是SQL的cast函数 , cast函数可以把一个类型转为另外一个类型

没想到吧这里才开始正文真辛苦你看到这了 .....

因此最终代码改为

<--  由 -->
order by created_time desc 
<--  改为 -->
order by created_time desc , CAST(remarks AS SIGNED) desc

并且我在这里还测试了其他情况(做过测试):

" CAST(remarks AS SIGNED) desc "

代表将remarks字段(varchar类型) 转为数字并且进行降序排序

如果remarks字段不包含数字例如: ABC你好那么不会参与排序

如果remarks字段是前部分是数字例如: 123ABC你好那么只会使用123 作为排序参与值

如果remarks字段是后部分是数字例如: ABC你好123 那么不会参与排序

如果remarks字段是中部分是数字例如: ABC123你好那么不会参与排序

总结: