MySql中4种批量更新的⽅法updatetable2,table1，批量更新⽤inser。。
。
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_创建新表：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=(der_state FROM tk_zyt_scenery_order o der_id=`on`),
attribute2=(der_state FROM tk_zyt_scenery_order o der_id=`on`)
WHERE EXISTS (der_state FROM tk_zyt_scenery_order o der_id=`on`);
这样效率⽐较低下, JOIN优化写法:
批量更新sql语句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=0group by memberId
)b
set a.followingCount =b.followingCount
dmanId = 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=0group by memberId
)b
set a.followingCount =b.followingCount
dmanId = 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 ct=if(c.slot=x.mslot,xt,0), c.slot=if(c.slot=x.mslot,0,c.slot);
delete from daily_hit_counter where slot>0and 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 的场景实际上需要的是 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 会造成新纪录的主键的值迅速增⼤。总有⼀天。达到最⼤值后就会因为数据太⼤溢出了。就没法再插⼊新纪录了。数据表满了，不是因为空间不够了，⽽是因为主键的值没法再增加了。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论