mysql一次更新多条不同的记录_MySQL

胖涛酱_9357

胖涛酱_9357

2016-06-01

1434人浏览

原创

bitsCN.com

mysql一次更新多条不同的记录

 

最近oschina上又有朋友问到了mysql中一次更新多条不同的记录的方法,我知道的方法有两种,使用on duplicate key update语法和使用 replace into语法。 

  这两个语法都需要主键索引或唯一索引支持,下面举例说明。 

  测试用的表结构和数据

 

1

CREATE TABLE `t` (

2

  `id` int(11) NOT NULL AUTO_INCREMENT,

3

  `c1` varchar(50) NOT NULL DEFAULT '',

4

  `c2` varchar(50) NOT NULL DEFAULT '',

5

  `c3` varchar(50) NOT NULL DEFAULT '',

6

  PRIMARY KEY (`id`),

7

  UNIQUE KEY `c1` (`c1`)

8

) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8 ;

9

insert into t values(1,2,3,4),(5,6,7,8);

on duplicate key update 语法

  on duplicate key update 语法的官方说明http://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-on-duplicate.html

 

1

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

  它会先执行插入操作,碰到有主键或唯一索引的列发生冲突时,对冲突的这一行,执行update操作,更新sql语句中指定的某几列。如果所有的列都不冲突,此语法和简单的insert into语法效果一样。例如:

 

01

mysql> insert into t (id,c1,c2)values(1,20,30),(5,60,70) on duplicate key update c1=values(c1),c2=values(c2);

02

Query OK, 4 rows affected (0.00 sec)

03

Records: 2  Duplicates: 2  Warnings: 0

04

 

05

mysql> select * from t;

06

+----+----+----+----+

07

| id | c1 | c2 | c3 |

08

+----+----+----+----+

09

|  1 | 20 | 30 | 4  |

10

|  5 | 60 | 70 | 8  |

11

+----+----+----+----+

12

2 rows in set (0.00 sec)

  结果是c1,c2这两列被更新了,c3这一列没有变。

 

replace into 语法

  replace into 语法的官方说明http://docs.oracle.com/cd/E17952_01/refman-5.5-en/replace.html

 

1

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

  replace和insert所作的工作完全相同,区别是当碰到有主键或唯一索引的列发生冲突时,对冲突的这一行,在insert前会先对这行数据执行delete操作。效果是这一行中没有被指定值的列会被更新成本列的默认值,如果所有的列都不冲突,此语法和简单的insert into语法效果一样。例如: 

  完全替换两行记录

01

mysql> replace into t (id,c1) values(1,200),(5,600);

02

Query OK, 4 rows affected (0.00 sec)

03

Records: 2  Duplicates: 2  Warnings: 0

04

 

05

mysql> select * from t;                            

06

+----+-----+----+----+

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载

07

| id | c1  | c2 | c3 |

08

+----+-----+----+----+

09

|  1 | 200 |    |    |

10

|  5 | 600 |    |    |

11

+----+-----+----+----+

12

2 rows in set (0.00 sec)

 

  不使用ID,使用唯一索引来替换记录

01

mysql> replace into t (c1,c2) values(200,3),(600,7);  

02

Query OK, 4 rows affected (0.00 sec)

03

Records: 2  Duplicates: 2  Warnings: 0

04

 

05

mysql> select * from t;                            

06

+-----+-----+----+----+

07

| id  | c1  | c2 | c3 |

08

+-----+-----+----+----+

09

| 127 | 200 | 3  |    |

10

| 128 | 600 | 7  |    |

11

+-----+-----+----+----+

12

2 rows in set (0.00 sec)

  效果是id也被替换掉了. 

 

  当使用唯一索引,并重且给唯一索引这一列加了重复的值时

01

mysql> replace into t (id,c1) values(127,200),(128,200);

02

Query OK, 5 rows affected (0.00 sec)

03

Records: 2  Duplicates: 3  Warnings: 0

04

 

05

mysql> select * from t;

06

+-----+-----+----+----+

07

| id  | c1  | c2 | c3 |

08

+-----+-----+----+----+

09

| 128 | 200 |    |    |

10

+-----+-----+----+----+

11

1 row in set (0.00 sec)

  最后为什么只剩一条记录了?插入(127,200)这一行前,会删掉id=127或c1=200的行,然后执行插入。插入(128,200)这一行前,会删掉id=128或c1=200的行,刚好前面插入的那一行中,c1=200,所以前面那一行也被删掉了,最后只留下了一行。

 

一次最多能更新多少条记录?

  mysql中没有一次更新记录数的限制,但是有sql语句长度的限制。如果需要执行超长的sql语句,需要调整max_allowed_packet这个配置参数。
 

  max_allowed_packet参数的官方说明http://docs.oracle.com/cd/E17952_01/refman-5.5-en/replication-features-max-allowed-packet.html

 

1

max_allowed_packet sets an upper limit on the size of any single message between the MySQL server and clients, including replication slaves.

  此参数规定mysql服务端和客户端之前的单个消息最大长度,在mysql主从同步时同样有效。

 

bitsCN.com

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

0

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

0

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

0

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

0

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

0

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

0

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

160

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程