数据库单表数据归档

-- 创建中间表
create table table_midle_20210719 like table;   -- Copy 表结构
INSERT INTO table_midle_20210719 (SELECT
* 
FROM
	table 
WHERE
	created_time IN (
	SELECT
		max( created_time ) 
	FROM
		table 
	GROUP BY
		spot_id 
	));
-- 将旧表改名
alter table table rename to table_old;
-- 将新表改名
alter table table_midle_20210719 rename to table;
-- 创建归档表
create table table_file_20210719 like table;
-- 将归档数据插入归档表
INSERT INTO table_file_20210719 (SELECT
*
FROM
	table_old 
WHERE
	created_time NOT IN (
	SELECT
		max( created_time ) 
	FROM
		table_old 
	GROUP BY
		spot_id 
	));