MySql中4种批量更新的方法update table2,table1，批量更新用insert into ...on duplicate key update,　慎用replace into.

2023-01-06 21:58:47

mysql 批量更新记录

MySql中4种批量更新的方法
最近在完成MySql项目集成的情况下，需要增加批量更新的功能，根据网上的资料整理了一下，很好用，都测试过，可以直接使用。

mysql 批量更新共有以下四种办法

1、将一个表的字段更新到另一个表中：

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

update table2,table1

set table2.name = table1.name

where table2.id=table1.id;

多列：

update  a,  b

set a.title=b.title, a.name=b.name

where a.id=b.id

子查询：update 更新表 set 字段 = (select 参考数据 from 参考表 where 参考表.id = 更新表.id);

update student

set city_name = (select name from city where code ＝student.city_code);

批量update where效率比批量insert into on duplicate key update慢很多.

mysql不支持select into。

可使用create table tb_new select...创建新表：create table tb2 select c1,c2,c3 from tb1 group by c1,c2,c3;

我们再来看几个负责写的

例如: 把表 tk_zyt_scenery_order的字段更新到 t_advs_order中去, 一般可能会这样写:

UPDATE t_advs_order SET

 attribute1=(SELECT o.order_state FROM tk_zyt_scenery_order o WHERE o.order_id=`on`),

 attribute2=(SELECT o.order_state FROM tk_zyt_scenery_order o WHERE o.order_id=`on`)

 WHERE EXISTS (SELECT o.order_state FROM tk_zyt_scenery_order o WHERE o.order_id=`on`);

这样效率比较低下, JOIN优化写法:

UPDATE table1 a JOIN table2 b

ON a.field1 = b.field1

SET

a.field2 = b.field2,

a.field3 = b.field3,

a.field4 = b.field4

Mysql跨表更新 多表update sql语句总结

假定我们有两张表，一张表为Product表存放产品信息，其中有产品价格列Price；另外一张表是ProductPrice表，我们要将ProductPrice表中的价格字段Price更新为Price表中价格字段的80%。

在Mysql中我们有几种手段可以做到这一点，一种是update table1 t1, table2 ts ...的方式：

UPDATE product p, productPrice pp

SET pp.price = pp.price * 0.8

WHERE p.productId = pp.productId

AND p.dateCreated < '2004-01-01' 

另外一种方法是使用inner join然后更新：

UPDATE product p

INNER JOIN productPrice pp

ON p.productId = pp.productId

SET pp.price = pp.price * 0.8

WHERE p.dateCreated < '2004-01-01' 

另外我们也可以使用left outer join来做多表update，比方说如果ProductPrice表中没有产品价格记录的话，将Product表的isDeleted字段置为1，如下sql语句：

UPDATE product p

LEFT JOIN productPrice pp

ON p.productId = pp.productId

SET p.deleted = 1

WHERE pp.productId IS null 

另外，上面的几个例子都是两张表之间做关联，但是只更新一张表中的记录，其实是可以同时更新两张表的，如下sql：

UPDATE product p

INNER JOIN productPrice pp

ON p.productId = pp.productId

SET pp.price = pp.price * 0.8,

p.dateUpdate = CURDATE()

WHERE p.dateCreated < '2004-01-01' 

两张表做关联，更新了ProductPrice表的price字段和Product表字段的dateUpdate两个字段。

MySql update inner join！MySql跨表更新 多表update sql语句？如何将select出来的部分数据update到另一个表里面？

项目中，评论数，关注数等数据，是实时更新的。+1，-1 这种。

有的时候，可能统计不准确。

需要写一个统计工具，更新校准下。

用Java写SQL和函数，代码很清晰，方便扩展，但是太慢了。

为了简单起见，只写sql来统计，然后更新。（不想写存储过程）

语句如下：

#更新一个人的 关注数 followingCount

[sql] view plain copy  在CODE上查看代码片派生到我的代码片

