一、主表主键ID设置为bigint类型,并设置为自动递增。
二、用navicat for mysql在mysql数据库创建存储过程proc_insertdata实现插入主表没有但临时表中有的数据
BEGIN
INSERT INTO alumni(realName,studentCode,startY,graduateTime,caption,nowpart,workUnit,duty,city,mobilephone,email) SELECT
ap.*
FROM
alumni_temp ap
LEFT JOIN alumni a ON a.realName=ap.realName AND a.nowpart=ap.nowpart AND LEFT(a.graduateTime,4)=LEFT(ap.graduateTime,4)
WHERE
a.realName IS NULL;
END
三、用navicat for mysql在mysql数据库创建存储过程proc_updatedata实现临时表最新数据补充更新主表除基础数据外的其他数据
BEGIN
UPDATE alumni a INNER JOIN alumni_temp ap ON a.realName=ap.realName AND a.nowpart=ap.nowpart AND LEFT(a.graduateTime,4)=LEFT(ap.graduateTime,4)
SET a.workUnit=ap.workUnit,a.duty=ap.duty,a.city=ap.city,a.mobilephone=ap.mobilephone,a.email=ap.email;
END
参考文献:https://blog.csdn.net/weixin_42659958/article/details/90298791