在具有不同值的行上连接两个相同的表

发布于 2024-11-06 12:13:50 字数 558 浏览 0 评论 0原文

我有两个相同的表，一张包含行的当前值，另一张包含新值。我试图仅选择新值表中的行，其中新值表中的任何列的值与旧值表中的列的值不同。我现在使用的查询如下所示：

SELECT `new`.`item_id` 
  FROM `new_items` AS `new` 
  JOIN `items`  AS `old` 
 WHERE new.item_id = old.item_id
   AND (new.price != old.price || 
        new.description != old.description || 
        new.description_long != old.description_long || 
        new.image_small != old.image_small || 
        new.image_large != old.image_large || 
        new.image_logo1 != old.image_logo1 )

但是，此查询执行时间太长。 MySQL 是否有更好的方法来做到这一点，或者有人知道更有效的查询吗？

原文

I have two identical tables, one with current values for rows, and one with new values. I am trying to select only the rows from the new values table where any column in the new values table has a different value than the column in the old values table. The query I am using now looks like:

SELECT `new`.`item_id` 
  FROM `new_items` AS `new` 
  JOIN `items`  AS `old` 
 WHERE new.item_id = old.item_id
   AND (new.price != old.price || 
        new.description != old.description || 
        new.description_long != old.description_long || 
        new.image_small != old.image_small || 
        new.image_large != old.image_large || 
        new.image_logo1 != old.image_logo1 )

However, this query takes WAY too long to execute. Does MySQL have a better way to do this or does anyone know a more efficient query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拿命拼未来 2024-11-13 12:13:50

用途：

SELECT n.item_id
  FROM NEW_ITEMS n
 WHERE EXISTS (SELECT NULL
                 FROM OLD_ITEMS o
                WHERE o.item_id = n.item_id
                  AND (o.price <> n.price
                   OR o.description <> n.description 
                   OR o.description_long <> n.description_long 
                   OR o.image_small <> n.image_small 
                   OR o.image_large <> n.image_large 
                   OR o.image_logo1 <> n.image_logo1))

对所有正在比较的列建立索引。

Use:

SELECT n.item_id
  FROM NEW_ITEMS n
 WHERE EXISTS (SELECT NULL
                 FROM OLD_ITEMS o
                WHERE o.item_id = n.item_id
                  AND (o.price <> n.price
                   OR o.description <> n.description 
                   OR o.description_long <> n.description_long 
                   OR o.image_small <> n.image_small 
                   OR o.image_large <> n.image_large 
                   OR o.image_logo1 <> n.image_logo1))

Index all of the columns being compared.

回复收藏 0 原文