In my case I want to maintain a table for store some kind of data and after some period remove from the first table and store to another table.
在我的情况下，我想维护一个表存储某种数据，并在一段时间后从第一个表中删除并存储到另一个表。
I want to clarify the what is the best practice in this kind of scenario. I am using MySql database in java base application.
我想澄清这种情况下的最佳做法是什么。我在java基础应用程序中使用MySql数据库。
3 个解决方案
#1
6
Generally, I follow this procedure. Incase I want to delete a row. I have a tinyint column called deleted. I mark this column for that row as true.
一般来说，我遵循这个程序。如果我想删除一行。我有一个名为deleted的tinyint列。我将该行的该列标记为true。
That indicates that that row has been marked as deleted, So I dont, pick it up.
这表明该行已被标记为已删除，所以我不要把它拿起来。
Later (maybe once a day), I run a script which in a single shot either delete the rows entirely or migrate them to another table... etc.
稍后（也许每天一次），我运行一个脚本，一次完全删除行或将它们迁移到另一个表...等。
This is useful as every time you delete a row (even if it's 1 row), mysql has to reindex (it's indexes). This might require significant system resources depending on your data size or number of indexes. You might not want to incur these overheads everytime...
这很有用，因为每次删除一行（即使它是1行），mysql必须重新索引（它的索引）。这可能需要大量系统资源，具体取决于您的数据大小或索引数。您可能不希望每次都产生这些开销......
#2
0
You did not provide enough information but I think if both tables have same data structure then you can avoid using two tables. Just add another column in first table and set status/type for those particular second table records.
您没有提供足够的信息，但我认为如果两个表具有相同的数据结构，那么您可以避免使用两个表。只需在第一个表中添加另一列，并为这些特定的第二个表记录设置状态/类型。
For Example:
例如：
id | Name | BirthDate | type
------------------------------------
1 | ABC | 01-10-2001 | firsttable
2 | XYZ | 01-01-2000 | secondtable
You can pick records like this:
你可以选择这样的记录：
select * from tablename where type='firsttable'
OR
要么
select * from tablename where type='secondtable'
#3
0
If you are archiving old data, there should be a way to set up a scheduled job in mysql. I know there is in SQL Server and it's the kind of function that most databases require, so I imagine it can be done in mySQL. Shecdule the job to run in the low-usage hours. Have it select all records more than a year old (or whatever amount of time of records you want to keep active) and move them to an archive table and then delete them. Depending on the number of records you would be moving, it might be best to do this once a week or daily. You don't want the number of records expiring to be so large it affects performance greatly or makes the job take too long.
如果要归档旧数据，应该有一种方法可以在mysql中设置预定作业。我知道SQL Server中存在大多数数据库所需的功能，所以我想它可以在mySQL中完成。 Shecdule工作在低使用时间运行。让它选择超过一年的所有记录（或您希望保持活动的记录的任何时间）并将它们移动到存档表，然后删除它们。根据您要移动的记录数量，最好每周或每天执行一次。您不希望过期的记录数量如此之大，会对性能产生很大影响，或者使作业耗时太长。
Inarchiving, the critical piece is to make sure you keep all the records that will be needed frequently and don't forget to consider reporting in that(many reports need to havea years worth or two years worth of data, do not archive records these reports should need). Then you also need to set up a way for users to access the archived records on the rare occasions they may need to see them.
归档，关键是确保您保留所有经常需要的记录，并且不要忘记考虑报告（许多报告需要有数年或两年的数据，不归档记录这些报告应该）。然后，您还需要为用户设置一种方法，以便在极少数情况下访问存档记录。
#1
6
Generally, I follow this procedure. Incase I want to delete a row. I have a tinyint column called deleted. I mark this column for that row as true.
一般来说，我遵循这个程序。如果我想删除一行。我有一个名为deleted的tinyint列。我将该行的该列标记为true。
That indicates that that row has been marked as deleted, So I dont, pick it up.
这表明该行已被标记为已删除，所以我不要把它拿起来。
Later (maybe once a day), I run a script which in a single shot either delete the rows entirely or migrate them to another table... etc.
稍后（也许每天一次），我运行一个脚本，一次完全删除行或将它们迁移到另一个表...等。
This is useful as every time you delete a row (even if it's 1 row), mysql has to reindex (it's indexes). This might require significant system resources depending on your data size or number of indexes. You might not want to incur these overheads everytime...
这很有用，因为每次删除一行（即使它是1行），mysql必须重新索引（它的索引）。这可能需要大量系统资源，具体取决于您的数据大小或索引数。您可能不希望每次都产生这些开销......
#2
0
You did not provide enough information but I think if both tables have same data structure then you can avoid using two tables. Just add another column in first table and set status/type for those particular second table records.
您没有提供足够的信息，但我认为如果两个表具有相同的数据结构，那么您可以避免使用两个表。只需在第一个表中添加另一列，并为这些特定的第二个表记录设置状态/类型。
For Example:
例如：
id | Name | BirthDate | type
------------------------------------
1 | ABC | 01-10-2001 | firsttable
2 | XYZ | 01-01-2000 | secondtable
You can pick records like this:
你可以选择这样的记录：
select * from tablename where type='firsttable'
OR
要么
select * from tablename where type='secondtable'
#3
0
If you are archiving old data, there should be a way to set up a scheduled job in mysql. I know there is in SQL Server and it's the kind of function that most databases require, so I imagine it can be done in mySQL. Shecdule the job to run in the low-usage hours. Have it select all records more than a year old (or whatever amount of time of records you want to keep active) and move them to an archive table and then delete them. Depending on the number of records you would be moving, it might be best to do this once a week or daily. You don't want the number of records expiring to be so large it affects performance greatly or makes the job take too long.
如果要归档旧数据，应该有一种方法可以在mysql中设置预定作业。我知道SQL Server中存在大多数数据库所需的功能，所以我想它可以在mySQL中完成。 Shecdule工作在低使用时间运行。让它选择超过一年的所有记录（或您希望保持活动的记录的任何时间）并将它们移动到存档表，然后删除它们。根据您要移动的记录数量，最好每周或每天执行一次。您不希望过期的记录数量如此之大，会对性能产生很大影响，或者使作业耗时太长。
Inarchiving, the critical piece is to make sure you keep all the records that will be needed frequently and don't forget to consider reporting in that(many reports need to havea years worth or two years worth of data, do not archive records these reports should need). Then you also need to set up a way for users to access the archived records on the rare occasions they may need to see them.
归档，关键是确保您保留所有经常需要的记录，并且不要忘记考虑报告（许多报告需要有数年或两年的数据，不归档记录这些报告应该）。然后，您还需要为用户设置一种方法，以便在极少数情况下访问存档记录。