使用 Knex.js 批量更新记录的 QL 方法-js教程-PHP中文网

QL Approaches to Bulk Update Records with Knex.js

In the world of web development, working with databases efficiently is crucial, especially when handling bulk operations like updating multiple records at once. Whether you’re managing inventory, processing user data, or handling transactions, the ability to perform bulk updates in a way that is both efficient and reliable is essential.

In this guide, we’ll break down three essential SQL techniques for bulk updating records with Knex.js, a versatile query builder for Node.js. Each approach is tailored to different scenarios, offering distinct benefits based on your specific use case. We’ll cover:

Single Update with Multiple Conditions:A method that allows you to update multiple records in a single query, making use of conditional logic to apply different updates based on specific criteria.
Batch Updates with Individual Queries in a Transaction:This approach leverages transactions to ensure atomicity, executing multiple update queries safely and efficiently.
Upsert (Insert or Update) Using onConflict:Ideal for scenarios where you need to either insert new records or update existing ones without risking duplicate data.

In the following sections, we will dive deeper into each of these methods, examining their implementation, benefits, and best use cases. By understanding these approaches, you can choose the most appropriate technique for your specific needs, optimizing both performance and data integrity in your applications.

1. Single Update with Multiple Conditions

When it comes to updating multiple records in a database, efficiency is key. One powerful technique is to use a single UPDATE query with multiple conditions. This method is particularly useful when you need to apply different updates to different records based on specific criteria, all within a single SQL statement.

The Concept:

The core idea behind the “Single Update with Multiple Conditions” approach is to use a single UPDATE query to modify multiple rows, with each row potentially receiving different values based on its unique characteristics. This is achieved through the use of CASE statements within the UPDATE query, allowing you to specify conditional logic for each field that needs to be updated.

Why Use This Approach:

Efficiency:For a small to moderate number of records (e.g., a few dozen to a couple of hundred), consolidating multiple updates into a single query can significantly improve performance by reducing the number of database round-trips. This can be especially beneficial when dealing with high-frequency updates. For very large datasets (thousands or more), however, this approach might not be as effective. We discuss alternative methods for handling large datasets later in this guide.
Simplicity:Managing updates with a single query is often simpler and more maintainable compared to executing multiple separate queries. This approach reduces the complexity of your database interactions and makes the code easier to understand, especially when dealing with a moderate number of updates.
Reduced Overhead:Fewer queries mean less overhead for the database, which can lead to better overall performance. This is particularly important in scenarios where network latency or database load could impact the speed of operations.
For very large numbers of records, we explore other strategies in this guide to manage potential overhead more effectively.

Example Implementation:

Here’s a practical example of how you can implement this approach using Knex.js, a popular SQL query builder for Node.js. This example demonstrates how to update multiple fields for several records in one go, using conditional logic to apply different updates based on the record’s 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);

登录后复制

What This Code Does:

Constructs the Query Header: Begins the UPDATE statement for the products table.
Builds Conditional Updates: Uses CASE statements to specify different updates for each field based on the product_id.
Generates the Full Query: Combines the update statements and WHERE clause.
Executes the Query: Runs the constructed query to apply the updates to the specified records.

By implementing this technique, you can efficiently handle bulk updates with conditional logic, making your database operations more streamlined and effective.

Note: In the provided example, we did not use a transaction because the operation involves a single SQL query. Since a single query inherently maintains data integrity and consistency, there's no need for an additional transaction. Adding a transaction would only increase overhead without providing additional benefits in this context.

Having explored the "Single Update with Multiple Conditions" approach, which works well for a moderate number of records and provides simplicity and efficiency, we now turn our attention to a different scenario. As datasets grow larger or when atomicity across multiple operations becomes crucial, managing updates effectively requires a more robust approach.

Batch Updates with Individual Queries in a Transaction is a method designed to address these needs. This approach involves executing multiple update queries within a single transaction, ensuring that all updates are applied atomically. Let's dive into how this method works and its advantages.

2. Batch Updates with Individual Queries in a Transaction

When dealing with bulk updates, especially for a large dataset, managing each update individually within a transaction can be a robust and reliable approach. This method ensures that all updates are applied atomically and can handle errors gracefully.

Why Use This Approach:

Scalability:For larger datasets where Single Update with Multiple Conditions might become inefficient, batch updates with transactions offer better control. Each query is executed separately, and a transaction ensures that all changes are committed together, reducing the risk of partial updates.
Error Handling:Transactions provide a safety net by ensuring that either all updates succeed or none do. This atomicity guarantees data integrity, making it ideal for scenarios where you need to perform multiple related updates.
Concurrency Control:Using transactions can help manage concurrent modifications to the same records, preventing conflicts and ensuring consistency.

Code Example

Here’s how you can implement batch updates with individual queries inside a transaction using 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); } };

登录后复制

Explanation

Transaction Initialization: The transaction is started using db.transaction(), which ensures that all subsequent queries are executed within this transaction.
Batch Updates: Each update query is constructed and added to an array of promises. This method allows for multiple updates to be performed concurrently.
Executing Queries: Promise.all(promises) is used to execute all update queries concurrently. This approach ensures that all updates are sent to the database in parallel.
Committing or Rolling Back: If all queries succeed, the transaction is committed with trx.commit(). If any query fails, the transaction is rolled back with trx.rollback(), ensuring that no partial updates are applied.

Using batch updates with individual queries inside a transaction provides a reliable way to manage large datasets. It ensures data integrity through atomic transactions and offers better control over concurrent operations. This method is especially useful whenSingle Update with Multiple Conditionsmay not be efficient for very large datasets.

3. Upsert (Insert or Update) Using onConflict

When you're working with data that might need to be inserted or updated depending on its existence in the database, an "upsert" operation is the ideal solution. This approach allows you to handle both scenarios—insert new records or update existing ones—in a single, streamlined operation. It's particularly useful when you want to maintain data consistency without having to write separate logic for checking whether a record exists.

Why Use This Approach:

Simplicity:An upsert enables you to combine the insert and update operations into a single query, simplifying your code and reducing the need for additional checks.
Efficiency:This method is more efficient than performing separate insert and update operations, as it minimizes database round-trips and handles conflicts automatically.
Conflict Handling:The onConflict clause lets you specify how to handle conflicts, such as when records with unique constraints already exist, by updating the relevant fields.

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'), });

登录后复制

Explanation

数据定义：我们定义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操作中正确运行，所涉及的列必须是唯一约束的一部分。它的工作原理如下：