<insert id="batchInsert" parameterType="java.util.List">\n insert into USER (id, name) values\n <foreach collection="list" item="model" index="index" separator=","> \n (#{model.id}, #{model.name})\n <\/foreach>\n<\/insert><\/code><\/pre>\n\u8fd9\u4e2a\u65b9\u6cd5\u63d0\u5347\u6279\u91cf\u63d2\u5165\u901f\u5ea6\u7684\u539f\u7406\u662f\uff0c\u5c06\u4f20\u7edf\u7684\uff1a<\/p>\n
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");\nINSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");\nINSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");\nINSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");\nINSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");<\/code><\/pre>\n\u8f6c\u5316\u4e3a\uff1a<\/p>\n
INSERT INTO `table1` (`field1`, `field2`) \nVALUES ("data1", "data2"),\n("data1", "data2"),\n("data1", "data2"),\n("data1", "data2"),\n("data1", "data2");<\/code><\/pre>\n\u5728MySql Docs\u4e2d\u4e5f\u63d0\u5230\u8fc7\u8fd9\u4e2atrick\uff0c\u5982\u679c\u8981\u4f18\u5316\u63d2\u5165\u901f\u5ea6\u65f6\uff0c\u53ef\u4ee5\u5c06\u8bb8\u591a\u5c0f\u578b\u64cd\u4f5c\u7ec4\u5408\u5230\u4e00\u4e2a\u5927\u578b\u64cd\u4f5c\u4e2d\u3002\u7406\u60f3\u60c5\u51b5\u4e0b\uff0c\u8fd9\u6837\u53ef\u4ee5\u5728\u5355\u4e2a\u8fde\u63a5\u4e2d\u4e00\u6b21\u6027\u53d1\u9001\u8bb8\u591a\u65b0\u884c\u7684\u6570\u636e\uff0c\u5e76\u5c06\u6240\u6709\u7d22\u5f15\u66f4\u65b0\u548c\u4e00\u81f4\u6027\u68c0\u67e5\u5ef6\u8fdf\u5230\u6700\u540e\u624d\u8fdb\u884c\u3002<\/p>\n
\u4e4d\u770b\u4e0a\u53bb\u8fd9\u4e2aforeach\u6ca1\u6709\u95ee\u9898\uff0c\u4f46\u662f\u7ecf\u8fc7\u9879\u76ee\u5b9e\u8df5\u53d1\u73b0\uff0c\u5f53\u8868\u7684\u5217\u6570\u8f83\u591a\uff0820+\uff09\uff0c\u4ee5\u53ca\u4e00\u6b21\u6027\u63d2\u5165\u7684\u884c\u6570\u8f83\u591a\uff085000+\uff09\u65f6\uff0c\u6574\u4e2a\u63d2\u5165\u7684\u8017\u65f6\u5341\u5206\u6f2b\u957f\uff0c\u8fbe\u5230\u4e8614\u5206\u949f\uff0c\u8fd9\u662f\u4e0d\u80fd\u5fcd\u7684\u3002\u5728\u8d44\u6599\u4e2d\u4e5f\u63d0\u5230\u4e86\u4e00\u53e5\u8bdd\uff1a<\/p>\n
Of course don’t combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don’t do it one at a time. You shouldn’t equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.<\/p><\/blockquote>\n
\u5b83\u5f3a\u8c03\uff0c\u5f53\u63d2\u5165\u6570\u91cf\u5f88\u591a\u65f6\uff0c\u4e0d\u80fd\u4e00\u6b21\u6027\u5168\u653e\u5728\u4e00\u6761\u8bed\u53e5\u91cc\u3002\u53ef\u662f\u4e3a\u4ec0\u4e48\u4e0d\u80fd\u653e\u5728\u540c\u4e00\u6761\u8bed\u53e5\u91cc\u5462\uff1f\u8fd9\u6761\u8bed\u53e5\u4e3a\u4ec0\u4e48\u4f1a\u8017\u65f6\u8fd9\u4e48\u4e45\u5462\uff1f<\/p>\n
\u6211\u67e5\u9605\u4e86\u8d44\u6599\u53d1\u73b0\uff1a<\/strong><\/p>\nInsert inside Mybatis foreach is not batch, this is a single (could become giant) SQL statement and that brings drawbacks:<\/p>\n
\n- some database such as Oracle here does not support.<\/li>\n
- in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.<\/li>\n<\/ul>\n
Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insertstatement in a Java Foreach loop. The most important thing is the session Executor type.<\/p>\n
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);\nfor (Model model : list) {\n session.insert("insertStatement", model);\n}\nsession.flushStatements();<\/code><\/pre>\nUnlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.<\/p>\n
\u4ece\u8d44\u6599\u4e2d\u53ef\u77e5\uff0c\u9ed8\u8ba4\u6267\u884c\u5668\u7c7b\u578b\u4e3aSimple\uff0c\u4f1a\u4e3a\u6bcf\u4e2a\u8bed\u53e5\u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u9884\u5904\u7406\u8bed\u53e5\uff0c\u4e5f\u5c31\u662f\u521b\u5efa\u4e00\u4e2aPreparedStatement\u5bf9\u8c61\u3002\u5728\u6211\u4eec\u7684\u9879\u76ee\u4e2d\uff0c\u4f1a\u4e0d\u505c\u5730\u4f7f\u7528\u6279\u91cf\u63d2\u5165\u8fd9\u4e2a\u65b9\u6cd5\uff0c\u800c\u56e0\u4e3aMyBatis\u5bf9\u4e8e\u542b\u6709<foreach><\/code>\u7684\u8bed\u53e5\uff0c\u65e0\u6cd5\u91c7\u7528\u7f13\u5b58\uff0c\u90a3\u4e48\u5728\u6bcf\u6b21\u8c03\u7528\u65b9\u6cd5\u65f6\uff0c\u90fd\u4f1a\u91cd\u65b0\u89e3\u6790sql\u8bed\u53e5\u3002<\/p>\n\nInternally, it still generates the same single insert statement with many placeholders as the JDBC code above.<\/p>\n
MyBatis has an ability to cache PreparedStatement, but this statement cannot be cached because it contains <foreach \/><\/code> element and the statement varies depending on the parameters. As a result, MyBatis has to 1) evaluate the foreach part and 2) parse the statement string to build parameter mapping [1] on every execution of this statement.<\/p>\nAnd these steps are relatively costly process when the statement string is big and contains many placeholders.<\/p>\n
[1] simply put, it is a mapping between placeholders and the parameters.<\/p>\n<\/blockquote>\n
\u4ece\u4e0a\u8ff0\u8d44\u6599\u53ef\u77e5\uff0c\u8017\u65f6\u5c31\u8017\u5728\uff0c\u7531\u4e8e\u6211foreach\u540e\u67095000+\u4e2avalues\uff0c\u6240\u4ee5\u8fd9\u4e2aPreparedStatement\u7279\u522b\u957f\uff0c\u5305\u542b\u4e86\u5f88\u591a\u5360\u4f4d\u7b26\uff0c\u5bf9\u4e8e\u5360\u4f4d\u7b26\u548c\u53c2\u6570\u7684\u6620\u5c04\u5c24\u5176\u8017\u65f6\u3002\u5e76\u4e14\uff0c\u67e5\u9605\u76f8\u5173\u8d44\u6599\u53ef\u77e5\uff0cvalues\u7684\u589e\u957f\u4e0e\u6240\u9700\u7684\u89e3\u6790\u65f6\u95f4\uff0c\u662f\u5448\u6307\u6570\u578b\u589e\u957f\u7684\u3002<\/p>\n
![\"\"](\"https:\/\/segmentfault.com\/img\/bVc17gE\" "\\"\\"")
<\/p>\n
\u6240\u4ee5\uff0c\u5982\u679c\u975e\u8981\u4f7f\u7528 foreach \u7684\u65b9\u5f0f\u6765\u8fdb\u884c\u6279\u91cf\u63d2\u5165\u7684\u8bdd\uff0c\u53ef\u4ee5\u8003\u8651\u51cf\u5c11\u4e00\u6761 insert \u8bed\u53e5\u4e2d values \u7684\u4e2a\u6570\uff0c\u6700\u597d\u80fd\u8fbe\u5230\u4e0a\u9762\u66f2\u7ebf\u7684\u6700\u5e95\u90e8\u7684\u503c\uff0c\u4f7f\u901f\u5ea6\u6700\u5feb\u3002\u4e00\u822c\u6309\u7ecf\u9a8c\u6765\u8bf4\uff0c\u4e00\u6b21\u6027\u63d220~50\u884c\u6570\u91cf\u662f\u6bd4\u8f83\u5408\u9002\u7684\uff0c\u65f6\u95f4\u6d88\u8017\u4e5f\u80fd\u63a5\u53d7\u3002<\/p>\n
\u91cd\u70b9\u6765\u4e86\u3002\u4e0a\u9762\u8bb2\u7684\u662f\uff0c\u5982\u679c\u975e\u8981\u7528<foreach><\/code>\u7684\u65b9\u5f0f\u6765\u63d2\u5165\uff0c\u53ef\u4ee5\u63d0\u5347\u6027\u80fd\u7684\u65b9\u5f0f\u3002\u800c\u5b9e\u9645\u4e0a\uff0cMyBatis\u6587\u6863\u4e2d\u5199\u6279\u91cf\u63d2\u5165\u7684\u65f6\u5019\uff0c\u662f\u63a8\u8350\u4f7f\u7528\u53e6\u5916\u4e00\u79cd\u65b9\u6cd5\u3002\uff08\u53ef\u4ee5\u770b http:\/\/www.mybatis.org\/mybati…<\/a> \u4e2d Batch Insert Support \u6807\u9898\u91cc\u7684\u5185\u5bb9\uff09<\/p>\nSqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);\ntry {\n SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);\n List<SimpleTableRecord> records = getRecordsToInsert(); \/\/ not shown\n \n BatchInsert<SimpleTableRecord> batchInsert = insert(records)\n .into(simpleTable)\n .map(id).toProperty("id")\n .map(firstName).toProperty("firstName")\n .map(lastName).toProperty("lastName")\n .map(birthDate).toProperty("birthDate")\n .map(employed).toProperty("employed")\n .map(occupation).toProperty("occupation")\n .build()\n .render(RenderingStrategy.MYBATIS3);\n \n batchInsert.insertStatements().stream().forEach(mapper::insert);\n \n session.commit();\n} finally {\n session.close();\n}<\/code><\/pre>\n\u5373\u57fa\u672c\u601d\u60f3\u662f\u5c06 MyBatis session \u7684 executor type \u8bbe\u4e3a Batch \uff0c\u7136\u540e\u591a\u6b21\u6267\u884c\u63d2\u5165\u8bed\u53e5\u3002\u5c31\u7c7b\u4f3c\u4e8eJDBC\u7684\u4e0b\u9762\u8bed\u53e5\u4e00\u6837\u3002<\/p>\n
Connection connection = DriverManager.getConnection("jdbc:mysql:\/\/127.0.0.1:3306\/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root");\nconnection.setAutoCommit(false);\nPreparedStatement ps = connection.prepareStatement(\n "insert into tb_user (name) values(?)");\nfor (int i = 0; i < stuNum; i++) {\n ps.setString(1,name);\n ps.addBatch();\n}\nps.executeBatch();\nconnection.commit();\nconnection.close();<\/code><\/pre>\n\u7ecf\u8fc7\u8bd5\u9a8c\uff0c\u4f7f\u7528\u4e86 ExecutorType.BATCH \u7684\u63d2\u5165\u65b9\u5f0f\uff0c\u6027\u80fd\u663e\u8457\u63d0\u5347\uff0c\u4e0d\u5230 2s \u4fbf\u80fd\u5168\u90e8\u63d2\u5165\u5b8c\u6210\u3002<\/p>\n
\u603b\u7ed3\u4e00\u4e0b\uff0c\u5982\u679cMyBatis\u9700\u8981\u8fdb\u884c\u6279\u91cf\u63d2\u5165\uff0c\u63a8\u8350\u4f7f\u7528 ExecutorType.BATCH \u7684\u63d2\u5165\u65b9\u5f0f\uff0c\u5982\u679c\u975e\u8981\u4f7f\u7528 <foreach><\/code>\u7684\u63d2\u5165\u7684\u8bdd\uff0c\u9700\u8981\u5c06\u6bcf\u6b21\u63d2\u5165\u7684\u8bb0\u5f55\u63a7\u5236\u5728 20~50 \u5de6\u53f3\u3002<\/p>\n\u53c2\u8003\u8d44\u6599<\/strong><\/p>\n\n- https:\/\/dev.mysql.com\/doc\/ref…<\/a><\/li>\n
- https:\/\/stackoverflow.com\/que…<\/a><\/li>\n
- https:\/\/stackoverflow.com\/que…<\/a><\/li>\n
- https:\/\/blog.csdn.net\/wlwlwlw…<\/a><\/li>\n
- http:\/\/blog.harawata.net\/2016…<\/a><\/li>\n
- https:\/\/www.red-gate.com\/simp…<\/a><\/li>\n
- https:\/\/stackoverflow.com\/que…<\/a><\/li>\n
- http:\/\/www.mybatis.org\/mybati…<\/a><\/li>\n<\/ol>\n
\u539f\u6587\u94fe\u63a5\uff1ahttps:\/\/blog.csdn.net\/huangha…<\/a><\/p>\n\u7248\u6743\u58f0\u660e\uff1a\u672c\u6587\u4e3aCSDN\u535a\u4e3b\u300chuanghanqian\u300d\u7684\u539f\u521b\u6587\u7ae0\uff0c\u9075\u5faaCC 4.0 BY-SA\u7248\u6743\u534f\u8bae\uff0c\u8f6c\u8f7d\u8bf7\u9644\u4e0a\u539f\u6587\u51fa\u5904\u94fe\u63a5\u53ca\u672c\u58f0\u660e\u3002<\/p>\n
\u8fd1\u671f\u70ed\u6587\u63a8\u8350\uff1a<\/strong><\/p>\n1.1,000+ \u9053 Java\u9762\u8bd5\u9898\u53ca\u7b54\u6848\u6574\u7406(2022\u6700\u65b0\u7248)<\/a><\/p>\n2.\u52b2\u7206\uff01Java \u534f\u7a0b\u8981\u6765\u4e86\u3002\u3002\u3002<\/a><\/p>\n3.Spring Boot 2.x \u6559\u7a0b\uff0c\u592a\u5168\u4e86\uff01<\/a><\/p>\n4.\u522b\u518d\u5199\u6ee1\u5c4f\u7684\u7206\u7206\u7206\u70b8\u7c7b\u4e86\uff0c\u8bd5\u8bd5\u88c5\u9970\u5668\u6a21\u5f0f\uff0c\u8fd9\u624d\u662f\u4f18\u96c5\u7684\u65b9\u5f0f\uff01\uff01<\/a><\/p>\n