Skip to main content

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 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:
CategoryDescriptionExamples
DDL — Data Definition LanguageCreate and manage schema objectsCREATE TABLE, ALTER TABLE, DROP INDEX
DML — Data Manipulation LanguageRead and write row dataSELECT, INSERT, UPDATE, DELETE
DCL — Data Control LanguageManage privilegesGRANT, REVOKE, CREATE USER
TCL — Transaction Control LanguageControl transaction boundariesBEGIN, COMMIT, ROLLBACK, SAVEPOINT

​
Data types

​
Numeric types

TypeStorageNotes
TINYINT1 byteSigned: -128 to 127
SMALLINT2 bytes
MEDIUMINT3 bytes
INT / INTEGER4 bytes
BIGINT8 bytesRequired for AUTO_RANDOM columns
FLOAT4 bytesApproximate
DOUBLE8 bytesApproximate
DECIMAL(p, s)VariableExact; use for currency
BIT(n)Variable1–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

DatabasePurpose
information_schemaRead-only views of schema metadata (tables, columns, indexes, statistics)
performance_schemaRuntime performance instrumentation (statements, waits, memory)
mysqlPrivilege tables, system variables, and user account data
sysHelper 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.

Build docs developers (and LLMs) love

Get started for freeTalk to us