Knex.js を使用してレコードを一括更新する QL のアプローチ-jsチュートリアル-php.cn

QL Approaches to Bulk Update Records with Knex.js

Web 開発の世界では、特に複数のレコードを一度に更新するような一括操作を処理する場合、データベースを効率的に操作することが重要です。在庫の管理、ユーザーデータの処理、トランザクションの処理のいずれの場合でも、効率的かつ信頼性の高い方法で一括更新を実行する機能は不可欠です。

このガイドでは、Node.js の多用途クエリビルダーである Knex.js を使用してレコードを一括更新するための 3 つの重要な SQL テクニックを詳しく説明します。各アプローチはさまざまなシナリオに合わせて調整されており、特定のユースケースに基づいて明確な利点を提供します。取り上げます:

複数の条件による単一更新:条件付きロジックを利用して、特定の条件に基づいてさまざまな更新を適用し、単一のクエリで複数のレコードを更新できる方法。
トランザクション内の個別のクエリによるバッチ更新:このアプローチでは、トランザクションを活用してアトミック性を確保し、複数の更新クエリを安全かつ効率的に実行します。
onConflict を使用した Upsert (挿入または更新):データが重複する危険を冒さずに新しいレコードを挿入するか、既存のレコードを更新する必要があるシナリオに最適です。

次のセクションでは、これらの各方法をさらに深く掘り下げ、その実装、利点、最適な使用例を検討します。これらのアプローチを理解することで、特定のニーズに最も適した手法を選択し、アプリケーションのパフォーマンスとデータ整合性の両方を最適化できます。

1. 複数の条件による単一の更新

データベース内の複数のレコードを更新する場合、効率が重要です。強力な手法の 1 つは、複数の条件を指定して単一の UPDATE クエリを使用することです。この方法は、特定の基準に基づいて、単一の SQL ステートメント内でさまざまな更新をさまざまなレコードに適用する必要がある場合に特に便利です。

コンセプト:

「複数の条件を使用した単一更新」アプローチの背後にある中心的な考え方は、単一の UPDATE クエリを使用して複数の行を変更し、各行がその固有の特性に基づいて異なる値を受け取る可能性があるということです。これは、UPDATE クエリ内で CASE ステートメントを使用することで実現され、更新が必要なフィールドごとに条件付きロジックを指定できます。

このアプローチを使用する理由:

効率:少数から中程度の数のレコード (例: 数十から数百) の場合、複数の更新を 1 つのクエリに統合すると、データベースの往復回数が減り、パフォーマンスが大幅に向上します。これは、高頻度の更新を扱う場合に特に有益です。ただし、非常に大規模なデータセット (数千以上) の場合、このアプローチはそれほど効果的ではない可能性があります。大規模なデータセットを処理するための代替方法については、このガイドの後半で説明します。
シンプルさ:
多くの場合、単一のクエリで更新を管理する方が、複数の個別のクエリを実行するよりも簡単で保守しやすくなります。このアプローチにより、データベースのやり取りの複雑さが軽減され、特に適度な数の更新を処理する場合にコードが理解しやすくなります。
オーバーヘッドの削減:
クエリが減ればデータベースのオーバーヘッドも減り、全体的なパフォーマンスの向上につながります。これは、ネットワーク遅延やデータベース負荷が操作の速度に影響を与える可能性があるシナリオでは特に重要です。非常に多数のレコードの場合、潜在的なオーバーヘッドをより効果的に管理するために、このガイドで他の戦略を検討します。

実装例:

ここでは、Node.js の人気のある SQL クエリビルダーである Knex.js を使用してこのアプローチを実装する方法の実践的な例を示します。この例では、条件付きロジックを使用して、レコードの ID に基づいてさまざまな更新を適用し、複数のレコードの複数のフィールドを一度に更新する方法を示します:

リーリー
このコードの機能:

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操作中正確運行，所涉及的列必須是唯一約束的一部分。它的工作原理如下：

單一唯一列：如果您在 onConflict 子句中使用單一資料列，則該列在整個表中必須是唯一的。這種唯一性保證了資料庫可以根據該列準確檢測一筆記錄是否已經存在。
多列：當onConflict子句中使用多列時，這些列的組合必須是唯一的。這種唯一性是透過唯一索引或約束來強制執行的，這確保了這些列的組合值在整個表中是唯一的。

索引與約束：
索引：一列或多列上的唯一索引允許資料庫有效地檢查值的唯一性。當您定義唯一索引時，資料庫將使用它來快速驗證指定列中的值是否已存在。這使得 onConflict 子句能夠準確地偵測和處理衝突。

約束：唯一約束可確保一列或多列中的值必須是唯一的。此約束對於 onConflict 子句的工作至關重要，因為它強制執行防止重複值的規則，並允許資料庫根據這些列檢測衝突。

與具有多個條件的單一更新方法類似，更新插入操作不需要事務。由於它涉及插入或更新記錄的單一查詢，因此它可以有效地運行，而無需管理事務的額外開銷。