精华内容
下载资源
问答
  • mysql批量更新批量更新多条记录的不同值实现方法 在mysql中批量更新我们可能使用update,replaceinto来操作,下面详细介绍mysql批量更新与性能。 批量更新 mysql更新语句很简单,更新一条数据的某个字段,一般...

    mysql 批量更新与批量更新多条记录的不同值实现方法

    在mysql中批量更新我们可能使用update,replace into来操作,下面详细介绍mysql批量更新与性能。

    批量更新

    mysql更新语句很简单,更新一条数据的某个字段,一般这样写:

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

    如果更新同一字段为同一个值,mysql也很简单,修改下where即可:

     UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

    这里注意‘other_values' 是一个逗号(,)分隔的字符串,如:1,2,3

    那如果更新多条数据为不同的值,可能很多人会这样写:

    foreach ($display_order as $id => $ordinal) { 

        $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id"; 

        mysql_query($sql); 

    }

    即是循环一条一条的更新记录。一条记录update一次,这样性能很差,也很容易造成阻塞。

    那么能不能一条sql语句实现批量更新呢?mysql并没有提供直接的方法来实现批量更新,但是可以用点小技巧来实现。

    UPDATE mytable 

        SET myfield = CASE id 

            WHEN 1 THEN 'value'

            WHEN 2 THEN 'value'

            WHEN 3 THEN 'value'

        END

    WHERE id IN (1,2,3)

    这里使用了case when 这个小技巧来实现批量更新。

    举个例子:

    UPDATE categories 

        SET display_order = CASE id 

            WHEN 1 THEN 3 

            WHEN 2 THEN 4 

            WHEN 3 THEN 5 

        END

    WHERE id IN (1,2,3)

    这句sql的意思是,更新display_order 字段,如果id=1 则display_order 的值为3,如果id=2 则 display_order 的值为4,如果id=3 则 display_order 的值为5。

    即是将条件语句写在了一起。

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

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

    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)

    到这里,已经完成一条mysql语句更新多条记录了。

    但是要在业务中运用,需要结合服务端语言,这里以php为例,构造这条mysql语句:

    $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 .= "END WHERE id IN ($ids)"; 

    echo $sql;

    这个例子,有8条记录进行更新。代码也很容易理解,你学会了吗

    性能分析

    当我使用上万条记录利用mysql批量更新,发现使用最原始的批量update发现性能很差,将网上看到的总结一下一共有以下三种办法:

    1.批量update,一条记录update一次,性能很差

    update test_tbl set dr='2' where id=1;

    2.replace into 或者insert into ...on duplicate key update

    replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

    或者使用

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

    3.创建临时表,先更新临时表,然后从临时表中update

    create temporary table tmp(id int(4) primary key,dr varchar(50));

    insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');

    update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

    注意:这种方法需要用户有temporary 表的create 权限。

    下面是上述方法update 100000条数据的性能测试结果:

    逐条update

    real    0m15.557s

    user    0m1.684s

    sys    0m1.372s

    replace into

    real    0m1.394s

    user    0m0.060s

    sys    0m0.012s

    insert into on duplicate key update

    real    0m1.474s

    user    0m0.052s

    sys    0m0.008s

    create temporary table and update:

    real    0m0.643s

    user    0m0.064s

    sys    0m0.004s

    就测试结果来看,测试当时使用replace into性能较好。

    replace into  和insert into on duplicate key update的不同在于:

    replace into 操作本质是对重复的记录先delete 后insert,如果更新的字段不全会将缺失的字段置为缺省值

    insert into 则是只update重复记录,不会改变其它字段。

    QUESTION:

    Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

    SOLVE:

    原因是在safe mode下,要强制安全点,update只能跟where了, 要取消这个限制,可以:

        SET SQL_SAFE_UPDATES=0;

    展开全文
  • mybatis批量更新数据三种方法效率对比

    万次阅读 多人点赞 2019-03-19 18:44:02
    探讨批量更新数据三种写法的效率问题。 实现方式有三种, 一种用for循环通过循环传过来的参数集合,循环出N条sql, 另一种 用mysql的case when 条件判断变相的进行批量更新 还有一个是用ON DUPLICATE KEY ...

    探讨批量更新数据三种写法的效率问题。

    实现方式有三种,

    一种用for循环通过循环传过来的参数集合,循环出N条sql,

    另一种 用mysql的case when 条件判断变相的进行批量更新  

    还有一个是用ON DUPLICATE KEY UPDATE进行批量更新

    下面进行实现。

    注意第一种方法要想成功,需要在db链接url后面带一个参数  &allowMultiQueries=true

    即:  jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true

    其实这种东西写过来写过去就是差不多一样的代码,不做重复的赘述,直接上代码。
     

     
        <!-- 批量更新第一种方法,通过接收传进来的参数list进行循环着组装sql -->
         <update id="updateBatch" parameterType="java.util.List" >
            <foreach collection="list" item="item" index="index" open="" close="" separator=";">
                update standard_relation
                <set >
                    <if test="item.standardFromUuid != null" >
                        standard_from_uuid = #{item.standardFromUuid,jdbcType=VARCHAR},
                    </if>
                    <if test="item.standardToUuid != null" >
                        standard_to_uuid = #{item.standardToUuid,jdbcType=VARCHAR},
                    </if>
                    <if test="item.gmtModified != null" >
                        gmt_modified = #{item.gmtModified,jdbcType=TIMESTAMP},
                    </if>
                </set>
                where id = #{item.id,jdbcType=BIGINT}
            </foreach>
        </update>
     
        <!-- 批量更新第二种方法,通过 case when语句变相的进行批量更新 -->
        <update id="updateBatch" parameterType="java.util.List" >
            update standard_relation
            <trim prefix="set" suffixOverrides=",">
                <trim prefix="standard_from_uuid =case" suffix="end,">
                    <foreach collection="list" item="i" index="index">
                        <if test="i.standardFromUuid!=null">
                            when id=#{i.id} then #{i.standardFromUuid}
                        </if>
                    </foreach>
                </trim>
                <trim prefix="standard_to_uuid =case" suffix="end,">
                    <foreach collection="list" item="i" index="index">
                        <if test="i.standardToUuid!=null">
                            when id=#{i.id} then #{i.standardToUuid}
                        </if>
                    </foreach>
                </trim>
                <trim prefix="gmt_modified =case" suffix="end,">
                    <foreach collection="list" item="i" index="index">
                        <if test="i.gmtModified!=null">
                            when id=#{i.id} then #{i.gmtModified}
                        </if>
                    </foreach>
                </trim>
            </trim>
            where
            <foreach collection="list" separator="or" item="i" index="index" >
                id=#{i.id}
            </foreach>
        </update>
    批量更新第三种方法,用ON DUPLICATE KEY UPDATE
     <insert id="updateBatch" parameterType="java.util.List">
            insert into standard_relation(id,relation_type, standard_from_uuid,
            standard_to_uuid, relation_score, stat,
            last_process_id, is_deleted, gmt_created,
            gmt_modified,relation_desc)VALUES
            <foreach collection="list" item="item" index="index" separator=",">
                (#{item.id,jdbcType=BIGINT},#{item.relationType,jdbcType=VARCHAR}, #{item.standardFromUuid,jdbcType=VARCHAR},
                #{item.standardToUuid,jdbcType=VARCHAR}, #{item.relationScore,jdbcType=DECIMAL}, #{item.stat,jdbcType=TINYINT},
                #{item.lastProcessId,jdbcType=BIGINT}, #{item.isDeleted,jdbcType=TINYINT}, #{item.gmtCreated,jdbcType=TIMESTAMP},
                #{item.gmtModified,jdbcType=TIMESTAMP},#{item.relationDesc,jdbcType=VARCHAR})
            </foreach>
            ON DUPLICATE KEY UPDATE
            id=VALUES(id),relation_type = VALUES(relation_type),standard_from_uuid = VALUES(standard_from_uuid),standard_to_uuid = VALUES(standard_to_uuid),
            relation_score = VALUES(relation_score),stat = VALUES(stat),last_process_id = VALUES(last_process_id),
            is_deleted = VALUES(is_deleted),gmt_created = VALUES(gmt_created),
            gmt_modified = VALUES(gmt_modified),relation_desc = VALUES(relation_desc)
        </insert>

     

     @Override
        public void updateStandardRelations() {
            List<StandardRelation> list=standardRelationMapper.selectByStandardUuid("xiemingjieupdate");
            for(StandardRelation tmp:list){
                tmp.setStandardFromUuid(tmp.getStandardFromUuid()+"update");
                tmp.setStandardToUuid(tmp.getStandardToUuid()+"update");
            }
            long begin=System.currentTimeMillis();
            standardRelationManager.updateBatch(list);
            long end=System.currentTimeMillis();
            System.out.print("当前的批量更新的方法用时"+(end-begin)+"ms");
        }

     

     

          sql语句for循环效率其实相当高的,因为它仅仅有一个循环体,只不过最后update语句比较多,量大了就有可能造成sql阻塞。

          case when虽然最后只会有一条更新语句,但是xml中的循环体有点多,每一个case when 都要循环一遍list集合,所以大批量拼sql的时候会比较慢,所以效率问题严重。使用的时候建议分批插入。

        duplicate key update可以看出来是最快的,但是一般大公司都禁用,公司一般都禁止使用replace into和INSERT INTO … ON DUPLICATE KEY UPDATE,这种sql有可能会造成数据丢失和主从上表的自增id值不一致。而且用这个更新时,记得一定要加上id,而且values()括号里面放的是数据库字段,不是java对象的属性字段。

    根据效率,安全方面综合考虑,选择适合的很重要。

     


    参考原文:https://blog.csdn.net/xu1916659422/article/details/77971696/ 

    展开全文
  • sql的update语句 批量更新某字段

    万次阅读 多人点赞 2018-10-26 09:22:34
    1 有时候要实现字段的批量替换 update 表名 set 字段A = replace(字段A ,"png","jpg" ); 2 批量拼接字符串到某字段 update 表名 set 字段A = CONCAT( 字段A , "xxx" ); update ...

    Mysql 批量替换:

    1 有时候要实现字段的批量替换

    update 表名 set 字段A = replace(字段A ,"png","jpg" );

     

    2 批量拼接字符串到某字段

    update 表名 set 字段A = CONCAT( 字段A  , "xxx"  );

    update 表名 set 字段A = CONCAT( "yyy" , 字段A  , "xxx"  );

     

    3 批量截取某字段,扔掉开始的4个字符

    update 表名 set 字段A=SUBSTR(字段A,4);

     

    4 批量截取某字段,保留结尾的3个字符
    update 表名 set 字段A=SUBSTR(字段A,-3);

     

    5 批量截取某字段,去掉结尾的2个字符

    update 表名 set 字段A=SUBSTR(字段A,1,LENGTH(字段A)-2);

     

    更详细的方法请参考MYSQL的SUBSTR函数

     

    特别的思路,可以替换复杂规则的字符(一定要看)

    1、将目标数据,借助navicat等工具,可以导出为update sql

    2、既然是update纯文本了,就可以用notepad++记事本工具,批量替换了

    3、替换时可以用正则匹配替换,正则匹配详见这篇文章  正则匹配html标签_匹配两字符串之间内容_匹配image标签

     

     

    ------------update+select----------------------------------以下是互联网收集,用的时候再仔细验证

    /*
      多表关联update的时候,记得要加exists()条件,否则不满足条件的记录被update称NULL:
      比如:stu表存在,但stu1表不存在的数据,对应的字段会被updat成NULL;
    */

    6 多表关联update单字段
    update stu t set t.NAME = (select t1.NAME from stu1 t1 where t1.ID = t.ID)
    where exists(select 1 from stu1 t2 where t2.ID = t.ID);

    7 多表关联update多字段
    update stu t set (t.NAME, t.SEX) = (select t1.NAME, t1.SEX from stu1 t1 where t1.ID = t.ID)
    where exists(select 1 from stu1 t2 where t2.ID = t.ID);

     

    UPDATE table1 alias
    SET (column_name,column_name ) = (
    SELECT (column_name, column_name)
    FROM table2
    WHERE column_name = alias.column_name)
    WHERE column_name = VALUE

    展开全文
  • MybatisPlus批量更新

    万次阅读 2019-11-05 11:06:05
    1、我们使用Mybatis批量更新时,通常是这样的: Mapper: void batchUpdateDemo(@Param("list") List<DemoDO> list); XML: <update id="batchUpdateDemo"> update `demo_table` set a = <...

    1、我们使用Mybatis批量更新时,通常是这样的:

    Mapper:

    void batchUpdateDemo(@Param("list") List<DemoDO> list);
    

    XML:

    <update id="batchUpdateDemo">
        update `demo_table`
        set a =
        <foreach collection="list" item="udo" separator=" " open="case id" close="end ,">
            when #{udo.id} then #{udo.a}
        </foreach>
        b =
        <foreach collection="list" item="udo" separator=" " open="case id" close="end">
            when #{udo.id} then #{udo.b}
        </foreach>
        where id in
        <foreach collection="list" item="udo" separator="," open="(" close=")">
            #{udo.id}
        </foreach>
    </update>

    字段少的时候可以接受 ,但是一多就emmm...

    2、那么mybatisPlus可以解决这一痛点

    mybatis-plus提供了

    IService 及其实现类 ServiceImpl<M extends BaseMapper<T>, T> 
    IService 实现类( 泛型:M 是 mapper 对象,T 是实体 , PK 是主键泛型 )

    我们在使用时只需继承该实现类即可,前提需要定义继承BaseMapper的Mapper和相应DO

    public class DemoServiceImpl extends ServiceImpl<DemoMapper,DmeoDO> {...}
    
    

    使用:

    @Autowired
    DemoServiceImpl cpService;
    
    public void mybatisPlusTest() {
        List<DemoDO> list = new ArrayList<>();
        DemoDO cdo1 = new DemoDO();
        cdo1.setId(4);
        cdo1.setName("测试11");
        DemoDO cdo2 = new DemoDO();
        cdo2.setId(5);
        cdo2.setName("测试22");
        list.add(cdo1);
        list.add(cdo2);
    
        cpService.updateBatchById(list,1000);
    }

    mybatis-plus安装可以参考:https://mp.csdn.net/postedit/102587131

     

    3、感兴趣的同学可以看一下batchUpdate的源码,底层还是使用了mybatis:

    public boolean updateBatchById(Collection<T> entityList, int batchSize) {
        Assert.notEmpty(entityList, "error: entityList must not be empty");
        String sqlStatement = sqlStatement(SqlMethod.UPDATE_BY_ID);
        try (SqlSession batchSqlSession = sqlSessionBatch()) {
            int i = 0;
            for (T anEntityList : entityList) {
                MapperMethod.ParamMap<T> param = new MapperMethod.ParamMap<>();
                param.put(Constants.ENTITY, anEntityList);
                batchSqlSession.update(sqlStatement, param);
                if (i >= 1 && i % batchSize == 0) {
                    batchSqlSession.flushStatements();
                }
                i++;
            }
            batchSqlSession.flushStatements();
        }
        return true;
    }

     

    展开全文
  • sql批量更新update嵌套select更新

    千次阅读 多人点赞 2021-05-28 16:35:02
    有两张表【user】和【city】,user表的 city_uuid、city_no 和 city 表的 city_uuid、city_no 一一对应,但是 user 表只有 city_uuid,这时候需要将 city 对应的 city_no 批量更新到 user 表中 批量更新方式 第一...
  • MyBatis批量更新Mysql数据

    万次阅读 2018-11-12 20:40:39
    MyBatis 批量更新数据 1. 数据库语句 #使用SQL一次批量更新多条记录 UPDATE rba_house_status SET audit_status = ( CASE WHEN house_id = 100023 then 3 END ), audit_desc = ( CASE WHEN house_id = 100010 ...
  • url:jdbc:mysql://localhost:3306/meicharacterEncoding=utf8&allowMultiQueries=true
  • mybatis批量插入、批量更新操作及null问题处理 mybatis批量插入、批量更新常规写法,及升级写法 null value in column “xxx” violates not-null constraint mybatis批量操作报错问题处理 批量插入 常规写法 <...
  • 批量更新 mysql更新语句很简单,更新一条数据的某个字段,一般这样写: UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value'; 如果更新同一字段为同一个值,mysql也很简单,修改下where...
  • CakePHP中的批量保存及批量更新

    千次阅读 2017-06-21 23:05:24
    在CakePHP中,批量保存及批量更新主要用到 newEntities() 、patchEntities()、saveMany()及updateAll()方法。
  • Mybatis传入List实现批量更新

    万次阅读 热门讨论 2018-08-17 16:51:20
    * 批量更新新库存 * @param list * @return */ int updateNewStock(@Param(value = "list") List<GreenBeanMsg> list); xml具体实现代码 <update id="updateNewStock" paramet...
  • 【批量删除】 session.query(User).filter(User.home=='shanghai').delete() ...【批量更新】 session.query(User).filter(User.home=='shanghai').update({JyUser.login_num:0}) session.commit() 【批量...
  • mybatis 批量更新

    千次阅读 2019-07-02 10:54:53
    mybatis 批量更新,首先要在数据库连接 url 上添加配置&allowMultiQueries=true,因为 mysql 默认不支持批量更新,所以需要开发人员手动设置 spring: datasource: url: jdbc:mysql://localhost:3306/test?...
  • mybatis批量更新踩坑

    千次阅读 2019-06-26 23:57:36
    说一下遇到的大坑,mybatis批量更新update语句,我们知道mysql是支持批量插入和批量删除的,因此它也是支持批量更新的,但是批量更新的方法跟批量插入删除略有不同,先简单说一下mybatis批量更新的方法吧,我说知道...
  • Oracle批量更新

    千次阅读 2017-06-28 16:36:13
    ①导出批量更新SQL select CONCAT(CONCAT(CONCAT(CONCAT('update test_a set a_date=', b_date), 'where a_id='), b_id), ' and a_flag=1;') from test
  • Yii2.0 批量更新以及批量插入

    千次阅读 2018-03-17 11:19:09
    批量更新方法1./** * 批量更新循环周期 * @param array $condition * $condition = ['advertise_id' =&gt; '','status' =&gt; '', 'weekdays'=&gt;[1,2,3]] 查询条件 * $params = ['status' =&...
  • hibernate批量更新与jdbc批量更新

    千次阅读 2012-12-14 20:37:25
    批量更新是指在一个事务中更新大批量数据,批量删除是指在一个事务中删除大批量数据。以下程序直接通过Hibernate API批量更新CUSTOMERS表中年龄大于零的所有记录的AGE字段: tx = session.begin...
  • mysql批量更新批量更新同一字段为不同值 1.MySQL更新一条记录语句: UPDATE user SET name='zhangsan' WHERE id=1 2.更新同一条字段为相同值 UPDATE user SET name='zhangsan' WHERE id in (1,2,3); 3.更新不同...
  • Jeesite 批量更新和批量插入

    千次阅读 2019-01-02 16:56:41
    Jeesite 批量更新和批量插入 xml配置 &lt;insert id="batchInsertList"&gt; INSERT INTO t_dc_bug( zt_bug_id, product_id ) VALUES &lt;foreach item="item" index="...
  • pip批量更新所有包

    千次阅读 2020-02-06 14:31:52
    安装批量更新命令 pip install pip-review 依次更新所有包 pip-review --local --interactive 命令执行后对于可更新的包,会依次确认是否更新,所以即使有个别包不想更新也不用担心。 参考文献 Python使用pip更新...
  • jdbc实现批量更新

    千次阅读 2019-01-10 10:34:59
    批量更新 1.批量更新优点 批处理:将一组更新语句作为一个单元,同时发送到数据库执行。 批处理有效的降低了应用程序和数据库之间的网络调用,相比单个SQL语句的处理,批处理效率更高。 2.批量更新API addBatch...
  • Mybatis中进行批量更新

    万次阅读 2019-05-06 12:43:47
    Mybatis中进行批量更新 Mybatis中进行批量更新 Sql 批量更新 一条sql语句来批量更新所有数据,下面直接看一下在mybatis中通常是怎么写的(去掉mybatis语法就是原生的sql语句了)。 <update id="updateBatch" ...
  • Hyperf数据库批量更新

    2020-06-01 20:29:36
    Hyperf没有批量更新的功能,网上的支持也比较少 但Hyperf是基于laravel的orm,因为可以搜下大神们写的laravel的批量更新的方法 //批量更新 public function updateBatch($multipleData = []) { try { $...
  • JDBC批量更新PreparedStatement和Statement

    千次阅读 2018-09-13 02:09:10
    JDBC批量更新PreparedStatement和Statement PreparedStatement批量更新 Statement批量更新 JDBC批量更新PreparedStatement和Statement PreparedStatement批量更新 Statement批量更新 package ...
  • MyBatis 批量更新批量更新

    千次阅读 2018-07-16 09:48:30
    Mapper的内容如下: ... ... ... ...import java.util.List;...public interface TbSysUserPhoneBelongMapper extends BaseMapper...-- 下面两个bean是增加的过滤器,为了解决批量更新被拦截了的问题 -->
  • 批量更新 mysql更新语句很简单,更新一条数据的某个字段,一般这样写: UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value'; 如果更新同一字段为同一个值,mysql也很简单,修改下where...
  • 今天,为了更多了解下,mybatis批量新增和批量更新在simple/batch模式 + MySQL的rewriteBatchedStatements下效率有什么区别,做了一次实验。 实验结果,让人意想不到,如果没有亲自测试,可能一直会被网上帖子误导...
  • Android RecyclerView批量更新notifyItemRangeChanged附录1的文章,介绍RecyclerView的定点更新,现在介绍RecyclerView的批量更新,RecyclerView的批量更新通过notifyItemRangeChanged实现,notifyItemRangeChanged...
  • mysql+mybatis 批量插入与批量更新

    千次阅读 2018-03-30 11:21:36
    首先批量更新需要增加数据库的链接必须加上但是数据库连接必须加上 allowMultiQueries=true 属性,不然会报错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 237,680
精华内容 95,072
关键字:

批量更新