MySQL은 여러 업데이트

난 당신이 한 번에 여러 행을 삽입 할 수 있다는 것을 알고, 한 번에 여러 행을 업데이트하는 방법은 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;

해결법

==============================
1.네, 가능합니다 - 당신은 INSERT ... ON DUPLICATE KEY UPDATE를 사용할 수 있습니다.

네, 가능합니다 - 당신은 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);
```

==============================

2.동적 값을 가지고 있기 때문에, 당신은 열을 업데이트 할 IF 또는 CASE을 사용해야합니다. 그것은 좀 추한 도착,하지만 작동합니다.

동적 값을 가지고 있기 때문에, 당신은 열을 업데이트 할 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);

==============================
3.문제는 오래된, 그러나 나는 다른 대답 항목을 확장하고 싶습니다.

문제는 오래된, 그러나 나는 다른 대답 항목을 확장하고 싶습니다.

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

나는 INSERT ... ON DUPLICATE KEY UPDATE 변형 "케이스 / 때 / 다음"절 변형 및 거래와 순진한 접근 방식을 포함하여 제안 변형, 세 가지에 대한 몇 가지 성능 테스트를했다. 여기 파이썬 코드와 결과를 얻을 수 있습니다. 전반적인 결론은 경우 문을 사용하여 변형이 배 빠른 다른 두 변종과 같은 것으로 판명 것입니다,하지만 난 개인적으로 가장 간단한 방법에 충실 그래서, 그것을 위해 정확하고 주입 안전한 코드를 작성하는 것은 매우 어렵다 : 트랜잭션을 사용.

편집 : Dakusan의 연구 결과 내 실적 추정은 매우 유효하지 않은 것을 증명한다. 또 다른, 더 정교한 연구에 대한이 답변을 참조하시기 바랍니다.

==============================

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

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

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;

==============================
5.다음의 모든 InnoDB에 적용됩니다.

다음의 모든 InnoDB에 적용됩니다.

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

3 가지 방법이 있습니다 :

난 그냥이 테스트 및 INSERT 방법은 6.7 배 더 빨리 나를 위해 거래 방법보다했다. 나는 모두 3,000 30,000 행 집합에 노력했다.

거래 방법은 여전히 실행하는 동안, 메모리, 또는 무언가에 결과를 일괄 처리하지만, 시간이 소요되는, 개별적으로 쿼리를 각각 실행한다. 거래 방법은 모두 복제 및 쿼리 로그에 꽤 비싸다.

더 나쁜 경우 방법은 / 30,000 기록 (느린 TRANSACTION에 비해 6.1 배) 승 41.1x 느린 삽입 방법보다했다. 그리고의 MyISAM에서 75X 느린. INSERT 및 CASE 방법은 심지어 ~ 1,000 기록을 끊었다. 심지어 100 개 개의 레코드에서의 CASE 방법은 간신히 빠릅니다.

그래서 일반적으로, 나는 INSERT 방법은 사용에 모두 가장 쉬운 느낌. 쿼리는 읽기 작업 만 1 개 쿼리를 취할 작고 쉽다. 이 이노와의 MyISAM 모두에 적용됩니다.

보너스 물건 :

삽입 기본이 아닌 필드 문제에 대한 해결책은 일시적으로 관련 SQL 모드를 해제하는 것입니다 : SET 세션 sql_mode을 = REPLACE ((교체 SESSION.sql_mode @@ "STRICT_TRANS_TABLES", ""), "STRICT_ALL_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).';');
}
```

==============================

6.임시 테이블을 사용하여

임시 테이블을 사용하여

// 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`");
}

==============================
7.
```
UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'
```
이것은 나중에 작동합니다.

여러 테이블에 대한 MySQL의 설명서에 대한 참조가 있습니다.
==============================
8.왜 아무도 하나 개의 쿼리에 여러 개의 문을 언급하지 않습니다?

왜 아무도 하나 개의 쿼리에 여러 개의 문을 언급하지 않습니다?

PHP에서, 당신은 mysqli 인스턴스의 multi_query 방법을 사용합니다.

PHP 매뉴얼에서

여기서 업데이트 30,000 원 다른 3 가지 방법으로 비교 한 결과이다. 코드 @Dakusan에서 답변을 기반으로하는 여기에서 찾을 수 있습니다

거래 : 5.5194580554962 삽입 : 0.20669293403625를 케이스 : 16.474853992462 멀티 : 0.0412278175354

당신이 볼 수 있듯이, 여러 문 쿼리는 가장 높은 답변을보다 더 효율적이다.

이 같은 오류 메시지가 나타나면 :
```
PHP Warning:  Error while sending SET_OPTION packet
```
당신은 내 컴퓨터에서 다시 시작 mysqld를 /etc/mysql/my.cnf하고있다 MySQL의 설정 파일에서의 max_allowed_packet을 늘려야합니다.
==============================
9.당신이 (하나 이상) 주입 명령을 방지하기 위해 구현 '멀티 문'이 비활성화의 MySQL의 '안전 장치'라는 변경할 수있는 설정이 있습니다. MySQL의의 '화려한'구현에 일반적인, 또한 효율적인 쿼리를 수행로부터 사용자를 방지 할 수 있습니다.

