MySQL의 다중 업데이트 > db

가능합니다. 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);

동적 값을 가지므로 열을 업데이트하려면 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);

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

요점은, 그것을 달성하는 가장 쉬운 방법은 트랜잭션과 함께 여러 쿼리를 래핑하는 것입니다. 대답은 INSERT ... ON DUPLICATE KEY UPDATE 는 좋은 해킹이지만, 단점과 한계를 알고 있어야합니다.

• 기본 키가 테이블에없는 행을 사용하여 쿼리를 시작한 경우 쿼리는 새로운 "절반 굽기"레코드를 삽입합니다. 아마 네가 원하는게 아닐거야.
• 기본값이없는 null이 아닌 필드가있는 테이블이 있고 쿼리에서이 필드를 터치하지 않으려면 "Field 'fieldname' doesn't have a default value" 메시지가 표시됩니다. 단일 행을 삽입하십시오. 엄격한 결정을 내리고 mysql 경고를 앱의 런타임 예외로 바꾸려면 문제가 발생할 수 있습니다.

INSERT ... ON DUPLICATE KEY UPDATE 변형, "case / when / then"절이 포함 된 변형 및 트랜잭션에 대한 순진한 접근법 등 3 가지 변형에 대한 성능 테스트를 수행했습니다. 파이썬 코드와 결과를 얻을 수 있습니다. 전반적인 결론은 case 문을 포함한 variant가 두 개의 다른 변형보다 2 배 빠르다는 것입니다. 그러나 정확한 및 인젝션 - 세이프 코드를 작성하는 것은 매우 어렵 기 때문에 가장 간단한 방법 인 트랜잭션 사용에 개인적으로 충실합니다.

편집 : Dakusan 결과는 내 실적 추정치가 유효하지 않다는 것을 증명합니다. 보다 정교한 연구를 위해이 답변 을보십시오.

다른 유용한 옵션이 아직 언급되지 않은 이유를 모르겠습니다.

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;

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

나는 3 가지 다른 방법의 속도가 중요하다는 것을 느낀다.

3 가지 방법이 있습니다.

1. INSERT : ON DUPLICATE KEY UPDATE가있는 INSERT
2. 거래 : 거래 내 각 레코드에 대한 업데이트를 수행하는 곳
3. 사례 : UPDATE 내의 각기 다른 레코드에 대한 케이스 / 사례

방금 테스트 한 결과 INSERT 메서드가 TRANSACTION 메서드보다 나 6.7 배 빠릅니다. 나는 3,000과 3 만 줄을 모두 시험해 보았습니다.

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

더 나쁜 것은 CASE 메서드가 INSERT 메서드가 30,000 레코드 (TRANSACTION보다 6.1 배 느리다)보다 41.1x 느리다는 것입니다. MyISAM에서는 75 배 느립니다. INSERT 및 CASE 메서드는 ~ 1,000 개의 레코드에서도 손상되었습니다. 100 레코드에서도 CASE 메서드는 매우 빠릅니다.

그래서 일반적으로 INSERT 메서드는 사용하기 가장 쉽고 사용하기 쉽습니다. 쿼리는 작고 읽기 쉬우 며 1 개의 쿼리 만 취합니다. 이것은 InnoDB와 MyISAM 모두에 적용됩니다.

보너스 물건 :

SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES","") INSERT 디폴트가 아닌 INSERT 문에 대한 해결책은 관련 SQL 모드를 일시적으로 해제하는 것입니다. SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES","") . 되돌릴 계획이라면 먼저 sql_mode 를 저장하십시오.

Auto_increment가 INSERT 메서드를 사용하여 위로 올라간다는 것을 알았던 다른 의견에 관해서는, 나는 그것도 테스트했고, 그렇지 않은 것으로 보인다.

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

UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

이것은 나중에 작동합니다.

MySQL 매뉴얼 에는 여러 테이블에 대한 참조가있다.

임시 테이블 사용

// Reorder items
function update_items_tempdb(&$items)
{
    shuffle($items);
    $table_name = uniqid('tmp_test_');
    $sql = "CREATE TEMPORARY TABLE `$table_name` ("
        ."  `id` int(10) unsigned NOT NULL AUTO_INCREMENT"
        .", `position` int(10) unsigned NOT NULL"
        .", PRIMARY KEY (`id`)"
        .") ENGINE = MEMORY";
    query($sql);
    $i = 0;
    $sql = '';
    foreach ($items as &$item)
    {
        $item->position = $i++;
        $sql .= ($sql ? ', ' : '')."({$item->id}, {$item->position})";
    }
    if ($sql)
    {
        query("INSERT INTO `$table_name` (id, position) VALUES $sql");
        $sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position"
            ." WHERE `$table_name`.id = `test`.id";
        query($sql);
    }
    query("DROP TABLE `$table_name`");
}

다중 명령문 (multiple statement)이라고 불리는 설정을 변경할 수 있습니다.이 문법은 (하나 이상의) 주입 명령을 방지하기 위해 구현 된 MySQL의 '안전 메커니즘'을 비활성화합니다. 일반적으로 MySQL의 '화려한'구현에 ​​따라 사용자가 효율적인 쿼리를 수행 할 수 없게됩니다.

여기 ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html )는 C 설정 구현에 대한 정보입니다.

PHP를 사용하고 있다면, mysqli를 사용하여 다중 문장을 처리 할 수있다 (PHP는 mysqli와 함께 제공되었다고 생각한다)

$con = new mysqli('localhost','user1','password','my_database');
$query = "Update MyTable SET col1='some value' WHERE id=1 LIMIT 1;";
$query .= "UPDATE MyTable SET col1='other value' WHERE id=2 LIMIT 1;";
//etc
$con->multi_query($query);
$con->close();

