mysql联表批处理操作

2019独角兽企业重金招聘Python工程师标准>>>

1 概述

mysql中的单表增删改查操作,可以说是基本中的基本.
实际工作中,常常会遇到一些基本用法难以处理的数据操作,譬如遇到主从表甚至多级关联表的情况(如一些历史问题数据的批量处理),考虑到效率问题,需要将请求次数控制在一个常量级别.
这种情况下,由于需要操作的(作为参数的)数据量较大,或者获取参数较为复杂,往往不太容易处理.
以下分三种情况结合常见问题分别说明下:

2 insert批处理操作

在提供了已知参数的情况下,insert的批处理操作一般有两种处理方式(这种情况适用于从前台传来的明细数据的插入操作).

1. java层处理,即jdbcTemplate的batchUpdate.

即提供一个sql模板和一组参数数组的集合,通过框架(如spring)中提供的jdbcTemplate的batchUpdate进行批处理操作.

//sql模板
String strSql="insert into t_am_assets_increase_detail  \n" +
                "(group_code,form_code,assets_code,remarks) \n" +
                "values \n" +
                "(?,?,?,?)";
//组成参数数组的集合
List<Object[]> lstParamArr=new ArrayList<Object[]>();
for (AmAssetsIncreaseDetail mAaid:lstAaid){
    Object[] arrParam={strGroupCode,strFormCode,mAaid.getAssets_code(),mAaid.getRemarks()};
    lstParamArr.add(arrParam);
}
//batchUpdate批处理操作
int[] arrResult=db.batchUpdate(strSql,lstParamArr);

需要说明:

• 使用此种用法需要加上事务注解,一方面可以避免某一条数据有问题,整体得以回滚;另一方面,加上事务比不加事务的插入速度要快(数据量越大越明显,因为这减少了每条数据插入时打开关闭事务锁的开销).
• 虽然spring也提供了另一种batchUpdate方法,即java方法的参数不是一个sql模板和一个参数数组的集合,而是sql语句数组,但强烈不建议使用该形式的batchUpdate,原因有二,一是使用该种形式的参数,几乎必然只能参数拼接sql,无法使用参数绑定,安全性上是一个问题;二是不使用sql模板,在插入效率上会很低(具体原因可以参考<高性能mysql>,此处不再细讲).

2.数据层处理.参考内容如下:

-- 以下是数据库层插入数据的一种常见形式
insert table (..) values (?,?),(?,?),..

本质上来说,这种处理相当于是把所有插入操作放在一条sql上,性能上较法一还要高.
如果说有什么不足,那就是当插入数量过多时(如超过1000条明细),sql语句会过长,特别是插入复杂表的情况.

3.特殊情况下的数据层处理:联表插入

此前讲到的两种方法,都适用于通用的批处理操作,即参数显式的情况.也说到,当插入数据数量过多时,则可能会产生速度较慢等不利影响.
如果参数均从外界直接传来,如从页面上或者导入功能传来,与原数据没有任何关联,则也只能这样处理.
但如果插入数据原本就在当前数据库的其他表格中,则可完全不必通过在后台先查询在插入的方式进行处理.

-- 譬如插入数据的源数据为t2,要插入到t1,则可使用如下模式执行:
insert into t1 (code,name,remarks,..)
(select code,name,remarks,.. from t2 where [condition]);

与正常插入sql语句有两个区别:

• 没有value/values;
• 也可提供插入自身表的数据,不过要先构建一个伪表(再套一层select * from(..));

2 update联表操作

与insert的第三种方式相似,在一些业务逻辑互相关联的表格间更新数据而非直接从外界(页面/导入)获取原始数据时,update同样可以使用联表操作使批处理更加容易和有效率.
该类操作特别适用于批量处理冗余类数据(如果有新人对冗余设计不太了解,可参考此前我的博客:mysql表结构设计优化建议).

• 先举一个比较简单的例子:
  譬如使用t_user来记录职员信息,user_code,user_name分别表示该职员的工号,姓名;
  t_salary_standard表示职员的工资标准,两个核心字段user_code和user_salary表示职员工号和当前薪资标准;
  t_salary_change表示职员的薪资变更表.user_code和add_salary分别表示职员工号和增加薪资.此处我们先简单化处理,假设表单审批完成后即立刻变更职员的工资标准;
  则很容易看出来,t_salary_standard算是一种t_salary_change的一种冗余表,当后者变更(以审批为生效时刻)时,前者也要随之变更.
  由于薪资变更表对应的职员数量往往不止一个,所需增加的工资一般也不都相同,所以不使用联表操作的话,就只能先全部查询出来待修改的用户薪资变更数据,再在批次性增加:

