MySQL

您所在的位置：网站首页 › mysql更改字段内容 › MySQL

MySQL

#MySQL| 来源: 网络整理| 查看: 265

前言

本文主要讲述在MySQL中批量更新数据的六种方法，希望对大家有所帮助。

一、IN

第一种方式是借助IN语句，这种方式局限性较大，更新结果必须一致，比如下面就是将满足条件的行的状态（status）都置为1。

如果是一部分置为1，一部分置为2等，则无法实现，要么就是写多条SQL语句。

Update users Set status=1 Where account IN ('xx1', 'xx2'); 复制代码二、For + Update

第二种方式是借助For循环+Update语句，即一条一条地更新，优点是清晰直观，能适用大部分情况，而且不容易出错，缺点是性能较差，且容易造成阻塞 ****。

如果是在MySQL客户端执行，这种方式其实不太方便，一般要生成多条Update语句，亦或者用存储过程实现；如果是在第三方库中执行，直接用 For循环 + 封装的Update语句即可实现，简单地不要不要的。

三、insert into...on duplicate key update

第三种方式是借助主键（或者唯一键）的唯一性进行更新，优点是支持批量更新，且更新结果不需要一致，缺点就是一般第三方库并不支持这种语法，需要写原生SQL，还有就是所有字段都必须有默认值（包括NULL）。

测试过程如下：

# 创建表 create table users ( id int(11) PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NUll DEFAULT '', age smallint, job varchar(255) ); # 插入测试数据 INSERT INTO go_business.users (id, name, age, job) VALUES (1, 'name1', 1, 'job1'); INSERT INTO go_business.users (id, name, age, job) VALUES (2, 'namw2', 2, 'job2'); INSERT INTO go_business.users (id, name, age, job) VALUES (3, 'name3', 3, 'job3'); INSERT INTO go_business.users (id, name, age, job) VALUES (4, 'name4', 4, 'job4'); INSERT INTO go_business.users (id, name, age, job) VALUES (5, 'name5', 5, 'job5'); # 批量更新 mysql> insert into users (id, job, age) values (1, 'job11', 11),(2, 'job22', 22) on duplicate key update job=values(job), age=values(age); # 最终结果 mysql> select * from users where id in (1, 2); +----+-------+------+-------+ | id | name | age | job | +----+-------+------+-------+ | 1 | name1 | 11 | job11 | | 2 | namw2 | 22 | job22 | +----+-------+------+-------+ 复制代码四、replace into

第四种方式是replace into ，从字面看就知道是替换的作用，相当于更新。它的语法与第三种方式比较相近，但是却比第三种方式危险，原因是如果更新时字段不全，没被覆盖到的字段会被置为默认值。

# 还是沿用上面的数据 replace into users(id, job, age) VALUES (1, 'job111', 111),(2, 'job222', 222); # 可以看到没被覆盖到的字段（name），会被置成默认值，这显然与我们的初衷不符 mysql> select * from users where id in (1, 2); +----+------+------+--------+ | id | name | age | job | +----+------+------+--------+ | 1 | | 111 | job111 | | 2 | | 222 | job222 | +----+------+------+--------+ 2 rows in set (0.00 sec) 复制代码

究其原因，replace into 操作的本质是对重复的记录先 delete 后 insert，所以如果更新的字段不全会将缺失的字段置为默认值；而 insert into 只是update重复记录，不会改变其它字段。

五、set...case...when...where

第五种方式是Set...Case...When...Where语句，优点是可以批量更新，也支持更新多个字段和更新多种结果，缺点就是语句较长，实现较为麻烦，且较容易出错。

一般是借助主键，或者唯一键进行更新，测试如下：

# 情况一：利用主键ID更新 update users set job = case id when 1 then 'job11' when 2 then 'job12' end, age = case id when 1 then 11 when 2 then 12 end where id IN (1, 2); mysql> select * from users where id in (1, 2); +----+-------+------+-------+ | id | name | age | job | +----+-------+------+-------+ | 1 | name1 | 11 | job11 | | 2 | name2 | 12 | job12 | +----+-------+------+-------+ 复制代码

使用不当主要有两种情况：

# 一、条件不存在 update users set job = case id when 1 then 'job11' when 3 then 'job13' end, age = case id when 1 then 11 when 2 then 12 end where id IN (1, 2); # 可以看到，如果条件不存在（id=3），对应的字段会被置为默认值，这显然也不是我们想要的 select * from users where id in (1, 2); +----+-------+------+-------+ | id | name | age | job | +----+-------+------+-------+ | 1 | name1 | 11 | job11 | | 2 | name2 | 12 | NULL | +----+-------+------+-------+ # 二、不带where子句 update users set job = case id when 1 then 'job11' when 2 then 'job12' end, age = case id when 1 then 11 when 2 then 12 end; # 这种方式极其危险，因为会进行全表更新，同时条件不存在的被置为默认值 select * from users; +----+-------+------+-------+ | id | name | age | job | +----+-------+------+-------+ | 1 | name1 | 11 | job11 | | 2 | name2 | 12 | job12 | | 3 | name3 | NULL | NULL | | 4 | name4 | NULL | NULL | | 5 | name5 | NULL | NULL | +----+-------+------+-------+ 复制代码

通过上面的测试我们可以看到，这种操作方式其实还蛮危险的，稍微不慎字段就会被更新为默认值，所以使用时要非常慎重，万万不可漏了Where子句。

六、创建临时表

第六种方式是创建临时表，临时表的思路是用另一张表的数据来替换，但一般情况下，我们并没有创建表的权限，所以这种思路不太现实。

# 临时表的字段只需要更新的字段即可 create temporary table users_tmp ( id int(11) PRIMARY KEY AUTO_INCREMENT, age smallint, job varchar(255) ); # 插入要更新的数据 insert into users_tmp(id, job, age) values (1, 'job11', 11), (2, 'job22', 22); # 有点类似连表更新 update users, users_tmp set users.job=users_tmp.job, users.age=users_tmp.age where users.id=users_tmp.id; 复制代码总结

上面一共介绍了六种办法，其中我比较推荐前三种，一是语法简单容易理解，最重要的是不容易出错，或者出错成本较低；而像第四、第五种其实不太推荐，因为真的容易出错；最后临时表这种方案，虽然也不麻烦，但我们往往并没有创建临时表的权限，所以不太现实。

【本文地址】

MySQL

MySQL

今日新闻

推荐新闻