BATCH

The BATCH syntax splits a DML statement into multiple statements in TiDB for execution. This means that there are no guarantees of transactional atomicity and isolation. Therefore, it is a “non-transactional” statement.

Currently, INSERT, REPLACE, UPDATE, and DELETE are supported in BATCH.

Based on a column, the BATCH syntax divides a DML statement into multiple ranges of scope for execution. In each range, a single SQL statement is executed.

For details about the usage and restrictions, see Non-transactional DML statements.

When you use multi-table join in a BATCH statement, you need to specify the full path of the column to avoid ambiguity:

  1. BATCH ON test.t2.id LIMIT 1 INSERT INTO t SELECT t2.id, t2.v, t3.v FROM t2 JOIN t3 ON t2.k = t3.k;

The preceding statement specifies the column to be split as test.t2.id, which is unambiguous. If you use the id as follows, an error is reported:

  1. BATCH ON id LIMIT 1 INSERT INTO t SELECT t2.id, t2.v, t3.v FROM t2 JOIN t3 ON t2.k = t3.k;
  3. Non-transactional DML, shard column must be fully specified

Synopsis

NonTransactionalDMLStmt

BATCH - 图1

DryRunOptions

BATCH - 图2

ShardableStmt

BATCH - 图3

  1. NonTransactionalDMLStmt ::=
  2.     'BATCH' ( 'ON' ColumnName )? 'LIMIT' NUM DryRunOptions? ShardableStmt
  4. DryRunOptions ::=
  5.     'DRY' 'RUN' 'QUERY'?
  7. ShardableStmt ::=
  8.     DeleteFromStmt
  9. |   UpdateStmt
  10. |   InsertIntoStmt
  11. |   ReplaceIntoStmt

MySQL compatibility

The BATCH syntax is TiDB-specific and not compatible with MySQL.

See also