文章目录
20191006
各位小伙伴,国庆快乐!
这里总结一下日常工作修复数据常用到一些技巧。
一、修复数据场景
没有应用场景,光谈技巧也是“假大空”。
场景1
比如BMI值的计算。系统中前期用户需要填写身高、体重即可;后来版本迭代,需要体现身高、体重趋势,所以加上了测量时间,根据测量时间和对应的身高、体重值来计算BMI.此时会面临已有的身高、体重数据的测量时间补不补?如何补?
场景2
该XX版本之前,XX活动需要为用户提供一个申请渠道,申请后的具体流程业务上还暂未考虑清楚,技术上要先收集这部分信息,以备不时之需。这个时候,技术实现是这样的:
一张临时表,比如:info_collect收集到用户的以下信息:
userName、 phone、hospitalId、hospitalName等。
在XX版本需求时,明确了该申请渠道后续的流程。所以之前的渠道需要废弃,切换到已有的通用模型中。此时已经收集到的信息如何处理?数据怎么修?
场景3
在XX版本需求中,说要给某个人需要处理的列表展示红点,类似微信中未阅读的内容有个红点提示。底层抽象的是待办事项-event_backlog模型。那么新进来的数据自然会有待办事项,存量数据呢?自然要修复,否则就会遗漏。
二、修复数据常用技巧
这里不针对前面的场景,而是罗列通用的需要修复数据的一些技巧。
技巧1 批量修改数据
说道修复数据,能用SQL搞定就用SQL搞定,But…有不少场合修复存量数据通过Sql是搞不定的,或者不好搞定。比如MySQL数据库表的某个字段是JSON格式的,通过SQL来修复比较麻烦而且受MYSQL版本限制。
有时候数据量大的话,需要批量修改,利用offset limit来,类似这样:
@Path("fix_health_data")
@POST
@Async
public void fixData(){
int offset = 0;
int limit = 100;
while(true){
LOGGER.info("Begin fix user health data ...")
List<String> userIds = healthInquiryService.getAllUserIds(offset,limit);
fixUserHealthData(userIds);
LOGGER.info("数据修复到第:"+offset+“条”)
if(CollectionUtils.isNotEmpty(userIds)){
int size = userIds.size();
if(size < 100){
return;
}else{
offset += limit;
}
}
}
}
上面的写法有点小瑕疵,因为size不可能> 100,所以还可以这么优化下:
优化后:
@Path("fix_health_data")
@POST
@Async
public void fixData(){
int offset = 0;
int limit = 100;
while(true){
LOGGER.info("Begin fix user health data ...")
List<String> userIds = healthInquiryService.getAllUserIds(offset,limit);
fixUserHealthData(userIds);
LOGGER.info("数据修复到第:"+offset+“条”)
if(CollectionUtils.isNotEmpty(userIds)){
int size = userIds.size();
if(size == 100){
offset += limit;
}else{
return;
}
}
}
}
优化后是一种思维的简化,不用考虑>的情况,因为根本就不可能;==表示还可能有数据。
技巧2 用到的SQL技巧
1) SQL技巧01 把一张表部分字段更新到另一张表中
update [left| right |inner ] join on set … where
我们把A表的created_time要插入到B表的created_time中。为什么这么做呢?因为有时候代码中把created_time直接写死了,用的是DateUtils.currentTime();你有不能简单的去更改底层的模型,影响范围太大,那么就可以先不用考虑时间,先通过代码修复后,然后走SQL修复下时间即可。
请看案例:
2张表:info_collection 和 common_application
目的:把info_collection中的created_time更新到common_application中的application_date(根据user_id来关联,注意约束条件:type、application_type来关联)
DDL如下:
create table info_collection(
id int(50) auto_increment,
user_id varchar(64),
type varchar(64),
information text,
created_by varchar(64),
created_time datetime default now(),
version bigint(20) default 0,
primary key(id)
) comment = "信息收集表";
insert into info_collection (user_id, type, information)
values
(
'7656', 'WUXIA', '{"userId":"001da9db-4fe5-47d7-9572-f0e90e9a489a",
"name":"过儿",
"phoneNum":"18722222222"}'
),
(
'7657', 'WUXIA', '{"userId":"0064baf9-661b-4698-ac2d-ba8264bbfd1a",
"name":"姑姑",
"phoneNum":"18711111111"}'
),
(
'7658', 'WUXIA', '{"userId":"0083593f-3464-470b-bb8f-d5e611a9cff7",
"name":"过儿",
"phoneNum":"15234567786"}'
);
create table common_application(
id int(50) auto_increment,
applicant varchar(20) comment "申请人",
application_type varchar(64) not null comment "申请类型",
application_date datetime default now(),
primary key(id)
) comment = "省略版通用申请表";
insert into common_application (applicant, applicationType)
values
('7656', 'EXPERT_INTENTION'),
('7657', 'EXPERT_INTENTION'),
('7658', 'EXPERT_INTENTION'),
('8659', 'MUSICIAN_PARTY');
怎么做呢?
update
common_application c
inner join info_collection i on c.applicant = i.user_id
set
c.application_date = i.created_time
where
c.application_type = "EXPERT_INTENTION"
and i.type = "WUXIA";
#生产环境自然还要谨慎些,加上操作的时间,例如: and c.updated_time > '2019-10-06 22:30'
结果:
2)SQL技巧02 把另一个表的数据插入到一个表
insert into … select
select … insert into…
或者复制表、复制表结构,也都会用到以上语句。
用insert into … select … where 案例:
这里我们就不在自己造很复杂的表了,能够说明问题即可。前面的示例虽然接近生产环境,但是理解字段含义比较复杂,不利于简明扼要的说明问题,反而没有突出重点。
这里就不举例了,网上很多,不重复劳动了哈。
三、总结
- 温习SQL高级教程
- 要想活学活用,还得处处留心+勤于总结
- mysql在8.0.11版本的时候,终端是可以登录mysql的,但是sequel pro可以连接上本地,But没法选择库,会出现以下画面。解决办法:换一个连接工具(Navicate Premium )就好了。
网上说
4、其实修复数据SQL技巧占大头,所以目的还是要温习SQL技巧。
好了,晚安。