MySQL Compatibility (original) (raw)

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:

Note

This page describes general differences between MySQL and TiDB. For more information on compatibility with MySQL in the areas of security, see Security Compatibility with MySQL.

You can try out TiDB features on TiDB Playground.

Unsupported features

Differences from MySQL

Auto-increment ID

For more details, see AUTO_INCREMENT.

Note


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, most 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.

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:

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:

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

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:

Default differences

TiDB has default differences when compared with MySQL 5.7 and MySQL 8.0:

Date and Time

TiDB supports named timezones with the following considerations:

Type system differences

The following column types are supported by MySQL but not by TiDB:

Regular expressions

For information about TiDB regular expression compatibility with MySQL, including REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), and REGEXP_SUBSTR(), see Regular expression compatibility with MySQL.

Incompatibility due to deprecated features

TiDB does not implement specific features deprecated in MySQL, including:

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:

Differences on pessimistic transaction (lock) with MySQL InnoDB

For differences on pessimistic transaction (lock) between TiDB and MySQL InnoDB, see Differences from MySQL InnoDB.