如果字段包含 null 或 0，则仅更新 MYSQL 字段 [英] Only update the MYSQL field if the field contains null or 0

问题描述

我想将数据插入到一个表中，然后如果该表已经具有相同的 rName 输入值，那么它是更新这一行(因为 rName 是一个唯一索引)，这一切都很好.

I want to insert data into a table, then if the table already has that same input value of rName then it is to update this row (as rName is a unique index) which it all does well.

但是在 UPDATE 中，我希望它只更新字段，如果该字段中还没有内容(NULL).

But on the UPDATE I would like it to only UPDATE the fields IF the field doesn’t already have something in it (NULL).

INSERT INTO $table  
(`rDate`, `rName`, `rFormat`, `rText`) VALUES 
(CURRENT_TIMESTAMP, 'name3', 'formate22') ON DUPLICATE KEY UPDATE 
 rFormat=VALUES(rFormat), 
 IF('rFiles'=NULL,rFiles=VALUES(rFiles), rFiles=VALUES(rSizeMB)), 
 rText=VALUES(rText)

这也是我正在做的事情的最佳选择吗?这似乎有点令人反感，因为我的字段数量大约是这里显示的两倍，我只是为了这个问题把它删掉了.

Also is this the best to do what I’m doing? It seems a bit repudiative, as I have about twice the amount of fields then shown here, I just cut it down for the question.

推荐答案

试试这个:

...
ON DUPLICATE KEY UPDATE
rFormat=VALUES(rFormat),
rFiles=IF(ISNULL(rFiles), VALUES(rFiles), VALUES(rSizeMB)),
rText=VALUES(rText);

我有一段时间没有使用更高级的 MySQL 东西(如 IF)，所以我对此并不乐观，但应该是正确的.关键部分是将 rFiles 设置为 IF 返回的值.

I haven't used more advanced MySQL stuff (like IF) in a bit, so I'm not positive about this, but it should be about right. The key part is setting rFiles to the value returned by the IF.

这篇关于如果字段包含 null 或 0，则仅更新 MYSQL 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！