数据库数据归档，有什么好办法

数据库数据归档，有什么好办法

首先，数据存档（data archiving）是将不再经常使用的数据移到一个单独的存储设备来进行长期保存的过程。

Mysql：

1、复制表并且按照条件插入数据（此种方法除了主键索引不包括其他索引）

CREATE TABLE lime_survey_549656_20151001 as select * from lime_survey_549656 where submitdate < "2015-10-01 00:00:00";

ALTER TABLE lime_survey_549656_20151001 change id id int primary key auto_increment;

CREATE TABLE lime_survey_186194_20151001 as select * from lime_survey_186194 where submitdate < "2015-10-01 00:00:00";

ALTER TABLE lime_survey_186194_20151001 change id id int primary key auto_increment;

CREATE TABLE lime_survey_279575_20151001 as select * from lime_survey_279575 where submitdate < "2015-10-01 00:00:00";

ALTER TABLE lime_survey_279575_20151001 change id id int primary key auto_increment; 2、数据归档成功后清理数据sql:

deletefrom lime_survey_549656 where submitdate < "2015-10-0100:00:00";

deletefrom lime_survey_186194 where submitdate < "2015-10-0100:00:00";

deletefrom lime_survey_279575 where submitdate < "2015-10-0100:00:00";

SQL Server：

利用SQL Server提供的分区表