MySQL의 여러 업데이트

mysql sql sql-update


한 번에 여러 행을 삽입 할 수 있다는 것을 알고 있습니다. MySQL에서 한 번에 여러 행을 업데이트하는 방법이 있습니까 (하나의 쿼리에서와 같이)?

편집 : 예를 들어 다음이 있습니다.

Name   id  Col1  Col2
Row1   1    6     1
Row2   2    2     3
Row3   3    9     5
Row4   4    16    8

다음 업데이트를 모두 하나의 쿼리로 결합하고 싶습니다.

UPDATE table SET Col1 = 1 WHERE id = 1;
UPDATE table SET Col1 = 2 WHERE id = 2;
UPDATE table SET Col2 = 3 WHERE id = 3;
UPDATE table SET Col1 = 10 WHERE id = 4;
UPDATE table SET Col2 = 12 WHERE id = 4;



Answer 1 Michiel de Mare


예, 가능합니다. INSERT ... ON DUPLICATE KEY UPDATE를 사용할 수 있습니다.

귀하의 예를 사용하여 :

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);



Answer 2 Harrison Fisk


동적 값이 있으므로 업데이트 할 열에 IF 또는 CASE를 사용해야합니다. 좀 못 생겼지 만 작동해야합니다.

귀하의 예를 사용하여 다음과 같이 할 수 있습니다.

UPDATE table SET Col1 = CASE id 
                          WHEN 1 THEN 1 
                          WHEN 2 THEN 2 
                          WHEN 4 THEN 10 
                          ELSE Col1 
                        END, 
                 Col2 = CASE id 
                          WHEN 3 THEN 3 
                          WHEN 4 THEN 12 
                          ELSE Col2 
                        END
             WHERE id IN (1, 2, 3, 4);



Answer 3 Roman Imankulov


질문은 오래되었지만 다른 답변으로 주제를 확장하고 싶습니다.

내 요점은이를 달성하는 가장 쉬운 방법은 트랜잭션으로 여러 쿼리를 래핑하는 것입니다. 허용되는 대답 INSERT ... ON DUPLICATE KEY UPDATE 는 좋은 해킹이지만 단점과 제한 사항을 알고 있어야합니다.

  • 말했듯이 테이블에 기본 키가없는 행으로 쿼리를 시작하면 쿼리는 새로운 "반만"레코드를 삽입합니다. 아마도 그것은 당신이 원하는 것이 아닙니다.
  • 기본값이없는 null이 아닌 필드가있는 테이블이 있고 쿼리에서이 필드를 건드리지 않으려면 "Field 'fieldname' doesn't have a default value" MySQL 경고가 표시됩니다. t 단일 행을 삽입하십시오. 엄격하게 결정하고 mysql 경고를 앱의 런타임 예외로 전환하면 문제가 발생할 것입니다.

INSERT ... ON DUPLICATE KEY UPDATE 변형, "case / when / then"절이있는 변형 및 트랜잭션에 대한 순진한 접근 방식을 포함하여 세 가지 제안 된 변형에 대해 몇 가지 성능 테스트를 수행했습니다. 여기 에서 파이썬 코드와 결과를 얻을 수 있습니다 . 전체적인 결론은 case 문이있는 변형은 다른 두 변형보다 두 배 빠르지 만 정확하고 주입이 안전한 코드를 작성하기가 매우 어렵 기 때문에 개인적으로 가장 간단한 접근 방식 인 트랜잭션 사용을 고수합니다.

편집 : Dakusan의 결과는 내 성능 평가가 유효하지 않다는 것을 증명합니다. 더 정교한 다른 연구에 대해서는 이 답변 을 참조하십시오 .




Answer 4 newtover


다른 유용한 옵션이 아직 언급되지 않은 이유가 확실하지 않습니다.

UPDATE my_table m
JOIN (
    SELECT 1 as id, 10 as _col1, 20 as _col2
    UNION ALL
    SELECT 2, 5, 10
    UNION ALL
    SELECT 3, 15, 30
) vals ON m.id = vals.id
SET col1 = _col1, col2 = _col2;



Answer 5 Dakusan


다음은 모두 InnoDB에 적용됩니다.

세 가지 방법의 속도를 아는 것이 중요하다고 생각합니다.

세 가지 방법이 있습니다.

  1. INSERT : ON DUPLICATE KEY UPDATE와 함께 INSERT
  2. 트랜잭션 : 트랜잭션 내 각 레코드에 대한 업데이트를 수행하는 위치
  3. CASE : UPDATE 내의 각기 다른 레코드에 대한 케이스 / 언제

방금 이것을 테스트 했는데 INSERT 방법이 TRANSACTION 방법보다 6.7 배 더 빠릅니다. 나는 3,000 행과 30,000 행을 모두 시도했습니다.

TRANSACTION 메서드는 여전히 각각의 쿼리를 개별적으로 실행해야하는데, 실행하는 동안 결과를 메모리 나 다른 곳에 일괄 처리하지만 시간이 걸립니다. TRANSACTION 방법은 복제 및 쿼리 로그 모두에서 상당히 비쌉니다.

더 나쁜 것은 CASE 방법이 30,000 개 레코드가있는 INSERT 방법보다 41.1 배 더 느 렸습니다 (TRANSACTION보다 6.1 배 느림). 그리고 75X 의 MyISAM 느린. INSERT 및 CASE 메서드는 최대 1,000 개의 레코드에서도 중단되었습니다. 100 개의 레코드에서도 CASE 방법이 엄청나게 빠릅니다.

따라서 일반적으로 INSERT 방법이 가장 좋고 사용하기 쉽습니다. 쿼리는 더 작고 읽기 쉬우 며 하나의 쿼리 만 수행합니다. 이것은 InnoDB와 MyISAM 모두에 적용됩니다.

보너스 항목 :

기본 필드가 아닌 INSERT 문제에 대한 해결책은 관련 SQL 모드를 일시적으로 해제하는 것입니다. SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES","") . 되돌릴 계획이라면 먼저 sql_mode 를 저장하십시오 .

다른 의견에 관해서는 auto_increment가 INSERT 메서드를 사용하여 올라간다는 것을 보았습니다. 이는 InnoDB의 경우이지만 MyISAM은 아닙니다.

테스트를 실행하는 코드는 다음과 같습니다. 또한 .SQL 파일을 출력하여 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).';');
}