MySQL Compatibility
TiDB is highly compatible with the MySQL protocol and the common features and syntax of MySQL 5.7 and MySQL 8.0. The ecosystem tools for MySQL (PHPMyAdmin, Navicat, MySQL Workbench, DBeaver and more) and the MySQL client can be used for TiDB.
However, some features of MySQL are not supported in TiDB. This could be because there is now a better way to solve the problem (such as the use of JSON instead of XML functions) or a lack of current demand versus effort required (such as stored procedures and functions). Additionally, some features might be difficult to implement in a distributed system.
It's important to note that TiDB does not support the MySQL replication protocol. Instead, specific tools are provided to replicate data with MySQL:
- Replicate data from MySQL: TiDB Data Migration (DM) is a tool that supports full data migration and incremental data replication from MySQL or MariaDB into TiDB.
- Replicate data to MySQL: TiCDC is a tool for replicating the incremental data of TiDB by pulling TiKV change logs. TiCDC uses the MySQL sink to replicate the incremental data of TiDB to MySQL.
You can try out TiDB features on TiDB Playground.
Unsupported features
- Stored procedures and functions
- Triggers
- Events
- User-defined functions
FULLTEXT
syntax and indexes #1793SPATIAL
(also known asGIS
/GEOMETRY
) functions, data types and indexes #6347- Character sets other than
ascii
,latin1
,binary
,utf8
,utf8mb4
, andgbk
. - SYS schema
- Optimizer trace
- XML Functions
- X-Protocol #1109
- Column-level privileges #9766
XA
syntax (TiDB uses a two-phase commit internally, but this is not exposed via an SQL interface)CREATE TABLE tblName AS SELECT stmt
syntax #4754CHECK TABLE
syntax #4673CHECKSUM TABLE
syntax #1895REPAIR TABLE
syntaxOPTIMIZE TABLE
syntaxHANDLER
statementCREATE TABLESPACE
statement- "Session Tracker: Add GTIDs context to the OK packet"
- Descending Index #2519
SKIP LOCKED
syntax #18207- Lateral derived tables #40328
Differences from MySQL
Auto-increment ID
In TiDB, the auto-incremental column values (IDs) are globally unique and incremental within a single TiDB server. To make the IDs incremental among multiple TiDB servers, you can use the
AUTO_INCREMENT
MySQL compatibility mode. However, the IDs are not necessarily allocated sequentially, so it is recommended that you avoid mixing default and custom values to prevent encountering theDuplicated Error
message.You can use the
tidb_allow_remove_auto_inc
system variable to allow or forbid removing theAUTO_INCREMENT
column attribute. To remove the column attribute, use theALTER TABLE MODIFY
orALTER TABLE CHANGE
syntax.TiDB does not support adding the
AUTO_INCREMENT
column attribute, and once removed, it cannot be recovered.For TiDB v6.6.0 and earlier versions, auto-increment columns in TiDB behave the same as in MySQL InnoDB, requiring them to be primary keys or index prefixes. Starting from v7.0.0, TiDB removes this restriction, allowing for more flexible table primary key definitions. #40580
For more details, see AUTO_INCREMENT
.
mysql> CREATE TABLE t(id INT UNIQUE KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t VALUES();
Query OK, 1 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES();
Query OK, 1 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES();
Query OK, 1 rows affected (0.00 sec)
mysql> SELECT _tidb_rowid, id FROM t;
+-------------+------+
| _tidb_rowid | id |
+-------------+------+
| 2 | 1 |
| 4 | 3 |
| 6 | 5 |
+-------------+------+
3 rows in set (0.01 sec)
As shown, because of the shared allocator, the id
increments by 2 each time. This behavior changes in MySQL compatibility mode, where there is no shared allocator and therefore no skipping of numbers.
Performance schema
TiDB utilizes a combination of Prometheus and Grafana for storing and querying performance monitoring metrics. In TiDB, performance schema tables do not return any results.
Query Execution Plan
The output format, content, and privilege settings of Query Execution Plan (EXPLAIN
/EXPLAIN FOR
) in TiDB differ significantly from those in MySQL.
In TiDB, the MySQL system variable optimizer_switch
is read-only and has no effect on query plans. Although optimizer hints can be used in similar syntax to MySQL, the available hints and their implementation might differ.
For more information, refer to Understand the Query Execution Plan.
Built-in functions
TiDB supports most of the built-in functions in MySQL, but not all. You can use the statement SHOW BUILTINS
to get a list of the available functions.
For more information, refer to the TiDB SQL Grammar.
DDL operations
In TiDB, all supported DDL changes can be performed online. However, there are some major restrictions on DDL operations in TiDB compared to MySQL:
When using a single
ALTER TABLE
statement to alter multiple schema objects (such as columns or indexes) of a table, specifying the same object in multiple changes is not supported. For example, if you execute theALTER TABLE t1 MODIFY COLUMN c1 INT, DROP COLUMN c1
command, theUnsupported operate same column/index
error is output.It is not supported to modify multiple TiDB-specific schema objects using a single
ALTER TABLE
statement, such asTIFLASH REPLICA
,SHARD_ROW_ID_BITS
, andAUTO_ID_CACHE
.TiDB does not support the changes of some data types using
ALTER TABLE
. For example, TiDB does not support the change from theDECIMAL
type to theDATE
type. If a data type change is unsupported, TiDB reports theUnsupported modify column: type %d not match origin %d
error. Refer toALTER TABLE
for more details.The
ALGORITHM={INSTANT,INPLACE,COPY}
syntax functions only as an assertion in TiDB, and does not modify theALTER
algorithm. SeeALTER TABLE
for further details.Adding/Dropping the primary key of the
CLUSTERED
type is unsupported. For more details about the primary key of theCLUSTERED
type, refer to clustered index.Different types of indexes (
HASH|BTREE|RTREE|FULLTEXT
) are not supported, and will be parsed and ignored when specified.TiDB supports
HASH
,RANGE
,LIST
, andKEY
partitioning types. Currently, theKEY
partition type does not support partition statements with an empty partition column list. For an unsupported partition type, TiDB returnsWarning: Unsupported partition type %s, treat as normal table
, where%s
is the specific unsupported partition type.Range, Range COLUMNS, List, and List COLUMNS partitioned tables support
ADD
,DROP
,TRUNCATE
, andREORGANIZE
operations. Other partition operations are ignored.Hash and Key partitioned tables support
ADD
,COALESCE
, andTRUNCATE
operations. Other partition operations are ignored.The following syntaxes are not supported for partitioned tables:
SUBPARTITION
{CHECK|OPTIMIZE|REPAIR|IMPORT|DISCARD|REBUILD} PARTITION
For more details on partitioning, see Partitioning.
Analyzing tables
In TiDB, Statistics Collection differs from MySQL in that it completely rebuilds the statistics for a table, making it a more resource-intensive operation that takes longer to complete. In contrast, MySQL/InnoDB performs a relatively lightweight and short-lived operation.
For more information, refer to ANALYZE TABLE
.
Limitations of SELECT
syntax
TiDB does not support the following SELECT
syntax:
SELECT ... INTO @variable
SELECT ... GROUP BY ... WITH ROLLUP
SELECT .. GROUP BY expr
does not implyGROUP BY expr ORDER BY expr
as it does in MySQL 5.7.
For more details, see the SELECT
statement reference.
UPDATE
statement
See the UPDATE
statement reference.
Views
Views in TiDB are not updatable and do not support write operations such as UPDATE
, INSERT
, and DELETE
.
Temporary tables
For more information, see Compatibility between TiDB local temporary tables and MySQL temporary tables.
Character sets and collations
To learn about the character sets and collations supported by TiDB, see Character Set and Collation Overview.
For information on the MySQL compatibility of the GBK character set, refer to GBK compatibility .
TiDB inherits the character set used in the table as the national character set.
Storage engines
TiDB allows for tables to be created with alternative storage engines. Despite this, the metadata as described by TiDB is for the InnoDB storage engine as a way to ensure compatibility.
To specify a storage engine using the --store
option, it is necessary to start the TiDB server. This storage engine abstraction feature is similar to MySQL.
SQL modes
TiDB supports most SQL modes:
- The compatibility modes, such as
Oracle
andPostgreSQL
are parsed but ignored. Compatibility modes are deprecated in MySQL 5.7 and removed in MySQL 8.0. - The
ONLY_FULL_GROUP_BY
mode has minor semantic differences from MySQL 5.7. - The
NO_DIR_IN_CREATE
andNO_ENGINE_SUBSTITUTION
SQL modes in MySQL are accepted for compatibility, but are not applicable to TiDB.
Default differences
TiDB has default differences when compared with MySQL 5.7 and MySQL 8.0:
- Default character set:
- TiDB’s default value is
utf8mb4
. - MySQL 5.7’s default value is
latin1
. - MySQL 8.0’s default value is
utf8mb4
.
- TiDB’s default value is
- Default collation:
- TiDB’s default collation is
utf8mb4_bin
. - MySQL 5.7’s default collation is
utf8mb4_general_ci
. - MySQL 8.0’s default collation is
utf8mb4_0900_ai_ci
.
- TiDB’s default collation is
- Default SQL mode:
- TiDB’s default SQL mode includes these modes:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
. - MySQL’s default SQL mode:
- The default SQL mode in MySQL 5.7 is the same as TiDB.
- The default SQL mode in MySQL 8.0 includes these modes:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
.
- TiDB’s default SQL mode includes these modes:
- Default value of
lower_case_table_names
:- The default value in TiDB is
2
, and only2
is currently supported. - MySQL defaults to the following values:
- On Linux:
0
. It means that table and database names are stored on disk according to the letter case specified in theCREATE TABLE
orCREATE DATABASE
statement. Name comparisons are case-sensitive. - On Windows:
1
. It means table names are stored in lowercase on disk, and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. - On macOS:
2
. It means table and database names are stored on disk according to the letter case specified in theCREATE TABLE
orCREATE DATABASE
statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case-sensitive.
- On Linux:
- The default value in TiDB is
- Default value of
explicit_defaults_for_timestamp
:- The default value in TiDB is
ON
, and onlyON
is currently supported. - MySQL defaults to the following values:
- For MySQL 5.7:
OFF
. - For MySQL 8.0:
ON
.
- For MySQL 5.7:
- The default value in TiDB is
Date and Time
TiDB supports named timezones with the following considerations:
- TiDB uses all the timezone rules presently installed in the system for calculation, typically the
tzdata
package. This makes it possible to use all timezone names without needing to import timezone table data. Importing timezone table data will not change the calculation rules. - Currently, MySQL uses the local timezone by default, then relies on the current timezone rules built into the system (for example, when daylight savings time begins) for calculation. Without importing timezone table data, MySQL cannot specify the timezone by name.
Type system differences
The following column types are supported by MySQL but not by TiDB:
- FLOAT4/FLOAT8
SQL_TSI_*
(includes SQL_TSI_MONTH, SQL_TSI_WEEK, SQL_TSI_DAY, SQL_TSI_HOUR, SQL_TSI_MINUTE, and SQL_TSI_SECOND, but excludes SQL_TSI_YEAR)
Incompatibility due to deprecated features
TiDB does not implement specific features deprecated in MySQL, including:
- Specifying precision for floating-point types. MySQL 8.0 deprecates this feature, and it is recommended to use the
DECIMAL
type instead. - The
ZEROFILL
attribute. MySQL 8.0 deprecates this feature, and it is recommended to pad numeric values in your application instead.
CREATE RESOURCE GROUP
, DROP RESOURCE GROUP
, and ALTER RESOURCE GROUP
statements
The following statements for creating, modifying, and dropping resource groups have different supported parameters than MySQL. For details, see the following documents: