Mysql数据迁移

此方法为同库数据迁移   tableA为备份表   tableB为原始表  create_time为时间跨度

INSERT INTO tableA SELECT * FROM tableB t WHERE t.create_time >= '2019-06-01 00:00:00' and t.create_time <= '2019-06-30 23:59:59';

删除原始表数据

DELETE FROM tableB where create_time >= '2019-12-01 00:00:00' and create_time <= '2019-12-31 23:59:59' ;

 

查询磁盘空间

select TABLE_SCHEMA, TABLE_NAME,data_length / 1048576 AS data_length, index_length/1048576 AS index_length, (data_length + index_length) / 1048576 as total , data_free/ 1048576 AS data_free from information_schema.tables where TABLE_SCHEMA='库名' order by data_free desc limit 100;

清理磁盘空间  optimize操作将进行锁表

optimize table table_name