为什么SQL语句将“缺失”列的值更新为NULL？

Question

我将SQL UPDATE语句传递给JSON对象，该对象的“列”少于表的“列”。为什么SQL语句将“缺失”列的值更新为NULL？

我将一个JSON对象传递给我的对象构造函数，然后该构造函数将JSON键/值设置为自己的。

如果我的JSON缺少键-值对(列)，则构造函数不应设置其相应的局部变量。那么为什么SQL UPDATE语句仍然将缺失的"column“更新为NULL呢？

//---1st File---//
const Foo = require('.Foo');

let json = {
  oneKey: 'oneValue',
  twoKey: 'twoValue',
  // threeKey: 'threeValue' // <--- if this is commented out, ...
};

let foo = new Foo(json);
Foo.update(foo, callback);

function callback(err, data) {
  if (err) console.log(err);
  console.log(data);
}
//---End 1st File---//

//---2nd File---//
const pool = require('.sql');

let Foo = function(that) {
  this.oneKey = that.oneKey;
  this.twoKey = that.twoKey;
  this.threeKey = that.threeKey; // <--- then this local var should not be set in memory. Inspecting the object does not give threeKey with a empty value so I assume its not set in memory...
};

Foo.updateOne = function(foo, callback) {
  let sql = `UPDATE table_name SET ? WHERE oneKey = ?`;
  pool.query(sql, [foo, foo.oneKey], callback); // <--- but after query, column threeKey is updated as NULL. Why?
};

module.exports = Foo;
//---End 2nd File---//

//---This also does not work for 2nd File---//
const pool = require('.sql');

let Foo = function(that) {
  if (!that.oneKey) this.oneKey = that.oneKey; // <--- I tried doing something like this in case the above wasn't quite right...
  if (!that.twoKey) this.twoKey = that.twoKey;
  if (!that.threeKey) this.threeKey = that.threeKey;
};

Foo.updateOne = function(foo, callback) {
  let sql = `UPDATE table_name SET ? WHERE oneKey = ?`;
  pool.query(sql, [foo, foo.oneKey], callback); // <--- but after query, the column still updates as NULL.
};

module.exports = Foo;
//---End Example---//

我期望发生的事情: SQL语句只更新列oneKey和twoKey，因为threeKey没有定义。

实际发生的情况: SQL语句更新oneKey和twoKey (这是应该的)，但也将threeKey更新为NULL。

Answer 1

这是我做的变通方法。基本上，首先查询数据库，然后用数据库中的值替换所有未定义的值。

const pool = require('./sql');
const _ = require('lodash');

Foo.readOne = function (foo, callback) {
    pool.query("SELECT * FROM table_name WHERE oneKey = ?", foo.oneKey, callback);
};

Foo.updateOne = function (foo, callback) {
    Foo.readOne(foo, function (err, data) {
        if (err) return callback(err);
        if (_.isEmpty(data)) return callback(new Error(`oneKey ${foo.oneKey} does not exist in the database!`))
        _.forEach(Object.keys(foo), key => {
            if (typeof foo[key] === 'undefined') foo[key] = data[0][key];
        })
    });
    pool.query("UPDATE table_name SET ? WHERE oneKey = ?", [foo, foo.oneKey], callback);
}