Mysql -删除空列以仅更新设置的值

Question

我正在使用mysql+php。php提供了将XLSX商品表导入到Mysql表的接口。我使用了一个用CREATE TABLE LIKE创建的临时表，即实时表的空克隆，用于在与实时表合并之前进行用户评估

我使用INSERT INTO finalTable SELECT * FROM importTable ON DUPLICATE KEY UPDATE ....来避免对每条记录使用INSERT。重复键上要更新的字段由php和SHOW COLUMNS决定，即临时表中的所有列。

我的问题是，XLSX不一定包含所有字段(名称、价格、类别)，也就是说，它可能只是现有记录的更新。当前方法将更新所有字段，无论它们在导入过程中是否设置，即如果XLSX仅包含价格更新，则其余字段为空，并将覆盖当前值。

我打算通过删除列(而不是行)来更改临时表。对于所有行都为null的。这样，SHOW COLUMNS将只返回可更新的列。如果字段需要置零，可以很容易地设置一个特殊的值，例如"!X!“并使用单个UPDATE语句来完成此操作。

有没有办法解决这个问题，或者你有什么更好的建议(我也对abando ON DUPLICATE KEY持开放态度)？

Answer 1

我被你的问题搞糊涂了。

我正在使用

...在重复键更新时，以避免为每条记录插入。

就像它说的，它插入或更新，你不会避免任何事情。如果希望避免插入，请使用INSERT IGNORE。

如果我是你，我会分两步合并这些表。

第一步，插入(只是必要的)：

INSERT INTO finalTable (col1, col2, ...)
SELECT i.col1, i.col2
FROM importTable i 
LEFT JOIN finalTable f  ON i.ID = f.ID
WHERE f.ID IS NULL;

第二步，更新：

我不明白，你为什么要删除列。不必要的步骤可能需要一段时间，最重要的是，如果在导入表中只有一列中的几行是NULL，那么使用NULL更新的问题仍然存在。所以，这就是解决方案。

UPDATE finalTable f
INNER JOIN importTable i ON f.ID = i.ID
SET f.col1 = COALESCE(i.col1, f.col1),
f.col2 = COALESCE(i.col2, f.col2),
...;

第二步的诀窍是，使用COALESCE()。此函数返回其第一个参数，该参数不为null。因此，如果您的导入表中有一列为null，则最终表中的值保持不变。

更新：

如果您坚持只使用一条语句，那么您当然可以这样做

INSERT INTO final f
SELECT * FROM import i
ON DUPLICATE KEY UPDATE
SET f.col1 = COALESCE(i.col1, f.col1),
f.col2 = COALESCE(i.col2, f.col2),
...;