Hướng dẫn multiple update query in mysql - nhiều cập nhật truy vấn trong mysql

Question

Tất cả những điều sau đây áp dụng cho Innodb.

Nội dung chính Show

Làm cách nào để chạy nhiều câu lệnh cập nhật trong MySQL?
3 lệnh cập nhật trong SQL là gì?
Chúng ta có thể đặt nhiều giá trị trong truy vấn cập nhật không?
Làm cách nào để cập nhật nhiều hàng cùng một lúc?

Tôi cảm thấy biết tốc độ của 3 phương pháp khác nhau là quan trọng.

Có 3 phương pháp:

Chèn: Chèn vào bản cập nhật khóa trùng lặp
Giao dịch: Trường hợp bạn thực hiện cập nhật cho mỗi bản ghi trong một giao dịch
Trường hợp: trong đó bạn là một trường hợp/khi nào cho mỗi bản ghi khác nhau trong bản cập nhật

Tôi vừa thử nghiệm điều này và phương thức chèn nhanh hơn 6,7 lần so với phương thức giao dịch. Tôi đã thử trên một bộ gồm cả 3.000 và 30.000 hàng.6.7x faster for me than the TRANSACTION method. I tried on a set of both 3,000 and 30,000 rows.

Phương thức giao dịch vẫn phải chạy từng truy vấn riêng lẻ, cần có thời gian, mặc dù nó ghép các kết quả trong bộ nhớ hoặc một cái gì đó, trong khi thực hiện. Phương pháp giao dịch cũng khá đắt trong cả nhật ký sao chép và truy vấn.

Thậm chí tệ hơn, phương thức trường hợp chậm hơn 41,1 lần so với phương thức chèn w/ 30.000 bản ghi (chậm hơn 6,1 lần so với giao dịch). Và chậm hơn 75 lần ở Myisam. Phương pháp chèn và trường hợp bị phá vỡ ngay cả tại ~ 1.000 hồ sơ. Ngay cả ở 100 bản ghi, phương pháp trường hợp hầu như không nhanh hơn.41.1x slower than the INSERT method w/ 30,000 records (6.1x slower than TRANSACTION). And 75x slower in MyISAM. INSERT and CASE methods broke even at ~1,000 records. Even at 100 records, the CASE method is BARELY faster.

Vì vậy, nói chung, tôi cảm thấy phương pháp chèn là tốt nhất và dễ sử dụng nhất. Các truy vấn nhỏ hơn và dễ đọc hơn và chỉ thực hiện 1 truy vấn hành động. Điều này áp dụng cho cả Innodb và Myisam.

Công cụ thưởng:

Giải pháp cho vấn đề chèn không mặc định là tạm thời tắt các chế độ SQL có liên quan: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""). Hãy chắc chắn để lưu sql_mode trước nếu bạn có kế hoạch hoàn nguyên nó.

Đối với các nhận xét khác, tôi đã thấy rằng nói rằng tự động tăng lên bằng phương pháp chèn, điều này dường như là trường hợp trong innodb, nhưng không phải là myisam.

Mã để chạy các bài kiểm tra như sau. Nó cũng xuất ra các tệp .sql để xóa trình thông dịch PHP trên đầu

<?php
//Variables
$NumRows=30000;

//These 2 functions need to be filled in
function InitSQL()
{

}
function RunSQLQuery($Q)
{

}

//Run the 3 tests
InitSQL();
for($i=0;$i<3;$i++)
    RunTest($i, $NumRows);

function RunTest($TestNum, $NumRows)
{
    $TheQueries=Array();
    $DoQuery=function($Query) use (&$TheQueries)
    {
        RunSQLQuery($Query);
        $TheQueries[]=$Query;
    };

    $TableName='Test';
    $DoQuery('DROP TABLE IF EXISTS '.$TableName);
    $DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
    $DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');

    if($TestNum==0)
    {
        $TestName='Transaction';
        $Start=microtime(true);
        $DoQuery('START TRANSACTION');
        for($i=1;$i<=$NumRows;$i++)
            $DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);
        $DoQuery('COMMIT');
    }
    
    if($TestNum==1)
    {
        $TestName='Insert';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)');
    }
    
    if($TestNum==2)
    {
        $TestName='Case';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
    }
    
    print "$TestName: ".(microtime(true)-$Start)."<br>\n";

    file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}