update behavior_redman_count a

inner join

(

select memberId,count(*) as followingCount from behavior_follow where type = 10

and isDelete=0 group by memberId

)b

set a.followingCount =b.followingCount

where a.redmanId = b.memberId;

[sql] view plain copy

update behavior_redman_count a

inner join

(

select memberId,count(*) as followingCount from behavior_follow where type = 10

and isDelete=0 group by memberId

)b

set a.followingCount =b.followingCount

where a.redmanId = b.memberId;

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

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

例子：insert into book (`Id`,`Author`,`CreatedTime`,`UpdatedTime`) values (1,'张飞2','2017-12-12 12:20','2017-12-12 12:20'),(2,'关羽2','2017-12-12 12:20','2017-12-12 12:20') on duplicate key update Author=values(Author),CreatedTime=values(CreatedTime),UpdatedTime=values(UpdatedTime);

replace into 和 insert into on duplicate key update的不同在于：
insert into on duplicate key update 则是只update重复记录，不会改变其它字段。而insert into是从一个表插入到另一张表。

replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值，用这个要悠着点否则不小心清空大量数据可不是闹着玩的。

使用on duplicate key update和REPLACE时，表中必须有唯一索引或主键，且不能为空值，否则REPLACE就和INSERT完全一样的。

-- 每次点击更新点击数

insert into daily_hit_counter(day,slot,cnt) values (current_date,rand()*100,1)

on duplicate key update cnt=cnt+1;  

-- 按天合并统计数，并删除多余行

update daily_hit_counter as c inner join (select day,sum(cnt) as cnt min(slot) as mslot from daily_hit_counter group by day) as x

using(day) set c.cnt=if(c.slot=x.mslot,x.cnt,0), c.slot=if(c.slot=x.mslot,0,c.slot);

delete from daily_hit_counter where slot>0 and cnt=0;

表的主健id要auto_increment,不然在insert into时出现locked,一直是update状态而不是updating状态.

表要有主键id,不然随着insert into会乱套了.

3、使用mysql 自带的语句构建批量更新

mysql 实现批量可以用点小技巧来实现:

UPDATE yoiurtable

SET dingdan = CASE id

WHEN 1 THEN 3

WHEN 2 THEN 4

WHEN 3 THEN 5

END

WHERE id IN (1,2,3)

这句sql 的意思是，更新dingdan 字段，如果id=1 则dingdan 的值为3，如果id=2 则dingdan 的值为4……

where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

例子：UPDATE book
SET Author = CASE id
WHEN 1 THEN '黄飞鸿'
WHEN 2 THEN '方世玉'
WHEN 3 THEN '洪熙官'
END
WHERE id IN (1,2,3)

如果更新多个值的话，只需要稍加修改：

UPDATE categories

SET dingdan = 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 book

SET Author = CASE id

WHEN 1 THEN '黄飞鸿2'

WHEN 2 THEN '方世玉2'

WHEN 3 THEN '洪熙官2'

END,

Code = CASE id

WHEN 1 THEN 'HFH2'

WHEN 2 THEN 'FSY2'

WHEN 3 THEN 'HXG2'

END

WHERE id IN (1,2,3)

4、.replace into 批量更新

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');
例子：replace into book (`Id`,`Author`,`CreatedTime`,`UpdatedTime`) values (1,'张飞','2016-12-12 12:20','2016-12-12 12:20'),(2,'关羽','2016-12-12 12:20','2016-12-12 12:20');

replace into 、insert into ...on duplicate key update和区别

insert into table (aa,bb,cc) values(xx,xx,xx),(oo,oo,oo) on duplicate key update aa=values(aa),bb=values(bb),cc=values(bb)+values(cc)