select user_code,add_salary from t_salary_chang where [condition];
-- 查询后再batchUpdate
update t_salary_standard set user_salary=user_salary+? where user_code=?

此种情况,使用联表处理就容易很多:

update t_salary_standard tss,t_salary_change tsc
set tss.user_salary=tss.user_salary+tsc.add_salary
where tss.user_code=tsc.user_code
and [other conditions]

由于省略了许多中间步骤,操作速度必然是要快于前一种的方法的.且薪资变更表的数据量越大,这种效率上的提升越明显.

• 以下是一个比较复杂的例子,逻辑稍微有些复杂,此处不再详细说明,读者可以根据sql大致推测出意义.

//此处的逻辑是:先将当前资产的管理数据更新到领用明细的历史数据中,再将领用总表的管理数据更新到当前资产的管理数据中;
update t_am_assets_use_detail taaud,t_am_assets taa,t_am_assets_use taau 
set taaud.history_organ_code=taa.using_organ_code,taaud.history_user_code=taa.custodian,taaud.history_store_code=taa.store_code,taaud.history_storage_places=taa.storage_places, 
taa.using_organ_code=taau.use_organ_code,taa.custodian=taau.use_user_code,taa.store_code=taau.store_code,taa.storage_places=taau.storage_places,taa.logic_state=2 
where taaud.group_code=? 
and taa.group_code=? 
and taau.group_code=? 
and taaud.form_code=? 
and taau.form_code=? 
and taaud.assets_code=taa.assets_code 
and taa.logic_state=1;

3.delete联表操作

类似update的联表操作,delete也有相应的联表操作.
当然就本人的开发经验而言,delete联表操作几乎仅用于历史问题数据的处理,几乎不用于日常系统使用的sql中.

• 此处同样先举一个简单的例子加以说明: t_customer表示客户信息,group_code,cust_code和cust_name表示企业编码,客户编码和客户名称;
  t_sale_form表示销售单据,group_code,form_code,cust_code表示表单编码和表单所属客户编码;
  譬如发现某个客户信息有问题,给t_customer表增加了is_error字段,有问题的客户信息is_error=1,要删除所有有问题客户的销售单据(一般这种删除都是考虑伪删除的,此处为了讲解方便,姑且采用真删除).
  如果不用批处理操作,就只能先查询出来所有客户信息,再根据客户信息删除主单据.

select group_code,cust_code from t_customer where is_error=1;
-- 此后再调用batchUpdate
delete from t_sale_form where group_code=? and cust_code=?;

使用联表,就很容易处理了:

delete tsf
from t_sale_form tsf,t_customer tc
where tsf.group_code=tc.group_code
and tsf.cust_code=tc.cust_code
and tc.is_error=1

• 以下是一个比较复杂的例子,同样不详细说明,仅作格式之参考.

-- group_code是企业编码,voucher_code是凭证号.
-- 此处的逻辑是,删除所有企业具有一定条件(体现在伪表中)的凭证明细
delete tvd
from t_voucher_detail tvd,(
select * from (
select tv2.voucher_code,tv.group_code 
from t_voucher tv 
inner join t_voucher_detail tvd on tvd.group_code=tv.group_code and tvd.voucher_code=tv.voucher_code and tvd.subject_one='5401'  
inner join t_voucher tv2 on tv2.group_code=tv.group_code and tv2.bill_code=tv.bill_code and tv2.is_del=0 
inner join t_voucher_detail tvd2 on tvd2.group_code=tv2.group_code and tvd2.voucher_code=tv2.voucher_code and tvd2.subject_one='5401' 
where tv.cdate>='2018-06-20' 
and tv2.voucher_id>tv.voucher_id
and left(tv.bill_code,4)='XSKD'
and tv.is_del=0
)wt
) wtv 
where tvd.group_code=wtv.group_code
and tvd.voucher_code=wtv.voucher_code

以下是将复杂sql格式简单化后的语句.

delete tvd
from t_voucher_detail tvd,(
select * from (...)wt
) wtv 
where tvd.group_code=wtv.group_code
and tvd.voucher_code=wtv.voucher_code

4.使用建议

1. 以上用法形式,均不止一种,此处拿来的,仅是本人常用的形式,更多形式可以搜索出来;
2. 联表操作,特别适用于两个及以上不全等字段的信息组操作(譬如update简单例子中user_code和add_salary不全相等,delete简单例子中的group_code和cust_code),因为一个字段的信息组操作可以通过子查询来实现;
3. 虽说多数情况适用于冗余数据操作,但只要是各个表格之间业务有关联的,均可使用联表操作.

转载于:https://my.oschina.net/yangyishe/blog/1861287