sql-statement-overview.md

title	summary
SQL Statement Overview	Learn about supported SQL statements in TiDB.

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 SHOW TELEMETRY	Shows telemetry data.
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.

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 RECOVER INDEX	Recovers the consistency based on the redundant indexes.
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.

SQL Statement	Description
ALTER RESOURCE GROUP	Modifies a resource group.
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.

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 Cloud provides Monitoring 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.

SQL Statement	Description
ALTER INSTANCE	Modifies an instance.
FLUSH STATUS	Included for compatibility with MySQL. TiDB Cloud provides Monitoring 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 ENGINES	Shows available storage engines.
SHOW PLUGINS	Shows installed plugins.
SHOW PROCESSLIST	Shows the current sessions connected to the same TiDB server.
SHOW PROFILES	Shows query profiles. Included for compatibility with MySQL. Currently only returns an empty result.

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 & TiDB Binlog

SQL Statement	Description
ADMIN [SET|SHOW|UNSET] BDR ROLE	Manages BDR roles.
CHANGE DRAINER	Modifies the status information for Drainer in the cluster.
CHANGE PUMP	Modifies the status information for Pump in the cluster.
SHOW DRAINER STATUS	Shows the status for all Drainer nodes in the cluster.
SHOW MASTER STATUS	Shows the latest TSO in the cluster.
SHOW PUMP STATUS	Shows the status information for all Pump nodes in the cluster.

Note:

TiCDC & TiDB Binlog are tools for replicating TiDB data to the upstream for TiDB Self-Hosted. Most SQL statements for TiCDC and TiDB Binlog are not applicable to TiDB Cloud. For TiDB Cloud, you can use the Changefeed feature in the TiDB Cloud console instead to stream data.

SQL Statement	Description
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.