用于删除超过两年的记录的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2969723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query to delete records older than two years
提问by Garrett Dumas
I need to clean out a very bloated SQL database by deleting records that are older than two years from a number of tables. What is the most efficient way of doing this?.
我需要通过从多个表中删除超过两年的记录来清理一个非常臃肿的 SQL 数据库。这样做的最有效方法是什么？
回答by Adam Robinson
Do you have any way to determine how "old" a record is? (i.e., is there a column in the table that represents either the age of the row or a date that can be used to calculate the age?). If so, it should be a simple
您有什么方法可以确定记录有多“旧”？（即，表中是否有一列代表行的年龄或可用于计算年龄的日期？）。如果是这样，它应该是一个简单的
DELETE FROM Table WHERE Age > 2
For example, if you have a DateTimecolumn called CreateDate, you could do this:
例如，如果您有一DateTime列名为CreateDate，您可以这样做：
DELETE FROM Table WHERE DATEADD(year, 2, CreateDate) < getdate()
回答by Paul Sasik
In addition to Adam Robinson's good answer: When performing this type of operation:
除了 Adam Robinson 的好回答：执行此类操作时：
- Run a SELECT query with the DELETE's WHERE clause first to make sure you're getting "the right data"
- Do a full backup
- Run the thing in "off" hours so as not to affect users too much
- 首先使用 DELETE 的 WHERE 子句运行 SELECT 查询，以确保您获得“正确的数据”
- 做一个完整的备份
- 在“关闭”时间运行该东西，以免对用户造成太大影响
回答by Chanoch
I've seen dba do this in a few different companies and it always seems to use the following format:
我在几家不同的公司看到 dba 这样做，它似乎总是使用以下格式：
- Backup the table
- Drop any indexes
- Select the rows you want to keep into a temp table
- Truncate the original table
- Insert (into your source table) from you temp table
- Recreate the indexes
- 备份表
- 删除任何索引
- 选择要保留到临时表中的行
- 截断原始表
- 从您的临时表插入（到您的源表中）
- 重新创建索引
The benefit to this approach is that this update doesnt write to the logs so they don't get blown by thousands of delete entries. It's also faster.
这种方法的好处是此更新不会写入日志，因此它们不会被数千个删除条目所破坏。它也更快。
The drawback is that the update doesn't write to the logs so your only option is to restore your backup.
缺点是更新不会写入日志，因此您唯一的选择是恢复备份。
You should think about putting house keeping in place. If the above, is too scary, then you could also use the house keeping to winnow the database over a matter of time.
你应该考虑把房子保持到位。如果上述情况太可怕了，那么您也可以使用管家在时间问题上对数据库进行筛选。
In MSSQL, you could create a job to run daily which deletes the first 1000 rows of your query. To steal Adam's query -
在 MSSQL 中，您可以创建一个每天运行的作业，以删除查询的前 1000 行。窃取亚当的查询 -
DELETE TOP 1000 FROM Table WHERE DATEADD(year, 2, CreateDate) < getdate()
DELETE TOP 1000 FROM Table WHERE DATEADD(year, 2, CreateDate) < getdate()
This would be very safe and would get rid of your data in three months or so safely and would them also maintain the size of the db in the future.
这将是非常安全的，并且会在三个月左右的时间内安全地清除您的数据，并且他们将来还会保持数据库的大小。
Your database will get to use this space in the future but if you want to recover the space you will need to shrink the database. Read around if you are interested - whether it is worth it depends on the amount of space to recover versus the total size of the db.
您的数据库将来会使用此空间，但如果您想恢复空间，则需要缩小数据库。如果您有兴趣，请仔细阅读 - 是否值得取决于要恢复的空间量与数据库的总大小。
