数据库-多步操作产生错误，请检查每一步的状态值

最新推荐文章于 2024-07-08 14:40:47 发布

原创最新推荐文章于 2024-07-08 14:40:47 发布 · 5.3k 阅读

0 ·

CC 4.0 BY-SA版权

Orale MySQL 专栏收录该内容

73 篇文章

订阅专栏

在数据迁移过程中遇到错误，通过对比字段类型和长度未发现问题。深入检查发现，存在birthday字段值超出常规范围，包含1900年前的不正确数据。通过设置查询过滤条件（生日>=1900年1月1日），解决了问题，并在迁移脚本中添加此条件确保迁移正常。

问题描述：在进行数据迁移过程中报的错，百度也找不到准确答案，哎，自己摸索吧

--按照id排序，导入成功4045条数据，后面就报错：多步操作产生错误，请检查每一步的状态值
[DSRzhizhi]
OrgDBName=zhizhilaoxitong
OrgTable=TC_DUD_PARTY
TarDBName=zhengshi
TarTable=T_XZ_PARTY
IsValid=1
IsTime=
OrgSign=
TarSign=
sGetSQL=select id,caseid,partytypeid,name,personid,sex,birthday,contact,address,diploma,partyrace,driverlicensetypeid,driverlicensedocid,driverlicenseissueoffice,businesslicensetype,businesslicensecode,partyunit,unitaddress,corporation,corperationsex,corperationaddress,corperationcontact,corperationidnumber,updatetime,updateaccountid,state,workunit,workunitaddr,businesslicensetypecontent from TC_DUD_PARTY order by id
sAddSQL=insert into T_XZ_PARTY (PARTY_ID,CASE_ID,party_type,party_name,party_idnum,party_sex,party_birth,party_contno,party_addr,party_edudegree,party_folk,types_driv_license,driver_licenseno,DRIVER_LICENSEISSUE,qc_category,qc_no,company_name,unit_address,legal_name,CORPERATIONSEX,corperationaddress,corperationcontact,corperationidnumber,modify_time,modify_person,ISESCAPE,party_post,unitaddr,businesslicensetypecontent,ZHIDUI,LAOXITONG) Values (:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,2,1)
TbTimeGap=5000
SWhereSQL=
SUpdateSQL=

对字段类型、长度都进行了对比，没有什么问题，难道是日期字段超出了范围么（机智^_^）

于是取4045后面的几条数据检查：

select id,
       caseid,
       partytypeid,
       name,
       personid,
       sex,
       birthday,
       contact,
       address,
       diploma,
       partyrace,
       driverlicensetypeid,
       driverlicensedocid,
       driverlicenseissueoffice,
       businesslicensetype,
       businesslicensecode,
       partyunit,
       unitaddress,
       corporation,
       corperationsex,
       corperationaddress,
       corperationcontact,
       corperationidnumber,
       updatetime,
       updateaccountid,
       state,
       workunit,
       workunitaddr,
       businesslicensetypecontent
 from (
select id,
       caseid,
       partytypeid,
       name,
       personid,
       sex,
       birthday,
       contact,
       address,
       diploma,
       partyrace,
       driverlicensetypeid,
       driverlicensedocid,
       driverlicenseissueoffice,
       businesslicensetype,
       businesslicensecode,
       partyunit,
       unitaddress,
       corporation,
       corperationsex,
       corperationaddress,
       corperationcontact,
       corperationidnumber,
       updatetime,
       updateaccountid,
       state,
       workunit,
       workunitaddr,
       businesslicensetypecontent,
       row_number() over(order by id) rn
  from TC_DUD_PARTY
  order by id) where rn between 4044 and 4048

果然发现问题，birthday这个字段值不对，随便查询下就查出了许多错误的数据

1900年到现在已经118岁了，应该足够了，小于1900年的都属于脏数据，所以查询的时候要进行过滤

where t.birthday >= to_date('1900/1/1 00:00:00', 'yyyy-MM-dd HH24:mi:ss')

select id,
       caseid,
       partytypeid,
       name,
       personid,
       sex,
       birthday,
       contact,
       address,
       diploma,
       partyrace,
       driverlicensetypeid,
       driverlicensedocid,
       driverlicenseissueoffice,
       businesslicensetype,
       businesslicensecode,
       partyunit,
       unitaddress,
       corporation,
       corperationsex,
       corperationaddress,
       corperationcontact,
       corperationidnumber,
       updatetime,
       updateaccountid,
       state,
       workunit,
       workunitaddr,
       businesslicensetypecontent
 from (
select id,
       caseid,
       partytypeid,
       name,
       personid,
       sex,
       birthday,
       contact,
       address,
       diploma,
       partyrace,
       driverlicensetypeid,
       driverlicensedocid,
       driverlicenseissueoffice,
       businesslicensetype,
       businesslicensecode,
       partyunit,
       unitaddress,
       corporation,
       corperationsex,
       corperationaddress,
       corperationcontact,
       corperationidnumber,
       updatetime,
       updateaccountid,
       state,
       workunit,
       workunitaddr,
       businesslicensetypecontent,
       row_number() over(order by id) rn
  from TC_DUD_PARTY t where t.birthday >= to_date('1900/1/1 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
  order by id) where rn between 4044 and 4048

最后别忘了对迁移脚本加上过滤条件：

[DSRzhizhi]
OrgDBName=zhizhilaoxitong
OrgTable=TC_DUD_PARTY
TarDBName=zhengshi
TarTable=T_XZ_PARTY
IsValid=1
IsTime=
OrgSign=
TarSign=
sGetSQL=select id,caseid,partytypeid,name,personid,sex,birthday,contact,address,diploma,partyrace,driverlicensetypeid,driverlicensedocid,driverlicenseissueoffice,businesslicensetype,businesslicensecode,partyunit,unitaddress,corporation,corperationsex,corperationaddress,corperationcontact,corperationidnumber,updatetime,updateaccountid,state,workunit,workunitaddr,businesslicensetypecontent from TC_DUD_PARTY where birthday >= to_date('1900/1/1 00:00:00', 'yyyy-MM-dd HH24:mi:ss') order by id
sAddSQL=insert into T_XZ_PARTY (PARTY_ID,CASE_ID,party_type,party_name,party_idnum,party_sex,party_birth,party_contno,party_addr,party_edudegree,party_folk,types_driv_license,driver_licenseno,DRIVER_LICENSEISSUE,qc_category,qc_no,company_name,unit_address,legal_name,CORPERATIONSEX,corperationaddress,corperationcontact,corperationidnumber,modify_time,modify_person,ISESCAPE,party_post,unitaddr,businesslicensetypecontent,ZHIDUI,LAOXITONG) Values (:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,2,1)
TbTimeGap=5000
SWhereSQL=
SUpdateSQL=

测试OK~~