One of the principles of improving SQL performance is to reduce the number of database accesses, and being able to merge SQL delivered is a good way to do that. Insert into t values(); insert into t values(); Insert into t values(),(); . In JDBC, the general flow of this transformation looks like this:

PreparedStatement preparedStatement = conn.prepareStatement("insert into t (c1,c2) values(? ,?) ");
Copy the code

Determine whether the executed SQL can be batch rewritten. Insert into T (id) values(1),(2). In fact, there are some cases where rewriteBatched fails when it should be expected by itself.

Take a look at the JDBC source code: Parseinfo.java, where SQL is parsed and conditional is determined. JDBC is a rough way to parse SQL. I check whether it is an INSERT statement, L check whether it is a load data statement, on, duplicate, key, update check whether it is a ON duplicate key update statement, pass? Get the position of the PrepareStatment placeholder by; Determine how many SQL and so on the request contains.

Placeholders, for example, are recorded here, all right? For example, insert into t (c1) values (?). Parses arrays of size 3:

  1. INSERT INTO t (c1,c2) VALUES (
  2. .
  3. )
for(i = this.statementStartPos; i < this.statementLength; ++i){
    char c = sql.charAt(i);
    if ((c == '? ')) {
        endpointList.add(new int[] { lastParmEnd, i });
        lastParmEnd = i + 1; }}Copy the code

Then determine whether SQL rewrite can be carried out, there are three judgment conditions, the most basic two conditions:

  1. this.numberOfQueries == 1, must be oneSQLThat is, it cannot besql; sqlIn the form.
  2. ! this.parametersInDuplicateKeyClause“This refers toon duplicate key update No placeholders after it. So for example,insert into t (c1) values (?) on duplicate key update c1 = ?I can’t rewrite it,insert into t (c1) values (?) on duplicate key update c1 = 123This one will do. Feel hereJDBCThe implementation is lazy)

The canRewrite() function must be true.

  1. If it isinsertStatement, ifSQLinselect, cannot be overwritten.
  2. on duplicate key updateStatement not availableLAST_INSERT_ID.
if (StringUtils.startsWithIgnoreCaseAndWs(sql, "INSERT", statementStartPos)) {
    if (StringUtils.indexOfIgnoreCase(statementStartPos, sql, "SELECT"."\ '`."\ '`, StringUtils.SEARCH_MODE__MRK_COM_WS) ! = -1) {
        return false;
    }
    if (isOnDuplicateKeyUpdate) {
        int updateClausePos = StringUtils.indexOfIgnoreCase(locationOfOnDuplicateKeyUpdate, sql, " UPDATE ");
        if(updateClausePos ! = -1) {
            return StringUtils.indexOfIgnoreCase(updateClausePos, sql, "LAST_INSERT_ID"."\ '`."\ '`, StringUtils.SEARCH_MODE__MRK_COM_WS) == -1; }}return true;
}
Copy the code

So there is a special case where rewriteBatchedStatements are invalid if the SQL contains select. For example, in this example, the table structure is as follows:

mysql> desc t1;
+------------+----------+------+------+---------+-------+
| Field      | Type     | Null | Key  | Default | Extra |
+------------+----------+------+------+---------+-------+
| id         | int(11)  | YES  |      | NULL    |       |
| selectNum  | int(11)  | YES  |      | NULL    |       |
| updateTime | datetime | YES  |      | NULL    |       |
+------------+----------+------+------+---------+-------+
3 rows in set (0.01 sec)
Copy the code

Use the above method to batch insert, Insert into t (id, selectNum, updateTime) values(1,11,’2021-01-01 11:11:11′),(1,11,’2021-01-01 11:11:11′); Insert two pieces of data. Let’s debug and see what we got in the database

Visible, the database received a single SQL, not integrated into a statement. So for batch inserts, try not to have select characters in the table structure.

The normal situation, should be able to start building value structure, batch is in buildRewriteBatchedParams.

if (this.canRewriteAsMultiValueInsert && session.getPropertySet().getBooleanProperty(PropertyKey.rewriteBatchedStatements).getValue()) {
    buildRewriteBatchedParams(sql, session, encoding);
}
Copy the code

Insert into t (c1,c2) values (? ,?) Is (? ,?) . So we have parseInfo for SQL and value.

excuteBatch

We directly to see if meets canRewriteAsMultiValueInsertAtSqlLevel is how to deal with, in executeBatchedInserts. Insert Batch = batchedArgs; insert Batch() = batchedArgs; insert Batch() = batchedArgs;

int numBatchedArgs = this.query.getBatchedArgs().size();
Copy the code

In prepareBatchedInsertSQL ((PreparedQuery
) this.query).getParseInfo().getParseInfoForBatch(numBatches) assembles a batch of values. JDBC implements a visitor that merges the composition of SQL and value:

  1. INSERT INTO t (c1,c2) VALUES (
  2. .
  3. ), (
  4. .
  5. )

Insert into T (c1,c2) values(,),(,), so I’m going to go to getSqlForBatch, and the sqlStrings that I’m going to use are the visitor array, Loop through the array, fill in the middle of each one, okay? Insert into t (c1,c2) values(? ,?) . (? ,?) .

final byte[][] sqlStrings = this.staticSql;
final int sqlStringsLength = sqlStrings.length;

for (int i = 0; i < sqlStringsLength; i++) {
    size += sqlStrings[i].length;
    size++; // for the '? '
}

StringBuilder buf = new StringBuilder(size);

for (int i = 0; i < sqlStringsLength - 1; i++) {
    buf.append(StringUtils.toString(sqlStrings[i], this.charEncoding));
    buf.append("?");
}

buf.append(StringUtils.toString(sqlStrings[sqlStringsLength - 1]));

return buf.toString();
Copy the code

That’s the end of the splicing process. If you set rewritebatchedchedstatements =true, you will not be able to concatenate them in any particular statement format, such as select. This may be in consideration of insert into… The select case, however, can also cause confusion for many people by simply banning it.