1. rds 스냅샷

2. mysql에서 db 생성 후 data 삽입

create database original_DB;

use original_DB;

create table member_info
(
    mem_name varchar(20) primary key,
    job_code varchar(10) not null,
    start_datetime datetime,
    end_datetime datetime
);


insert into member_info(mem_name,job_code,start_datetime,end_datetime) values ('궁예','왕',str_to_date('20200401','%Y%m%d'),str_to_date('20210330','%Y%m%d'));
insert into member_info(mem_name,job_code,start_datetime,end_datetime) values ('사달라','외화벌이',str_to_date('20180401','%Y%m%d'),str_to_date('20220330','%Y%m%d'));
insert into member_info(mem_name,job_code,start_datetime,end_datetime) values ('이토히로부미','강도',str_to_date('20191231','%Y%m%d'),str_to_date('20200417','%Y%m%d'));
insert into member_info(mem_name,job_code,start_datetime,end_datetime) values ('펭수','웅변가',str_to_date('20210222','%Y%m%d'),str_to_date('20220331','%Y%m%d'));
insert into member_info(mem_name,job_code,start_datetime,end_datetime) values ('제프베조스','부자',str_to_date('20170321','%Y%m%d'),str_to_date('20311211','%Y%m%d'));
insert into member_info(mem_name,job_code,start_datetime,end_datetime) values ('한치두치세치네치','볶음밥볶음밥',str_to_date('20130102','%Y%m%d'),str_to_date('20200410','%Y%m%d'));
insert into member_info(mem_name,job_code,start_datetime,end_datetime) values ('송강호','운전기사',str_to_date('20191230','%Y%m%d'),str_to_date('20210430','%Y%m%d'));
insert into member_info(mem_name,job_code,start_datetime,end_datetime) values ('정은','폭탄성애자',str_to_date('20200321','%Y%m%d'),str_to_date('20200413','%Y%m%d'));

3. 2020년 4월 15일에 일한 사람을 검색하는 sql 쿼리

select * from member_info
where start_datetime<=str_to_date('20200415','%Y%m%d') && end_datetime>=str_to_date('20200415','%Y%m%d');

4. ON DUPLICATE KEY UPDATE로 테이블 백업하기

CREATE DATABASE log_DB;

create table log_DB.member_info AS select * from original_DB.member_info;

select * from log_DB.member_info;

delete from member_info where start_datetime < str_to_date('20200416','%Y%m%d');
update member_info set job_code='우주대스타' where mem_name='펭수';

INSERT INTO original_DB.member_info
SELECT * FROM log_DB.member_info
ON DUPLICATE KEY UPDATE
  original_DB.member_info.mem_name = log_DB.member_info.mem_name,
  original_DB.member_info.job_code = log_DB.member_info.job_code,
  original_DB.member_info.start_datetime = log_DB.member_info.start_datetime,
  original_DB.member_info.end_datetime = log_DB.member_info.end_datetime;
  -- ... 다른 필드들도 동일하게 업데이트

select * from original_DB.member_info;