MySQL의 여러 업데이트

MySQL의 여러 업데이트

한 번에 여러 행을 삽입할 수 있는 것으로 알고 있는데, MySQL에서 여러 행을 한 번에 업데이트하는 방법(예: 1개의 쿼리에서)이 있습니까?

편집: 예를 들어 다음과 같은 항목이 있습니다.

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;

네, 가능합니다.INSERT를 사용할 수 있습니다.중복된 키 업데이트 시.

예를 들어 다음과 같습니다.

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 테이블 SET Col1 = CASE ID1 그 후 1일 때2 그 후 2일 2시4시 10분ELSE Col1끝.Col2 = CASE ID3이 되면 3이 된다4시 이후 12시ELSE Col2끝.WHERE ID IN (1, 2, 3, 4);

오래된 질문이지만, 다른 답변으로 주제를 확장하고 싶습니다.

제 말은, 이를 실현하는 가장 쉬운 방법은 트랜잭션으로 여러 쿼리를 묶는 것입니다.된 답변은 "" 입니다.INSERT ... ON DUPLICATE KEY UPDATE훌륭한 해킹이지만 그 결점과 한계를 알아야 합니다.

• 앞에서 설명한 바와 같이 프라이머리 키가 테이블에 존재하지 않는 행을 사용하여 쿼리를 실행하면 쿼리는 새로운 "반쪽짜리" 레코드를 삽입합니다.아마 네가 원하는 게 아닐 거야
• 에서 이 """null""이 표시됩니다."Field 'fieldname' doesn't have a default value"MySQL을 사용합니다.mysql 를 실행 시합니다.

하는 3가지 변종 중 .INSERT ... ON DUPLICATE KEY UPDATEvariant, "case / when / then" 절과 트랜잭션에 대한 순진한 접근 방식을 가진 변형입니다.여기서 python 코드와 결과를 얻을 수 있습니다.전체적인 결론은 케이스 스테이트먼트가 있는 바리안트가 다른 바리안트보다 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. 삽입: 중복 키 업데이트가 있는 삽입
2. 트랜잭션: 트랜잭션 내의 각 레코드에 대해 업데이트를 수행하는 경우
3. 케이스: 업데이트 내의 서로 다른 레코드의 케이스/시기

방금 테스트한 결과, INSERT 방식이 TRANSACTION 방식보다 6.7배 빨랐습니다.3,000열과 30,000열 세트를 모두 입어봤어요.

TRANSACTION 메서드는 여전히 각각의 쿼리를 개별적으로 실행해야 합니다.이 경우 실행 중에 결과를 메모리 등에 배치하지만 시간이 걸립니다.TRANSACTION 메서드는 복제 로그와 쿼리 로그 모두에서 비용이 많이 듭니다.

게다가 CASE 방식은 30,000 레코드의 INSERT 방식보다 41.1배 느렸습니다(TRANSACTION보다 6.1배 느림).MyISAM에서는 75배 느립니다.INSERT 및 CASE 메서드는 1,000개까지의 기록에서도 깨졌습니다.100개의 레코드에서도 CASE 방법은 거의 빠르지 않습니다.

그래서 일반적으로는 INSERT 방식이 가장 좋고 사용하기 쉽다고 생각합니다.쿼리는 더 작고 읽기 쉬우며 작업 쿼리는 1개만 차지합니다.InnoDB와 MyISAM 모두 해당됩니다.

보너스 내용:

INSERT non-default-field 문제의 해결책은 관련 SQL 모드를 일시적으로 끄는 것입니다.SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""). 꼭 저장해 주세요.sql_mode원래대로 되돌릴 생각이라면 먼저.

INSERT 방식으로 auto_increment가 올라간다고 하는 다른 코멘트에 대해서는 InnoDB는 그렇지만 MyISAM은 아닌 것 같습니다.

테스트를 수행하기 위한 코드는 다음과 같습니다.출력도 합니다.php 인터프리터 오버헤드를 제거하기 위한 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`");
}

왜 아무도 하나의 쿼리에서 여러 문장을 언급하지 않는가?

php에서는multi_querymysqli 인스턴스 메서드.

php 매뉴얼에서

MySQL은 선택적으로 하나의 문 문자열에 여러 문을 포함할 수 있습니다.여러 문을 동시에 전송하면 클라이언트와 서버의 라운드 트립이 줄어들지만 특별한 처리가 필요합니다.

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

5.
(영어: 0.20669293403625

0. 티:: 0.0412278175354

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

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

PHP Warning:  Error while sending SET_OPTION packet

하다 보면 조금 더 될 요.max_allowed_packet 중 mysql config " " " "/etc/mysql/my.cnfmysqld.mysqld를 재시작합니다.

여러 개의 주입 명령을 방지하기 위해 구현된 MySQL의 '안전 메커니즘'을 비활성화하는 '멀티 스테이트먼트'라는 설정이 있습니다.MySQL의 '명석한' 구현에서는 일반적으로 사용자가 효율적인 쿼리를 수행할 수 없습니다.

여기(http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html) 는, 설정의 C 의 실장에 관한 정보입니다.

PHP를 사용하는 경우 mysqli를 사용하여 여러 문장을 작성할 수 있습니다(ph는 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();

도움이 됐으면 좋겠다.

삽입할 ID를 제공하기 위해 동일한 테이블의 별칭을 지정할 수 있습니다(행별 업데이트를 수행하는 경우:

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

또, 다른 테이블에서도 갱신할 수 있는 것이 분명합니다.이 경우 업데이트는 "SELECT(선택)" 문으로 지정되는 테이블의 데이터를 제공합니다.두 번째 테이블은 영향을 받지 않도록 쿼리에서 업데이트 값을 명시적으로 명시하고 있습니다.

업데이트 시 Join을 사용하는 데 관심이 있을 수도 있습니다. 이 방법도 가능합니다.

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.

편집: 업데이트 중인 값이 데이터베이스의 다른 위치에서 나오지 않으면 여러 업데이트 쿼리를 발행해야 합니다.

그리고 이제 쉬운 방법은

update my_table m, -- let create a temp table with populated values
    (select 1 as id, 20 as value union -- this part will be generated
     select 2 as id, 30 as value union -- using a backend code
     -- for loop 
     select N as id, X as value
        ) t
set m.value = t.value where t.id=m.id -- now update by join - quick

예..INSERT ON DUPLICATE KEY UPDATE sql 문을 사용할 수 있습니다.구문: 중복 키 업데이트 a=VALUES(a), b=VALUES(b), c=VALUES(c) 시 table_name (a,b,c) VALUES (1,2,3), (4,5,6)에 삽입

사용하다

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

주의:

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

@newtober의 답변을 받아 MySql 8의 새로운 json_table 함수를 사용하여 확장했습니다.이를 통해 다음과 같은 코드로 SQL 텍스트를 작성하는 대신 저장 프로시저를 생성하여 워크로드를 처리할 수 있습니다.

drop table if exists `test`;
create table `test` (
  `Id` int,
  `Number` int,
  PRIMARY KEY (`Id`)
);
insert into test (Id, Number) values (1, 1), (2, 2);

DROP procedure IF EXISTS `Test`;
DELIMITER $$
CREATE PROCEDURE `Test`(
    p_json json
)
BEGIN
    update test s
        join json_table(p_json, '$[*]' columns(`id` int path '$.id', `number` int path '$.number')) v 
        on s.Id=v.id set s.Number=v.number;
END$$
DELIMITER ;

call `Test`('[{"id": 1, "number": 10}, {"id": 2, "number": 20}]');
select * from test;

drop table if exists `test`;

순수한 SQL보다 몇 ms 느리지만 SQL 텍스트를 코드로 생성하는 것보다 더 큰 타격을 입게 되어 기쁩니다.대용량 레코드 세트(JSON 객체의 최대 크기는 1Gb)로 성능이 어느 정도인지 모르겠지만, 한 번에 10,000개의 행을 업데이트할 때 항상 사용합니다.

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

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'

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

이것은 당신이 원하는 것을 성취할 것입니다.아이디만 더 추가하세요.시험해 봤어요.

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

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

언급URL : https://stackoverflow.com/questions/3432/multiple-updates-in-mysql