mysql批量更新多条记下的相同个字段为差别值的艺术,批量翻新的三种办法

近年项目需求用到批量更新数据Curry的多寡,在网上找了弹指间那地点的例子,觉得这些还不易,分享给大家。

先是mysql更新数据的某部字段,一般那样写:

批量创新的方法:

批量翻新的办法:

在这一个业务里内部涉及到了更新两张数据表,那么大家是否会想到非凡简单,立即上代码

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

1 ) 逐条更新

1 ) 逐条更新

$sql = "update newhouse_clicks set clicks=6,type=1,update_time=time() where is=$value['id']";

也得以这么用in钦点要翻新的记录:

代码如下:

代码如下:

 当中多少表名为newhouse_clicks,有七个字段,主键id,type(类型-整型)字段,clicks(点击量-整型)字段,update_time(整型)字段
那般做真正并未难点吧?比如说批量更新当前城市下的持有楼盘,比如说日本东京,打个比方壹仟条数据,那么在工作里面是否即将那样写

UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');
UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';
UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';
$data = array(id=>1,id=>2,..........id=>1000);//省略数据
foreach($data as $key=>$value) {
    $sql = "update newhouse_clicks set clicks=6,type=1,update_time=time() where is=$value['id']";
}

此间注意 ‘other_values’
是2个逗号(,)分隔的字符串,如:1,2,3

假使更新同一字段为同2个值,mysql也一点也不细略,修改下where即可:

设若更新同一字段为同1个值,mysql也很简单,修改下where即可:

 那样在论及到多张表*一千条数据,那么会不会有相当大的延时呢?

mysql批量更新多条记下的相同个字段为差别值的艺术,批量翻新的三种办法。万一更新多条数据同时每条记下要翻新的值不一致,可能过多人会这么写:

代码如下:

代码如下:

结果是的,她这么写确实是导致了服务器的晚点!

foreach ($values as $id => $myvalue) {
 $sql = "UPDATE mytable SET myfield = $myvalue WHERE id = $id";
 mysql_query($sql);
}
 UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');
 UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

大家只要熟练JAVA等语言应该了解,JAVA会内部提供了批量翻新sql的效应,那么作为世界上抓牢的语言PHP能否形成呢?答案是足以滴!

就是循环一条一条的翻新记录。一条记录update一遍,那样品质很差,也很简单造成堵塞。

那就算更新多条数据为不相同的值,大概过四人会如此写:

亚洲必赢官网 ,那假诺更新多条数据为区别的值,大概过多少人会那样写:

那么大家上学一下批量更新的sql语句。

那正是说能否一条sql语句完成批量更新呢?

代码如下:

代码如下:

UPDATE newhouse_clicks
    SET clicks = CASE id
        WHEN 1 THEN 1000
        WHEN 2 THEN 2000
        WHEN 3 THEN 3000
    END
WHERE id IN (1,2,3)

mysql并从未提供间接的章程来促成批量更新,可是足以用点小技巧来达成。

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

 稍安勿躁,大家详解一下那条sql语句的意味:
更新newhouse_clicks数据表中的clicks字段,当id=1是设置值为1000,当id=2时设置值为3000,当id=3时设置值为两千


UPDATE mytable
 SET myfield = CASE id
 WHEN 1 THEN 'myvalue1'
 WHEN 2 THEN 'myvalue2'
 WHEN 3 THEN 'myvalue3'
 END
WHERE other_field ('other_values')

便是循环一条一条的更新记录。一条记录update3次,那样品质很差,也很不难导致堵塞。

正是循环一条一条的更新记录。一条记录update3次,那样品质很差,也很简单导致堵塞。

那便是说更新三个字段能或不能够到位吗?当然能够,贴代码:

假设where条件查询出记录的id不在CASE范围内,my田野同志将被安装为空。

这正是说能或不能一条sql语句完成批量立异呢?mysql并从未提供直接的措施来兑现批量更新,可是能够用点小技巧来贯彻。

那么能否一条sql语句实现批量更新呢?mysql并不曾提供直接的点子来促成批量革新,可是足以用点小技巧来实现。