희망이 도움이됩니다.

가능하면 업데이트에 대한 조인을 사용하는 것도 좋습니다.

Update someTable Set someValue = 4 From someTable s Inner Join anotherTable a on s.id = a.id Where a.id = 4
-- Only updates someValue in someTable who has a foreign key on anotherTable with a value of 4.

편집 : 업데이트중인 값이 데이터베이스의 다른 곳에서 오지 않는다면 여러 개의 업데이트 쿼리를 실행해야합니다.

동일한 테이블을 별명으로 지정하여 삽입하려는 ID를 부여 할 수 있습니다 (행 단위 업데이트를 수행하는 경우 :

UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET 
col1 = 1
,col2 = 2
. . . 
WHERE 
tab1.id = tab2.id;

또한 다른 테이블에서도 업데이트 할 수 있다는 것이 명백해야합니다. 이 경우 업데이트는 "SELECT"문구를 두 배로하여 지정한 테이블의 데이터를 제공합니다. 명시 적으로 조회에서 갱신 값을 나타내므로 두 x 째 테이블은 영향을 ^ 지 않습니다.

하나의 쿼리에서 여러 문장을 언급하지 않는 이유는 무엇입니까?

PHP에서는 mysqli 인스턴스의 multi_query 메소드를 사용한다.

PHP 매뉴얼에서

MySQL은 선택적으로 하나의 명령문 문자열에 여러 개의 명령문을 포함 할 수 있습니다. 한 번에 여러 개의 명령문을 보내면 클라이언트 - 서버 왕복은 줄어들지 만 특수 처리가 필요합니다.

다음은 업데이트 30,000 개 원시의 다른 3 가지 메소드와 비교 한 결과입니다. 코드는 @Dakusan의 답을 기반으로 여기 에서 찾을 수 있습니다.

거래 : 5.5194580554962 
삽입물 : 0.20669293403625 
사례 : 16.474853992462 
멀티 : 0.0412278175354 

보시다시피 여러 문장의 쿼리가 가장 높은 답변보다 효율적입니다.

다음과 같은 오류 메시지가 표시되는 경우

PHP Warning:  Error while sending SET_OPTION packet

내 컴퓨터에서 /etc/mysql/my.cnf mysql 설정 파일의 max_allowed_packet 을 늘린 다음 mysqld를 다시 시작해야 할 수도있다.

용도

REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);

참고 사항 :

• id는 기본 고유 키 여야합니다.
• 외래 키를 사용하여 테이블을 참조하는 경우 REPLACE는 삽입을 삭제하므로 오류가 발생할 수 있습니다

다음은 한 테이블의 모든 행을 업데이트합니다.

Update Table Set
Column1 = 'New Value'

다음은 Column2 값이 5보다 큰 모든 행을 갱신합니다.

Update Table Set
Column1 = 'New Value'
Where
Column2 > 5

하나 이상의 테이블을 업데이트하는 모든 Unkwntech 의 사례가 있습니다.

UPDATE table1, table2 SET
table1.col1 = 'value',
table2.col1 = 'value'
WHERE
table1.col3 = '567'
AND table2.col6='567'

예 : INSERT ON DUPLICATE KEY UPDATE sql 문을 사용하면 가능합니다. 구문 : INSERT INTO table_name (a, b, c) VALUES (1,2,3), (4,5,6) ON 중복 키 업데이트 a = VALUES (a), b = VALUES (b), c = VALUES (c)

PHP로 이것을했습니다. 세미콜론을 사용하여 배열로 분할 한 다음 루프를 통해 제출하십시오.

$con = new mysqli('localhost','user1','password','my_database');
$batchUpdate = true; /*You can choose between batch and single query */
$queryIn_arr = explode(";", $queryIn);

if($batchUpdate)    /* My SQL prevents multiple insert*/
{
    foreach($queryIn_arr as $qr)
    {
        if(strlen($qr)>3)
        {
            //echo '<br>Sending data to SQL1:<br>'.$qr.'</br>';
            $result = $conn->query($qr);
        }

    }
}
else
{
    $result = $conn->query($queryIn);
}
$con->close();

UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

이것은 당신이 찾고있는 것을 달성해야합니다. 더 많은 ID를 추가하십시오. 나는 그것을 시험했다.

UPDATE `your_table` SET 

`something` = IF(`id`="1","new_value1",`something`), `smth2` = IF(`id`="1", "nv1",`smth2`),
`something` = IF(`id`="2","new_value2",`something`), `smth2` = IF(`id`="2", "nv2",`smth2`),
`something` = IF(`id`="4","new_value3",`something`), `smth2` = IF(`id`="4", "nv3",`smth2`),
`something` = IF(`id`="6","new_value4",`something`), `smth2` = IF(`id`="6", "nv4",`smth2`),
`something` = IF(`id`="3","new_value5",`something`), `smth2` = IF(`id`="3", "nv5",`smth2`),
`something` = IF(`id`="5","new_value6",`something`), `smth2` = IF(`id`="5", "nv6",`smth2`) 

// PHP처럼 만들었습니다.

$q = 'UPDATE `your_table` SET ';

foreach($data as $dat){

  $q .= '

       `something` = IF(`id`="'.$dat->id.'","'.$dat->value.'",`something`), 
       `smth2` = IF(`id`="'.$dat->id.'", "'.$dat->value2.'",`smth2`),';

}

$q = substr($q,0,-1);

따라서 하나의 쿼리로 구멍 테이블을 업데이트 할 수 있습니다.