SQL Overview

TiDB implements a MySQL 8.0-compatible SQL dialect. Most MySQL clients, drivers, and tools work with TiDB without modification. Where behavior differs from MySQL, this page and the linked references call it out explicitly.

Statement categories

TiDB SQL statements fall into four categories:

Category	Description	Examples
DDL — Data Definition Language	Create and manage schema objects	`CREATE TABLE`, `ALTER TABLE`, `DROP INDEX`
DML — Data Manipulation Language	Read and write row data	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
DCL — Data Control Language	Manage privileges	`GRANT`, `REVOKE`, `CREATE USER`
TCL — Transaction Control Language	Control transaction boundaries	`BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`

Data types

Numeric types

Type	Storage	Notes
`TINYINT`	1 byte	Signed: -128 to 127
`SMALLINT`	2 bytes
`MEDIUMINT`	3 bytes
`INT` / `INTEGER`	4 bytes
`BIGINT`	8 bytes	Required for `AUTO_RANDOM` columns
`FLOAT`	4 bytes	Approximate
`DOUBLE`	8 bytes	Approximate
`DECIMAL(p, s)`	Variable	Exact; use for currency
`BIT(n)`	Variable	1–64 bits

String types

CHAR(n), VARCHAR(n), BINARY(n), VARBINARY(n), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, ENUM, SET

Date and time types

DATE, TIME, DATETIME, TIMESTAMP, YEAR

TIMESTAMP values are stored in UTC and converted to the session time zone on retrieval. DATETIME values are stored as-is with no time zone conversion.

JSON type

TiDB supports the JSON type with the same path expression syntax as MySQL 8.0, including functions like JSON_EXTRACT, JSON_SET, JSON_ARRAYAGG, and the -> / ->> operators.

Spatial types

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION

Spatial index support in TiDB is limited compared to MySQL. Verify your spatial query requirements before relying on spatial indexes in production.

Built-in functions

TiDB supports the majority of MySQL 8.0 built-in functions, organized into these groups:

Aggregate: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, BIT_AND, BIT_OR, STD, VARIANCE
Window: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
String: CONCAT, SUBSTRING, TRIM, REPLACE, UPPER, LOWER, REGEXP_REPLACE, FORMAT
Math: ROUND, CEIL, FLOOR, ABS, MOD, POWER, SQRT, LOG, RAND
Date/time: NOW, CURDATE, DATE_FORMAT, DATEDIFF, DATE_ADD, TIMESTAMPDIFF, UNIX_TIMESTAMP
JSON: JSON_OBJECT, JSON_ARRAY, JSON_EXTRACT, JSON_CONTAINS, JSON_TYPE
Information: DATABASE, USER, VERSION, TIDB_VERSION, LAST_INSERT_ID

System databases

Database	Purpose
`information_schema`	Read-only views of schema metadata (tables, columns, indexes, statistics)
`performance_schema`	Runtime performance instrumentation (statements, waits, memory)
`mysql`	Privilege tables, system variables, and user account data
`sys`	Helper views that simplify access to `performance_schema` data

TiDB-specific extensions

AUTO_RANDOM

AUTO_RANDOM is a TiDB alternative to AUTO_INCREMENT for BIGINT PRIMARY KEY columns. It randomizes the high bits of the generated ID to avoid write hotspots on a single TiKV region.

CREATE TABLE orders (
  id    BIGINT PRIMARY KEY AUTO_RANDOM,
  total DECIMAL(10, 2) NOT NULL
);

SHARD_ROW_ID_BITS

For tables without an explicit AUTO_RANDOM or AUTO_INCREMENT primary key, TiDB generates an implicit _tidb_rowid. SHARD_ROW_ID_BITS shards that ID space to spread writes across regions.

CREATE TABLE events (
  payload JSON
) SHARD_ROW_ID_BITS = 4;

TTL tables

TTL (Time To Live) tables automatically delete rows older than a configured interval, removing the need for manual purge jobs.

CREATE TABLE logs (
  id         BIGINT PRIMARY KEY AUTO_RANDOM,
  message    TEXT,
  created_at DATETIME NOT NULL DEFAULT NOW()
) TTL = `created_at` + INTERVAL 1 MONTH;

TTL deletion runs as a background job and is not transactional with application writes. Do not rely on TTL for compliance-grade data removal; use explicit DELETE statements for that use case.

Next steps

DDL

Create and manage tables, indexes, and partitions.

DML

Query and modify row data with SELECT, INSERT, UPDATE, and DELETE.

Transactions

Control transaction boundaries and isolation levels.

SQL Language

Advanced SQL

HTTP API

SQL Overview

Statement categories

Data types

Numeric types

String types

Date and time types

JSON type

Spatial types

Built-in functions

System databases

TiDB-specific extensions

AUTO_RANDOM

SHARD_ROW_ID_BITS

TTL tables

Next steps

DDL

DML

Transactions

​Statement categories

​Data types

​Numeric types

​String types

​Date and time types

​JSON type

​Spatial types

​Built-in functions

​System databases

​TiDB-specific extensions

​AUTO_RANDOM

​SHARD_ROW_ID_BITS

​TTL tables

​Next steps

DDL

DML

Transactions

Statement categories

Data types

Numeric types

String types

Date and time types

JSON type

Spatial types

Built-in functions

System databases

TiDB-specific extensions

AUTO_RANDOM

SHARD_ROW_ID_BITS

TTL tables

Next steps