mysql - 如何设计一个表来有效地将可能是新的或与前一天相同的每日数据保存到 MySQL 中？

Question

我有一个脚本可以每天检查网站上的一些产品销售价格，并希望将其存储在 MySQL 数据库中。

有一个产品表，存储产品ID、名称、属性等。我想到了两种方法来构建销售价格表。

1. 产品 ID、销售价格、销售价格日期。如果有销售价格，这将每天存储每个产品的记录。很可能最终会保存重复很多天的相同记录。

2. productID、SalePrice、FromDate、ToDate。当第一次找到产品的销售价格时，它会插入一条记录，其中 productID、SalePrice、FromDate、ToDate 为空。如果之后几天没有变化，什么也不做。当某一天检测到同一产品的新销售价格时，它会将 ToDate 更新为 today()-1。

哪种方式更好？我想第一种方法每天都会保存大量重复数据。第二种方式，我如何比较源数据与什么是销售价格表并只插入新的，更新现有的 ToDate 并忽略其余的？处理这样的数据是否需要更多资源？FromDate 和 ToDate 是否会使将来的查询难以构建或执行速度变慢？我正在考虑的查询类型将查找给定日期范围的第一个销售价格开始日期，查找给定日期范围内产品销售的总天数，查找产品何时打折或不打折例如，给出一个日期范围。我对 MySQL 很陌生，所以没有检查高级查询。

谢谢！

标签： mysqldatabasedatabase-design

Answer 1

我会采用第二种方法，因为它在节省内存和您的用例方面似乎更有意义。现在对于您的每个问题：

问：如何比较源数据与销售价格表并仅插入新数据、更新现有 ToDate 并忽略其余数据？

答：您将 SalePrice 与销售价格表和您的源数据之间的相同 productID 进行比较，您从销售价格表中获取 productID 的最新记录（其中 ToDate 为空）。另一种看待它的方式是，您只从源数据中查询 SalePrice 值与销售价格表中的值不同的产品，或者产品尚未插入销售价格表但在新的源数据中（新产品，附带每日数据）。

问：处理这样的数据是否需要更多资源？A：我会，但这并不意味着你会花费大量的计算能力，这只是一些逻辑而不是旧的insert into table select * from another_table. 您将节省内存，因此必须进行权衡。当您使用 SQL 分析您的表时，您不必进行聚合（就像您使用方法 1 一样），因此这节省了一些计算能力。

问：FromDate 和 ToDate 是否会使未来的查询难以构建或执行速度变慢？A：假设您想获取某个产品在某个日期的价格 -> WHERE productId = your_productID and your_desired_date between DateFrom 和 DateTo 假设您想了解某个产品的销售价格随时间的变化 -> WHERE productId = your_productID 等等...所以根据您的 SQL 技能，我认为这应该不难。使用方法 1，您必须进行聚合，使用方法 2，您必须更多地使用时间逻辑。

问：我正在考虑的查询类型将是在给定日期范围内查找销售价格的第一个开始日期，在给定日期范围内查找产品销售的总天数，查找产品何时打折打折，例如给出日期范围。

答：我认为如果您的用例是查找销售价格的第一个开始日期（第一种方法您必须使用聚合函数 MIN( ））。此外，如果您使用方法 2，如果您想获得总天数，您的查询将更加高效，因为您使用 DATEDIFF(DateFrom, DateTo)，这是 psevdo 代码，请参阅 MySQL 文档以获取查找日期差异的函数以天为单位的日期之间。如果您想查看产品是否在日期范围内打折，方法 2 会比方法 1 更麻烦，但不会很多。您只需要查看您的表格中是否有该产品在日期范围内的记录，如果没有，则该产品没有在销售，否则它在销售。

结论：我会采用方法2。