批量立异的多种办法,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

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

假设更新同一字段为同八个值,mysql也很简短,修改下where即可:

 这样在关系到多张表*一千条数据,那么会不会有一点都不小的延时呢?

借使更新多条数据同时每条记下要立异的值分歧,恐怕过四个人会那样写:

代码如下:

代码如下:

结果是的,她这一来写确实是造成了服务器的过期!

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批量更新多条记下的如出一辙个字段为不一致值的方法。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是设置值为一千,当id=2时设置值为三千,当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')

就是循环一条一条的换代记录。一条记录update三遍,那样品质很差,也很简单导致堵塞。

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

那么更新八个字段能还是不可能到位呢?当然能够,贴代码:

若是where条件查询出记录的id不在CASE范围内,my田野(field)将被设置为空。

那正是说能或不可能一条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循环内部。那样写导致一个标题,即是严重的堵截,现实生活中就有这么二个例证:

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条记下举行更新。代码也很简单驾驭,你学会了呢

       
我们一定都会选第二个方案吧,没人会傻不拉几的去跑12遍才能拿完快递的。

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奥迪Q5IMA奥德赛Y

注意:假设表中的3个旧记录与贰个用以P锐界IMA纳瓦拉Y

总结

KEY或3个UNIQUE索引的新记录具有同等的值,则在新记录被插入在此之前,旧记录被去除。 
注意,除非表有3个PEscortIMAKoleosY
KEY或UNIQUE索引,不然,使用二个REPLACE语句没有意义。该

KEY或贰个UNIQUE索引的新记录具有同等的值,则在新记录被插入在此之前,旧记录被去除。 
留意,除非表有三个P奥迪Q5IMA瑞鹰Y
KEY或UNIQUE索引,不然,使用三个REPLACE语句没有意思。该

上述正是那篇作品的全体内容了,希望本文的内容对大家的学习只怕办事能带来一定的拉拉扯扯,假设有问号我们能够留言调换。

语句会与INSERubiconT相同,因为尚未索引被用于明确是否新行复制了任何的行。

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

您或然感兴趣的文章:

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