使用13位时间戳作为逻辑删除标识

Blade 未结 1 984
10114571
10114571 剑圣 2021-04-12 15:07

一、该问题的重现步骤是什么?

1. 选定某一个微服务,选定一张表T_aaa,修改逻辑删除字段is_deleted类型为bigint(13)

2. 在对应微服务的application-.yml配置中增加如下配置

:
  :
    :
      : REPLACE(unix_timestamp(current_timestamp(3)),'.','')
      : 0

3.重启服务后,从页面发起表T_aaa的某一条数据的删除逻辑


二、你期待的结果是什么?实际看到的又是什么?

期望:对应数据进行正常的逻辑删除,且逻辑删除标识为13位时间戳

实际报错信息如下:

2021-04-12 14:04:36.351 ERROR 65809 --- [  XNIO-1 task-5] o.s.c.l.e.BladeRestExceptionTranslator   : 服务器异常


org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 

### Error updating database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: UPDATE sy_banks SET is_delete=REPLACE(unix_timestamp(current_timestamp(3)),'.','') WHERE id IN (  

?

 )  AND is_delete=0

### The error may exist in org/springblade/direct/mapper/BanksMapper.java (best guess)

### The error may involve org.springblade.direct.mapper.BanksMapper.deleteBatchIds

### The error occurred while executing an update

### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: UPDATE sy_banks SET is_delete=REPLACE(unix_timestamp(current_timestamp(3)),'.','') WHERE id IN (  

?

 )  AND is_delete=0

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)

at com.sun.proxy.$Proxy154.update(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:288)

at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:65)

at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)

at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)

at com.sun.proxy.$Proxy168.deleteBatchIds(Unknown Source)

at com.baomidou.mybatisplus.extension.service.IService.removeByIds(IService.java:139)

at org.springblade.core.mp.mybase.MyBaseServiceImpl.deleteLogic(MyBaseServiceImpl.java:102)

at org.springblade.core.mp.mybase.MyBaseServiceImpl$$FastClassBySpringCGLIB$$e2b44c90.invoke(<generated>)

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)

at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:119)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)

at org.springblade.direct.service.impl.BanksServiceImpl$$EnhancerBySpringCGLIB$$646e45df.deleteLogic(<generated>)


三、你正在使用的是什么产品,什么版本?在什么操作系统上?

产品:BladeX

版本:2.8.0

操作系统:macOS


四、请提供详细的错误堆栈信息,这很重要。


五、若有更多详细信息,请在下面提供。

1条回答
  • 2021-04-12 16:01

    长度不够,并且不推荐这么改。is_deleted是一个状态字段标注是否已删除,你改成时间戳是没意义的。

    如果要改的话,推荐你新增一个delete_time字段,然后类型为timestamp,把时间戳给这个字段才是合理的

    image.png

    image.png

    作者追问:2021-04-12 19:03

    当前逻辑以0和1标识的话,无法进行对表字段添加唯一索引,
    这种情况,对于大数据量表的数据插入,还要进行一次判重,甚至多次判重,对性能影响较大

    回答: 2021-04-13 14:56

    想做唯一索引就不要用逻辑删除插件了,自定义删除方法,去掉is_deleted字段,同时新增镜像表

    删除主表数据的时候复制一份到镜像表,那么也能实现逻辑删除,主表你再去做唯一索引

    0 讨论(1)
提交回复