당신이 (하나 이상) 주입 명령을 방지하기 위해 구현 '멀티 문'이 비활성화의 MySQL의 '안전 장치'라는 변경할 수있는 설정이 있습니다. MySQL의의 '화려한'구현에 일반적인, 또한 효율적인 쿼리를 수행로부터 사용자를 방지 할 수 있습니다.

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

당신이 PHP를 사용하는 경우, 당신은 멀티 문을 수행 할 mysqli를 사용할 수 있습니다 (I는 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();
```
희망이 도움이.
==============================
10.당신은 ID가 당신이 행 단위 갱신을 수행하는 경우 (에 의해 삽입 할의 당신에게 동일한 테이블 별칭을 수 있습니다 :

당신은 ID가 당신이 행 단위 갱신을 수행하는 경우 (에 의해 삽입 할의 당신에게 동일한 테이블 별칭을 수 있습니다 :
```
UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET 
col1 = 1
,col2 = 2
. . . 
WHERE 
tab1.id = tab2.id;
```
또한, 그것은 당신이 또한 다른 테이블에서 업데이트 할 수 있습니다 분명 보인다해야한다. 이 경우, 테이블에서 당신에게 데이터를 제공하는 "SELECT"문으로 업데이트 복식, 당신은 지정된다. 당신은 명시 적으로 업데이트 값 때문에 쿼리에서 주장하는, 두 번째 테이블은 영향을받지 않습니다.
==============================
11.또한 업데이트에 조인뿐만 아니라 수있는 사용에 관심이있을 수 있습니다.

또한 업데이트에 조인뿐만 아니라 수있는 사용에 관심이있을 수 있습니다.
```
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.
```
편집 : 업데이트중인 값이 데이터베이스에 다른 곳에서 오는되지 않은 경우, 여러 업데이트 쿼리를 실행해야합니다.

==============================

12.사용하다

사용하다

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

참고 :

==============================
13.다음은 하나 개의 테이블에있는 모든 행을 업데이트합니다

다음은 하나 개의 테이블에있는 모든 행을 업데이트합니다
```
Update Table Set
Column1 = 'New Value'
```
열 2의 값이 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'
```
==============================
14.예 가깝지만 INSERT ON DUPLICATE KEY UPDATE SQL 문을 사용하여 가능하다 .. 통사론: INSERT INTO의 TABLE_NAME (A, B, C) VALUES (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE A = VALUES (a), B = VALUES (b), C = VALUES (c)

예 가깝지만 INSERT ON DUPLICATE KEY UPDATE SQL 문을 사용하여 가능하다 .. 통사론: INSERT INTO의 TABLE_NAME (A, B, C) VALUES (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE A = VALUES (a), B = VALUES (b), C = VALUES (c)
==============================
15.
```
UPDATE tableName SET col1='000' WHERE id='3' OR id='5'
```
이 you'r이 찾고있는 무엇을 달성해야한다. 그냥 더 아이디의를 추가합니다. 나는 그것을 테스트했습니다.

==============================

16.

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

당신은 하나 개의 쿼리로 구멍 테이블을 업데이트 할 수 있도록

from https://stackoverflow.com/questions/3432/multiple-updates-in-mysql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 오라클 SQL은 : 다른 테이블의 데이터로 테이블을 업데이트 (0)	2020.03.09
[SQL] 사용하여 SQL 업데이트 쿼리 조인 (0)	2020.03.09
[SQL] 권장 SQL 데이터베이스 태그의 디자인 또는 태그 [마감] (0)	2020.03.08
[SQL] 첫번째 날짜 오름차순, 널 (null)에 의해 PostgreSQL의 종류? (0)	2020.03.08
[SQL] 왼쪽은 NULL이 WHERE IS 가입 대 NOT이 NOT IN 대 EXISTS 사이의 차이점은 무엇입니까? (0)	2020.03.08

복붙노트

[SQL] MySQL은 여러 업데이트

MySQL은 여러 업데이트

해결법

1.네, 가능합니다 - 당신은 INSERT ... ON DUPLICATE KEY UPDATE를 사용할 수 있습니다.

2.동적 값을 가지고 있기 때문에, 당신은 열을 업데이트 할 IF 또는 CASE을 사용해야합니다. 그것은 좀 추한 도착,하지만 작동합니다.

3.문제는 오래된, 그러나 나는 다른 대답 항목을 확장하고 싶습니다.

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

5.다음의 모든 InnoDB에 적용됩니다.

6.임시 테이블을 사용하여

7.

8.왜 아무도 하나 개의 쿼리에 여러 개의 문을 언급하지 않습니다?

10.당신은 ID가 당신이 행 단위 갱신을 수행하는 경우 (에 의해 삽입 할의 당신에게 동일한 테이블 별칭을 수 있습니다 :

11.또한 업데이트에 조인뿐만 아니라 수있는 사용에 관심이있을 수 있습니다.

12.사용하다

13.다음은 하나 개의 테이블에있는 모든 행을 업데이트합니다

14.예 가깝지만 INSERT ON DUPLICATE KEY UPDATE SQL 문을 사용하여 가능하다 .. 통사론: INSERT INTO의 TABLE_NAME (A, B, C) VALUES (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE A = VALUES (a), B = VALUES (b), C = VALUES (c)

15.

16.

'SQL' 카테고리의 다른 글

티스토리툴바