Function to achieve batch insert with mysql go library since go sql is lacking the functionality

[matejsp]

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

[methane]

Thank you for reporting. I understand there is a need for this feature.
But I am focusing compression support for now. So please don't write PR until compression is merged.

[DejeroDaniel]

Just use sqlx like most do. I don't think the driver maintainers need to support this feature.

https://github.com/jmoiron/sqlx/blob/master/README.md