大数据量插入更新方案

流程图

原始表：

CREATE TABLE PRODUCTS (
PRODUCT_ID VARCHAR(50) PRIMARY KEY,
PRODUCT_NAME VARCHAR(100),
category VARCHAR(100)
 )ENGINE=InnoDB;
 
INSERT INTO PRODUCTS VALUES ('1','空调','电器');
INSERT INTO PRODUCTS VALUES ('2','JAVA','书籍');

临时表：

CREATE TABLE NEWPRODUCTS (
PRODUCT_ID VARCHAR(50) PRIMARY KEY,
PRODUCT_NAME VARCHAR(100),
category VARCHAR(100)
 )ENGINE=InnoDB;
 
INSERT INTO NEWPRODUCTS VALUES ('1','冰箱','电器');
INSERT INTO NEWPRODUCTS VALUES ('3','C++','书籍');

mysql:

insert into products  
select * from newproducts 
on duplicate key update product_name=values(product_name),category=VALUES(category)

<insert id="insertOrUpdate" parameterType="java.util.List">
        INSERT INTO class_info (class_name,class_num,school_num,teacher_num,update_date,deleted,source)
        values
        <foreach collection="list" item="item"  separator=",">
            (#{item.className} ,#{item.classNum} ,#{item.schoolNum},#{item.teacherNum} ,#{item.updateDate} ,#{item.deleted},#{item.source})
        </foreach>
        ON DUPLICATE KEY UPDATE class_name=values(class_name),class_num=values(class_num),
        school_num=values(school_num),teacher_num=values(teacher_num),update_date=values(update_date),deleted=values(deleted),source=values(source)
</insert>

关键字: on duplicate key update；
规则：如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，执行update语句而不是insert语句，反之，则执行insert语句
注意：必须有主键或者唯一索引

oracle参考链接: 点击.