环境:
Mysql8.0.24
表syrk_rkjbxx与weijianwei_syrk_jbdjxx表的索引皆是不规范的身份证号(syrk_gmsfhm)。
1.两表关联update,对比后发现第二种好用,比较快:
EXPLAIN UPDATE syrk_rkjbxx syrk_rkjbxx
LEFT JOIN weijianwei_syrk_jbdjxx
ON syrk_rkjbxx.syrk_gmsfhm = weijianwei_syrk_jbdjxx.syrk_gmsfhm
SET
syrk_rkjbxx.syrk_csrq = weijianwei_syrk_jbdjxx.syrk_csrq,
syrk_rkjbxx.syrk_jkzk = weijianwei_syrk_jbdjxx.syrk_jkzk,
syrk_rkjbxx.syrk_swsj = weijianwei_syrk_jbdjxx.syrk_swsj,
syrk_rkjbxx.update_time = CURRENT_TIMESTAMP;
EXPLAIN UPDATE syrk_rkjbxx,weijianwei_syrk_jbdjxx
SET
syrk_rkjbxx.syrk_csrq = weijianwei_syrk_jbdjxx.syrk_csrq,
syrk_rkjbxx.syrk_jkzk = weijianwei_syrk_jbdjxx.syrk_jkzk,
syrk_rkjbxx.syrk_swsj = weijianwei_syrk_jbdjxx.syrk_swsj,
syrk_rkjbxx.update_time = CURRENT_TIMESTAMP
WHERE
syrk_rkjbxx.syrk_gmsfhm = weijianwei_syrk_jbdjxx.syrk_gmsfhm;
2.某种特殊情况下exists和in,发现第二种好用,比较快:
EXPLAIN SELECT
syrk_zjlx,
syrk_zjhm,
syrk_gmsfhm,
syrk_xm,
syrk_csrq,
syrk_jkzk,
syrk_swsj,
CURRENT_TIMESTAMP AS update_time
FROM
weijianwei_syrk_jbdjxx
WHERE
NOT EXISTS (
SELECT
syrk_rkjbxx.syrk_gmsfhm
FROM
syrk_rkjbxx
WHERE
syrk_rkjbxx.syrk_gmsfhm = weijianwei_syrk_jbdjxx.syrk_gmsfhm
OR weijianwei_syrk_jbdjxx.is_delete != 1
)
EXPLAIN SELECT
syrk_zjlx,
syrk_zjhm,
syrk_gmsfhm,
syrk_xm,
syrk_csrq,
syrk_jkzk,
syrk_swsj,
CURRENT_TIMESTAMP AS update_time
FROM
weijianwei_syrk_jbdjxx
WHERE
weijianwei_syrk_jbdjxx.is_delete != 1
AND weijianwei_syrk_jbdjxx.syrk_gmsfhm NOT IN ( SELECT syrk_rkjbxx.syrk_gmsfhm FROM syrk_rkjbxx )