使用 Knex.js 批次更新記錄的 QL 方法

在 Web 開發領域，有效地使用資料庫至關重要，尤其是在處理批次操作（例如一次更新多個記錄）時。無論您是管理庫存、處理用戶資料還是處理交易，以高效可靠的方式執行批量更新的能力都是至關重要的。

在本指南中，我們將詳細介紹使用 Knex.js（Node.js 的多功能查詢建構器）批次更新記錄的三種基本 SQL 技術。每種方法都針對不同的場景量身定制，根據您的特定用例提供獨特的優勢。我們將介紹：

1. 具有多個條件的單一更新：一種允許您在單一查詢中更新多個記錄的方法，利用條件邏輯根據特定條件應用不同的更新。

2. 在事務中使用單一查詢進行批次更新：這種方法利用事務來確保原子性，安全且有效率地執行多個更新查詢。

3. 使用 onConflict 進行更新插入（插入或更新）：非常適合需要插入新記錄或更新現有記錄而不冒重複資料風險的場景。

在以下部分中，我們將更深入地研究每種方法，檢查它們的實作、優點和最佳用例。透過了解這些方法，您可以選擇最適合您的特定需求的技術，從而優化應用程式中的效能和資料完整性。

---

1. 多條件單次更新

當涉及更新資料庫中的多筆記錄時，效率是關鍵。一項強大的技術是使用具有多個條件的單一 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);

這段程式碼的作用：

1. 建構查詢標頭：開始產品表的 UPDATE 語句。

2. 建置條件更新：使用CASE語句根據product_id為每個欄位指定不同的更新。

3. 產生完整查詢：組合更新語句和 WHERE 子句。

4. 執行查詢：執行建構的查詢以將更新套用到指定記錄。

透過實作此技術，您可以使用條件邏輯來有效地處理批次更新，使您的資料庫操作更加簡化和有效。

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

1. Transaction Initialization: The transaction is started using db.transaction(), which ensures that all subsequent queries are executed within this transaction.

2. Batch Updates: Each update query is constructed and added to an array of promises. This method allows for multiple updates to be performed concurrently.

3. 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.

4. 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 when Single Update with Multiple Conditions may 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

1. 資料定義：我們定義productData，它是表示我們要插入或更新的產品記錄的物件陣列。每個物件包含一個product_id、store_id、product_name、價格和類別。

2. 插入或更新：knex('products').insert(productData) 函數嘗試將 ProductData 陣列中的每筆記錄插入 products 表中。

3. 處理衝突：onConflict(['product_id', 'store_id']) 子句指定如果product_id 和 store_id 的組合發生衝突，則應執行下一步。

4. 合併（衝突時更新）：當偵測到衝突時，merge({...}) 方法會使用productData 中的新產品名稱、價格和類別值更新現有記錄。 knex.raw('EXCLUDED.column_name') 語法用於引用已插入的值，允許資料庫使用這些值更新現有記錄。

為了使 onConflict 子句在 upsert 操作中正確運行，所涉及的列必須是唯一約束的一部分。其工作原理如下：

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

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

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

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

---

結論

每種技術都具有獨特的優勢，從簡化程式碼和減少資料庫互動到確保資料完整性和有效處理衝突。透過選擇最適合您的用例的方法，您可以在應用程式中實現更有效率、更可靠的更新。

了解這些方法可以讓您根據特定需求自訂資料庫操作，從而提高效能和可維護性。無論您是處理大量更新還是複雜的資料管理任務，選擇正確的策略對於優化工作流程並在開發專案中取得更好的成果至關重要。

以上是使用 Knex.js 批次更新記錄的 QL 方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！