执行一条sql语句update多条记录实现思路

通常情况下,我们会使用以下SQL语句来更新字段值:


代码如下:

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

但是,如果你想更新多行数据,并且每行记录的各字段值都是各不一样,你会怎么办呢?举个例子,我的博客有三个分类目录(免费资源、教程指南、橱窗展示),这些分类目录的信息存储在数据库表categories中,并且设置了显示顺序字段 display_order,每个分类占一行记录。如果我想重新编排这些分类目录的顺序,例如改成(教程指南、橱窗展示、免费资源),这时就需要更新categories表相应行的display_order字段,这就涉及到更新多行记录的问题了,刚开始你可能会想到使用循环执行多条UPDATE语句的方式,就像以下的php程序示例:


代码如下:

foreach ($display_order as $id => $ordinal) {
$sql="UPDATE categories SET display_order = $ordinal WHERE id = $id";
mysql_query($sql);
}

这种方法并没有什么任何错误,并且代码简单易懂,但是在循环语句中执行了不止一次SQL查询,在做系统优化的时候,我们总是想尽可能的减少数据库查询的次数,以减少资源占用,同时可以提高系统速度。
幸运的是,还有更好的解决方案,下面列举两种常用的方案只不过SQL语句稍微复杂点,但是只需执行一次查询即可,语法如下:

•第一种:IF--THEN语句结合


代码如下:

UPDATE mytable
SET myfield = CASE other_field
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
END
WHERE id IN (1,2,3)

回到我们刚才的分类目录的例子,我们可以使用以下SQL语句:


代码如下:

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)

以上方案大大减少了数据库的查询操作次数,大大节约了系统资源,但是该怎样与我们的编程语言结合起来呢?我们还是用刚才分类目录的例子,以下是php的程序示例:


代码如下:

$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语句
}
$sql .= "END WHERE id IN ($ids)";
echo $sql;
mysql_query($sql);

在这个例子中总共更新了8行数据,但是只执行了一次数据库查询,相比于循环执行8次UPDATE语句,以上例子所节约的时间可以说是微不足道的。但是想想,当你需要更新10,0000或者更多行记录时,你会发现这其中的好处!唯一要注意的问题是SQL语句的长度,需要考虑程序运行环境所支持的字符串长度,我目前获得的数据:SQL语句长度达到1,000,960在php中仍然可以顺利执行,我查询了php文档并没有发现明确规定字符串最大长度。

•第二种INSERT方式
MySql中INSERT语法具有一个条件DUPLICATE KEY UPDATE,这个语法和适合用在需要判断记录是否存在,不存在则插入存在则更新的记录。
基于上面这种情况,针对更新记录,仍然使用insert语句,不过限制主键重复时,更新字段。如下:


代码如下:

INSERT INTO t_member (id, name, email) VALUES
(1, 'nick', 'nick@126.com'),
(4, 'angel','angel@163.com'),
(7, 'brank','ba198@126.com')
ON DUPLICATE KEY UPDATE name=VALUES(name), email=VALUES(email);

注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!

(0)

