Description
Issue description
I would like to achieve batch insert like in Java or Python.
I see that this is still not after 10 years supported in Go making it slow and insecure (contact of parameters into string making it sql injection prone).
Currently I see a lot of code that contacts strings or sqls with additional parameters.
Workarounds:
https://stackoverflow.com/questions/12486436/how-do-i-batch-sql-statements-with-package-database-sql
https://gist.github.com/michaelcale/c8bb0c8674f1b1cfbd6dc2029bfe18f4
How to use prepared statements to reduce roundtrips.
https://medium.com/@xuan11290/understand-mysql-prepared-statement-1eb1bda59f7b
There is one issue openend in Go but it lacks progress. It saddens me taht such important feature is waiting for more than 10 years.
golang/go#5171
However I saw one clever workaround for postgresql using pg.CopyIn that is 2.4 times faster than string values/args aproach that is for now only possible in mysql.
Example code
Example code is for java:
String[] EMPLOYEES = new String[]{"Zuck","Mike","Larry","Musk","Steve"};
String[] DESIGNATIONS = new String[]{"CFO","CSO","CTO","CEO","CMO"};
String insertEmployeeSQL = "INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) "
+ "VALUES (?,?,?)";
for(int i = 0; i < EMPLOYEES.length; i++){
String employeeId = UUID.randomUUID().toString();
employeeStmt.setString(1,employeeId);
employeeStmt.setString(2,EMPLOYEES[i]);
employeeStmt.setString(3,DESIGNATIONS[i]);
employeeStmt.addBatch();
}
employeeStmt.executeBatch();
x, err := db.Begin()
if err != nil {
log.Fatal(err)
}
defer tx.Rollback()
stmt, err := tx.Prepare("INSERT INTO foo VALUES (?)")
if err != nil {
log.Fatal(err)
}
for i := 0; i < 10; i++ {
_, err = stmt.Exec(i)
if err != nil {
log.Fatal(err)
}
}
stmt.Close()
err = tx.Commit()
if err != nil {
log.Fatal(err)
}
// stmt.Close() runs here!
I would like to ExecBatch with in one go with one roundtrip.
Error log
/
Configuration
Driver version (or git SHA): 1.6.0
Go version: 1.22.3
Server version: MySQL 8.0.29
Server OS: Amazon Linux 2023