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;