PHP如何批量创新MYSQL中的数据,批量更新的八种方式

by admin on 2019年3月9日

不久前项目供给用到批量翻新数据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’
是3个逗号(,)分隔的字符串,如:1,2,3

若果更新同一字段为同三个值,mysql也非常粗略,修改下where即可:

假如更新同一字段为同1个值,mysql也极粗略,修改下where即可:

PHP如何批量创新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能否不辱义务呢?答案是能够滴!

正是循环一条一条的换代记录。一条记录update3次,那样品质很差,也很不难造成堵塞。

那假如更新多条数据为不一致的值,恐怕过三人会那样写:

那要是更新多条数据为分歧的值,大概过多个人会这么写:

那么我们学习一下批量翻新的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语句的趣味:
PHP如何批量创新MYSQL中的数据,批量更新的八种方式。更新newhouse_clicks数据表中的clicks字段,当id=1是设置值为一千,当id=2时设置值为三千,当id=3时设置值为3000


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回,那样品质很差,也很简单造成堵塞。

便是循环一条一条的更新记录。一条记录update1次,那样质量很差,也很容易导致堵塞。

那么更新四个字段能或无法到位呢?当然能够,贴代码:

万一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是设置值为1000,当id=2时设置值为三千,当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)

bf88必发唯一官网,这正是说,那种景况怎么做?

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循环之中。那样写导致二个难题,就是严重的堵截,现实生活中就有诸如此类一个事例:

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.获得第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条记下实行更新。代码也很不难领会,你学会了吗

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

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

3)使用replace into

3)使用replace into

       
总括机便是上述原理,在for循环里边去财富取多少,就是相仿第二种方案。批量取多少正是类似于第二种方案。(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取批量数额吧,那一点在面试的时候本人是常事公开试题去问的,相对会化为衡量一位技术水平的正经)

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;

首先判断数据是还是不是留存;
借使不存在,则插入;.要是存在,则更新。能够因此这么些函数进行反复立异

第二判断数据是或不是留存;
假诺不设有,则插入;.倘若存在,则更新。能够经过这几个函数进行反复创新

转自  

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

专注:如若表中的三个旧记录与多个用来P帕杰罗IMALX570Y

留意:假诺表中的1个旧记录与一个用来P酷威IMAPAJEROY

总结

KEY或贰个UNIQUE索引的新记录具有同样的值,则在新记录被插入在此之前,旧记录被删去。 
留意,除非表有三个P中华VIMA昂CoraY
KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意思。该

KEY或三个UNIQUE索引的新记录具有同样的值,则在新记录被插入在此以前,旧记录被删去。 
在意,除非表有一个PMuranoIMA奥迪Q5Y
KEY或UNIQUE索引,不然,使用二个REPLACE语句没有意义。该

如上便是那篇小说的全体内容了,希望本文的始末对大家的上学恐怕工作能拉动一定的相助,假如有疑难大家能够留言沟通。

语句会与INSE奇骏T相同,因为没有索引被用来明确是还是不是新行复制了其余的行。

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

您也许感兴趣的小说:

  • 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地图