create table if not exists talent_card_advisor (
|
id bigint not null auto_increment,
|
card_level varchar(64) not null,
|
advisor_name varchar(64) not null,
|
department varchar(128) default null,
|
mobile varchar(20) default null,
|
consult_url varchar(500) default null,
|
status tinyint not null default 1,
|
created_at datetime not null default current_timestamp,
|
updated_at datetime not null default current_timestamp on update current_timestamp,
|
primary key (id),
|
unique key uk_talent_card_advisor_level (card_level)
|
) engine=InnoDB default charset=utf8mb4 comment='人才卡服务专员配置';
|
|
create table if not exists talent_dynamic_code (
|
id bigint not null auto_increment,
|
code_token varchar(128) not null,
|
card_id bigint not null,
|
talent_id bigint not null,
|
merchant_id bigint default null,
|
expires_at datetime not null,
|
used_at datetime default null,
|
status tinyint not null default 0 comment '0有效 1已核销 2已失效',
|
created_at datetime not null default current_timestamp,
|
primary key (id),
|
unique key uk_talent_dynamic_code_token (code_token),
|
key idx_talent_dynamic_code_card (card_id, status),
|
key idx_talent_dynamic_code_expire (expires_at)
|
) engine=InnoDB default charset=utf8mb4 comment='短时效人才动态码';
|
|
insert into talent_card_advisor(card_level, advisor_name, department, mobile, status)
|
select '卓越人才卡', '陈婉璇', '区委组织部', '08123509008', 1
|
where not exists (select 1 from talent_card_advisor where card_level = '卓越人才卡');
|
|
insert into talent_card_advisor(card_level, advisor_name, department, mobile, status)
|
select '杰出人才卡', '陈婉璇', '区委组织部', '08123509008', 1
|
where not exists (select 1 from talent_card_advisor where card_level = '杰出人才卡');
|
|
insert into talent_card_advisor(card_level, advisor_name, department, mobile, status)
|
select '菁英人才卡', '陈婉璇', '区委组织部', '08123509008', 1
|
where not exists (select 1 from talent_card_advisor where card_level = '菁英人才卡');
|
