NodeJS操作MySQL如何实现字段为空时更新指定列否则更新另一列

你可以借助MySQL自带的IF()函数在更新语句里直接实现判断逻辑，同时建议你放弃直接拼接SQL字符串的写法，改用参数化查询避免SQL注入风险，修改后的代码如下：

app.post('/api/update', (req, res) => {
    const convidn = req.body.conversationid
    const currentuser = req.body.current
    // 注意确认WHERE条件里的字段名是否和你数据库实际字段一致，你原代码里写的是converid
    const sql = `UPDATE messages 
                 SET whodeleted = IF(whodeleted IS NULL, ?, whodeleted),
                     whodeleted2 = IF(whodeleted IS NULL, whodeleted2, ?)
                 WHERE converid = ?`
    db.query(sql, [currentuser, currentuser, convidn], (err, result) => {
        if (err) throw err;
        console.log("Number of records updated: " + result.affectedRows);
    });
})

逻辑说明 #

• IF(whodeleted IS NULL, ?, whodeleted)的判断逻辑是：如果当前行的whodeleted字段为NULL，就将传入的currentuser赋值给whodeleted，否则保持whodeleted原有值不变
• IF(whodeleted IS NULL, whodeleted2, ?)的判断逻辑是：如果当前行的whodeleted字段不为NULL，就将传入的currentuser赋值给whodeleted2，否则保持whodeleted2原有值不变
• 用参数化查询传入三个参数，按顺序对应SQL语句里的三个?占位符，完全避免SQL注入风险

内容的提问来源于stack exchange，提问作者user14098664