a、更新操作，values后可跟多条记录，主键或唯一值存在，则按最后提供的规则进行更新，规则自定义，cc=values(bb)+values(cc)
b、只更新最后添加的字段，bb=values(bb)
c、更新时，如果有类似create_date 不为空而且没有默认值的列，需要在key和value中添加，但最后更新的列中不一定包含此列。 ex: insert into table (aa,bb,create_date) values (xx,xx,now()) on duplicate key update aa = values(aa),bb=value(bb)

批量替换更新
[sql] view plain copy
replace into table (aa,bb,cc) values(xxx,xxx,xxx),(ooo,ooo,ooo),(ccc,ccc,ccc)

该方式表是要有唯一主键的。原理跟A差不多。判断主键，存在即更新，不存在则插入。如果只更新其中1、2个字段的时候，不要用这个方法，否则会将其他的字段置空，或者执行前将values值填写完整。
不过该方法有个坑，对于配置有主从服务器的时候，会导致从库的自增主键与主库的自增主键无法保持一致。
区别
a、replace into 操作本质是对重复的记录先delete 后insert，自动增长ID会变，如果更新的字段不全会将缺失的字段置为缺省值。
b、insert into 则是只update重复记录，不会改变其它字段, 所以建议用insert into ...on duplicate key update。

Mysql中replace into用法详细说明

许多使用replace into 的场景实际上需要的是insert into... on duplicate key update ，在正确理解replace into 的行为和副作用的前提下，谨慎使用replace into
Replace into是Insert into的增强版。在向表中插入数据时，我们经常会遇到这样的情况：1、首先判断数据是否存在；2、如果不存在，则插入；3、如果存在，则更新。

在SQL Server中可以这样处理：

if not exists (select 1 from t where id = 1)
insert into t(id, update_time) values(1, getdate())
else
update t set update_time = getdate() where id = 1

那么 mysql 中如何实现这样的逻辑呢？MySQL 中有更简单的方法： replace into

replace into t(id, update_time) values(1, now());

或

replace into t(id, update_time) select 1, now();

replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

MySQL replace into 有三种形式：

1. replace into tbl_name(col_name, ...) values(...)

2. replace into tbl_name(col_name, ...) select ...

3. replace into tbl_name set col_name=value, ...

第一种形式类似于insert into的用法，

第二种replace select的用法也类似于insert select，这种用法并不一定要求列名匹配，事实上，MYSQL甚至不关心select返回的列名，它需要的是列的位置。例如，replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2; 这个例子使用replace into从 tb2中将所有数据导入tb1中。

第三种replace set用法类似于update set用法，使用一个例如“SET col_name = col_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。

前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

另外replace into的描述

REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。

所有列的值均取自在REPLACE语句中被指定的值。所有缺失的列被设置为各自的默认值，这和INSERT一样。您不能从当前行中引用值，也不能在新行中使用值。如果您使用一个例如“SET col_name = col_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。

REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1，则一行被插入，同时没有行被删除。如果该数大于1，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。

受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。
1. 尝试把新行插入到表中
2. 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时：
a. 从表中删除含有重复关键字值的冲突行
b. 再次尝试把新行插入到表中

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},…),(…),…
或：

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, …
或：

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT …

REPLACE INTO `table` (`unique_column`,`num`) VALUES ('$unique_value',$num);跟INSERT INTO `table` (`unique_column`,`num`) VALUES('$unique_value',$num) ON DUPLICATE UPDATE num=$num;还是有些区别的，区别就是replace into的时候会删除老记录。如果表中有一个自增的主键。那么就要出问题了。

首先，因为新纪录与老记录的主键值不同，所以其他表中所有与本表老数据主键id建立的关联全部会被破坏。
其次，就是，频繁的REPLACE INTO 会造成新纪录的主键的值迅速增大。总有一天。达到最大值后就会因为数据太大溢出了。就没法再插入新纪录了。数据表满了，不是因为空间不够了，而是因为主键的值没法再增加了。

码农公寓

mysql 批量更新记录

相关文章