MySQL 使用存储过程去除重复数据

筛选出混合重复，且 user_id 不为 0 的作品

SELECT
		max( id ) AS id,
			count( 1 ) AS c ,
			number,
			reason,
			`title`,
			org_id,
			act_id,
			user_id,
			created_at
		FROM
			creations_copy1
		WHERE
			user_id != 0
		GROUP BY
			org_id,
			`act_id`,
			user_id,
			created_at
		HAVING
			c > 1
		ORDER BY
			c ASC

对混合重复的作品 user_id 改为 0

UPDATE creations_copy1 AS c
SET c.user_id = 0
WHERE
	c.id IN (
	SELECT
		a1.id
	FROM
		(
		SELECT
			max( id ) AS id,
			number,
			reason,
			`title`,
			org_id,
			act_id,
			user_id,
			created_at,
			count( 1 ) AS c
		FROM
			creations_copy1
		WHERE
			user_id != 0
			AND number = ''
		GROUP BY
			org_id,
			`act_id`,
			user_id,
			created_at
		HAVING
			c > 1
		ORDER BY
			created_at ASC
		) AS a1
	)

对 user_id=0 并且重复的记录，进行日期自增 1 秒

UPDATE creations AS c
SET c.created_at = DATE_ADD( c.created_at, INTERVAL 1 SECOND )
WHERE
	c.id IN (
	SELECT
		a1.id
	FROM
		(
		SELECT
			id,
			`title`,
			org_id,
			act_id,
			user_id,
			created_at,
			count( 1 ) AS c
		FROM
			creations
		WHERE
		    user_id = 0
		GROUP BY
			org_id,
			`act_id`,
			user_id,
			created_at
		HAVING
			c > 1
		ORDER BY
		c DESC
	) AS a1)

自增 1 秒后，依然可能会有数据重复，需要多次调用。
这里用到了 MySQL 的存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateProcess`()
BEGIN
UPDATE creations AS c
SET c.created_at = DATE_ADD( c.created_at, INTERVAL 1 SECOND )
WHERE
	c.id IN (
	SELECT
		a1.id
	FROM
		(
		SELECT
			id,
			`title`,
			org_id,
			act_id,
			user_id,
			created_at,
			count( 1 ) AS c
		FROM
			creations
		WHERE
		    user_id = 0
		GROUP BY
			org_id,
			`act_id`,
			user_id,
			created_at
		HAVING
			c > 1
		ORDER BY
		c DESC
	) AS a1);
END

创建后，只需要在 MySQL 执行

CALL UpdateProcess();

最后，增加三字段的唯一索引，避免下次出错