如何为两个相关联的表创建关联表数据，以及验证和清理重复数据

CREATE TABLE `t_dic_equ_spare_rela` (
  `id` varchar(32) NOT NULL COMMENT 'ID',
  `equ_dic_id` varchar(32) DEFAULT NULL COMMENT '设备字典ID',
  `spare_dic_id` varchar(32) DEFAULT NULL COMMENT '备件字典ID',
  `createdBy` varchar(100) DEFAULT NULL COMMENT '创建人',
  `createdAt` datetime DEFAULT NULL COMMENT '创建时间',
  `updatedBy` varchar(100) DEFAULT NULL COMMENT '修改人',
  `updatedAt` datetime DEFAULT NULL COMMENT '修改时间',
  `deletedBy` varchar(100) DEFAULT NULL COMMENT '删除人',
  `deletedAt` datetime DEFAULT NULL COMMENT '删除时间',
  `vsn` int(11) DEFAULT NULL COMMENT '版本号',
  `sync_flag` varchar(32) CHARACTER SET utf8 DEFAULT NULL COMMENT '数据同步标识',
  `server_vsn` varchar(32) CHARACTER SET utf8 DEFAULT NULL COMMENT '服务版本',
  `node_flag` varchar(32) CHARACTER SET utf8 DEFAULT NULL COMMENT '节点标识',
  `isDeleted` int(11) DEFAULT NULL COMMENT '逻辑删除标识',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_equ_spare_unique` (`equ_dic_id`,`spare_dic_id`),
  KEY `idx` (`equ_dic_id`,`spare_dic_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备备件关系表';


-- 验证是否重复
SELECT equ_dic_id, spare_dic_id, COUNT(*) AS count
FROM t_dic_equ_spare_rela
GROUP BY equ_dic_id, spare_dic_id
HAVING COUNT(*) > 1;


-- 添加关联数据
INSERT INTO t_dic_equ_spare_rela (equ_dic_id, spare_dic_id)
SELECT equ.id AS equ_dic_id, spare.id AS spare_dic_id
FROM t_dic_equ equ
JOIN t_dic_spare spare ON equ.model = spare.remark;


-- 验证 联表信息
SELECT spare.`name`,equ.*
FROM t_dic_equ equ
JOIN t_dic_equ_spare_rela rela ON equ.id = rela.equ_dic_id
JOIN t_dic_spare spare ON rela.spare_dic_id = spare.id
WHERE equ.id = '1796386344781017104' and spare.`name`= '飞轮轴承螺丝'   ORDER BY spare.`name` DESC;


-- 清除重复数据  看情况而定
DELETE t1
FROM t_dic_equ t1
JOIN t_dic_equ t2 ON t1.model = t2.model AND t1.id > t2.id;

-- 设备字典id和船号一样，就被认定为一行，删除多余行
DELETE t1
FROM t_equm_equ t1
JOIN t_equm_equ t2 ON t1.equ_dic_id = t2.equ_dic_id
                    AND t1.ship_id = t2.ship_id
                    AND t1.id > t2.id;


-- 对指定的关联id做匹配填写
UPDATE t_dic_equ AS e
JOIN t_dic_mfrs AS m ON e.`mfrs_name` = m.`name`
SET e.`mfrs_id` = m.`id`