zhumj/db/migrate001.sql

drop table if exists member_info;
create table member_info
(
    id_no       varchar(20)    not null,
    created_at  datetime,
    created_by  varchar(255),
    modified_at datetime,
    modified_by varchar(255),
    address     varchar(255),
    money       decimal(10, 2) not null,
    name        varchar(255),
    phone       varchar(255),
    sex         varchar(255),
    primary key (id_no)
) engine = InnoDB;
alter table deposit_refund_apply
    add column user_id bigint;
alter table room_rate
    add column day_price decimal(10, 2);
alter table user
    add column expire_at datetime;
alter table user
    add column temporal bit not null;
alter table user
    modify id_no varchar(20) null;
alter table deposit_record
    add column id_no varchar(20);
alter table individual_rent
    add column id_no varchar(20) not null;
alter table personal_fee
    add column id_no varchar(20) not null;
alter table recharge_record
    add column id_no varchar(20);
alter table user_money_record
    add column id_no varchar(20);
# alter table user add constraint FKr3smmmn5papqgokju1m26wjj8 foreign key (id_no) references member_info (id_no);

update user_money_record join user on user_money_record.user_id = user.id
set user_money_record.id_no = ifnull(user.id_no, '');

update deposit_record join user on deposit_record.user_id = user.id
set deposit_record.id_no = ifnull(user.id_no, '');

update individual_rent join user on individual_rent.user_id = user.id
set individual_rent.id_no = ifnull(user.id_no, '');

update personal_fee join user on personal_fee.user_id = user.id
set personal_fee.id_no = ifnull(user.id_no, '');

update recharge_record join user on recharge_record.user_id = user.id
set recharge_record.id_no = ifnull(user.id_no, '');

insert into member_info(id_no, created_at, created_by, modified_at, modified_by, address, money, name, phone, sex)
SELECT id_no,
       now(),
       'root(69)',
       now(),
       'root(69)',
       address,
       0,
       name,
       phone,
       sex
from checkin_info
where id_no is not null
group by id_no;

update member_info join user on member_info.id_no = user.id_no
set member_info.money = user.money;