python - MySQL:在重复键上插入不同值时设置 "diff"位？

Question

我有一个 cronjob (J1)，它每晚计算约 100 万客户的产品类别偏好。大多数客户的偏好是稳定的。但也有异常(exception)，每天都有新客户。我想通过将“diff”位设置为 1 来了解这些更改。然后另一个 cronjob (J2) 可以对此类客户执行某些操作(例如向他们发送通知)并将其设置回 0。

表格如下所示:

CREATE TABLE customers (
  customer_id VARCHAR(255),
  preference VARCHAR(255),
  diff TINYINT(1),
  PRIMARY KEY (customer_id),
  KEY (diff)
);

AFAIK，INSERT .. ON DUPLICATE KEY 不知道非键值是否不同。所以你不能使用类似下面的东西，对吗？

INSERT customers AS ("sql for J1") ON DUPLICATE KEY
_AND_PREFERENCE_DIFFERS_ SET diff=1;

那么最好的方法是什么？

a) 将表 customers 重命名为 customer_yesterday。通过运行 J1 创建一个新表 customers。 LEFT JOIN 两个表并设置 customers 的差异位。 (优点:更快？缺点:需要正确处理所有差异，例如客户没有出现在今天的输出中的情况)

b) 循环 J1 的输出(使用 python mysql 连接器)，通过 customer_id 查询 customer，并且仅在值不同或者是新客户时插入。 (优点:易于理解逻辑；缺点:速度慢？)

还有更好的解决方案吗？

更新:正如 @Barmar 所问，假设 J1 的 sql 是一个事务分组 sql，例如

SELECT 
  customer_id,
  GROUP_CONCAT(DISTINCT product_category SEPARATOR ',')
FROM transaction
WHERE date between _30_days_ago_ and _today_;

Answer 1

将 J1 的 SQL 设为使用 LEFT JOIN 的查询来过滤掉首选项未更改的客户。

INSERT INTO customers (customer_id, preference)
SELECT t1.*
FROM (
    SELECT customer_id, 
           GROUP_CONCAT(DISTINCT product_category ORDER BY product_category SEPARATOR ',') AS preference
    FROM transaction
    WHERE date BETWEEN _30_days_ago_ AND _today_) AS t1
LEFT JOIN customers AS c ON t1.customer_id = c.customer_id AND t1.preference = c.preference
WHERE t1.customer_id IS NULL
ON DUPLICATE KEY UPDATE preference = VALUES(preference), diff = 1

我已向 GROUP_CONCAT 添加了 ORDER BY 选项，以便它始终以一致的顺序返回类别。否则，当订单发生变化时，可能会导致误报。

我觉得有必要指出，在表列中存储逗号分隔值通常是糟糕的数据库设计。您应该使用多对多关系表。