Important

You are viewing the documentation of TiDB v8.4 (DMR). PingCAP does not provide bug fixes for v8.4. Any bugs will be fixed in future releases. For general purposes, please use the latest LTS version of the TiDB database.

SQL Statement Overview

TiDB uses SQL statements that aim to follow ISO/IEC SQL standards, with extensions for MySQL and TiDB-specific statements where necessary.

Schema management / Data definition statements (DDL)

SQL Statement	Description
`ALTER DATABASE`	Modifies a database.
`ALTER SEQUENCE`	Modifies a sequence.
`ALTER TABLE ... ADD COLUMN`	Adds a column to an existing table.
`ALTER TABLE ... ADD INDEX`	Adds an index to an existing table.
`ALTER TABLE ... ALTER INDEX`	Changes an index definition.
`ALTER TABLE ... CHANGE COLUMN`	Changes a column definition.
`ALTER TABLE ... COMPACT`	Compacts a table.
`ALTER TABLE ... DROP COLUMN`	Drops a column from a table.
`ALTER TABLE ... MODIFY COLUMN`	Modifies a column definition.
`ALTER TABLE ... RENAME INDEX`	Renames an index.
`ALTER TABLE`	Changes a table definition.
`CREATE DATABASE`	Creates a new database.
`CREATE INDEX`	Creates a new index on a table.
`CREATE SEQUENCE`	Creates a new sequence object.
`CREATE TABLE LIKE`	Copies the definition of an existing table, without copying any data.
`CREATE TABLE`	Creates a new table.
`CREATE VIEW`	Creates a new view.
`DROP DATABASE`	Drops an existing database.
`DROP INDEX`	Drops an index from a table.
`DROP SEQUENCE`	Drops a sequence object.
`DROP TABLE`	Drops an existing table.
`DROP VIEW`	Drops an existing view.
`RENAME TABLE`	Renames a table.
`SHOW COLUMNS FROM`	Shows the columns from a table.
`SHOW CREATE DATABASE`	Shows the CREATE statement for a database.
`SHOW CREATE SEQUENCE`	Shows the CREATE statement for a sequence.
`SHOW CREATE TABLE`	Shows the CREATE statement for a table.
`SHOW DATABASES`	Shows a list of databases that the current user has privileges to.
`SHOW FIELDS FROM`	Shows columns of a table.
`SHOW INDEXES`	Shows indexes of a table.
`SHOW SCHEMAS`	An alias to `SHOW DATABASES`, which shows a list of databases that the current user has privileges to.
`SHOW TABLE NEXT_ROW_ID`	Shows the next row ID for a table.
`SHOW TABLE REGIONS`	Shows the Region information of a table in TiDB.
`SHOW TABLE STATUS`	Shows various statistics about tables in TiDB.
`SHOW TABLES`	Shows tables in a database.
`TRUNCATE`	Truncates all data from a table.

Data manipulation statements (DML)

SQL Statement	Description
`BATCH`	Splits a DML statement into multiple statements in TiDB for execution.
`DELETE`	Deletes rows from a table.
`INSERT`	Inserts new rows into a table.
`REPLACE`	Replaces existing rows or inserts new rows.
`SELECT`	Reads data from a table.
`TABLE`	Retrieves rows from a table.
`UPDATE`	Updates existing rows in a table.
`WITH`	Defines common table expressions.

Transaction statements

SQL Statement	Description
`BEGIN`	Begins a new transaction.
`COMMIT`	Commits the current transaction.
`ROLLBACK`	Rolls back the current transaction.
`SAVEPOINT`	Sets a savepoint within a transaction.
`SET TRANSACTION`	Changes the current isolation level on a `GLOBAL` or `SESSION` basis.
`START TRANSACTION`	Starts a new transaction.

Prepared statements

SQL Statement	Description
`DEALLOCATE`	Deallocates a prepared statement, freeing associated resources.
`EXECUTE`	Executes a prepared statement with specific parameter values.
`PREPARE`	Creates a prepared statement with placeholders.

Administrative statements

SQL Statement	Description
`ADMIN CANCEL DDL`	Cancels a DDL job.
`ADMIN CHECK [TABLE\\|INDEX]`	Checks the integrity of a table or index.
`ADMIN CHECKSUM TABLE`	Computes the checksum of a table.
`ADMIN CLEANUP INDEX`	Cleans up indexes from a table.
`ADMIN PAUSE DDL`	Pauses DDL operations.
`ADMIN RESUME DDL`	Resumes DDL operations.
`ADMIN SHOW DDL [JOBS\\|JOB QUERIES]`	Shows DDL jobs or job queries.
`ADMIN`	Performs various administrative tasks.
`FLUSH TABLES`	Included for MySQL compatibility. It has no effective usage in TiDB.
`SET <variable>`	Modifies a system variable or user variable.
`SET [NAMES\\|CHARACTER SET]`	Set a character set and collation.
`SPLIT REGION`	Splits a Region into smaller Regions.

Data import and export

SQL Statement	Description
`CANCEL IMPORT JOB`	Cancels an ongoing import job.
`IMPORT INTO`	Imports data into a table via the Physical Import Mode of TiDB Lightning.
`LOAD DATA`	Loads data into a table from Amazon S3 or Google Cloud Storage.
`SHOW IMPORT JOB`	Shows the status of an import job.

Backup & restore

SQL Statement	Description
`BACKUP`	Performs a distributed backup of the TiDB cluster.
`FLASHBACK CLUSTER`	Restores the cluster to a specific snapshot.
`FLASHBACK DATABASE`	Restores a database and its data deleted by the `DROP` statement.
`FLASHBACK TABLE`	Restore the tables and data dropped by the `DROP` or `TRUNCATE` operation.
`RECOVER TABLE`	Recovers a deleted table and the data on it.
`RESTORE`	Restores a database from a backup.
`SHOW BACKUPS`	Shows backup tasks.
`SHOW RESTORES`	Shows restore tasks.

