Table
This page provides a comprehensive overview of table operations in TiDB Cloud Lake, organized by functionality for easy reference.
Table Creation
| Command | Description |
|---|---|
| CREATE TABLE | Creates a new table with specified columns and options |
| CREATE TABLE ... LIKE | Creates a table with the same column definitions as an existing one |
| CREATE TABLE ... AS | Creates a table and inserts data based on the results of a SELECT query |
| CREATE TRANSIENT TABLE | Creates a table without Time Travel support |
| CREATE EXTERNAL TABLE | Creates a table with data stored in a specified external location |
| ATTACH TABLE | Creates a table by associating it with an existing table |
Table Modification
| Command | Description |
|---|---|
| ALTER TABLE | Modifies table columns, comments, Fuse options, external connections, or swaps metadata with another table |
| RENAME TABLE | Changes the name of a table |
Table Information
| Command | Description |
|---|---|
| DESCRIBE TABLE / SHOW FIELDS | Shows information about the columns in a given table |
| SHOW FULL COLUMNS | Retrieves comprehensive details about the columns in a given table |
| SHOW CREATE TABLE | Shows the CREATE TABLE statement that creates the named table |
| SHOW TABLES | Lists the tables in the current or a specified database |
| SHOW TABLE STATUS | Shows the status of the tables in a database |
| SHOW DROP TABLES | Lists the dropped tables in the current or a specified database |
Table Deletion & Recovery
| Command | Description | Recovery Option |
|---|---|---|
| TRUNCATE TABLE | Removes all data from a table while preserving the table's schema | FLASHBACK TABLE |
| DROP TABLE | Deletes a table | UNDROP TABLE |
| VACUUM TABLE | Permanently removes historical data files of a table (Enterprise Edition) | Not recoverable |
| VACUUM DROP TABLE | Permanently removes data files of dropped tables (Enterprise Edition) | Not recoverable |
Table Optimization
| Command | Description |
|---|---|
| OPTIMIZE TABLE | Compacts or purges historical data to save storage space and enhance query performance |
| SET CLUSTER KEY | Configures a cluster key to enhance query performance for large tables |
Note
Table optimization is an advanced operation. Please carefully read the documentation before proceeding to avoid potential data loss.