DELETE-PolarDB(PolarDB)-阿里云帮助中心

The DELETE statement removes rows from one or more logical tables that match a WHERE condition and returns the number of deleted rows.

Syntax

Single logical table

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM [schema_name.]tbl_name
    [WHERE where_condition]

Warning

Omitting WHERE deletes all rows in the table.

Multiple logical tables

Form 1: target-FROM

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Form 2: FROM-USING

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM [schema_name.]tbl_name[.*] [, [schema_name.]tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Parameters

Parameter	Description
`LOW_PRIORITY`	Delays the DELETE until all pending read operations on the table complete.
`QUICK`	Related to the MySQL storage engine. See MySQL documentation.
`IGNORE`	Continues execution and ignores errors during deletion.
`schema_name`	The name of the schema (database) containing the target table. Optional.
`tbl_name`	The name of the logical table to delete rows from.
`table_references`	The table join expression used to identify rows to delete. Used with multi-table forms.
`where_condition`	An expression that evaluates to true for each row to delete. Without this clause, all rows are deleted.

Note

All modifiers are pushed down to the MySQL storage layer unchanged and do not affect PolarDB-X 1.0 processing.

Limitations

PolarDB-X 1.0 blocks DELETE statements that cannot be pushed down and would affect more than 10,000 rows. In this case, you must use hints so that the DELETE statement can be supported.

The following statements trigger this limit (shard key for t1, t2, and t3 is id):

-- Single-table delete with ORDER BY: cannot be pushed down
DELETE FROM t1 ORDER BY name LIMIT 10001;

-- Multi-table delete (target-FROM form): cannot be pushed down
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.name LIMIT 10001;

-- Multi-table delete (FROM-USING form): cannot be pushed down
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.name LIMIT 10001;

To run DELETE statements that exceed the 10,000-row limit, use a SQL hint to override the restriction.