UPDATE newhouse_clicks
    SET clicks = CASE id
        WHEN 1 THEN 1000
        WHEN 2 THEN 2000
        WHEN 3 THEN 3000
    END,
    type = CASE id
        WHEN 1 THEN 1
        WHEN 2 THEN 6
        WHEN 3 THEN 8
    END
WHERE id IN (1,2,3)

设若更新多少个值的话,只要求稍加修改:

2)使用case when then

2)使用case when then

那条sql语句的意思正是翻新newhouse_clicks数据表中的clicks字段,当id=1是设置值为一千,当id=2时设置值为3000,当id=3时设置值为三千,更新type字段,当id=1时立异为type字段为1,当id=2时更新type字段为6,当id=3时,更新type字段为8。
那正是说,世界上最好的语言PHP不就能够拼出来sql了啊?

UPDATE mytable
 SET myfield1 = CASE id
 WHEN 1 THEN 'myvalue11'
 WHEN 2 THEN 'myvalue12'
 WHEN 3 THEN 'myvalue13'
 END,
 myfield2 = CASE id
 WHEN 1 THEN 'myvalue21'
 WHEN 2 THEN 'myvalue22'
 WHEN 3 THEN 'myvalue23'
 END
WHERE id IN (1,2,3)

代码如下:

代码如下:

//查询数据库返回的数据格式
$newhouse_clicks = array(
      1 => 2,
      2 => 3,
      3 => 8,
      4 => 9,
  );
$ids = implode(',', array_keys($newhouse_clicks ));
$sql = "UPDATE newhouse_clicks SET clicks = CASE id ";
foreach ( $newhouse_clicks as $key => $value) {
    $sql .= sprintf("WHEN %d THEN %d ", $key, $value);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql;

此地以php为例,构造这两条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 mytable 
    SET myfield = CASE id 
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3)

世家查看一下是或不是和咱们上边的sql语句是同等的哟!

① 、更新多条单个字段为不一样值, mysql方式

那里运用了case when 那个小技巧来兑现批量翻新。

此间运用了case when 那些小技巧来促成批量翻新。

那正是说,我们真是的数据是或不是要比那纷纭呀?肯定的,看题,我们普通在数据Curry面取出来的数目格式是或不是都以那般的。

$ids_values = array(
 1 => 11,
 2 => 22,
 3 => 33,
 4 => 44,
 5 => 55,
 6 => 66,
 7 => 77,
 8 => 88,
);

