在 web 开发领域，高效地使用数据库至关重要，尤其是在处理批量操作（例如一次更新多个记录）时。无论您是管理库存、处理用户数据还是处理交易，以高效可靠的方式执行批量更新的能力都是至关重要的。
在本指南中，我们将详细介绍使用 knex.js（node.js 的多功能查询生成器）批量更新记录的三种基本 sql 技术。每种方法都针对不同的场景量身定制，根据您的具体用例提供独特的优势。我们将介绍：
具有多个条件的单一更新： 一种允许您在单个查询中更新多个记录的方法，利用条件逻辑根据特定条件应用不同的更新。
在事务中使用单个查询进行批量更新： 这种方法利用事务来确保原子性，安全高效地执行多个更新查询。
upsert（插入或更新）使用 onconflict： 非常适合需要插入新记录或更新现有记录而不冒重复数据风险的场景。
在下面的部分中，我们将深入研究每种方法，检查它们的实现、优点和最佳用例。通过了解这些方法，您可以选择最适合您的特定需求的技术，优化应用程序中的性能和数据完整性。
当涉及到更新数据库中的多条记录时，效率是关键。一项强大的技术是使用具有多个条件的单个 update 查询。当您需要根据特定条件对不同记录应用不同的更新（所有这些都在单个 sql 语句中）时，此方法特别有用。
理念：
“多条件单次更新”方法背后的核心思想是使用单个 update 查询来修改多行，每行可能根据其独特的特征接收不同的值。这是通过在 update 查询中使用 case 语句来实现的，允许您为需要更新的每个字段指定条件逻辑。
为什么使用这种方法：
效率： 对于少量到中等数量的记录（例如几十到几百条），将多个更新合并到单个查询中可以通过减少数据库往返次数来显着提高性能。这在处理高频更新时尤其有用。然而，对于非常大的数据集（数千或更多），这种方法可能不那么有效。我们将在本指南后面讨论处理大型数据集的替代方法。
简单性： 与执行多个单独的查询相比，使用单个查询管理更新通常更简单且更易于维护。这种方法降低了数据库交互的复杂性，并使代码更易于理解，特别是在处理适度数量的更新时。
减少开销：更少的查询意味着更少的数据库开销，这可以带来更好的整体性能。这在网络延迟或数据库负载可能影响操作速度的情况下尤其重要。
对于大量记录，我们在本指南中探索其他策略，以更有效地管理潜在开销。
示例实现：
这里有一个实际示例，说明如何使用 knex.js（node.js 的流行 sql 查询构建器）来实现此方法。此示例演示如何一次性更新多条记录的多个字段，使用条件逻辑根据记录的 id 应用不同的更新：
const queryheaderproductupdate = 'update products set '; // start of the sql update query
const updatesproductupdate = []; // array to hold the individual update statements
const parametersproductupdate = []; // array to hold the parameters for the query
const updateproducts = [
{ product_id: 1, name: 'new name 1', price: 100, status: 'active' },
{ product_id: 2, name: 'new name 2', price: 150, status: 'inactive' },
{ product_id: 3, name: 'new name 3', price: 200, status: 'active' }
];
// extract the product ids to use in the where clause
const productids = updateproducts.map(p => p.product_id);
// build the update statements for each field
updateproducts.foreach((item) => {
// add conditional logic for updating the 'name' field
updatesproductupdate.push('name = case when product_id = ? then ? else name end');
parametersproductupdate.push(item.product_id, item.name);
// add conditional logic for updating the 'price' field
updatesproductupdate.push('price = case when product_id = ? then ? else price end');
parametersproductupdate.push(item.product_id, item.price);
// add conditional logic for updating the 'status' field
updatesproductupdate.push('status = case when product_id = ? then ? else status end');
parametersproductupdate.push(item.product_id, item.status);
// add 'updated_at' field with the current timestamp
updatesproductupdate.push('updated_at = ?');
parametersproductupdate.push(knex.fn.now());
// add 'updated_by' field with the user id
updatesproductupdate.push('updated_by = ?');
parametersproductupdate.push(req.user.userid);
});
// construct the full query by joining the individual update statements and adding the where clause
const queryproductupdate = `${queryheaderproductupdate + updatesproductupdate.join(', ')} where product_id in (${productids.join(', ')})`;
// execute the update query
await db.raw(queryproductupdate, parametersproductupdate);
这段代码的作用：
构造查询标头：开始产品表的 update 语句。
构建条件更新：使用case语句根据product_id为每个字段指定不同的更新。
生成完整查询：组合更新语句和 where 子句。
执行查询：运行构造的查询以将更新应用于指定的记录。
通过实施此技术，您可以使用条件逻辑高效处理批量更新，使您的数据库操作更加简化和有效。
注意：在提供的示例中，我们没有使用事务，因为该操作涉及单个 sql 查询。由于单个查询本身就可以维护数据的完整性和一致性，因此不需要额外的事务。添加交易只会增加开销，而不会在这种情况下提供额外的好处。
探索了“多条件单次更新”方法（该方法适用于中等数量的记录并提供简单性和效率）之后，我们现在将注意力转向不同的场景。随着数据集变得越来越大，或者当多个操作之间的原子性变得至关重要时，有效地管理更新需要更强大的方法。
在事务中使用单个查询进行批量更新是一种旨在满足这些需求的方法。此方法涉及在单个事务中执行多个更新查询，确保以原子方式应用所有更新。让我们深入了解一下这种方法的工作原理及其优点。
在处理批量更新时，尤其是对于大型数据集，在事务中单独管理每个更新可能是一种稳健且可靠的方法。此方法确保所有更新都以原子方式应用，并且可以优雅地处理错误。
为什么使用这种方法：
可扩展性： 对于较大的数据集，使用多个条件进行单一更新可能会变得效率低下，使用事务进行批量更新可以提供更好的控制。每个查询单独执行，事务确保所有更改一起提交，降低部分更新的风险。
错误处理： 事务通过确保所有更新都成功或没有更新来提供安全网。这种原子性保证了数据的完整性，非常适合需要执行多个相关更新的场景。
并发控制： 使用事务可以帮助管理对同一记录的并发修改，防止冲突并确保一致性。
代码示例
以下是如何使用 knex.js 在事务中通过单个查询实现批量更新：
const updaterecordsinbatch = async () => {
// example data to update
const datatoupdate = [
{ id: 1, name: 'updated name 1', price: 100 },
{ id: 2, name: 'updated name 2', price: 200 },
{ id: 3, name: 'updated name 3', price: 300 }
];
// start a transaction
const trx = await db.transaction();
const promises = [];
try {
// iterate over the data and push update queries to the promises array
datatoupdate.foreach(record => {
promises.push(
trx('products')
.update({
name: record.name,
price: record.price,
updated_at: trx.fn.now()
})
.where('id', record.id)
);
});
// execute all queries concurrently
await promise.all(promises);
// commit the transaction
await trx.commit();
console.log('all records updated successfully.');
} catch (error) {
// rollback the transaction in case of error
await trx.rollback();
console.error('update failed:', error);
}
};
解释
事务初始化：使用db.transaction()启动事务，这确保了所有后续查询都在该事务内执行。
批量更新：构建每个更新查询并将其添加到承诺数组中。此方法允许同时执行多个更新。
执行查询：promise.all(promises) 用于同时执行所有更新查询。这种方法确保所有更新都并行发送到数据库。
提交或回滚：如果所有查询都成功，则使用 trx.commit() 提交事务。如果任何查询失败，事务将使用 trx.rollback() 回滚，确保不会应用部分更新。
在事务内使用批量更新和单个查询提供了管理大型数据集的可靠方法。它通过原子事务确保数据完整性，并提供对并发操作的更好控制。当具有多个条件的单一更新对于非常大的数据集可能效率不高时，此方法特别有用。
当您处理可能需要插入或更新的数据（取决于数据库中是否存在）时，“upsert”操作是理想的解决方案。这种方法允许您在单个简化的操作中处理两种情况：插入新记录或更新现有记录。当您想要保持数据一致性而无需编写单独的逻辑来检查记录是否存在时，它特别有用。
为什么使用这种方法：
简单性： upsert 使您能够将插入和更新操作合并到单个查询中，从而简化代码并减少额外检查的需要。
效率： 这种方法比执行单独的插入和更新操作更高效，因为它最大限度地减少了数据库往返并自动处理冲突。
冲突处理： onconflict 子句让您可以通过更新相关字段来指定如何处理冲突，例如当具有唯一约束的记录已经存在时。
const productData = [
{
product_id: 1,
store_id: 101,
product_name: 'Product A',
price: 10.99,
category: 'Electronics',
},
{
product_id: 2,
store_id: 102,
product_name: 'Product B',
price: 12.99,
category: 'Books',
},
{
product_id: 3,
store_id: 103,
product_name: 'Product C',
price: 9.99,
category: 'Home',
},
{
product_id: 4,
store_id: 104,
product_name: 'Product D',
price: 15.49,
category: 'Garden',
},
];
await knex('products')
.insert(productData)
.onConflict(['product_id', 'store_id'])
.merge({
product_name: knex.raw('EXCLUDED.product_name'),
price: knex.raw('EXCLUDED.price'),
category: knex.raw('EXCLUDED.category'),
});
解释
数据定义：我们定义productdata，它是一个对象数组，代表我们想要插入或更新的产品记录。每个对象包含一个product_id、store_id、product_name、价格和类别。
插入或更新：knex('products').insert(productdata) 函数尝试将productdata 数组中的每条记录插入到products 表中。
处理冲突：onconflict(['product_id', 'store_id']) 子句指定，如果product_id 和 store_id 的组合发生冲突，则执行下一步。
合并（冲突时更新）：当检测到冲突时，merge({...}) 方法会使用productdata 中的新产品名称、价格和类别值更新现有记录。 knex.raw('excluded.column_name') 语法用于引用已插入的值，允许数据库使用这些值更新现有记录。
为了使 onconflict 子句在 upsert 操作中正确运行，所涉及的列必须是唯一约束的一部分。它的工作原理如下：
索引和约束：
索引： 一列或多列上的唯一索引允许数据库有效地检查值的唯一性。当您定义唯一索引时，数据库将使用它来快速验证指定列中的值是否已存在。这使得 onconflict 子句能够准确地检测和处理冲突。
约束： 唯一约束可确保一列或多列中的值必须是唯一的。此约束对于 onconflict 子句的工作至关重要，因为它强制执行防止重复值的规则，并允许数据库根据这些列检测冲突。
与具有多个条件的单一更新方法类似，更新插入操作不需要事务。由于它涉及插入或更新记录的单个查询，因此它可以高效运行，而无需管理事务的额外开销。
每种技术都具有独特的优势，从简化代码和减少数据库交互到确保数据完整性和有效处理冲突。通过选择最适合您的用例的方法，您可以在应用程序中实现更高效、更可靠的更新。
了解这些方法可以让您根据特定需求定制数据库操作，从而提高性能和可维护性。无论您是处理批量更新还是复杂的数据管理任务，选择正确的策略对于优化工作流程并在开发项目中取得更好的成果至关重要。