CREATE TABLE

The CREATE TABLE statement is used to create a new table in TiDB. The syntax is the same as MySQL, with a few extra TiDB features.

Basic Syntax

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [constraint],
    column_name data_type [constraint],
    ...
    [table_constraints]
) [table_options];

Simple Table Example

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

AUTO_RANDOM (TiDB Feature)

In TiDB, AUTO_RANDOM is recommended over AUTO_INCREMENT for primary keys to reduce hotspots:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    user_id INT NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Clustered Index

TiDB supports clustered indexes to optimize read performance:

-- Default: clustered index on the PRIMARY KEY
CREATE TABLE sessions (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id INT NOT NULL,
    data JSON,
    expires_at TIMESTAMP
);

Table with Multiple Indexes

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(200) NOT NULL,
    category_id INT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    description TEXT,
    attributes JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    INDEX idx_created (created_at),
    FULLTEXT INDEX idx_search (name, description)
);

Note: FULLTEXT indexes are parsed but not active in TiDB Self-Managed. They are only available on TiDB Cloud Starter.

Table with CHECK Constraint

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_RANDOM,
    balance DECIMAL(12, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'IDR',
    CHECK (balance >= 0),
    CHECK (currency IN ('IDR', 'USD', 'EUR'))
);

Viewing Table Structure

-- Show the table definition
SHOW CREATE TABLE users;

-- Show the table's columns
DESCRIBE users;
-- or
SHOW COLUMNS FROM users;

-- Show all tables in the database
SHOW TABLES;

Modifying a Table

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Change the data type
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);

Dropping a Table

DROP TABLE IF EXISTS users;

In the next chapter, we will look at the data types supported by TiDB.