在 Web 開發領域，有效地使用資料庫至關重要，尤其是在處理批次操作（例如一次更新多個記錄）時。無論您是管理庫存、處理用戶資料還是處理交易，以高效可靠的方式執行批量更新的能力都是至關重要的。
在本指南中，我們將詳細介紹使用 Knex.js（Node.js 的多功能查詢產生器）批次更新記錄的三種基本 SQL 技術。每種方法都針對不同的場景量身定制，根據您的特定用例提供獨特的優勢。我們將介紹：
具有多個條件的單一更新：一種允許您在單一查詢中更新多個記錄的方法，利用條件邏輯根據特定條件應用不同的更新。
在事務中使用單一查詢進行批次更新：這種方法利用事務來確保原子性，安全且有效率地執行多個更新查詢。
Upsert（插入或更新）使用 onConflict：非常適合需要插入新記錄或更新現有記錄而不冒重複資料風險的場景。
在下面的部分中，我們將深入研究每種方法，檢查它們的實作、優點和最佳用例。透過了解這些方法，您可以選擇最適合您的特定需求的技術，優化應用程式中的效能和資料完整性。
當涉及到更新資料庫中的多個記錄時，效率是關鍵。一項強大的技術是使用具有多個條件的單一 UPDATE 查詢。當您需要根據特定條件對不同記錄套用不同的更新（所有這些都在單一 SQL 語句中）時，此方法特別有用。
理念：
「多條件單次更新」方法背後的核心思想是使用單一 UPDATE 查詢來修改多行，每行可能根據其獨特的特徵接收不同的值。這是透過在 UPDATE 查詢中使用 CASE 語句來實現的，讓您可以為需要更新的每個欄位指定條件邏輯。
為什麼要用這種方法：
效率：對於少量到中等數量的記錄（例如幾十到幾百），將多個更新合併到單一查詢中可以透過減少資料庫往返次數來顯著提高效能。這在處理高頻更新時尤其有用。然而，對於非常大的數據集（數千或更多），這種方法可能不那麼有效。我們將在本指南後面討論處理大型資料集的替代方法。
簡單性：與執行多個單獨的查詢相比，使用單一查詢管理更新通常更簡單且更易於維護。這種方法降低了資料庫互動的複雜性，並使程式碼更易於理解，特別是在處理適度數量的更新時。
減少開銷：更少的查詢意味著更少的資料庫開銷，這可以帶來更好的整體效能。這在網路延遲或資料庫負載可能影響操作速度的情況下尤其重要。
對於大量記錄，我們在本指南中探索其他策略，以更有效地管理潛在開銷。
範例實作：
這裡有一個實際範例，說明如何使用 Knex.js（Node.js 的流行 SQL 查詢建構器）來實作此方法。此範例示範如何一次更新多筆記錄的多個字段，使用條件邏輯根據記錄的 ID 應用不同的更新：
這段程式碼的作用：
建構查詢標頭：開始產品表的 UPDATE 語句。
建置條件更新：使用CASE語句根據product_id為每個欄位指定不同的更新。
產生完整查詢：組合更新語句和 WHERE 子句。
執行查詢：執行建構的查詢以將更新套用到指定的記錄。
透過實作此技術，您可以使用條件邏輯來有效地處理批次更新，使您的資料庫操作更加簡化和有效。
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.
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.
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 배열의 각 레코드를 제품 테이블에 삽입하려고 시도합니다.
충돌 처리: onCon conflict(['product_id', 'store_id']) 절은 product_id와 store_id의 조합에서 충돌이 발생하면 다음 단계가 실행되어야 함을 지정합니다.
Merge(충돌 시 업데이트): 충돌이 감지되면 merge({...}) 메서드는 productData의 새 product_name, 가격 및 카테고리 값으로 기존 레코드를 업데이트합니다. knex.raw('EXCLUDED.column_name') 구문은 삽입되었을 값을 참조하는 데 사용되며, 이를 통해 데이터베이스는 이러한 값으로 기존 레코드를 업데이트할 수 있습니다.
onConstrict절이upsert작업에서 올바르게 작동하려면 관련 열이 고유 제약 조건의 일부여야 합니다. 작동 방식은 다음과 같습니다.
색인 및 제약 조건:
인덱스:하나 이상의 열에 대한 고유 인덱스를 사용하면 데이터베이스가 값의 고유성을 효율적으로 확인할 수 있습니다. 고유 인덱스를 정의하면 데이터베이스는 이를 사용하여 지정된 열의 값이 이미 존재하는지 빠르게 확인합니다. 이를 통해 onConfluence 절이 충돌을 정확하게 감지하고 처리할 수 있습니다.
제약 조건:고유 제약 조건은 하나 이상의 열에 있는 값이 고유해야 함을 보장합니다. 이 제약 조건은 중복 값을 방지하는 규칙을 적용하고 데이터베이스가 이러한 열을 기반으로 충돌을 감지할 수 있도록 허용하므로 onConfluence 절이 작동하는 데 매우 중요합니다.
여러 조건을 사용한 단일 업데이트 접근 방식과 유사하게 upsert 작업에는 트랜잭션이 필요하지 않습니다. 레코드를 삽입하거나 업데이트하는 단일 쿼리가 포함되므로 트랜잭션 관리에 따른 추가 오버헤드 없이 효율적으로 작동합니다.
각 기술은 코드 단순화 및 데이터베이스 상호 작용 감소부터 데이터 무결성 보장 및 충돌 효율적 처리에 이르기까지 뚜렷한 이점을 제공합니다. 사용 사례에 가장 적합한 방법을 선택하면 애플리케이션에서 보다 효율적이고 안정적인 업데이트를 얻을 수 있습니다.
이러한 접근 방식을 이해하면 데이터베이스 작업을 특정 요구 사항에 맞게 조정하여 성능과 유지 관리성을 모두 향상할 수 있습니다. 대량 업데이트를 처리하든 복잡한 데이터 관리 작업을 처리하든, 워크플로를 최적화하고 개발 프로젝트에서 더 나은 결과를 달성하려면 올바른 전략을 선택하는 것이 중요합니다.
以上是使用 Knex.js 批次更新記錄的 QL 方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！
