mysql修数据踩的坑

因为原来表设计不满足复制的业务逻辑，又加了一层表的封装。所以原来的表要按照新表的字段insert新表中。

修数据中，原来的按照字段的方式不好用了。因为表结构有改动，有新字段，有改名字的字段，所以有的要写死。

INSERT INTO t_project_news (id, tenant_id, created_by_id, created_at, updated_by_id, updated_at, version, status, comment_count,
like_count, title, content, attachment_ids, new_attachments_str, project_id)
 SELECT id, tenant_id, created_by_id, created_at, updated_by_id, updated_at, version, status, comment_count,
like_count, title, content, attachment_ids, new_attachments_str, project_id 
 FROM t_post
 WHERE project_id is not null and status = 0;

上面的sql不再试用到这个场景。

INSERT INTO t_project_news_index (tenant_id, created_by_id, created_at, updated_by_id, updated_at, status, object_id,  relate_id)
 SELECT tenant_id, created_by_id, created_at, updated_by_id, updated_at, version, status, project_id as object_id,  id as relate_id 
 FROM t_project_news
 WHERE project_id =10000003910;

 update t_project_news_index set object_type = 200, relate_type = 101 where object_id = 10000003910;

改为如上面的sql这样分成两条sql执行

修数据时害怕重复执行引入脏数据，随意给表加了唯一索引，

 ALTER TABLE t_project_news_index ADD UNIQUE (object_id, relate_id); 

但是以前有的脏数据要用自连接查询去查出并删掉。

select * from t_project_news_index a join t_project_news_index b on a.`object_id` = b.`object_id` and a.`relate_id` = b.`relate_id` and a.id != b.id group by a.object_id;