mybatis中使用MySql特有语法ON DUPLICATE KEY UPDATE，并使用联合索引禁止重复插入

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 
	);