mysql 索引重复 更新_MySQL——ON DUPLICATE KEY UPDATE添加索引值实现重复插入变更update...

1. INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;

INSERT INTO f_cameras (cameraname,cameraIndexCode) VALUES (?,?) ON DUPLICATE KEY UPDATE

cameraIndexCode= VALUES(cameraIndexCode),camername= VALUES(cameraname)

public void insertRegionList(List regionInfoData){

Log4JConfigureServlet.LOGINFO.info("insertRegionList start");

Log4JConfigureServlet.LOGINFO.info("regionInfoData.size()=" + regionInfoData.size());

List factoryList = new ArrayList<>();

List areaList = new ArrayList<>();

for (Region cc : regionInfoData){

if(cc.getParentIndexCode().equals("root000000")){

factoryList.add(cc);

}else if(!cc.getParentIndexCode().equals("-1")){

areaList.add(cc);

}

}

Log4JConfigureServlet.LOGINFO.info("factoryList==="+ JSON.toJSONString(factoryList));

Log4JConfigureServlet.LOGINFO.info("areaList===" + JSON.toJSONString(areaList));

Connection conn = null;

PreparedStatement pst = null;

PreparedStatement pst2 = null;

ResultSet rs = null;

try {

conn = DbUtil.getConnection();

if(conn!=null) {

conn.setAutoCommit(false);// 更改JDBC事务的默认提交方式

//cameraIndexCode设置为Unique索引

String sql = "INSERT INTO f_factory (factoryid,factory) " +

"VALUES (?,?) " +

"ON DUPLICATE KEY UPDATE " +

"factoryid= VALUES(factoryid)," +

"factory=VALUES(factory)";

String sql2 = "INSERT INTO f_areas (areaid,areaname,factoryid) " +

"VALUES (?,?,?) " +

"ON DUPLICATE KEY UPDATE " +

"areaid= VALUES(areaid)," +

"areaname=VALUES(areaname)," +

"factoryid=VALUES(factoryid)";

pst = (PreparedStatement) conn.prepareStatement(sql);

pst2 = (PreparedStatement) conn.prepareStatement(sql2);

Log4JConfigureServlet.LOGINFO.info("insertRegionList conn.prepareStatement(sql)");

for (Region cc:factoryList) {

pst.setString(1, cc.getIndexCode());

pst.setString(2, cc.getName());

pst.addBatch();

Log4JConfigureServlet.LOGINFO.info("insertRegionList pst.addBatch()");

}

for (Region cc:areaList){

pst2.setString(1, cc.getIndexCode());

pst2.setString(2, cc.getName());

pst2.setString(3, cc.getParentIndexCode());

pst2.addBatch();

Log4JConfigureServlet.LOGINFO.info("insertRegionList pst2.addBatch()");

}

pst.executeBatch();

pst2.executeBatch();

Log4JConfigureServlet.LOGINFO.info("insertRegionList pst.executeBatch()");

conn.commit();

conn.setAutoCommit(true);

Log4JConfigureServlet.LOGINFO.info("insertRegionList end");

}

} catch (Exception e) {

try {

if(conn!=null)

conn.rollback();

} catch (SQLException ex) {

ex.printStackTrace();

}

String error = e.getCause().getLocalizedMessage();

if (error == null) {

error = e.getCause().toString();

}

Log4JConfigureServlet.LOGINFO.info("操作数据库失败错误为 is " + error);

} finally {

DbUtil.close(rs, pst, conn);

}

}

springboot mapper.xml写法：insert into f_cameras (cameraname, cameraIndexCode)

values (#{cameraname,jdbcType=VARCHAR}, #{cameraIndexCode,jdbcType=VARCHAR})

ON DUPLICATE KEY UPDATE

cameraname=#{cameraa,jdbcType=VARCHAR},carNum=#{cameraIndexCode,jdbcType=INTEGER}, optTime=CURRENT_TIMESTAMP2.新建Unique索引当Unique索引栏位的原始值和插入值一样时，执行update。反之，执行insert