$ids = implode(',', array_keys($ids_values ));
$sql = "UPDATE mytable SET myfield = CASE id ";
foreach ($ids_values as $id=> $myvalue) {
 $sql .= sprintf("WHEN %d THEN %d ", $id, $myvalue);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql.";<br/>";

example :

example :

//查询数据库返回的数据格式
$newhouse_clicks = array(
     1 => array('clicks'=>1,'type'=>1,'update_time'=>time()),
     2 => array('clicks'=>2,'type'=>2,'update_time'=>time()),
     3 => array('clicks'=>3,'type'=>3,'update_time'=>time()),
     4 => array('clicks'=>4,'type'=>4,'update_time'=>time()),
);
?>

输出:

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

那正是说,这种处境如何做?

UPDATE mytable SET myfield = CASE id WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN 5 THEN 55 WHEN 6 THEN 66 WHEN 7 THEN 77 WHEN 8 THEN 88 END WHERE id IN (1,2,3,4,5,6,7,8);

那句sql的趣味是,更新display_order 字段,如果id=1 则display_order
的值为3,如果id=2 则 display_order 的值为4,如果id=3 则 display_order
的值为5。
就是将规范语句写在了协同。这里的where部分不影响代码的进行,可是会增高sql执行的频率。确认保障sql语句仅执行必要修改的行数,那里唯有3条数据举行更新,而where子句确定保障唯有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行数据实施。

    
<?php
//查询数据库返回的数据格式
$newhouse_clicks = array(
    1 => array('clicks'=>1,'type'=>1,'update_time'=>time()),
    2 => array('clicks'=>2,'type'=>2,'update_time'=>time()),
    3 => array('clicks'=>3,'type'=>3,'update_time'=>time()),
    4 => array('clicks'=>4,'type'=>4,'update_time'=>time()),
);
    //获取所有的id
    $newhouse_clicks_keys = array_keys($newhouse_clicks);
    //拼接批量更新sql语句
    $sql = "UPDATE newhouse_clicks SET ";
    //合成sql语句
    foreach ($newhouse_clicks[1] as $key => $value) {
        $sql .= "{$key} = CASE id ";
        foreach ($newhouse_clicks as $newhouse_clicks_key=>$newhouse_clicks_value) {

            $sql .= sprintf("WHEN %d THEN %d ", $newhouse_clicks_key, $newhouse_clicks_value[$key]);
        }
        $sql .= "END, ";
    }
    //把最后一个,去掉
    $sql = substr($sql, 0, strrpos($sql,',')); 
    //合并所有id
    $ids = implode(',', $newhouse_clicks_keys);
    //拼接sql
    $sql .= " WHERE ID IN ({$ids})";
    echo $sql;

二 、更新八个字段为区别值, PDO格局

即便更新两个值的话,只需求稍加修改:

万一更新三个值的话,只需求稍加修改:

实在,写了那般一大堆,是否就是为了拼装成mysql语句呀。

$data = array(array('id' => 1, 'myfield1val' => 11, 'myfield2val' => 111), array('id' => 2, 'myfield1val' => 22, 'myfield2val' => 222));
$where_in_ids = implode(',', array_map(function($v) {return ":id_" . $v['id'];}, $data));
$update_sql = 'UPDATE mytable SET';
$params = array();

$update_sql .= ' myfield1 = CASE id';
foreach($data as $key => $item) {
 $update_sql .= " WHEN :id_" . $key . " THEN :myfield1val_" . $key . " ";
 $params[":id_" . $key] = $item['id'];
 $params[":myfield1val_" . $key] = $item['myfield1val'];
}
$update_sql .= " END";

$update_sql .= ',myfield2 = CASE id';
foreach($data as $key => $item) {
 $update_sql .= " WHEN :id_" . $key . " THEN :myfield2val_" . $key . " ";
 $params[":id_" . $key] = $item['id'];
 $params[":myfield1va2_" . $key] = $item['myfield2val'];
}
$update_sql .= " END";

$update_sql .= " WHERE id IN (" . $where_in_ids . ")";
echo $update_sql.";<br/>";
var_dump($params);

代码如下:

代码如下:

功勋卓著告成!速度是还是不是像丝般顺滑!

输出:

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

       
老多程序员越发是初大家,很简单进入二个误区,把在sql中取多少套在for循环里边。那样写导致3个题目,正是严重的堵截,现实生活中就有这么二个例证:

UPDATE mytable SET myfield1 = CASE id WHEN :id_0 THEN :myfield1val_0 WHEN :id_1 THEN :myfield1val_1 END,myfield2 = CASE id WHEN :id_0 THEN :myfield2val_0 WHEN :id_1 THEN :myfield2val_1 END WHERE id IN (:id_1,:id_2);

array (size=6)
 ':id_0' => int 1
 ':myfield1val_0' => int 11
 ':id_1' => int 2
 ':myfield1val_1' => int 22
 ':myfield1va2_0' => int 111
 ':myfield1va2_1' => int 222

到此地,已经完结一条mysql语句更新多条记下了。

到此地,已经形成一条mysql语句更新多条记下了。

       
比如说,你在12层办公室,那是快递员给您通话让你去楼下取快递(总共12件),你去取快递有三种办法:

除此以外两种批量立异形式

PHP实例代码如下:

PHP实例代码如下:

       
1.获得第②件快递,跑回12层,放好后,接着去提取下一件特快专递,放好12层后再跟着去取下一件快递。

1. replace into 批量更新

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

        2.二回性把富有的快递都获得12层。

replace into mytable(id, myfield) values (1,'value1'),(2,'value2'),(3,'value3');

以此例子,有8条记下进行更新。代码也很不难明白,你学会了呢

本条例子,有8条记下举行立异。代码也很不难领悟,你学会了呢

       
大家肯定都会选第3个方案吗,没人会傻不拉几的去跑十一遍才能拿完快递的。

2. insert into …on duplicate key update批量更新

3)使用replace into