Bạn có một sinh viên bảng với id, score1 và score2 như thế này, trong đó id là chìa khóa chính:

Tôi	score1	score2
1	10	9
2	8	3
3	10	6
4	4	8

Và đây là bảng dữ liệu mới mà bạn muốn cập nhật lên:

Tôi	score1	score2
1	5	8
2	10	8
3	8	3
4	10	7

Và đây là bảng dữ liệu mới mà bạn muốn cập nhật lên:

Có một vài cách để làm điều đó.

UPDATE students SET score1 = 5, score2 = 8 WHERE id = 1;
UPDATE students SET score1 = 10, score2 = 8 WHERE id = 2;
UPDATE students SET score1 = 8, score2 = 3 WHERE id = 3;
UPDATE students SET score1 = 10, score2 = 7 WHERE id = 4;

1. Bạn có thể viết nhiều truy vấn cập nhật như thế này và chạy tất cả chúng cùng một lúc:

UPDATE students s
JOIN (
    SELECT 1 as id, 5 as new_score1, 8 as new_score2
    UNION ALL
    SELECT 2, 10, 8
    UNION ALL
    SELECT 3, 8, 3
    UNION ALL
    SELECT 4, 10, 7
) vals ON s.id = vals.id
SET score1 = new_score1, score2 = new_score2;

2. Hoặc bạn có thể cập nhật với câu lệnh tham gia:

INSERT INTO students 
    (id, score1, score2)
    VALUES 
        (1, 5, 8),
        (2, 10, 8),
        (3, 8, 3),
        (4, 10, 7)
    ON DUPLICATE KEY UPDATE 
        score1 = VALUES(score1),
    score2 = VALUES(score2);

3. Hoặc bạn có thể sử dụng

UPDATE students SET score1 = 5, score2 = 8 WHERE id = 1;
UPDATE students SET score1 = 10, score2 = 8 WHERE id = 2;
UPDATE students SET score1 = 8, score2 = 3 WHERE id = 3;
UPDATE students SET score1 = 10, score2 = 7 WHERE id = 4;

0

Cần một công cụ GUI tốt cho MySQL? TablePlus là một công cụ hiện đại, gốc với giao diện người dùng thanh lịch, cho phép bạn quản lý đồng thời nhiều cơ sở dữ liệu như MySQL, PostgreSQL, SQLite, Microsoft SQL Server, và hơn thế nữa.. It’s free anyway!

Tải xuống TablePlus cho Mac. Dù sao thì nó cũng miễn phí! Download TablePlus for Windows.

Không phải trên Mac? Tải xuống bảng cho Windows. Download TablePlus for Linux

Trên Linux? Tải xuống bảng cho Linux Download TablePlus for iOS.

Làm cách nào để chạy nhiều câu lệnh cập nhật trong MySQL?

Có thể sử dụng Cập nhật nhiều cột Cập nhật MySQL để cập nhật nhiều cột bằng cách chỉ định danh sách phân tách dấu phẩy của cột_name = new_value. Trong đó cột_name là tên của cột sẽ được cập nhật và new_value là giá trị mới mà cột sẽ được cập nhật.MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.

3 lệnh cập nhật trong SQL là gì?

3 lệnh cập nhật trong SQL là gì ?..

Chèn - Thêm một bản ghi đơn hoặc nhiều trong bảng ..

Cập nhật - Sửa đổi một bản ghi hiện có ..

Xóa - Xóa một bản ghi khỏi cơ sở dữ liệu ..

Chúng ta có thể đặt nhiều giá trị trong truy vấn cập nhật không?

Chúng tôi có thể cập nhật nhiều cột bằng cách chỉ định nhiều cột sau lệnh SET trong câu lệnh Update.Câu lệnh cập nhật luôn được theo sau bởi lệnh set, nó chỉ định cột khi yêu cầu cập nhật.. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.

Làm cách nào để cập nhật nhiều hàng cùng một lúc?

Đầu tiên, chỉ định tên bảng mà bạn muốn thay đổi dữ liệu trong mệnh đề cập nhật.Thứ hai, gán một giá trị mới cho cột mà bạn muốn cập nhật.Trong trường hợp bạn muốn cập nhật dữ liệu trong nhiều cột, mỗi cặp cột = giá trị được phân tách bằng dấu phẩy (,).Thứ ba, chỉ định các hàng bạn muốn cập nhật trong mệnh đề WHERE.

programming mysql MySQL FOR UPDATE