MySQL Advanced (14) 다양한 값 구현 방법을 가진 여러 레코드의 일괄 업데이트 및 일괄 업데이트

mysql 다양한 값 구현 방법을 사용하여 여러 레코드의 일괄 업데이트 및 일괄 업데이트

mysql에서는 🎜을 사용할 수 있습니다. >업데이트, 로 교체하여 운영하는데, 다음은 mysql 일괄 업데이트 및 성능에 대한 자세한 소개입니다.

일괄 업데이트

mysql업데이트 문은 매우 간단하며 데이터의 특정 필드를 업데이트합니다.

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';

동일한 필드를 동일한 값으로 업데이트하면 mysql도 매우 간단합니다. 🎜>어디그게 다입니다:

UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

여기서 '

other_values'를 참고하세요 은 쉼표(,) 문자열로 구분됩니다. 예: 1,2,3

그런 다음 여러 데이터 조각을 다른 값으로 업데이트하면 많은 사람들이 다음과 같이 쓸 것입니다.

foreach ($display_order as $id => $ordinal) { 
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id"; 
    mysql_query($sql); 
}

는 업데이트 기록을 하나씩 반복하는 것입니다. 기록은 한 번

업데이트하는데, 이는 성능이 저하되고 쉽게 차단될 수 있습니다.

그럼

sql 문을 사용하여 일괄 업데이트를 구현할 수 있나요? mysql은 일괄 업데이트를 구현하는 직접적인 방법을 제공하지 않지만 몇 가지 트릭을 사용하면 구현할 수 있습니다.

UPDATE mytable 
    SET myfield = CASE id 
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3)

를 사용하여 일괄 업데이트를 수행하는 경우의 작은 트릭입니다.

예:

UPDATE categories 
    SET display_order = CASE id 
        WHEN 1 THEN 3 
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
    END
WHERE id IN (1,2,3)

이 문장

sql은 , 업데이트를 의미합니다. display_order 필드, id=1 이면 display_order 값은 3, id=2 인 경우 display_order 의 값은 입니다. 4, id=3 인 경우 display_order 의 값은 5. 은 조건문을 함께 작성하는 것입니다.

여기서

where

부분은 코드 실행에 영향을 미치지 않지만 sql 실행 효율성을 향상시킵니다. sql 문이 수정해야 하는 행 수만 실행하는지 확인하세요. 여기서는 3개의 데이터만 업데이트됩니다. , where 절은 3 행의 데이터만 실행되도록 보장합니다. 여러 값을 업데이트하는 경우 약간만 수정하면 됩니다.

UPDATE categories 
    SET display_order = CASE id 
        WHEN 1 THEN 3 
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
    END, 
    title = CASE id 
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

여기까지는 하나 항목이 완료되었습니다.

mysql

문은 여러 레코드를 업데이트합니다. 하지만 비즈니스에서 사용하려면 서버측 언어와 결합해야 합니다. 여기서는

php

를 예로 들어 mysql 문:

$display_order = array( 
    1 => 4, 
    2 => 1, 
    3 => 2, 
    4 => 3, 
    5 => 9, 
    6 => 5, 
    7 => 8, 
    8 => 9 
); 
$ids = implode(',', array_keys($display_order)); 
$sql = "UPDATE categories SET display_order = CASE id "; 
foreach ($display_order as $id => $ordinal) { 
    $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal); 
} 
$sql .= "END WHERE id IN ($ids)"; 
echo $sql;

这个例子，有8条记录进行更新。代码也很容易理解，你学会了吗

性能分析

当我使用上万条记录利用mysql批量更新，发现使用最原始的批量update发现性能很差，将网上看到的总结一下一共有以下三种办法：

1.批量update，一条记录update一次，性能很差

update test_tbl set dr='2' where id=1;

2.replace into 或者insert into ...on duplicate key update

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

或者使用

insert into test_tbl (id,dr) values  (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

3.创建临时表，先更新临时表，然后从临时表中update

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

注意：这种方法需要用户有temporary 表的create 权限。

下面是上述方法update 100000条数据的性能测试结果：

逐条update

real    0m15.557s

user    0m1.684s

sys    0m1.372s

replace into

real    0m1.394s

user    0m0.060s

sys    0m0.012s

insert into on duplicate key update

real    0m1.474s

user    0m0.052s

sys    0m0.008s

create temporary table and update:

real    0m0.643s

user    0m0.064s

sys    0m0.004s

就测试结果来看，测试当时使用replace into性能较好。

replace into  和insert into on duplicate key update的不同在于：

replace into　操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值

insert into 则是只update重复记录，不会改变其它字段。

QUESTION:

Error Code: 1175. You are using safe update mode and you tried to update a table without a 
WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

SOLVE:

原因是在safe mode下,要强制安全点,update只能跟where了, 要取消这个限制,可以:

SET SQL_SAFE_UPDATES=0;

以上就是mysql进阶(十四) 批量更新与批量更新多条记录的不同值实现方法 的内容，更多相关内容请关注PHP中文网（www.php.cn）！