Placement policy

SQL Statement	Description
`ALTER PLACEMENT POLICY`	Modifies a placement policy.
`ALTER RANGE`	Modifies the range of a placement policy.
`CREATE PLACEMENT POLICY`	Creates a new placement policy.
`DROP PLACEMENT POLICY`	Drops an existing placement policy.
`SHOW CREATE PLACEMENT POLICY`	Shows the `CREATE` statement for a placement policy.
`SHOW PLACEMENT FOR`	Shows placement rules for a specific table.
`SHOW PLACEMENT LABELS`	Shows available placement labels.
`SHOW PLACEMENT`	Shows placement rules.

Resource groups

SQL Statement	Description
`ALTER RESOURCE GROUP`	Modifies a resource group.
`CALIBRATE RESOURCE`	Estimates and outputs the Request Unit (RU) capacity of the current cluster.
`CREATE RESOURCE GROUP`	Creates a new resource group.
`DROP RESOURCE GROUP`	Drops a resource group.
`QUERY WATCH`	Manages the runaway query watch list.
`SET RESOURCE GROUP`	Sets a resource group.
`SHOW CREATE RESOURCE GROUP`	Shows the `CREATE` statement for a resource group.

Utility statements

SQL Statement	Description
`DESC`	An alias to `DESCRIBE`, which shows the structure of a table.
`DESCRIBE`	Shows the structure of a table.
`DO`	Executes an expression but does not return any results.
`EXPLAIN`	Shows the execution plan of a query.
`TRACE`	Provides detailed information about query execution.
`USE`	Sets the current database.

Show statements

SQL Statement	Description
`SHOW BUILTINS`	Lists builtin functions.
`SHOW CHARACTER SET`	Lists character sets.
`SHOW COLLATIONS`	Lists collations.
`SHOW ERRORS`	Shows errors from previously executed statements.
`SHOW STATUS`	Included for compatibility with MySQL. TiDB uses Prometheus and Grafana for centralized metrics collection instead of `SHOW STATUS` for most metrics.
`SHOW VARIABLES`	Shows system variables.
`SHOW WARNINGS`	Shows warnings and notes from previously executed statements.

Instance management

SQL Statement	Description
`ALTER INSTANCE`	Modifies an instance.
`FLUSH STATUS`	Included for compatibility with MySQL. TiDB uses Prometheus and Grafana for centralized metrics collection instead of `SHOW STATUS` for most metrics.
`KILL`	Kills a connection in any TiDB instance in the current TiDB cluster.
`SHOW CONFIG`	Shows the configuration of various components of TiDB.
`SHOW ENGINES`	Shows available storage engines.
`SHOW PLUGINS`	Shows installed plugins.
`SHOW PROCESSLIST`	Shows the current sessions connected to the same TiDB server.
`SHOW PROFILES`	Included for compatibility with MySQL. Currently, it only returns an empty result.
`SHUTDOWN`	Stops the client-connected TiDB instance, not the entire TiDB cluster.

Locking statements

SQL Statement	Description
`LOCK STATS`	Locks statistics of tables or partitions.
`LOCK TABLES`	Locks tables for the current session.
`UNLOCK STATS`	Unlocks statistics of tables or partitions.
`UNLOCK TABLES`	Unlocks tables.

Account management / Data Control Language

SQL Statement	Description
`ALTER USER`	Modifies a user.
`CREATE ROLE`	Creates a role.
`CREATE USER`	Creates a new user.
`DROP ROLE`	Drops an existing role.
`DROP USER`	Drops an existing user.
`FLUSH PRIVILEGES`	Reloads the in-memory copy of privileges from the privilege tables.
`GRANT <privileges>`	Grants privileges.
`GRANT <role>`	Grants a role.
`RENAME USER`	Renames an existing user.
`REVOKE <privileges>`	Revokes privileges.
`REVOKE <role>`	Revokes a role.
`SET DEFAULT ROLE`	Sets a default role.
`SET PASSWORD`	Changes a password.
`SET ROLE`	Enables roles in the current session.
`SHOW CREATE USER`	Shows the `CREATE` statement for a user.
`SHOW GRANTS`	Shows privileges associated with a user.
`SHOW PRIVILEGES`	Shows available privileges.

TiCDC

SQL Statement	Description
`ADMIN [SET\\|SHOW\\|UNSET] BDR ROLE`	Manages BDR roles.
`SHOW MASTER STATUS`	Shows the latest TSO in the cluster.

Statistics and plan management

SQL Statement	Description
`ANALYZE TABLE`	Collects statistics about a table.
`CREATE BINDING`	Creates an execution plan binding for a SQL statement.
`DROP BINDING`	Drops an execution plan binding from a SQL statement.
`DROP STATS`	Drops statistics from a table.
`EXPLAIN ANALYZE`	Works similar to `EXPLAIN`, with the major difference that it will execute the statement.
`LOAD STATS`	Loads statistics into TiDB.
`SHOW ANALYZE STATUS`	Shows statistics collection tasks.
`SHOW BINDINGS`	Shows created SQL bindings.
`SHOW STATS_HEALTHY`	Shows an estimation of how accurate statistics are believed to be.
`SHOW STATS_HISTOGRAMS`	Shows the histogram information in statistics.
`SHOW STATS_LOCKED`	Shows the tables whose statistics are locked.
`SHOW STATS_META`	Shows how many rows are in a table and how many rows are changed in that table.