SqlServer 对语句的条数和参数的数量都有限制,分别是 1000 和 2100
sqlserver批量插入时动态sql允许的最大参数数量是2100个。即下面代码中“#”的数量。
<insert id="batchInsert" parameterType="list">
insert into Adv_permeability values
<foreach collection="permeabilityList" separator="," item="permeability">
(#{permeability.areaId}, #{permeability.areaName}, #{permeability.flag})
</foreach>
</insert>
Mysql 对执行的SQL语句大小进行限制,相当于对字符串进行限制。根据报错内容,默认允许最大SQL是 4M 。这个错误是 Mysql 的JDBC包抛出的。
## 查询当前大小
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 4194304 |
+----------------------+
1 row in set (0.00 sec)
## 修改为256M
mysql> SET GLOBAL max_allowed_packet=268435456;
Query OK, 0 rows affected (0.00 sec)
## 退出客户端重新登录
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 268435456 |
+----------------------+
1 row in set (0.00 sec)
Mybatis 解析动态SQL的源码
// 开始解析
public void parse() {
if (!configuration.isResourceLoaded(resource)) {
configurationElement(parser.evalNode("/mapper"));
configuration.addLoadedResource(resource);
bindMapperForNamespace();
}
parsePendingResultMaps();
parsePendingChacheRefs();
parsePendingStatements();
}
// 解析mapper
private void configurationElement(XNode context) {
try {
String namespace = context.getStringAttribute("namespace");
if (namespace.equals("")) {
throw new BuilderException("Mapper's namespace cannot be empty");
}
builderAssistant.setCurrentNamespace(namespace);
cacheRefElement(context.evalNode("cache-ref"));
cacheElement(context.evalNode("cache"));
parameterMapElement(context.evalNodes("/mapper/parameterMap"));
resultMapElements(context.evalNodes("/mapper/resultMap"));
sqlElement(context.evalNodes("/mapper/sql"));
buildStatementFromContext(context.evalNodes("select|insert|update|delete"));
} catch (Exception e) {
throw new BuilderException("Error parsing Mapper XML. Cause: " + e, e);
}
}
// 创建 select|insert|update|delete 语句
private void buildStatementFromContext(List<XNode> list, String requiredDatabaseId) {
for (XNode context : list) {
final XMLStatementBuilder statementParser = new XMLStatementBuilder(configuration, builderAssistant, context, requiredDatabaseId);
try {
statementParser.parseStatementNode();
} catch (IncompleteElementException e) {
configuration.addIncompleteStatement(statementParser);
}
}
}
// 填充参数,创建语句
public BoundSql getBoundSql(Object parameterObject) {
DynamicContext context = new DynamicContext(configuration, parameterObject);
rootSqlNode.apply(context);
SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();
SqlSource sqlSource = sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings());
BoundSql boundSql = sqlSource.getBoundSql(parameterObject);
for (Map.Entry<String, Object> entry : context.getBindings().entrySet()) {
boundSql.setAdditionalParameter(entry.getKey(), entry.getValue());
}
return boundSql;
}
从开始到结束, Mybatis 都没有对填充的条数和参数的数量做限制。
结语
SqlServer 对语句的条数和参数的数量都有限制,分别是 1000 和 2100。
Mysql 对语句的长度有限制,默认是 4M。
Mybatis 对动态语句没有数量上的限制。