mysql插入更新insert into

插入更新

背景：在某些情况下，我们需要保证插入的数据只有一条记录，业务层通常是先查询，有返回值则更新，否则插入数据。

mysql有特定的语句可以一条sql完成，从而避免业务层累赘的代码逻辑。

eg:商品信息goods同步到第三方接口，新增数据为入库，后续为修改。在日志记录log对于goods_id保持唯一。连表查询时，如果log种goods_id为空说明是新增，否则更新日期小于goods种的日期则为修改。

不要问为什么没有使用MQ，有些行业它就是不让用。

SELECT DISTINCT 
			CASE WHEN l.goods_id is null THEN 0 ELSE 1 END upstate,  -- 新增更新状态0新增 1更新
                m.*
                FROM  goods m 
                LEFT JOIN (SELECT goods_id, MAX(  last_update_datetime)last_update_datetime FROM event_goods_log GROUP BY goods_id) e  ON e.goods_id= m.goods_id
                LEFT JOIN  goods l  ON m.bill_id= l.bill_id
                WHERE m.status=1 
                AND (e.bill_id  IS NULL OR  e.last_update_datetime < m.last_update_datetime)
                ORDER BY  m.last_update_datetime DESC 

1.存在则更新（不影响其他字段），不存在则插入

insert into 表名(字段1,字段2) VALUES(值1,值2)
ON DUPLICATE KEY UPDATE username=值3

如上语句的意思的意思是如果字段1不存在（主键，唯一索引或者组合索引能唯一条件不存在），则执行插入语句，存在则执行更新语句，该更新只更新需要的字段，不影响其他字段的值；

2.存在则更新（先删除后更新），不存在则插入

REPLACE INTO 表名(字段1,字段2)
VALUES(值A,值B);
如上语句的意思的意思是如果字段1不存在（主键，索引或者唯一条件不存在），则执行插入语句，存在则执行更新语句，该更新是将该条存在的记录删除，然后再插入，所以其他的字段的值都是NULL；

3.存在则忽略，不存在插入

INSERT IGNORE INTO 表名(字段1,字段2)
VALUES(值A,值B);
如上语句的意思是如果字段1不存在（主键，索引或者唯一条件不存在），则执行插入语句，如果存在，直接忽略不修改任何数据

总结如下：

ON DUPLICATE key update是根据索引字段是否重复来判断是否执行，如果重复则执行update，否则则执行insert。

优先级：主键>唯一索引

• 当主键重复时则执行update
• 当主键不重复，唯一索引重复时也执行update
• 当主键和唯一索引值都不重复才执行insert
  PS：特殊情况，当主键重复执行update时，如果此时唯一索引字段与其他字段也重复则会报错

1.如果表中不存在主键记录，replace和insertupdate都与insert是一样的特点。
2.如 果表中存在主键记录，replace相当于执行delete 和 insert两条操作，而insertupdate的相当于执行if exist do update else do insert操作。因此，如果replace填充的字段不全，则会导致未被更新的字段都会修改为默认值，并且如果有自增id的话，自增id会变化为最新的 值（这样如果是以自增id为标志的话可能导致记录丢失）；而insert*update只是更新部分字段，对于未被更新的字段不会变化（不会强制修改为默 认值）。