Как мне выполнить пакетные SQL-запросы с помощью пакета Godang database.sql?
В Java я бы сделал это следующим образом:
// Create a prepared statement
String sql = "INSERT INTO my_table VALUES(?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
// Insert 10 rows of data
for (int i=0; i<10; i++) {
pstmt.setString(1, ""+i);
pstmt.addBatch();
}
// Execute the batch
int [] updateCounts = pstmt.executeBatch();
Как бы добиться того же в Голанге?
Я не знаю точно, что пакетная обработка в Java выполняется на уровне SQL, но вы можете использовать транзакции для одновременного выполнения нескольких операций. Просто убедитесь, что ваш механизм DB поддерживает его.
func (db *DB) Begin() (*Tx, error)
Начало начинается с транзакции. Уровень изоляции зависит от драйвера.
func (tx *Tx) Prepare(query string) (*Stmt, error)
Подготовить создает подготовленный оператор для использования в транзакции.
Commit совершает транзакцию.
Так как функция db.Exec variadic, один из вариантов (который на самом деле делает только один кругооборот в одной сети) заключается в том, чтобы самим построить инструкцию и взорвать аргументы и передать их.
Пример кода:
func BulkInsert(unsavedRows []*ExampleRowStruct) error {
valueStrings := make([]string, 0, len(unsavedRows))
valueArgs := make([]interface{}, 0, len(unsavedRows) * 3)
for _, post := range unsavedRows {
valueStrings = append(valueStrings, "(?, ?, ?)")
valueArgs = append(valueArgs, post.Column1)
valueArgs = append(valueArgs, post.Column2)
valueArgs = append(valueArgs, post.Column3)
}
stmt := fmt.Sprintf("INSERT INTO my_sample_table (column1, column2, column3) VALUES %s", strings.Join(valueStrings, ","))
_, err := db.Exec(stmt, valueArgs...)
return err
}
В простом тесте я побежал, это решение примерно в 4 раза быстрее при вставке 10 000 строк, чем в Begin, Prepare, Commit, представленном в другом ответе, - хотя фактическое улучшение будет зависеть от вашей индивидуальной настройки, сетевых задержек, и др.
Если вы используете PostgreSQL, тогда pq поддерживает массовый импорт.
Развернув ответ Avi Flax, мне понадобилось предложение ON CONFLICT DO UPDATE в моем INSERT.
Решением этого является копирование во временную таблицу (для удаления в конце транзакции), затем INSERT из временной таблицы в постоянную таблицу.
Вот код, на котором я остановился:
func (fdata *FDataStore) saveToDBBulk(items map[fdataKey][]byte) (err error) {
tx, err := fdata.db.Begin()
if err != nil {
return errors.Wrap(err, "begin transaction")
}
txOK := false
defer func() {
if !txOK {
tx.Rollback()
}
}()
// The ON COMMIT DROP clause at the end makes sure that the table
// is cleaned up at the end of the transaction.
// While the "for{..} state machine" goroutine in charge of delayed
// saving ensures this function is not running twice at any given time.
_, err = tx.Exec(sqlFDataMakeTempTable)
// CREATE TEMPORARY TABLE fstore_data_load
// (map text NOT NULL, key text NOT NULL, data json)
// ON COMMIT DROP
if err != nil {
return errors.Wrap(err, "create temporary table")
}
stmt, err := tx.Prepare(pq.CopyIn(_sqlFDataTempTableName, "map", "key", "data"))
for key, val := range items {
_, err = stmt.Exec(string(key.Map), string(key.Key), string(val))
if err != nil {
return errors.Wrap(err, "loading COPY data")
}
}
_, err = stmt.Exec()
if err != nil {
return errors.Wrap(err, "flush COPY data")
}
err = stmt.Close()
if err != nil {
return errors.Wrap(err, "close COPY stmt")
}
_, err = tx.Exec(sqlFDataSetFromTemp)
// INSERT INTO fstore_data (map, key, data)
// SELECT map, key, data FROM fstore_data_load
// ON CONFLICT DO UPDATE SET data = EXCLUDED.data
if err != nil {
return errors.Wrap(err, "move from temporary to real table")
}
err = tx.Commit()
if err != nil {
return errors.Wrap(err, "commit transaction")
}
txOK = true
return nil
}
Адаптация Andrew solution для PostgreSQL, которая не поддерживает заполнитель ?, работает следующее:
func BulkInsert(unsavedRows []*ExampleRowStruct) error {
valueStrings := make([]string, 0, len(unsavedRows))
valueArgs := make([]interface{}, 0, len(unsavedRows) * 3)
i := 0
for _, post := range unsavedRows {
valueStrings = append(valueStrings, fmt.Sprintf("($%d, $%d, $%d)", i*3+1, i*3+2, i*3+3))
valueArgs = append(valueArgs, post.Column1)
valueArgs = append(valueArgs, post.Column2)
valueArgs = append(valueArgs, post.Column3)
i++
}
stmt := fmt.Sprintf("INSERT INTO my_sample_table (column1, column2, column3) VALUES %s", strings.Join(valueStrings, ","))
_, err := db.Exec(stmt, valueArgs...)
return err
}
Вот пример решения @Debasish Mitra, если вы используете Postgres.
Пример функционирования: https://play.golang.org/p/dFFD2MrEy3J
Альтернативный пример: https://play.golang.org/p/vUtW0K4jVMd
data := []Person{{"John", "Doe", 27}, {"Leeroy", "Jenkins", 19}}
vals := []interface{}{}
for _, row := range data {
vals = append(vals, row.FirstName, row.LastName, row.Age)
}
sqlStr := 'INSERT INTO test(column1, column2, column3) VALUES %s'
sqlStr = ReplaceSQL(sqlStr, "(?, ?, ?)", len(data))
//Prepare and execute the statement
stmt, _ := db.Prepare(sqlStr)
res, _ := stmt.Exec(vals...)
func ReplaceSQL
func ReplaceSQL(stmt, pattern string, len int) string {
pattern += ","
stmt = fmt.Sprintf(stmt, strings.Repeat(pattern, len))
n := 0
for strings.IndexByte(stmt, '?') != -1 {
n++
param := "$" + strconv.Itoa(n)
stmt = strings.Replace(stmt, "?", param, 1)
}
return strings.TrimSuffix(stmt, ",")
}
Пакетирование невозможно через интерфейсы, доступные в базе данных /sql. Однако конкретный драйвер базы данных может поддерживать его отдельно. Например, https://github.com/ziutek/mymysql, похоже, поддерживает доработку с MySQL.
Для Postgres lib pq поддерживает массовые вставки: https://godoc.org/github.com/lib/pq#hdr-Bulk_imports
Но того же можно достичь с помощью приведенного ниже кода, но он действительно полезен, когда кто-то пытается выполнить массовое условное обновление (измените запрос соответствующим образом).
Для выполнения подобных массовых вставок для Postgres вы можете использовать следующую функцию.
// ReplaceSQL replaces the instance occurrence of any string pattern with an increasing $n based sequence
func ReplaceSQL(old, searchPattern string) string {
tmpCount := strings.Count(old, searchPattern)
for m := 1; m <= tmpCount; m++ {
old = strings.Replace(old, searchPattern, "$"+strconv.Itoa(m), 1)
}
return old
}
Так что выше образец становится
sqlStr := "INSERT INTO test(n1, n2, n3) VALUES "
vals := []interface{}{}
for _, row := range data {
sqlStr += "(?, ?, ?)," // Put "?" symbol equal to number of columns
vals = append(vals, row["v1"], row["v2"], row["v3"]) // Put row["v{n}"] blocks equal to number of columns
}
//trim the last ,
sqlStr = strings.TrimSuffix(sqlStr, ",")
//Replacing ? with $n for postgres
sqlStr = ReplaceSQL(sqlStr, "?")
//prepare the statement
stmt, _ := db.Prepare(sqlStr)
//format all vals at once
res, _ := stmt.Exec(vals...)
Возьмите идею Эндрю Си и адаптируйте ее для моей работы, используя скалярные переменные sql. Это прекрасно работает для этого конкретного требования в моей работе. Может быть, это кому-то пригодится, потому что полезно имитировать пакетные транзакции sql в golang. Это идея.
func BulkInsert(unsavedRows []*ExampleRowStruct) error {
valueStrings := make([]string, 0, len(unsavedRows))
valueArgs := make([]interface{}, 0, len(unsavedRows) * 3)
i := 0
for _, post := range unsavedRows {
valueStrings = append(valueStrings, fmt.Sprintf("(@p%d, @p%d, @p%d)", i*3+1, i*3+2, i*3+3))
valueArgs = append(valueArgs, post.Column1)
valueArgs = append(valueArgs, post.Column2)
valueArgs = append(valueArgs, post.Column3)
i++
}
sqlQuery := fmt.Sprintf("INSERT INTO my_sample_table (column1, column2, column3) VALUES %s", strings.Join(valueStrings, ","))
var params []interface{}
for i := 0; i < len(valueArgs); i++ {
var param sql.NamedArg
param.Name = fmt.Sprintf("p%v", i+1)
param.Value = valueArgs[i]
params = append(params, param)
}
_, err := db.Exec(sqlQuery, params...)
return err
}
Еще одна хорошая библиотека с цепочечным синтаксисом - go-pg
https://github.com/go-pg/pg/wiki/Writing-Queries#insert
Вставьте несколько книг одним запросом:
err := db.Model(book1, book2).Insert()