相关推荐

  • 执行一条sql语句update多条记录实现思路

    通常情况下,我们会使用以下SQL语句来更新字段值: 复制代码 代码如下: UPDATE mytable SET myfield='value' WHERE other_field='other_value'; 但是,如果你想更新多行数据,并且每行记录的各字段值都是各不一样,你会怎么办呢?举个例子,我的博客有三个分类目录(免费资源.教程指南.橱窗展示),这些分类目录的信息存储在数据库表categories中,并且设置了显示顺序字段 display_order,每个分类占一行记录.如果我想重新编排这

  • Java MyBatis是如何执行一条SQL语句的

    目录 背景 阅读环境 阅读过程 加载XML的过程 创建Mapper 获得一个Mapper 执行一个Mapper的方法 结论 背景 在前两天的一次面试中,面试官问了一个和标题一样的问题,由于一直认为MyBatis只是一个ORM框架,所以并没有对他有过深入的了解,于是被问到了,那么这一篇文章来从源码探究一下MyBatis是如何执行一条SQL语句的. 阅读环境 源码环境,github直接下载的main分支代码,版本号为 3.5.11-SNAPSHOT Ide Jetbrain Idea 2021.2.

  • 浅谈MyBatis 如何执行一条 SQL语句

    前言 Mybatis 是 Java 开发中比较常用的 ORM 框架.在日常工作中,我们都是直接通过 Spring Boot 自动配置,并直接使用,但是却不知道 Mybatis 是如何执行一条 SQL 语句的,而这篇文章就是来揭开 Mybatis 的神秘面纱. 基础组件 我们要理解 Mybatis 的执行过程,就必须先了解 Mybatis 中都有哪一些重要的类,这些类的职责都是什么? SqlSession 我们都很熟悉,它对外提供用户和数据库之间交互需要使用的方法,隐藏了底层的细节.它默认是实现类

  • JSP 多条SQL语句同时执行的方法

    复制代码 代码如下: con.setAutoCommit(true);//设为true,每次executeUpdate将立刻被执行 sql = "insert into table1(lable1) values('001')"; rs = stmt.executeUpdate(sql); sql = "insert into table2(lable2) values('002')"; rs = stmt.executeUpdate(sql); 这是一段基本的SQ

  • Spring 中jdbcTemplate 实现执行多条sql语句示例

    说一下Spring框架中使用jdbcTemplate实现多条sql语句的执行: 很多情况下我们需要处理一件事情的时候需要对多个表执行多个sql语句,比如淘宝下单时,我们确认付款时要对自己银行账户的表里减去订单所需的钱数,即需要更新银行账户的表,同时需要更新淘宝订单的表将订单状态改为"已付款",这就需要先后执行多个sql(仅仅用于表达执行多的SQL的举例说明,具体淘宝如何实现并不是很清楚~~~~~); 但如果这中间出现电脑断网断电等问题,仅将我们银行账户的钱扣掉了,订单状态并没有改,那我

  • iBatis习惯用的16条SQL语句

    iBatis 简介: iBatis 是apache 的一个开源项目,一个O/R Mapping 解决方案,iBatis 最大的特点就是小巧,上手很快.如果不需要太多复杂的功能,iBatis 是能够满足你的要求又足够灵活的最简单的解决方案,现在的iBatis 已经改名为Mybatis 了. 官网为:http://www.mybatis.org/ 1.输入参数为单个值 <delete id="com.fashionfree.stat.accesslog.deleteMemberAccessLo

  • 详解一条sql语句在mysql中是如何执行的

    概览 最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的. 一.mysql架构分析 下面是mysql的一个简要架构图: mysql主要分为Server层和存储引擎层 Server层:主要包括连接器.查询缓存.分析器.优化器.执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程.触发器.视图,函数等,还有一个通用的日志模块 bing

  • MyBatis一次执行多条SQL语句的操作

    有个常见的场景:删除用户的时候需要先删除用户的外键关联数据,否则会触发规则报错. 解决办法不外乎有三个: 1.多条sql分批执行: 2.存储过程或函数调用: 3.sql批量执行. 今天我要说的是MyBatis中如何一次执行多条语句(使用mysql数据库). 1.修改数据库连接参数加上allowMultiQueries=true,如: hikariConfig.security.jdbcUrl=jdbc:mysql://xx.xx.xx:3306/xxxxx?characterEncoding=u

  • 52条SQL语句教你性能优化

    1, 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2,应尽量避免在 where 子句中对字段进行 null 值判断,创建表时NULL是默认值,但大多数时候应该使用NOT NULL,或者使用一个特殊的值,如0,-1作为默 认值. 3,应尽量避免在 where 子句中使用!=或<>操作符, MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE. 4,应尽量避免

  • 一条SQL语句在MySQL中是如何执行的

    目录 一.mysql架构分析 1.1 连接器 1.2 查询缓存 1.3 分析器 1.4 优化器 1.5 执行器 二.语句分析 2.1 查询语句 2.2 更新语句 三.总结 一.mysql架构分析 下面是mysql的一个简要架构图: mysql主要分为Server层和存储引擎层 Server层:主要包括连接器.查询缓存.分析器.优化器.执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程.触发器.视图,函数等,还有一个通用的日志模块 binglog日志模块. 存储引擎: 主要负责数据的存储和

随机推荐