当前位置:首页 > 编程教程 > mysql技术文章 > mysql 批量更新与批量更新多条记录的不同值实现方法

详细介绍mysql 批量更新与批量更新多条记录的不同值实现方法技巧

  • 发布时间:
  • 作者:码农之家
  • 点击:166

这篇文章主要知识点是关于mysql、的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下电子书

PHP+MySQL动态网站开发从入门到精通
  • 类型:PHP网站开发大小:36.2 MB格式:PDF作者:张工厂
立即下载

mysql 批量更新与批量更新多条记录的不同值实现方法

批量更新

mysql更新语句很简单,更新一条数据的某个字段,一般这样写:

 

 

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

 

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

 

 

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

 

即是循环一条一条的更新记录。一条记录update一次,这样性能很差,也很容易造成阻塞。

那么能不能一条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)

 

这里使用了case when 这个小技巧来实现批量更新。
举个例子:

 

 

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重复记录,不会改变其它字段。

以上就是本次给大家分享的关于java的全部知识点内容总结,大家还可以在下方相关文章里找到相关文章进一步学习,感谢大家的阅读和支持。

MySQL 相关电子书
学习笔记
网友NO.980005

高级MySQL数据库面试问题 附答案

因为有大家的支持,我们才能做到现在,感谢你们这一路上对我们的支持.在这篇文章中,我们将主要针对MySQL的实用技巧,讲讲面试中相关的问题. 1. 如何使用SELECT语句找到你正在运行的服务器的版本并打印出当前数据库的名称? 答: 下面的语句的结果会显示服务器的版本和当前的数据库名称 mysql SELECT VERSION(), DATABASE(); +-------------------------+------------+ | VERSION() | DATABASE() | +-------------------------+------------+ | 5.5.34-0ubuntu0.13.10.1 | NULL | +-------------------------+------------+ 1 row in set (0.06 sec) 在Database一列中显示NULL是因为我们当前没有选择任何数据库。因此,使用下面的语句先选择一个数据库,就能看到相应的结果。 mysql use Tecmint;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql select VERSION(), DATABASE();+---……

网友NO.982838

mysqld-nt: Out of memory (Needed 1677720 bytes)解决方法

今天发现网站有点慢,发现mysql日志中提示mysqld-nt: Out of memory (Needed 1677720 bytes),经排查是由于最近调整了mysql的一些参数导致,以为内存大就不怕了,32位系统真心内容利用率很低,据说不超过4G,我们的32G内存真浪费了,以后还是使用win2008 r2或centos系统做服务器吧。废话不多说下面为大家分享下解决方法: 因为mysql版本不同可能配置略有区别,主要就是设置如下参数 key_buffer、key_buffer_size、read_buffer_size、sort_buffer_size记住了有这个参数的就改,没有也不要添加。修改后一般是降低,然后重启mysql服务即可。 核心提示:检查mysqld配置my.conf,着重看key_buffer_size, max_heap_table_size, tmp_table_size几个参数,推荐设置key_buffer_size值为max_heap_table_size的1/4. 因为服务器内存而大富余比较多,前些天把my.conf里的好几个参数调得相当大,1G甚至2G,但并不稳定,mysqld报出过……

网友NO.721815

mysql5.7.18解压版启动mysql服务

mysql5.7.18解压版启动mysql服务,具体内容如下 1.下载mysql社区版 2.解压到D:\Program Files 3.在D:\Program Files\mysql-5.7.18-winx64\bin下,新建文件my.ini,内容如下: [client] port=3306 default-character-set=utf8 [mysqld] bind-address = 127.0.0.1 port=3306 character_set_server=utf8 basedir ="D:\Program Files\mysql-5.7.18-winx64" datadir ="D:\Program Files\mysql-5.7.18-winx64\data" tmpdir ="D:\Program Files\mysql-5.7.18-winx64\data" socket ="D:\Program Files\mysql-5.7.18-winx64\data\mysql.sock" log-error="D:\Program Files\mysql-5.7.18-winx64\data\mysql_error.log" max_connections=100 table_open_cache=256 query_cache_size=1M tmp_table_size=32M thread_cache_size=8 innodb_data_home_dir="D:\Program Files\mysql-5.7.18-winx64\data\" innodb_flush_log_at_trx_commit =1 innodb_log_buffer_size=128M innodb_buffer_pool_size=128M innodb_log_file_size=10M innodb_thread_concurrency=16 innodb-autoextend-increment=1000 join_buffer_size = 128M sort_buffer_size = 32M read_rnd_bu……

网友NO.210589

Mysql数据库实现多字段过滤的方法

我国移动互联网进入了飞速发展阶段,互联网人才日益受到企业的重视,其中PHP开发人才便是其中之一,在互联网旅游、金融、餐饮、娱乐、社交等一些新兴企业与软件开发企业中,PHP开发岗位相对占有核心地位,今天给大家分享的技术知识是—— mysql数据库如何实现多字段过滤。 1.多字段过滤查询 类比现实:查询公司中户籍是北京的、年龄超过30岁、性别是男的同事信息; 查询场景:查询商品名称是'King doll'、商品价格是9.49的商品。 查询SQL: SELECT prod_id, prod_name, prod_price F ROM Products W HERE prod_name = 'King doll' AND prod_price = 9.49; 查询结果: 2.多字段过滤连接 WHERE字句中有多个字段进行查询过滤,过滤条件如何连接的呢?MySql允许给出多个WHERE字句进行过滤,它们可以使用AND或者OR进行连接! AND连接类比现实:飞鹰小学5年2班身高超过1米3、不戴眼镜的男同学去……

网友NO.891713

MySQL存储过程中游标循环的跳出和继续操作示例

最近遇到这样的问题,在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅. 1.REPEAT REPEAT Statements; UNTIL expression END REPEAT demo DECLARE num INT; DECLARE my_string VARCHAR(255); REPEAT SET my_string =CONCAT(my_string,num,','); SET num = num +1; UNTIL num 5 END REPEAT; 2.WHILE WHILE expression DO Statements; END WHILE demo DECLARE num INT; DECLARE my_string VARCHAR(255); SET num =1; SET str =''; WHILE num span10DO SET my_string =CONCAT(my_string,num,','); SET num = num +1; END WHILE; 3.LOOP(这里面有非常重要的ITERATE,LEAVE) DECLARE num INT; DECLARE str VARCHAR(255); SET num =1; SET my_string =''; loop_label: LOOP IF num 10THEN LEAVE loop_label; ENDIF; SET num = num +1; IF(num mod3)THEN ITERATE loop_label; ELSE SET my_string =CONCAT(my_string,num,','); ENDIF; END LOOP; PS:可以这……

<
1
>

Copyright 2018-2020 www.xz577.com 码农之家

版权投诉 / 书籍推广 / 赞助:520161757@qq.com