Documentation Index
Fetch the complete documentation index at: https://mintlify.com/pingcap/tidb/llms.txt
Use this file to discover all available pages before exploring further.
TiDB supports the MySQL 8.0 DDL syntax. DDL operations in TiDB are performed online — table reads and writes continue while schema changes execute in the background.
CREATE DATABASE
CREATE DATABASE IF NOT EXISTS shop
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
CREATE TABLE
CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_RANDOM,
sku VARCHAR(64) NOT NULL,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
stock INT NOT NULL DEFAULT 0,
metadata JSON,
created_at DATETIME NOT NULL DEFAULT NOW(),
updated_at DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
UNIQUE KEY uq_sku (sku),
KEY idx_price (price)
);
Column constraints
| Constraint | Behavior |
|---|
NOT NULL | Rejects NULL on insert/update |
DEFAULT value | Applies when the column is omitted from INSERT |
UNIQUE | Enforces uniqueness; allows one NULL per column |
PRIMARY KEY | NOT NULL + unique; one per table |
CHECK (expr) | Validates the expression on each write (parsed but not enforced in older TiDB versions — see note below) |
CHECK constraints are parsed for MySQL compatibility but were not enforced before TiDB v7.2. Starting with v7.2, enforcement is enabled by default. Verify your TiDB version before relying on CHECK for data integrity.
Indexes
-- Single-column index
CREATE INDEX idx_name ON products (name);
-- Composite index
CREATE INDEX idx_sku_price ON products (sku, price);
-- Prefix index (useful for large VARCHAR/TEXT columns)
CREATE INDEX idx_name_prefix ON products (name(32));
-- Unique index
CREATE UNIQUE INDEX uq_sku ON products (sku);
AUTO_INCREMENT and AUTO_RANDOM
AUTO_INCREMENT generates monotonically increasing IDs, which concentrates writes at the end of the key range and creates a hotspot.
AUTO_RANDOM randomizes the upper bits of a BIGINT PRIMARY KEY to distribute writes across regions:
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_RANDOM(5),
user_id BIGINT NOT NULL,
total DECIMAL(10, 2),
created_at DATETIME NOT NULL DEFAULT NOW()
);
The optional shard-bit argument (for example AUTO_RANDOM(5)) controls how many bits are used for sharding. The default is 5 bits, providing 32 shards. You cannot retrieve the next AUTO_RANDOM value with LAST_INSERT_ID(); use SELECT LAST_INSERT_ID() after the INSERT instead.
ALTER TABLE
-- Add a column
ALTER TABLE products ADD COLUMN weight DECIMAL(8, 3) AFTER price;
-- Drop a column
ALTER TABLE products DROP COLUMN weight;
-- Modify a column type
ALTER TABLE products MODIFY COLUMN name VARCHAR(512) NOT NULL;
-- Rename a column
ALTER TABLE products RENAME COLUMN sku TO product_sku;
-- Add an index
ALTER TABLE products ADD INDEX idx_stock (stock);
-- Drop an index
ALTER TABLE products DROP INDEX idx_stock;
-- Rename the table
ALTER TABLE products RENAME TO catalog_items;
Online DDL
TiDB executes DDL changes without acquiring a table-level lock. DML statements (SELECT, INSERT, UPDATE, DELETE) continue uninterrupted during the schema change. Long-running DDL (for example building a large index) can be monitored with:
Partitioning
TiDB supports RANGE, LIST, HASH, and KEY partitioning. Partition pruning is applied automatically at query time.
RANGE partitioning
CREATE TABLE sales (
id BIGINT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(12, 2),
PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
LIST partitioning
CREATE TABLE regions (
id BIGINT NOT NULL,
region TINYINT NOT NULL,
PRIMARY KEY (id, region)
)
PARTITION BY LIST (region) (
PARTITION p_apac VALUES IN (1, 2, 3),
PARTITION p_emea VALUES IN (4, 5, 6),
PARTITION p_amer VALUES IN (7, 8, 9)
);
HASH partitioning
CREATE TABLE sessions (
id BIGINT NOT NULL PRIMARY KEY AUTO_RANDOM,
user_id BIGINT NOT NULL,
data JSON
)
PARTITION BY HASH (user_id) PARTITIONS 8;
KEY partitioning
CREATE TABLE cache (
cache_key VARCHAR(255) NOT NULL,
value BLOB,
PRIMARY KEY (cache_key)
)
PARTITION BY KEY (cache_key) PARTITIONS 4;
Dropping a RANGE partition is an efficient way to purge old data — for example ALTER TABLE sales DROP PARTITION p2022 — because it removes the underlying storage without scanning rows.
TTL tables
TTL tables delete rows automatically based on a datetime column, without requiring scheduled jobs or manual DELETE statements.
CREATE TABLE audit_log (
id BIGINT PRIMARY KEY AUTO_RANDOM,
action VARCHAR(128) NOT NULL,
actor VARCHAR(64) NOT NULL,
created_at DATETIME NOT NULL DEFAULT NOW()
) TTL = `created_at` + INTERVAL 90 DAY
TTL_ENABLE = 'ON';
-- Modify TTL interval on an existing table
ALTER TABLE audit_log TTL = `created_at` + INTERVAL 180 DAY;
-- Disable TTL without dropping the definition
ALTER TABLE audit_log TTL_ENABLE = 'OFF';
TTL purges run asynchronously and are not part of any user transaction. If your compliance policy requires auditable deletion, use explicit DELETE statements inside a transaction instead of TTL.
DROP TABLE and TRUNCATE TABLE
-- Remove the table and all data permanently
DROP TABLE IF EXISTS archive_2021;
-- Delete all rows but keep the table structure
TRUNCATE TABLE staging_data;
TRUNCATE TABLE is faster than DELETE FROM table for large tables because it drops and recreates the underlying storage rather than generating individual row-delete records.