导入导出mysql表数据到csv

导出：

SELECT 字段名 INTO OUTFILE '路径'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n' FROM 表名

FROM 表名 也可以写在 into 前面

导入：

LOAD DATA INFILE 'e:/productsupplier.csv' REPLACE INTO TABLE `productsupplier` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

为同名城市加上洲名作为后缀

update 表名 ci1 set ci1.city_name = CONCAT(ci1.city_name, '_',ci1.state) where ci1.city_name in ( select city_name from (select city_name,state, count(*) as num from us_city_top1000 ci group by city_name) as g where g.num>1)