MySQL insert same values n times

Question

I need to insert same value in MySQL database table. The only different value is an autogenerated value so I need to execute N times this query:

INSERT INTO unità (idProdotto, idFornitore, `idFattura-documentoIngresso`, Stato, NumPassaporto) VALUES (?,?,?,?,?)

Using a while cycle in my Java PreparedStatement adding a big number of elements the process results to be slow.

Is there an alternative solution similar to "LIMIT N" in "UPDATE"?

public void addUnit(Unit unit, int number) throws SQLException {
    String query = "INSERT INTO unità (idProdotto, idFornitore, `idFattura-documentoIngresso`, Stato, NumPassaporto) VALUES (?,?,?,?,?)";

    connection.setAutoCommit(false);
    try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
        preparedStatement.setInt(1, unit.getProduct().getId());
        preparedStatement.setInt(2, unit.getFornitore().getId());
        preparedStatement.setInt(3, unit.getInvoice().getIdInvoice());
        preparedStatement.setString(4, String.valueOf(unit.getStato()));
        preparedStatement.setString(5, unit.getPassport());
        int i=0;
        while (i < number) {
            ++i;
            preparedStatement.executeUpdate();
        }
        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        e.printStackTrace();
    }
}

Answer 1

Insert supports syntax for inserting multiple rows:

INSERT INTO unità (...) 
VALUES (?,?,?,?,?),
       (?,?,?,?,?),
       (?,?,?,?,?),
       (?,?,?,?,?),
       ...

You can keep going until the SQL statement is as long as the value of max_allowed_packet (for which the default value is 64MB).

Another form of multi-row insert can be done like this:

INSERT INTO unità (a, b, c) SELECT 1, 2, 3 FROM <table>;

This would insert as many rows as the rows matched by the select. You can use constant expressions in the select-list, so you insert the same values on many rows (i.e. you don't need to reference columns of the <table>).

The fastest bulk-load is LOAD DATA LOCAL INFILE. You would have to prepare a CSV file with your data, and do some other configuration steps to let this work. See the documentation.

I did a presentation Load Data Fast! about benchmarking the different methods of bulk data loading for MySQL. That slide deck might give you some good comparisons.