Pendekatan QL untuk Kemas Kini Pukal Rekod dengan Knex.js

Dalam dunia pembangunan web, bekerja dengan pangkalan data dengan cekap adalah penting, terutamanya apabila mengendalikan operasi pukal seperti mengemas kini berbilang rekod sekaligus. Sama ada anda mengurus inventori, memproses data pengguna atau mengendalikan transaksi, keupayaan untuk melakukan kemas kini pukal dengan cara yang cekap dan boleh dipercayai adalah penting.

Dalam panduan ini, kami akan membahagikan tiga teknik SQL penting untuk mengemas kini rekod pukal dengan Knex.js, pembina pertanyaan serba boleh untuk Node.js. Setiap pendekatan disesuaikan dengan senario yang berbeza, menawarkan faedah yang berbeza berdasarkan kes penggunaan khusus anda. Kami akan meliputi:

1. Kemas Kini Tunggal dengan Berbilang Syarat: Kaedah yang membolehkan anda mengemas kini berbilang rekod dalam satu pertanyaan, menggunakan logik bersyarat untuk menggunakan kemas kini berbeza berdasarkan kriteria tertentu.

2. Kemas Kini Berkelompok dengan Pertanyaan Individu dalam Transaksi: Pendekatan ini memanfaatkan urus niaga untuk memastikan atomicity, melaksanakan berbilang pertanyaan kemas kini dengan selamat dan cekap.

3. Sisipkan (Sisipkan atau Kemas Kini) Menggunakan onConflict: Sesuai untuk senario di mana anda perlu sama ada memasukkan rekod baharu atau mengemas kini rekod sedia ada tanpa mempertaruhkan data pendua.

Dalam bahagian berikut, kami akan menyelami lebih mendalam setiap kaedah ini, meneliti pelaksanaan, faedah dan kes penggunaan terbaiknya. Dengan memahami pendekatan ini, anda boleh memilih teknik yang paling sesuai untuk keperluan khusus anda, mengoptimumkan prestasi dan integriti data dalam aplikasi anda.

---

1. Kemas Kini Tunggal dengan Pelbagai Syarat

Apabila mengemas kini berbilang rekod dalam pangkalan data, kecekapan adalah penting. Satu teknik yang berkuasa ialah menggunakan satu pertanyaan KEMASKINI dengan berbilang syarat. Kaedah ini amat berguna apabila anda perlu menggunakan kemas kini yang berbeza pada rekod yang berbeza berdasarkan kriteria tertentu, semuanya dalam satu pernyataan SQL.

Konsepnya:

Idea teras di sebalik pendekatan "Kemas Kini Tunggal dengan Pelbagai Syarat" ialah menggunakan pertanyaan KEMASKINI tunggal untuk mengubah suai berbilang baris, dengan setiap baris berpotensi menerima nilai yang berbeza berdasarkan ciri uniknya. Ini dicapai melalui penggunaan pernyataan CASE dalam pertanyaan KEMASKINI, membolehkan anda menentukan logik bersyarat untuk setiap medan yang perlu dikemas kini.

Mengapa Gunakan Pendekatan Ini:

• Kecekapan: Untuk bilangan rekod yang kecil hingga sederhana (mis., beberapa dozen hingga beberapa ratus), menggabungkan berbilang kemas kini ke dalam satu pertanyaan boleh meningkatkan prestasi dengan ketara dengan mengurangkan bilangan pangkalan data pergi dan balik. Ini boleh memberi manfaat terutamanya apabila berurusan dengan kemas kini frekuensi tinggi. Untuk set data yang sangat besar (beribu-ribu atau lebih), bagaimanapun, pendekatan ini mungkin tidak begitu berkesan. Kami membincangkan kaedah alternatif untuk mengendalikan set data yang besar kemudian dalam panduan ini.

• Kesederhanaan: Menguruskan kemas kini dengan satu pertanyaan selalunya lebih mudah dan lebih boleh diselenggara berbanding dengan melaksanakan berbilang pertanyaan berasingan. Pendekatan ini mengurangkan kerumitan interaksi pangkalan data anda dan menjadikan kod lebih mudah difahami, terutamanya apabila berurusan dengan bilangan kemas kini yang sederhana.

• Penurunan Overhed: Lebih sedikit pertanyaan bermakna kurang overhed untuk pangkalan data, yang boleh membawa kepada prestasi keseluruhan yang lebih baik. Ini amat penting dalam senario di mana kependaman rangkaian atau beban pangkalan data boleh memberi kesan kepada kelajuan operasi.
  Untuk jumlah rekod yang sangat besar, kami meneroka strategi lain dalam panduan ini untuk mengurus potensi overhed dengan lebih berkesan.

Contoh Pelaksanaan:

Berikut ialah contoh praktikal cara anda boleh melaksanakan pendekatan ini menggunakan Knex.js, pembina pertanyaan SQL yang popular untuk Node.js. Contoh ini menunjukkan cara mengemas kini berbilang medan untuk beberapa rekod sekali gus, menggunakan logik bersyarat untuk menggunakan kemas kini berbeza berdasarkan ID rekod:

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);

