javascript - 具有 50 多列的 DUPLICATE KEY UPDATE 上的批量插入

Question

我正在使用 Node.js 和 MySQL 插入 500 多行和 50 多列，如果键已经存在，则更新该行。

已经有 StackOverflow 帖子解释了如何批量插入以及如何批量插入 + 更新，但是这些解决方案不能很好地适应许多列。对于应该更新的每一列，column_name = VALUES(column_name)如果您有 50 多列，他们建议添加确实很烦人的添加。关于如何更快地实现这一目标的任何建议？

标签： javascriptmysqlnode.js

Answer 1

我通常使用类似于下面概述的方法来完成此操作。这提供了一个模型，不知道有多少列或列名是什么。我使用它的一个版本作为函数，并传递一个对象和表名，这两者都可以利用，因此这适用于多个表。只需确保在构建对象时它们具有与目标表的列名匹配的键。

// an example element with multiple objects each containing the
// same key/value structure in my case this is often the result
// set of a prior query
let test_payload = [
  {
    record_id: 1001,
    value_1: 'robot',
    value_2: null,
    value_3: 'cat'
  },
  {
    record_id: 1002,
    value_1: 'robot',
    value_2: null,
    value_3: 'dog'
  },
  {
    record_id: 9001,
    value_1: 'robot',
    value_2: 'cat',
    value_3: "dog"
  }
];

// let the column name strings of the query be taken from the first
// object in your set
let the_keys = Object.keys(test_payload[0]);

// a placeholder for our UPDATE string
let updates = ''
// loop through the_keys and extend the string
for (var i = the_keys.length - 1; i >= 0; i--) {
    updates += `${the_keys[i]} = VALUES(${the_keys[i]})`
  // add a comma and space after every
  // entry but the last
  i > 0 ? updates += ', ' : ' '
}

// let the VALUES be mapped from the values of your objects into an
// array of arrays
let values = test_payload.map( obj => the_keys.map( key => obj[key]));

// build a query for your column names and pass your values using the
// standard question mark place holder. 1 example first and the query.
// an alternate query approach below to consider with explanation

// 1. and example for your specific use case
let sql = 'INSERT INTO table_name (' + the_keys.join(',') + ') VALUES ? ON DUPLICATE KEY UPDATE ' + updates + ';';
// call the query function and pass it your query string, along with values
db.query(sql, [values], function (error, results) {
  if (error) throw error;
  console.log('query results:', results);
  // do something else with your results…
});

// it is sometimes my personal preference to maintain a column such as
// update_count which can be leveraged to gauge what number of the total query
// are inserts vs update. if no value changes on an exiting record, no update
// will be counted in results.changedRows count - this approach ensures at least
// one value is always updated and registers in results.changedRows
let sql_example_with_update_count = 'INSERT INTO table_name (' + the_keys.join(',') + ') VALUES ? ON DUPLICATE KEY UPDATE update_count = update_count + 1;';

// for reference of each built item:
console.log(the_keys)
["record_id", "value_1", "value_2", "value_3"]

console.log(updates)
"value_3 = VALUES(value_3), value_2 = VALUES(value_2), value_1 = VALUES(value_1), record_id = VALUES(record_id)"

console.log(values)
[[1001, "robot", null, "cat"], [1002, "robot", null, "dog"], [9001, "robot", "cat", "dog"]]

此示例假设您知道如何初始化和使用连接。如果没有，您可以查看此示例：https ://github.com/dusthaines/mysqljs_setup_snippet/blob/master/app.js