最近在做调度时，需要生成中间表数据，使用sql如下：
-- 脚本1 INSERT INTO ct_zr_pay_paid_overdue_payment_record ( created_time) SELECT now() FROM (business_contract bc, fund_channel fc, acct_loan al, acct_payment_schedule aps) LEFT JOIN plan_payment_way ppw ON aps.serialno = ppw.serialno WHERE bc.fundno = fc.fundno AND bc.serialno = al.serialno AND bc.serialno = aps.objectno AND bc.orgId = 'EDU2017071000000002' AND date_format('20200211', '%Y%m') = date_format(aps.finishdate, '%Y%m') AND aps.finishdate < date_format('20200211', '%Y/%m/%d') -- 脚本2 INSERT INTO ct_zr_pay_paid_overdue_payment_record ( created_time) SELECT now() FROM (business_contract bc, fund_channel fc, acct_loan al, acct_payment_schedule aps) LEFT JOIN plan_payment_way ppw ON aps.serialno = ppw.serialno WHERE bc.fundno = fc.fundno AND bc.serialno = al.serialno AND bc.serialno = aps.objectno AND bc.orgId = '' AND date_format('20200211', '%Y%m') = date_format(aps.finishdate, '%Y%m') AND aps.finishdate < date_format('20200211', '%Y/%m/%d')
在执行时两个脚本查询出的数据都是0条，但是脚本1报错，脚本2不报错，脚本1报错：Incorrect datetime value: ''，经过排查，可以通过两个方法避免报错问题，但生产上还是需要用方法二
-- 方法一 -- 允许无效格式日期时间 -- 详细说明见stackOverFlow: --https://stackoverflow.com/questions/22806870/incorrect-datetime-value-database-error-number-1292 SET SESSION SQL_MODE='ALLOW_INVALID_DATES' -- 方法二 -- 在查询后在union all一个空值即可 INSERT INTO ct_zr_pay_paid_overdue_payment_record ( created_time) SELECT now() FROM (business_contract bc, fund_channel fc, acct_loan al, acct_payment_schedule aps) LEFT JOIN plan_payment_way ppw ON aps.serialno = ppw.serialno WHERE bc.fundno = fc.fundno AND bc.serialno = al.serialno AND bc.serialno = aps.objectno AND bc.orgId = 'EDU2017071000000002' AND date_format('20200211', '%Y%m') = date_format(aps.finishdate, '%Y%m') AND aps.finishdate < date_format('20200211', '%Y/%m/%d') union all select now() from ct_zr_pay_paid_overdue_payment_record where 1=2
