mysql操作大批量的数据

创建了4张视图

# c1
select * from `citys` where (`citys`.`pid` = 0)
# c2
select * from `citys` join `c1` on (`citys`.`pid` = `c1`.`id`)
# c3
select * from `citys` join `c2` on (`citys`.`pid` = `c2`.`id`)
# c4
select * from `c1` left join `c2` on (`c2`.`pid` = `c1`.`id`) left join `c3` on (`c3`.`pid` = `c2`.`id`) where (`c1`.`pid` = 0)

百万数据分页问题

SELECT content.`小区` `name`,c4.city_id,c4.area_id,content.`详细地址` address,1 AS `type`,c4.circleId
            FROM content left JOIN c4 on (c4.city_name = content.`市` or c4.city_name = concat(content.`市`,'市'))
            and (c4.area_name = content.`区` or c4.area_name = concat(content.`区`,'区')) AND c4.circle_name = content.`商圈`
            WHERE content.id BETWEEN 1000001 AND 1001000 ORDER BY content.id ASC

这个语句参考：mysql百万级数据查询优化

百万级数据插入数据库

image.png

=CONCATENATE("INSERT INTO content (`市`,`区`,`商圈`,`小区`,`详细地址`) VALUES ('",A2,"','",B2,"','",C2,"','",D2,"','",E2,"');")

参考：
快速导入100万条Excel数据到MySql
Excel数据快速导入mysql的几个办法