mysql数据库自动转储_mysql数据库数据定时封装转储

1.封装转储存储过程

CREATE  PROCEDURE `add_resident_portrait`()

BEGIN

TRUNCATE TABLE resident_portrait;

INSERT INTO resident_portrait (

vehicleNumber,

parkid,

vehicleType,

vehicleBrand,

vehiclePhoto,

vehicleColor,

vehicleResidentFlag,

estate_address,

building_code,

unit_code,

floor,

areasize,

house_number,

estate_type,

delivery_time,

checkin_time,

rent_flag,

volResource,

volIntro,

checkStatus,

volunteerType,

signTime,

nation,

marr_status,

edu_status,

belief,

height,

weight,

resaddr,

restype,

healths,

volunid,

mail,

wechat,

huimg_url,

blood,

resimg_url,

workplace,

profession,

address,

phone_num,

mobnum,

political,

photo_url,

street,

cu,

area,

pro,

build,

NAME,

sex,

birthday,

idcard,

fatherid,

motherid

) SELECT

#车牌号

vehicleinfo.vehicleNumber,

#停车场编号

vehicleinfo.parkid,

#车辆类型

vehicleinfo.vehicleType,

#车辆品牌

vehicleinfo.vehicleBrand,

#车辆照片

vehicleinfo.vehiclePhoto,

#车辆颜色

vehicleinfo.vehicleColor,

#车辆业主属性

vehicleinfo.vehicleResidentFlag,

#房产地址

pr_estate_info.estate_address,

#楼号

pr_estate_info.building_code,

#单元号

pr_estate_info.unit_code,

#楼层

pr_estate_info.floor,

#面积

pr_estate_info.area,

#门牌号

pr_estate_info.house_number,

#房产类型

pr_estate_info.estate_type,

#交房时间

pr_estate_info.delivery_time,

#入住时间

pr_estate_info.checkin_time,

#是否出租

pr_estate_info.rent_flag,

#志愿者资质

volunteerinfo.volResource,

#志愿者可提供服务简介

volunteerinfo.volIntro,

#志愿者审核状态

volunteerinfo.checkStatus,

#志愿者类型

volunteerinfo.volunteerType,

#志愿者注册时间

volunteerinfo.signTime,

#民族

cu_resinfo.nation,

#婚姻状态

cu_resinfo.marr_status,

#教育情况

cu_resinfo.edu_status,

#宗教信仰

cu_resinfo.belief,

#身高

cu_resinfo.height,

#体重

cu_resinfo.weight,

#户籍地址

cu_resinfo.resaddr,

#户籍类型

cu_resinfo.restype,

#健康情况

cu_resinfo.healths,

#志愿者id

cu_resinfo.volunid,

#邮箱

cu_resinfo.mail,

#微信

cu_resinfo.wechat,

#房产证照片地址

cu_resinfo.huimg_url,

#血型

cu_resinfo.blood,

#户口本照片地址

cu_resinfo.resimg_url,

#工作单位

cu_resinfo.workplace,

#职业

cu_resinfo.profession,

#地址

cu_resinfo.address,

#固定电话

cu_resinfo.phone_num,

#手机

cu_resinfo.mobnum,

#政治面貌

cu_resinfo.political,

#照片地址

cu_resinfo.photo_url,

#所属街道

cu_resinfo.street,

#所属社区

cu_resinfo.cu,

#所属小区

cu_resinfo.area,

#所属物业

cu_resinfo.pro,

#所属楼宇

cu_resinfo.build,

#姓名

cu_resinfo. NAME,

#性别

cu_resinfo.sex,

#生日

cu_resinfo.birthday,

#身份证号

cu_resinfo.idcard,

#父亲id

cu_resinfo.fatherid,

#母亲id

cu_resinfo.motherid

FROM

cu_resinfo,

pr_estate_info,

vehicleinfo,

volunteerinfo

WHERE

cu_resinfo.id = vehicleinfo.staffid

AND cu_resinfo.id = pr_estate_info.staffid

AND cu_resinfo.volunid = volunteerinfo.id;

END

2.定时执行任务

CREATE EVENT

dump_resident_portrait

ON SCHEDULE EVERY 7 DAY

STARTS '2018-09-02 00:00:00' DO

CALL add_resident_portrait;