我们这数据采集比较频繁可能一个月都能达到上百万条数据,但是系统只展示24小时的数据,旧数据也不能仍就写了一个存储过程 来拆分表让系统查询速度提升。
CREATE DEFINER=`root`@`%` PROCEDURE `caterings`()
BEGIN
SET @TABLE_NAME = CONCAT("lc_catering_", DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y_%m'));
SET @CREATE_TABLE_SQL = CONCAT("CREATE TABLE IF NOT EXISTS ", @TABLE_NAME, " LIKE catering");
PREPARE CREATE_TABLE FROM @CREATE_TABLE_SQL;
EXECUTE CREATE_TABLE;SET @MIGRATION_DATA_SQL = CONCAT("INSERT INTO ", @TABLE_NAME," (id, name, sbx_concr_td, ngr_td,mgr_td, online_rype, sbl_status, sb_status, create_time,mn, lat, lng, update_time, temperature,humidity,innertdate_time) SELECT id, name, sbx_concr_td, ngr_td,mgr_td, online_rype, sbl_status, sb_status, create_time,mn, lat, lng, update_time, temperature,humidity,innertdate_time FROM catering WHERE innertdate