MySql关于新增和修改的特殊操作

新增时重复数据自动忽略（insert ignore）

如果插入的数据在表中已经存在（主键或者唯一键已存在），使用insert ignore 语法可以忽略插入重复的数据

insert ignore into cpz_squadron_user (squadron_id, user_id) values (#{squadronId}, #{userId})

CREATE TABLE cpz_squadron_user (
	id BIGINT auto_increment COMMENT 'ID' PRIMARY KEY,
	squadron_id BIGINT NOT NULL COMMENT '中队',
	user_id BIGINT NOT NULL COMMENT '人员',
	update_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	CONSTRAINT unique_index UNIQUE ( user_id ) 
) COMMENT '中队人员' COLLATE = utf8mb4_bin row_format = DYNAMIC;
CREATE INDEX squadron_index ON cpz_squadron_user ( squadron_id );
CREATE INDEX user_index ON cpz_squadron_user ( user_id );

上述语句中cpz_squadron_user表建立了唯一索引（user_id），实现需求：如果该用户已被划分中队则忽略插入

新增时已存在则更新（insert into ... on duplicate key update ...）

如果插入数据时表中已经存在则根据唯一键进行更新操作

insert into cpz_guide_point (line_id, line_name, type, start_id, start_name, end_id, end_name, position)
values (#{lineId}, #{lineName}, #{type}, #{startId}, #{startName}, #{endId}, #{endName}, #{position})
on duplicate key update position = #{position}

CREATE TABLE cpz_guide_point (
	id BIGINT auto_increment COMMENT 'ID' PRIMARY KEY,
	line_id BIGINT NOT NULL COMMENT '线路$search$$select[]select$',
	line_name VARCHAR ( 64 ) NOT NULL COMMENT '路段名称',
	type CHAR DEFAULT '1' NULL COMMENT '类型$search$$select[{"key":0,"value":"上行"},{"key":1,"value":"下行"}]select$',
	start_id BIGINT NULL COMMENT '起始站点$select[]select$',
	start_name VARCHAR ( 64 ) NOT NULL COMMENT '起始站点',
	end_id BIGINT NULL COMMENT '结束站点$select[]select$',
	end_name VARCHAR ( 64 ) NOT NULL COMMENT '起始站点',
	position JSON NULL COMMENT '参考点位',
	remark VARCHAR ( 225 ) NULL COMMENT '备注$notlist$',
	create_by VARCHAR ( 64 ) DEFAULT '' NULL COMMENT '创建者',
	create_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
	update_by VARCHAR ( 64 ) DEFAULT '' NULL COMMENT '更新者',
	update_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
CONSTRAINT 区间不重复 UNIQUE ( line_id, type, start_id, end_id ) 
) COMMENT '参考点位' COLLATE = utf8mb4_bin row_format = DYNAMIC;

cpz_guide_point表建立了唯一索引（line_id, type, start_id, end_id），实现需求：如果没有线路&类型&起止站点对应的数据则插入，否则更新

根据主键或者唯一索引判断新增或修改（replace into）

• replace into原理

  replace是insert的增强版，replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据，否则直接插入新数据

• replace into在binlog中的表现形式

  表只存在pk

  • pk冲突 binlog(update)
  • pk不冲突 binlog(insert)

  表存在pk和uk

  • pk冲突 uk不冲突 binlog(delete + insert)
  • pk不冲突 uk冲突 binlog(update)
  • pk冲突 uk冲突 binlog(delete + insert)
  • pk不冲突 uk不冲突 binlog(insert)

• replace into的应用注意事项

  • 插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据
  • 如果数据库里边有这条记录，则直接修改这条记录；如果没有则，则直接插入，在有外键的情况下，对主表进行这样操作时，因为如果主表存在一条记录，被从表所用时，直接使用replace into是会报错的，这和replace into的内部原理是相关（先删除然后再插入）

• 自增主键可能带来的问题

  • replace操作在自增主键的情况下，某些情况冲突时执行的是delete+insert,但是在记录binlog时,却记录成了update操作,update操作不会涉及到auto_increment的修改。

  • 备库应用了binlog之后，备库的表的auto_increment属性不变。如果主备库发生主从切换，备库变为原来的主库，写新的主库则有风险发生主键冲突

  • 频繁的REPLACE INTO 会造成新纪录的主键的值迅速增大。达到最大值后就会因为数据太大溢出了就没法再插入新纪录了。数据表满了，不是因为空间不够了，而是因为主键的值没法再增加了