Go语言大容量插入到Oracle表中，数据库/SQL包不工作

gr8qqesn 于 2023-09-28 发布在 Go

关注(0)|答案(1)|浏览(112)

我的要求是通过database/sql pkg向oracle表中插入多行。下面是我的代码片段，我一直在尝试：

db, err := sql.Open("oracle", connectionUrl)
     if err != nil {
        e.logger.Error("Can't create connection: " + err.Error())
        return err
    }

    valueStrings := make([]string, 0, len(metrics))
    valueArgs := make([]interface{}, 0, len(metrics)*9)
    for _, post := range metrics {
        valueStrings = append(valueStrings, "(?, ?, ?, ?, ?, ?, ?, ?, ?)")
        valueArgs = append(valueArgs, post.Timestamp)
        valueArgs = append(valueArgs, post.MetricName)
        valueArgs = append(valueArgs, post.MetricType)
        valueArgs = append(valueArgs, post.MetricUnit)
        valueArgs = append(valueArgs, post.MetricDescription)
        valueArgs = append(valueArgs, post.MetricValue)
        valueArgs = append(valueArgs, post.Host)
        valueArgs = append(valueArgs, post.ResourceAttributes)
        valueArgs = append(valueArgs, post.MetricAttributes)

    }

    sql := fmt.Sprintf("INSERT INTO my_table(TS, NAME, TYPE, UNIT, DESCRIPTION, VALUE, HOST, RESOURCE_ATTRIBUTES, METRIC_ATTRIBUTES) VALUES %s",
        strings.Join(valueStrings, ","))
    logger.Info("#### Insert sql : " + sql)
    logger.Info("### ValueArgs :")
    fmt.Println(valueArgs)

    result, err := db.Exec(sql, valueArgs...)
    if err != nil {
    e.logger.Error("Bulk insert failed: " + err.Error())
    return err
    }

我可以看到数据库连接成功，SQL成功创建为：

INSERT INTO my_table(TS, NAME, TYPE, UNIT, DESCRIPTION, VALUE, HOST, RESOURCE_ATTRIBUTES, METRIC_ATTRIBUTES) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?)

但是当执行带有valueArgs的sql时，它返回错误。完全不知道问题出在哪里。错误日志为：大容量插入失败：EOF {“kind”：“exporter”，“data_type”：“metrics”，“name”：“oracle”}有什么建议可以修改上面的代码并使其正常工作吗？

go

来源：https://stackoverflow.com/questions/77067425/bulk-insert-into-oracle-table-with-database-sql-package-not-working

1条答案

按热度按时间

41zrol4v1#

在Oracle版本23之前，INSERT INTO ... VALUES (...),(...),(...)不是有效语法。您可以使用INSERT INTO ... SELECT ...或INSERT ALL ...
举例来说：

valueStrings = append(valueStrings, "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ? FROM DUAL")

和

sql := fmt.Sprintf("INSERT INTO my_table(TS, NAME, TYPE, UNIT, DESCRIPTION, VALUE, HOST, RESOURCE_ATTRIBUTES, METRIC_ATTRIBUTES) %s",
        strings.Join(valueStrings, " UNION ALL "))

看看这个数组插入示例，看起来你可以使用如下内容：

注意：我不使用GO，所以我试图适应您的代码，并为您提供一个总体思路;语法可能不正确，您需要更正它。*

sql := "INSERT INTO my_table(TS, NAME, TYPE, UNIT, DESCRIPTION, VALUE, HOST, RESOURCE_ATTRIBUTES, METRIC_ATTRIBUTES) VALUES (:ts, :mn, :mt, :mu, :md, :mv, :h, :ra, :ma)"

tsArgs := make([]interface{}, 0, len(metrics))
mnArgs := make([]interface{}, 0, len(metrics))
mtArgs := make([]interface{}, 0, len(metrics))
muArgs := make([]interface{}, 0, len(metrics))
mdArgs := make([]interface{}, 0, len(metrics))
mvArgs := make([]interface{}, 0, len(metrics))
hArgs := make([]interface{}, 0, len(metrics))
raArgs := make([]interface{}, 0, len(metrics))
maArgs := make([]interface{}, 0, len(metrics))

for _, post := range metrics {
    tsArgs = append(tsArgs, post.Timestamp)
    mnArgs = append(mnArgs, post.MetricName)
    mtArgs = append(mtArgs, post.MetricType)
    muArgs = append(muArgs, post.MetricUnit)
    mdArgs = append(mdArgs, post.MetricDescription)
    mvArgs = append(mvArgs, post.MetricValue)
    hArgs = append(hArgs, post.Host)
    raArgs = append(raArgs, post.ResourceAttributes)
    maArgs = append(maArgs, post.MetricAttributes)
}

result, err := db.Exec(sql, tsArgs, mnArgs, mtArgs, muArgs, mdArgs, mvArgs, hArgs, raArgs, maArgs)
if err != nil {
    e.logger.Error("Bulk insert failed: " + err.Error())
    return err
}

赞(0）回复(0）举报 2023-09-28

我来回答

Go语言大容量插入到Oracle表中，数据库/SQL包不工作

1条答案

相关问题

热门标签

最新问答

Go语言 大容量插入到Oracle表中，数据库/SQL包不工作

1条答案

相关问题

热门标签

最新问答

Go语言大容量插入到Oracle表中，数据库/SQL包不工作