MYSQL数据库另类导出导入技巧

有一张城市表 city(city_id,city_name,pro_id)

一张省份表 province(pro_id,pro_name)

现在需要把这两张表中的数据合并到另外一张区域表中

t_area(pro_id,pro_name,city_id,city_name)

 

先用union all把select语句联系起来

SELECT CONCAT('select ''',c.`pro_id`,''',''',p.`pro_name`,''',''',c.`city_id`,''',''',c.`city_name`,''' union all')
FROM city c INNER JOIN province p 
 ON c.pro_id = p.`pro_id`

运行结果

 

把这些select语句拷贝到文本文件中,在最前面加上INSERT INTO `t_area`,同时去掉最后面的union all就可以了

INSERT INTO `t_area` SELECT '39','广东','547','广州' UNION ALL
SELECT '39','广东','548','深圳' UNION ALL
SELECT '39','广东','549','珠海' UNION ALL
SELECT '39','广东','550','汕头' UNION ALL
SELECT '39','广东','551','韶关' UNION ALL
SELECT '39','广东','552','河源' UNION ALL
SELECT '39','广东','553','梅州' UNION ALL
SELECT '39','广东','554','惠州'