3)使用replace into

       
计算机就是上述原理,在for循环内部去能源取多少,正是看似第1种方案。批量取多少正是近似于第二种方案。(PS一下:不要以为在mysql中取多少有这种题材,redis也会

insert into mytable(id, myfield1, myfield2) values (1,'value11','value21'),(2,'value12','value22'),(3,'value13','value23') on duplicate key update myfield1=values(myfield2),values(myfield2)+values(id);

代码如下:

代码如下:

有那种难题,要不然怎么会有pipeline取批量数量吧,那点在面试的时候自个儿是时常公开试题去问的,相对会化为衡量1个人技术水平的正式)

3. 临时表

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

 

DROP TABLE IF EXISTS `tmptable`;
create temporary table tmptable(id int(4) primary key,myfield varchar(50));
insert into tmptable values (1,'value1'),(2,'value2'),(3,'value3');
update mytable, tmptable set mytable.myfield = tmptable.myfield where mytable.id = tmptable.id;

首先判断数据是或不是留存;
若是不设有,则插入;.如若存在,则更新。能够透过这一个函数实行数13遍更新

先是判断数据是还是不是存在;
假使不存在,则插入;.假设存在,则更新。能够透过这些函数进行频仍翻新

转自  

  1. 【replace into】和【insert
    into】更新都依靠于主键或唯一值,并都或然引致新增记录的操作的结构隐患
  2. 【replace
    into】操作本质是对重复记录先delete然后insert,要是更新的字段不全缺点和失误的字段将被设置成缺省值
  3. 【insert into】则只是update重复的记录,更改的字段只好依循公式值
  4. 【一时表】格局需求用户有temporary 表的create 权限
  5.  数量较少时【replace into】和【insert
    into】质量最好,数量大时【目前表】最好,【CASE】则拥有通用型也不具结构隐患

小心:如若表中的二个旧记录与三个用来PPRADOIMA酷威Y

只顾:尽管表中的3个旧记录与三个用以PPRADOIMA帕杰罗Y

总结

KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入从前,旧记录被去除。 
留意,除非表有1个P大切诺基IMA奥迪Q3Y
KEY或UNIQUE索引,不然,使用二个REPLACE语句没有意义。该

KEY或二个UNIQUE索引的新记录具有同样的值,则在新记录被插入在此之前,旧记录被剔除。 
专注,除非表有1个P揽胜IMA瑞虎Y
KEY或UNIQUE索引,不然,使用三个REPLACE语句没有意义。该

以上正是那篇小说的全体内容了,希望本文的剧情对我们的就学恐怕干活能拉动一定的支援,尽管有疑问大家能够留言沟通。

语句会与INSELacrosseT相同,因为从没索引被用来明确是或不是新行复制了别样的行。

语句会与INSEQX56T相同,因为尚未索引被用来明确是否新行复制了其他的行。

您或者感兴趣的稿子:

  • mysql
    批量立异与批量革新多条记下的不等值达成格局
  • MYSQL批量插入数据的落实代码
  • 批量替换 MySQL
    内定字段中的字符串
  • MySQL数据表字段内容的批量改动、清空、复制等立异命令
  • java 下实施mysql
    批量插入的两种办法及用时
  • python文件读写并选择mysql批量插入示例分享(python操作mysql)
  • 批量杀死MySQL连接的种种格局详解
  • MySQL批量插入遇上绝无仅有索引幸免格局
  • 用shell脚本在mysql表中批量插入数据的方法
  • MySQL中批量删减钦定前缀表的sql语句

4)创造近年来表,先更新一时表,然后从一时表中update

4)创设暂且表,先更新权且表,然后从一时半刻表中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;
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 权限。

小心:这种办法需求用户有temporary 表的create 权限。

 

 

网站地图xml地图