Mysql批量操作整理

最新推荐文章于 2025-01-11 17:56:08 发布
最新推荐文章于 2025-01-11 17:56:08 发布
阅读量2.7k 收藏
点赞数
分类专栏： MySQL C++ 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/ljp1919/article/details/52005118
版权

背景

面对批量频繁的数据库操作，本文尝试从经验整理mysql批量查询，插入等操作的方法

批量查询

情景1：查询同个字段多个值

采用方法：
采用In方式。返回的结果顺序对应In中值的顺序。这种同一字段不同值，采用In方式可以避免多次的任务提交。当然，也可以通过任务的START TRANSACTION和COMMIT方式将多个select语句一次性进行提交。这种事务命令可以用以一序列的数据库操作。
注意该方式对于值不在In中的查询是不会有返回的结果，那么就造成一定程度上不清楚In中的值对应的是select返回结果中的哪一条。此时，可以通过该记录的带有主键字段，对结果进行标识的。
代码：

        int reqsize=500;
        Json::Value rspTemp;
        boost::unordered_map<uint32_t, uint32_t> idMap;//记录krcid和对应的位置
        string krcids="(";
        for(int i=0; i<reqsize; i++)
        {
            //批量请求,in操作
            uint32_t temp = JsonReq[i].asInt();//即，In中的所有候选值
            idMap[temp] = i;
            rspTemp[i]["krcid"]=temp;
            rspTemp[i]["fmt"] ="";
            krcids +=Uint32ToString(temp);
            if(i != reqsize-1)
            {
                 krcids.append(",");
            }

        }
        krcids.append(")");
        string sql = "select ID, UC from table1 where ID IN ";
        sql = sql + krcids;
        DataTable dbTable;
        db.ExecSQL(sql, dbTable);
        int rowCount = dbTable.GetRowCount();

        for (int i = 0; i < rowCount; ++i)
        {
            int krcid =  dbTable[i][0].AsInt();//对krcid对应的fmt进行赋值
            int fmt =  dbTable[i][1].AsInt();//返回结果用json格式
            uint32_t rowId =idMap[krcid];
            rspTemp[rowId]["krcid"]=krcid;
            rspTemp[rowId]["fmt"]=fmt;
        }

情景2：涉及到联合查询

批量插入

采用方法1：Value方式合并数据

最简单的就是批量的语句，一次次执行insert操作，最慢这种。使用START TRANSACTION和COMMIT的事务型方式进行批量插入，效率会比一条条插入要高得多。这是因为进行一个INSERT操作时，MySQL**内部会建立一个事务**，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。采用value的方法可以合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。同时，也
代码：

    int linecount=0;
    time_t start,stop;
    start = time(NULL);
    string finalstr;
    vector<uint32_t> vecorigina;
    vector<uint32_t> vectrans;
    vector<uint32_t> vecsound;
    vector<uint32_t> veckrci;
    int tasknum =5000;
    for(int i=0; i<rowCount; i++)
    {
        linecount++;
        uint32_t krcid = dbTable[i][0].AsInt();
        uint32_t origiuid = VecUids[i];//构造出来的数据
        uint32_t transuid = VecUids[i+rowCount];
        uint32_t sounduid = VecUids[i+2*rowCount];

        string sql_insert = "insert into tbl1 (id1,id2,id3,id4,modifytime) VALUES";
        string condition = "(?,?,?,?,now())";
        if(linecount%tasknum != 0 && linecount<rowCount)
        {
            finalstr = finalstr +condition+",";
            veckrci.push_back(krcid);
            vecorigina.push_back(origiuid);
            vectrans.push_back(transuid);
            vecsound.push_back(sounduid);
        }
        else
        {
            finalstr = finalstr +condition;
            finalstr = sql_insert+ finalstr;
            veckrci.push_back(krcid);
            vecorigina.push_back(origiuid);
            vectrans.push_back(transuid);
            vecsound.push_back(sounduid);
            dblyric_Debug.PrepareStatment(finalstr);
            int z=1;
            if(linecount==rowCount)//尾数处理
            {
                for(int j=0;j<veckrci.size();j++)
                {
                    dblyric_Debug.SetInt(z++, veckrci[j]);
                    dblyric_Debug.SetInt(z++, vecorigina[j]);
                    dblyric_Debug.SetInt(z++, vectrans[j]);
                    dblyric_Debug.SetInt(z++, vecsound[j]);
                }
                if (!dblyric_Debug.ExecuteUpdate())
                {

                    std::cout<<"update 失败! "<<linecount<<std::endl;
                }
                break;
            }
            if(veckrci.size()!=tasknum || vecorigina.size() !=tasknum || vectrans.size() !=tasknum||vecsound.size()!=tasknum)
            {
                std::cout<<"信息缺失!"<<linecount<<std::endl;
            }
            int j=0;
            for(;j<veckrci.size();j++)
            {
                dblyric_Debug.SetInt(z++, veckrci[j]);
                dblyric_Debug.SetInt(z++, vecorigina[j]);
                dblyric_Debug.SetInt(z++, vectrans[j]);
                dblyric_Debug.SetInt(z++, vecsound[j]);
            }
            if (!dblyric_Debug.ExecuteUpdate())
            {
                std::cout<<"update1 失败! "<<linecount<<std::endl;
            }

            finalstr = "";
            veckrci.clear();
            vecorigina.clear();
            vectrans.clear();
            vecsound.clear();
            stop =time(NULL);           
            std::cout<<"5K耗时："<<double(stop-start)<<std::endl;
            start = stop;
        }

    }

