mybatis中使用ON DUPLICATE KEY UPDATE
注意:
ON DUPLICATE KEY UPDATE为Mysql特有语法,这是个坑;语句的作用,当insert已经存在的记录(主键重复、或者联合索引重复)时,执行Update
<insert id="insertInfo">
insert into t_cust_
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="ip != null">
ip,
</if>
<if test="status != null">
status,
</if>
<if test="collectTime != null">
collect_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=VARCHAR},
</if>
<if test="ip != null">
#{ip, jdbcType=VARCHAR},
</if>
<if test="status != null">
#{status,jdbcType=INTEGER},
</if>
<if test="collectTime != null">
#{collectTime,jdbcType=TIMESTAMP},
</if>
</trim>
ON DUPLICATE KEY UPDATE
ip = VALUES(ip) ,
status = VALUES(status) ,
collect_time = VALUES(collect_time)
</insert>
创建联合索引
alter table table_name add unique index index_key_name(Column_1,Column_2,...);
表内有重复数据后,如何创建联合唯一主键?使用ignore即可
alter ignore table table_name add unique index index_key_name(Column_1,Column_2,...);
但是在MySQL 5.7.4及以后就移除了对ignore的支持
As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.
在5.7.4之前的版本中,如果使用它,则会收到警告.就像是:
1 warning(s): 1681 ‘IGNORE’ is deprecated and will be removed in a future release.
所以还要先删除每组重复的信息并保留id最小的一条
DELETE
FROM
t_cust_
WHERE
id NOT IN (
SELECT
id
FROM
(
SELECT
MIN( id ) id,
count( ip ) ip
FROM
t_cust_
GROUP BY
ip,
STATUS,
collect_time
HAVING
COUNT( ip ) >= 1
) temp
);