这是我要加入的七个表的表结构：
-- tables: en, fr, de, zh_cn, es, ru, pt_br
`geoname_id` INT (11),
`continent_code` VARCHAR (200),
`continent_name` VARCHAR (200),
`country_iso_code` VARCHAR (200),
`country_name` VARCHAR (200),
`subdivision_1_name` VARCHAR (200),
`subdivision_2_name` VARCHAR (200),
`city_name` VARCHAR (200),
`time_zone` VARCHAR (200)
这是新的表结构，所有数据都将存储在其中：
CREATE TABLE `geo_lists` (
`city_id` int (11), -- en.geoname_id (same for all 7 tables)
`continent_code` varchar (2), -- en.continent_code (same for all 7 tables)
`continent_name` varchar (200), -- en.continent_name (just in english)
`country_code` varchar (2), -- en.country_iso_code (same for all 7 tables)
`en_country_name` varchar (200), -- en.country_name
`fr_country_name` varchar (200), -- fr.country_name
`de_country_name` varchar (200), -- de.country_name
`zh_country_name` varchar (200), -- zh_cn.country_name
`es_country_name` varchar (200), -- es.country_name
`ru_country_name` varchar (200), -- ru.country_name
`pt_country_name` varchar (200), -- pt_br.country_name
`en_state_name` varchar (200), -- en.subdivision_1_name
`fr_state_name` varchar (200), -- fr.subdivision_1_name
`de_state_name` varchar (200), -- de.subdivision_1_name
`zh_state_name` varchar (200), -- zh_cn.subdivision_1_name
`es_state_name` varchar (200), -- es.subdivision_1_name
`ru_state_name` varchar (200), -- ru.subdivision_1_name
`pt_state_name` varchar (200), -- pt_br.subdivision_1_name
`en_province_name` varchar (200), -- en.subdivision_2_name
`fr_province_name` varchar (200), -- fr.subdivision_2_name
`de_province_name` varchar (200), -- de.subdivision_2_name
`zh_province_name` varchar (200), -- zh_cn.subdivision_2_name
`es_province_name` varchar (200), -- es.subdivision_2_name
`ru_province_name` varchar (200), -- ru.subdivision_2_name
`pt_province_name` varchar (200), -- pt_br.subdivision_2_name
`en_city_name` varchar (200), -- en.city_name
`fr_city_name` varchar (200), -- fr.city_name
`de_city_name` varchar (200), -- de.city_name
`zh_city_name` varchar (200), -- zh_cn.city_name
`es_city_name` varchar (200), -- es.city_name
`ru_city_name` varchar (200), -- ru.city_name
`pt_city_name` varchar (200), -- pt_br.city_name
`time_zone` varchar (30) -- en.time_zone (same for all 7 tables)
);
我想将它们全部加入，使用区域设置（语言）代码作为列名的前缀。
最佳答案
哦！@加布里埃布兰卡你说得对，如果是这样的话，试试这个问题，让我知道它是否有效。您可以复制和粘贴：
insert into geo_lists
-- columns
(city_id, continent_code, continent_name, country_code, time_zone,
en_country_name,
fr_country_name,
de_country_name,
zh_country_name,
es_country_name,
ru_country_name,
pt_country_name,
en_state_name,
fr_state_name,
de_state_name,
zh_state_name,
es_state_name,
ru_state_name,
pt_state_name,
en_province_name,
fr_province_name,
de_province_name,
zh_province_name,
es_province_name,
ru_province_name,
pt_province_name,
en_city_name,
fr_city_name,
de_city_name,
zh_city_name,
es_city_name,
ru_city_name,
pt_city_name)
-- end columns
select
en.city_id, en.continent_code, en.continent_name, en.country_code, en.time_zone,
en.country_name as en_country_name,
fr.country_name as fr_country_name,
de.country_name as de_country_name,
zh.country_name as zh_country_name,
es.country_name as es_country_name,
ru.country_name as ru_country_name,
pt.country_name as pt_country_name,
en.state_name as en_state_name,
fr.state_name as fr_state_name,
de.state_name as de_state_name,
zh.state_name as zh_state_name,
es.state_name as es_state_name,
ru.state_name as ru_state_name,
pt.state_name as pt_state_name,
en.province_name as en_province_name,
fr.province_name as fr_province_name,
de.province_name as de_province_name,
zh.province_name as zh_province_name,
es.province_name as es_province_name,
ru.province_name as ru_province_name,
pt.province_name as pt_province_name,
en.city_name as en_city_name,
fr.city_name as fr_city_name,
de.city_name as de_city_name,
zh.city_name as zh_city_name,
es.city_name as es_city_name,
ru.city_name as ru_city_name,
pt.city_name as pt_city_name
from en, fr, de, zh_cn, es, ru, pt_br
where en.city_id = fr.city_id
and fr.city_id = de.city_id
and de.city_id = zh_cn.city_id
and zh_cn.city_id = es.city_id
and es.city_id = ru.city_id
and ru.city_id = pt_br.city_id
关于mysql - 从多个表插入其他，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31092956/