采用方法2：数据有序插入

数据有序插入可以提高插入效率。数据有序的插入是指插入记录在主键上是有序排列，例如datetime是记录的主键：

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('2', 'userid_2', 'content_2',2);

修改成下面语句，效率更高：

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('2', 'userid_2', 'content_2',2);

这是由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。可以参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

合并数据+事务的方法在较小数据量时，性能提高是很明显的，数据量较大时（1千万以上），性能会急剧下降，这是由于此时数据量超过了innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好，在数据量较大时，有序数据索引定位较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能。
注意事项：
1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。
2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。

采用方法3：通过文件（LOAD DATA LOCAL INFILE)

该方法测试过程中发现，数据量越大优势体现越是明显。按照网络上的说法是，20倍的提升：
According to the MySQL documentation, LOAD DATA INFILE is “roughly 20 times faster” than a MySQL INSERT statement。
SQL语句如下：

LOAD DATA LOCAL INFILE 'D:\\Users\\jason\\Desktop\\1009.txt'
INTO TABLE tbl1(Hash,Song,Id);

注意，可能需要使用到的SELECT … INTO OUTFILE，进行数据的构造。

http://dev.mysql.com/doc/refman/5.7/en/select-into.html

使用批处理对MySQL进行数据批量操作
03-03
批处理是一种非交互式运行MySQL程序的方法，如同您在MySQL中使用的命令一样，你仍然将使用这些命令。本文介绍了使用批处理对MySQL进行数据批量操作的方法。
整理的一些实用WordPress后台MySQL操作命令
10-27
在执行任何可能会影响数据的批量操作之前，备份WordPress数据库是防止数据丢失的重要步骤。备份通常可以通过专业的备份插件或者使用命令行工具来完成，例如使用mysqldump工具导出数据库。 通过执行SQL命令，我们...
sqlServer、MySql批量操作插件
weixin_34260991的博客
01-05 87
SqlServer: 1、EntityFramework.BulkInsert 。只适用于SqlServer。要想实现MySql批量插入，得自己实现一些方法，注册驱动之类，暂时没能力。 2、z.entityframwork.extensions 。适用于多钟数据库，包括mysql和sqlserver。但是是收费的，只当月免费。每月会发布一个免费版本，如此，每月都要更新版本才能免费使用...
MySQL教程之：批量使用mysql
zhuguoli200851的博客
01-11 722
在前几节中，您以交互方式使用mysql输入语句并查看结果。如果要获取交互式输出格式批量模式，请使用mysql-t。有些情况不允许交互式使用，例如，当您从cron作业运行查询时。在这种情况下，您必须使用批量模式。如果您希望脚本继续，即使其中的某些语句产生错误，您应该使用--force命令行选项。如果您重复运行查询（例如，每天或每周），将其设为脚本可以避免每次执行时重新键入它。当您以这种方式使用mysql时，您正在创建一个脚本文件，然后执行该脚本。您可以将脚本分发给其他人，以便他们也可以运行语句。
mysql 批量操作
安静的码农
03-06 296
insert INSERT INTO example ALUES (100, 'Name 1', 'Value 1', 'Other 1'), (101, 'Name 2', 'Value 2', 'Other 2'), (102, 'Name 3', 'Value 3', 'Other 3'), (103, 'Name 4', 'Value 4', 'Other 4'); update\del...
mysql批量操作语句
weixin_34355881的博客
11-12 144
2019独角兽企业重金招聘Python工程师标准>>> ...
c mysql 批量插入_c#之mysql四种带事务批量插入
weixin_32256355的博客
01-19 1104
前言对于像我这样的业务程序员开发一些表单内容是家常便饭的事情，说道表单 我们都避免不了多行内容的提交，多行内容保存，自然要用到数据库，如果循环打扰我数据库，数据库也会觉得很累，从而增加数据库服务器压力。所以小子不才，根据平时经验总结了一下用到的批量插入的方法代码。本文是针对c#语言开发，数据库是mysql数据库。当然我这是单片机的本地电脑运行，跟服务器上有很大区别，到了服务器上可能跑的比这快的多，...
MySQL 主键批量修改的坑与解决方案
私聊前往站内信：https://i.youkuaiyun.com/#/msg/chat/weixin_44976692
12-08 1万+
在 MySQL 中，主键的唯一性和不可重复性决定了直接批量修改主键可能导致冲突。使用临时值避免冲突：操作简单，适用性强。借助中间表迁移数据：适合复杂场景，安全可靠。按顺序更新主键：适合范围较小的批量更新。事务回滚保证安全性：适合操作前后不可中断的场景。通过这些方法，可以高效、安全地完成主键修改，同时避免常见错误。希望本文对你在数据库操作中有所帮助！
MySQL操作命令整理
weixin_39893889的博客
10-30 274
MySQL操作命令整理
python mysql批量查询_Python + MySQL 批量查询百度收录
weixin_36054993的博客
01-19 864
做SEO的同学，经常会遇到几百或几千个站点，然后对于收录情况去做分析的情况那么多余常用的一些工具在面对几千个站点需要去做收录分析的时候，那么就显得不是很合适。在此特意分享给大家一个批量查询百度收录状况的代码使用 Python + MySQL(MariaDB) 配合使用import pymysqlfrom urllib import requestimport reimport timeimport...
SQL SERVER 自动整理代码插件
08-03
可以帮您整理SQL 代码，让代码更加规范
mysql 批量操作存储过程
无聊的博客
12-09 281
DROP PROCEDURE IF EXISTS test_update; DELIMITER$$ CREATE PROCEDURE test_update () BEGIN DECLARE table_name_idx VARCHAR(40) DEFAULT 'table'; DECLARE i INT DEFAULT 1; DECLARE table_name VARCHAR(40) DEFAULT ''; -- ex: table01,table02......table31 W
mysql的批量操作_mysql批量操作
weixin_39689870的博客
02-08 119
&allowMultiQueries=true1、批量删除Mapper.javapublic void deleteCommunityRelUser(@Param("communityId") String communityId,@Param("userIdList") List userIdList) throws Exception;sql.xmlDELETE FROM COMMUN...
MySQL 四种批量更新操作
Just Play的博客
12-09 2560
原文 http://blog.youkuaiyun.com/u014520745/article/details/52416002 转载一篇大批量更新mysql数据的办法，为了便于阅读根据原文做了缩减。 mysql 批量更新如果一条条去更新效率是相当的慢, 循环一条一条的更新记录,一条记录update一次，这样性能很差，也很容易造成阻塞。 mysql 批量更新共有以下四种办法1、.replace into
MySQL批量命令
taian1665的博客
04-21 291
自增归0 SELECT concat('ALTER TABLE ', table_name, ' AUTO_INCREMENT=1;') FROM information_schema.tables WHERE table_schema='auction'; 删除表 SELECT concat('DROP TABLE ', table_name, ';') FROM information_s...
MySQL批量插入数据详解
良月柒
06-04 7351
批量插入数据是MySQL中提高插入效率、减少开销的重要手段。本文详细介绍了几种常见的批量插入方法，包括使用单条INSERT语句、命令、存储过程以及使用事务保证原子性等。同时，提供了一些优化和最佳实践建议。
MySQL中的批量操作（修改，插入）
热门推荐
Asurplus
11-04 20万+
在平常的项目中，我们会需要批量操作数据库的时候，例如：批量修改，批量插入，那我们不应该使用for循环去操作数据库，这样会导致我们反复与数据库发生连接和断开连接，影响性能和增加操作时间 所以我们可以使用编写SQL批量修改的方式去操作数据库 1、批量修改 UPDATE check_order_pl_detail SET remarks = CASE id ...
mysql批量修改
最新发布
03-15
<think>好的，我现在要帮用户解决在MySQL中实现批量修改数据的问题。用户已经提供了一些相关引用，我需要仔细看看这些引用内容，然后整理出合适的回答。 首先，引用1提到了MySQL批量修改原生SQL的流程，但具体代码示例可能没有详细给出。引用2讨论了批量插入的方法，比如使用`addBatch`和`executeBatch`，以及MyBatis-Plus的批量处理，默认1000条一批次。引用3则提到MyBatis传入List进行批量更新，需要在数据库连接URL中添加`allowMultiQueries=true`参数。引用4涉及游标和InnoDB的事务支持，可能和批量操作的事务处理有关。 接下来，我需要确定用户的需求是批量更新，而不仅仅是插入。常见的批量更新方法有几种：使用CASE WHEN语句、使用临时表、使用INSERT ... ON DUPLICATE KEY UPDATE，或者结合编程语言的批处理功能，比如JDBC的批处理。 根据引用2和3，使用JDBC的批处理或者MyBatis-Plus的批量操作是比较高效的方法，特别是处理大量数据时。而使用CASE WHEN可以在一条SQL中更新多条记录，但需要注意SQL长度限制。引用3提到的allowMultiQueries参数可能在使用多条UPDATE语句时是必需的。 另外，事务处理也很重要，引用4提到InnoDB支持事务，确保批量操作的原子性。所以，在代码示例中应该包含事务的控制。 现在需要将这些方法整理成步骤，并给出对应的示例。例如，CASE WHEN的方法需要构建一个包含多个WHEN条件的UPDATE语句；临时表方法需要创建临时表，然后通过JOIN来更新；JDBC批处理则需要循环添加参数，最后执行批处理。 同时要注意用户提到的示例代码，可能需要展示不同方法的SQL语句和编程层面的实现，比如Java的JDBC或MyBatis的示例。此外，引用3中的MyBatis示例可能需要配置allowMultiQueries，这也是需要注意的地方。 最后，要确保回答结构清晰，分点说明，并给出相关问题，帮助用户进一步了解相关内容。比如性能优化、事务处理、不同方法的适用场景等。</think>### 逐步解答：MySQL 批量修改数据的方法 #### 方法1：使用 `CASE WHEN` 动态更新 通过构建动态条件实现单条SQL批量更新，适用于数据量较小的情况。 ```sql UPDATE user SET age = CASE id WHEN 1 THEN 20 WHEN 2 THEN 25 WHEN 3 THEN 30 END, name = CASE id WHEN 1 THEN 'Tom' WHEN 2 THEN 'Jerry' WHEN 3 THEN 'Spike' END WHERE id IN (1,2,3); ``` **说明**：通过`CASE`为不同`id`设置不同字段值，需明确主键或唯一标识。 --- #### 方法2：临时表关联更新 通过创建临时表存储批量数据，再关联更新目标表，适合数据量较大的场景。 ```sql -- 创建临时表 CREATE TEMPORARY TABLE tmp_user (id INT, age INT, name VARCHAR(50)); -- 插入临时数据 INSERT INTO tmp_user VALUES (1,20,'Tom'), (2,25,'Jerry'), (3,30,'Spike'); -- 关联更新 UPDATE user u JOIN tmp_user t ON u.id = t.id SET u.age = t.age, u.name = t.name; ``` **优点**：避免单条SQL过长，提升可维护性。 --- #### 方法3：`INSERT ... ON DUPLICATE KEY UPDATE` 若需批量插入或更新，可利用唯一键约束实现。 ```sql INSERT INTO user (id, age, name) VALUES (1,20,'Tom'), (2,25,'Jerry'), (3,30,'Spike') ON DUPLICATE KEY UPDATE age = VALUES(age), name = VALUES(name); ``` **要求**：表中需有唯一索引（如`id`为主键）。 --- #### 方法4：编程语言批处理（以Java为例） 通过JDBC批处理减少网络IO，提升效率[^2]。 ```java Connection conn = DriverManager.getConnection(url); conn.setAutoCommit(false); // 关闭自动提交 PreparedStatement ps = conn.prepareStatement( "UPDATE user SET age=?, name=? WHERE id=?" ); for (User user : userList) { ps.setInt(1, user.getAge()); ps.setString(2, user.getName()); ps.setInt(3, user.getId()); ps.addBatch(); // 添加到批处理 } ps.executeBatch(); // 批量执行 conn.commit(); // 提交事务 ``` **优化点**：设置`rewriteBatchedStatements=true`参数可加速批处理[^2]。 --- #### 方法5：MyBatis 批量更新（需配置参数） 在MyBatis中实现批量更新需配置`allowMultiQueries=true`[^3]。 ```xml <update id="batchUpdate"> <foreach collection="list" item="item" separator=";"> UPDATE user SET age=#{item.age}, name=#{item.name} WHERE id=#{item.id} </foreach> </update> ``` **注意**：需在数据库连接URL中添加参数： ```properties jdbc:mysql://host:port/db?allowMultiQueries=true ``` --- ### 性能优化建议 1. **事务控制**：批量操作时显式开启事务，减少事务提交次数[^4]。 2. **分批处理**：每批次处理1000-5000条数据，避免内存溢出[^2]。 3. **索引优化**：确保`WHERE`条件字段有索引，但批量更新期间可暂时移除非必要索引。 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值