REPLACE
February 7, 2025 ยท View on GitHub
The REPLACE statement is semantically a combined DELETE+INSERT statement. It can be used to simplify application code.
Synopsis
ReplaceIntoStmt ::=
'REPLACE' PriorityOpt IntoOpt TableName PartitionNameListOpt InsertValues
PriorityOpt ::=
( 'LOW_PRIORITY' | 'HIGH_PRIORITY' | 'DELAYED' )?
IntoOpt ::= 'INTO'?
TableName ::=
Identifier ( '.' Identifier )?
PartitionNameListOpt ::=
( 'PARTITION' '(' Identifier ( ',' Identifier )* ')' )?
InsertValues ::=
'(' ( ColumnNameListOpt ')' ( ValueSym ValuesList | SelectStmt | '(' SelectStmt ')' | UnionStmt ) | SelectStmt ')' )
| ValueSym ValuesList
| SelectStmt
| UnionStmt
| 'SET' ColumnSetValue? ( ',' ColumnSetValue )*
Note:
Starting from v6.6.0, TiDB supports Resource Control. You can use this feature to execute SQL statements with different priorities in different resource groups. By configuring proper quotas and priorities for these resource groups, you can gain better scheduling control for SQL statements with different priorities. When resource control is enabled, statement priority (
PriorityOpt) will no longer take effect. It is recommended that you use Resource Control to manage resource usage for different SQL statements.
Examples
mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO t1 (c1) VALUES (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+----+----+
| id | c1 |
+----+----+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+----+
3 rows in set (0.00 sec)
mysql> REPLACE INTO t1 (id, c1) VALUES(3, 99);
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM t1;
+----+----+
| id | c1 |
+----+----+
| 1 | 1 |
| 2 | 2 |
| 3 | 99 |
+----+----+
3 rows in set (0.00 sec)
MySQL compatibility
The REPLACE statement in TiDB is fully compatible with MySQL. If you find any compatibility differences, report a bug.