Apakah Fungsi Kod Ini:

1. Membina Pengepala Pertanyaan: Memulakan pernyataan KEMASKINI untuk jadual produk.

2. Membina Kemas Kini Bersyarat: Menggunakan penyata CASE untuk menentukan kemas kini berbeza bagi setiap medan berdasarkan product_id.

3. Menjana Pertanyaan Penuh: Menggabungkan pernyataan kemas kini dan klausa WHERE.

4. Melaksanakan Pertanyaan: Menjalankan pertanyaan yang dibina untuk menggunakan kemas kini pada rekod yang ditentukan.

Dengan melaksanakan teknik ini, anda boleh mengendalikan kemas kini pukal dengan logik bersyarat dengan cekap, menjadikan operasi pangkalan data anda lebih diperkemas dan berkesan.

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. Definisi Data: Kami mentakrifkan productData, susunan objek yang mewakili rekod produk yang ingin kami masukkan atau kemas kini. Setiap objek mengandungi product_id, store_id, product_name, harga dan kategori.

2. Sisipkan atau Kemas Kini:Fungsi knex('products').insert(productData) cuba memasukkan setiap rekod daripada tatasusunan productData ke dalam jadual produk.

3. Kendalikan Konflik:Klausa onConflict(['product_id', 'store_id']) menyatakan bahawa jika konflik berlaku pada gabungan product_id dan store_id, langkah seterusnya hendaklah dilaksanakan.

4. Gabung (Kemas kini tentang Konflik): Apabila konflik dikesan, kaedah gabungan({...}) mengemas kini rekod sedia ada dengan nilai_nama produk, harga dan kategori baharu daripada productData. Sintaks knex.raw('EXCLUDED.column_name') digunakan untuk merujuk kepada nilai yang akan dimasukkan, membenarkan pangkalan data mengemas kini rekod sedia ada dengan nilai ini.

Untuk klausa onConflict berfungsi dengan betul dalam operasi upsert, lajur yang terlibat mestilah sebahagian daripada kekangan unik. Begini cara ia berfungsi:

• Lajur Unik Tunggal: Jika anda menggunakan satu lajur dalam klausa onConflict, lajur itu mestilah unik merentas jadual. Keunikan ini memastikan pangkalan data dapat mengesan dengan tepat sama ada rekod sudah wujud berdasarkan lajur ini.
• Berbilang Lajur: Apabila berbilang lajur digunakan dalam klausa onConflict, gabungan lajur ini mestilah unik. Keunikan ini dikuatkuasakan oleh indeks atau kekangan unik, yang memastikan bahawa nilai gabungan lajur ini adalah unik merentas jadual.

Indeks dan Kekangan:
Indeks: Indeks unik pada satu atau lebih lajur membolehkan pangkalan data menyemak keunikan nilai dengan cekap. Apabila anda mentakrifkan indeks unik, pangkalan data akan menggunakannya untuk mengesahkan dengan cepat sama ada nilai dalam lajur yang ditentukan sudah wujud. Ini membolehkan klausa onConflict mengesan dan mengendalikan konflik dengan tepat.

Kekangan: Kekangan unik memastikan bahawa nilai dalam satu atau lebih lajur mestilah unik. Kekangan ini penting untuk klausa onConflict berfungsi, kerana ia menguatkuasakan peraturan yang menghalang nilai pendua dan membenarkan pangkalan data mengesan konflik berdasarkan lajur ini.

Serupa dengan pendekatan Kemas Kini Tunggal dengan Pelbagai Syarat, operasi upsert tidak memerlukan transaksi. Memandangkan ia melibatkan satu pertanyaan yang sama ada memasukkan atau mengemas kini rekod, ia beroperasi dengan cekap tanpa overhed tambahan untuk menguruskan transaksi.

---

Kesimpulan

Setiap teknik memberikan kelebihan yang berbeza, daripada memudahkan kod dan mengurangkan interaksi pangkalan data kepada memastikan integriti data dan mengendalikan konflik dengan cekap. Dengan memilih kaedah yang paling sesuai untuk kes penggunaan anda, anda boleh mencapai kemas kini yang lebih cekap dan boleh dipercayai dalam aplikasi anda.

Memahami pendekatan ini membolehkan anda menyesuaikan operasi pangkalan data anda mengikut keperluan khusus anda, meningkatkan prestasi dan kebolehselenggaraan. Sama ada anda berurusan dengan kemas kini pukal atau tugas pengurusan data yang kompleks, memilih strategi yang betul adalah penting untuk mengoptimumkan aliran kerja anda dan mencapai hasil yang lebih baik dalam projek pembangunan anda.

Atas ialah kandungan terperinci Pendekatan QL untuk Kemas Kini Pukal Rekod dengan Knex.js. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!