MySQL 将数据插入表并检查重复值

Question

我有一个像这样的目标表t1(parent和parent_country是相应的ID)，其中有几千个条目:

id    parent    parent_country  name         type

1     0         0               USA          country
2     0         0               Canada       country
3     1         1               Alabama      state
4     1         1               California   state
5     4         1               Los Angeles  city
...

和一个源表t2，如下所示，有几千个条目:

loc_text_1    loc_text_2    loc_text_3 

Long Beach    California    USA              
Frankfurt     Hessen        Germany              
Los Angeles   California    USA               
...

如何将 t2 中的值插入到 t1 中，并且(当然)仅插入那些不在 t1 中的城市、州和国家/地区？ (t1中的id是自增主键)

Answer 1

在 (parent, name) 上为 t1 添加唯一 key 。

alter table t1 add unique (parent, name);

这样您就可以使用INSERT IGNORE来避免插入重复项。

然后先复制国家/地区，然后复制州/省/自治区/直辖市，然后复制城市:

-- copy countries
insert ignore into t1(parent, parent_country, name, type)
  select distinct 0, 0, loc_text_3, 'country'
  from t2
;

-- copy states
insert ignore into t1(parent, parent_country, name, type)
  select distinct t1.id, t1.id, t2.loc_text_2, 'state'
  from t2
  join t1 on t1.name = t2.loc_text_3
  where t1.type = 'country'
;

-- copy cities
insert ignore into t1(parent, parent_country, name, type)
  select st.id, st.parent, t2.loc_text_1, 'city'
  from t2
  join t1 st on st.name = t2.loc_text_2
  join t1 co on co.name = t2.loc_text_3 and co.id = st.parent
  where st.type = 'state'
    and co.type = 'country'
;

演示:https://www.db-fiddle.com/f/hhpkvgFK6opjFmZwYb7QFv/0