使用Coldfusion和MySQL解析和比较数据

首先,我将解释我需要做什么,然后我认为我可以实现它.我目前的计划在理论上似乎效率很低,所以我的问题是是否有更好的方法来实现它.

我有2个表 – 我们称之为’Products’和’Products_Temp’,两者都是相同的.我需要下载大量文件(XML或XLS),其中包含供应商的产品详细信息(库存,定价等).然后将它们解析为Products_Temp表.现在,我计划使用CF Scheduled Tasks来处理下载,Navicat会进行实际的解析 – 我很高兴这足够和足够高效.

下一步是我在努力的地方 – 一旦文件被下载和解析,我需要寻找数据的任何变化.这将与Products表进行比较.如果找到了更改,那么应该添加或更新该行(如果应该删除它,那么我需要标记它而不是仅删除它).比较所有数据后,应清空products_temp表.

我知道比较表并相应地同步它们的方法,但是我遇到的问题是我将处理来自不同来源的多个文件.我曾考虑只使用products表并附加/更新,但我不确定如何管理’flag deleted’要求.

现在,我知道我可以使其工作的唯一方法是遍历products_temp表,执行各种cfquerys并在完成后删除该行.然而,这看起来非常低效,而且考虑到我们可能会处理数十万行,如果我们每天更新所有内容,则不太可能有效.

任何有关更好路线的指示或建议都将受到赞赏！

解决方法:

两种回应都有可能.只是为了扩大你的选择..

选项1

如果mySQL支持某种散列,则基于每行,您可以使用comodoro’s suggestion的变体来避免硬删除.

识别变更

要识别更改,请在主键上执行内部联接并检查哈希值.如果它们不同,则产品已更改并应更新：

UPDATE Products p INNER JOIN Products_Temp tmp ON tmp.ProductID = p.ProductID
SET p.ProductName = tmp.ProductName
, p.Stock = tmp.Stock
, ...
, p.DateLastChanged = now()
, p.IsDiscOntinued= 0
WHERE tmp.TheRowHash <> p.TheRowHash


识别已删除

使用简单的外部联接来标识临时表中不存在的记录,并将它们标记为“已删除”

UPDATE Products p LEFT JOIN Products_Temp tmp ON tmp.ProductID = p.ProductID
SET p.DateLastChanged = now()
, p.IsDiscOntinued= 1
WHERE tmp.ProductID IS NULL


识别新的

最后,使用类似的外部联接来插入任何“新”产品.

INSERT INTO Products ( ProductName, Stock, DateLastChanged, IsDiscontinued, .. )
SELECT tmp.ProductName, tmp.Stock, now() AS DateLastChanged, 0 AS IsDiscontinued, ...
FROM Products_Temp tmp LEFT JOIN Products p ON tmp.ProductID = p.ProductID
WHERE p.ProductID IS NULL


选项#2

如果每行散列不可行,则另一种方法是Sharondio’s suggestion的变体.

将“status”列添加到临时表,并通过一系列连接将所有导入的记录标记为“新”,“已更改”或“未更改”. (默认值应为“已更改”).

确定联合国改变

首先在所有字段上使用内部联接来标识未更改的产品. (注意,如果你的表包含任何可以为空的字段,请记住使用类似合并的东西.否则,结果可能会因为空值不等于任何值而产生偏差.

UPDATE Products_Temp tmp INNER JOIN Products p ON tmp.ProductID = p.ProductID
SET tmp.Status = 'Unchanged'
WHERE p.ProductName = tmp.ProductName
AND p.Stock = tmp.Stock
...


识别新的

像以前一样,使用外部联接来标识“新”记录.

UPDATE Products_Temp tmp LEFT JOIN Products p ON tmp.ProductID = p.ProductID
SET tmp.Status = 'New'
WHERE p.ProductID IS NULL


通过消除过程,临时表中的所有其他记录都被“更改”.计算完状态后,可以更新Products表：

/* update changed products */
UPDATE Products p INNER JOIN Products_Temp tmp ON tmp.ProductID = p.ProductID
SET p.ProductName = tmp.ProductName
, p.Stock = tmp.Stock
, ...
, p.DateLastChanged = now()
, p.IsDiscOntinued= 0
WHERE tmp.status = 'Changed'
/* insert new products */
INSERT INTO Products ( ProductName, Stock, DateLastChanged, IsDiscontinued, .. )
SELECT tmp.ProductName, tmp.Stock, now() AS DateLastChanged, 0 AS IsDiscontinued, ...
FROM Products_Temp tmp
WHERE tmp.Status = 'New'
/* flag deleted records */
UPDATE Products p LEFT JOIN Products_Temp tmp ON tmp.ProductID = p.ProductID
SET p.DateLastChanged = now()
, p.IsDiscOntinued= 1
WHERE tmp.ProductID IS NULL