mysql - 使用 Anorm 批量插入具有许多列的表

Question

我正在尝试使用 Anorm(在 play framework 2.3.1 中)对 MySQL 数据库表进行批量插入。我正在构建的应用程序除了需要批量数据插入外，还有一个标准的 Web 前端，我想尝试将逻辑保持在同一个软件堆栈上。

插入只会进入相同的几个表。

一次要插入的行数将达到数百，可能会达到数千，我预计由于 anorm/mysql/其他限制，我可能需要在某个时候限制插入的行数。

我使用的 MySQL 驱动是 mysql-connector-java - 5.1.31

下面是一个简化的用例。

使用表格:

CREATE TABLE table1
(
  col1    INTEGER   NOT NULL,
  col2    BIGINT,
  col3    VARCHAR(255)
); 

和 scala 代码:

import play.api.Play.current
import play.api.db.DB
import anorm._ 

object TestInserts {

  DB.withConnection("spo") { implicit conn => 

    val theInserts = Seq(
       Seq[NamedParameter]('val1 -> 1, 'val2 -> Some(1L), 'val3 -> Some("One"))
      ,Seq[NamedParameter]('val1 -> 2, 'val2 -> Some(2L), 'val3 -> Some("Two"))
      ,Seq[NamedParameter]('val1 -> 3, 'val2 -> Some(3L), 'val3 -> Some("Three"))
    )

    val insertBatchSQL = BatchSql( SQL("insert into table1 (col1, col2, col3) values ({val1}, {val2}, {val3})"), theInserts)  

    insertBatchSQL.execute

  } 

}

我收到以下错误

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3688)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3670)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3715)
at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3659)
at com.jolbox.bonecp.PreparedStatementHandle.setInt(PreparedStatementHandle.java:828)
at anorm.ToStatement$intToStatement$.set(ToStatement.scala:164)
at anorm.ToStatement$intToStatement$.set(ToStatement.scala:163)
...

我查看了 play 框架中测试批量插入的测试类 https://github.com/playframework/playframework/blob/master/framework/src/anorm/src/test/scala/anorm/BatchSqlSpec.scala据我所知，它的工作原理应该是一样的。

任何关于如何解决这个问题或者我是否应该以不同方式解决这个问题的建议都会很棒。

Answer 1

我会选择选项 B。我对 BatchSql 不是很熟悉，因为我上次检查它只是按顺序执行大量查询，这非常慢。我建议将所有内容聚合到一个查询中。使用一千个插入执行单个查询比执行一千个单个插入要快得多。

为方便起见，假设您有 Seq of

case class Test(val1: Int, val2: Option[Long], val3: Option[String])

然后您可以像这样构建查询:

val values: Seq[Test] = Seq(....)

/* Index your sequence for later, to map to inserts and parameters alike */
val indexedValues = values.zipWithIndex

/* Create the portion of the insert statement with placeholders, each with a unique index */
val rows = indexValues.map{ case (value, i) =>
    s"({val1_${i}}, {val2_${i}}, {val3_${i}})"
}.mkString(",")

/* Create the NamedParameters for each `value` in the sequence, each with their unique index in the token, and flatten them together */
val parameters = indexedValues.flatMap{ case(value, i) =>
    Seq(
        NamedParameter(s"val1_${i}" -> value.val1),
        NamedParameter(s"val2_${i}" -> value.val2),
        NamedParameter(s"val3_${i}" -> value.val3)
    ) 
}

/* Execute the insert statement, applying the aggregated parameters */
SQL("INSERT INTO table1 (col1, col2, col3) VALUES " + rows)
    .on(parameters: _ *)
    .executeInsert()

注意事项:

在继续之前，您必须检查 values 是否为空，否则会生成无效的 SQL 语句。

根据要插入的行数和列数，创建准备好的语句的标记解析器最终会因为需要解析的标记数量(以及字符串大小)而变慢。在有几列的几百行之后我注意到了这一点。这可以有所缓解。由于 Scala 是一种强类型语言，Int 和 Long 不会对 SQL 注入(inject)构成威胁。您可以仅对这些列使用字符串插值/连接来准备 SQL 语句，并通常将不安全的列与 NamedParameter 绑定(bind)。这将减少需要解析的 token 数量。