TiDB - MySQL Compatibility

TiDB is highly compatible with the MySQL 5.7 protocol and common MySQL 5.7 features and syntax. MySQL 5.7 ecosystem tools such as PHPMyAdmin, Navicat, MySQL Workbench, mysqldump, and Mydumper/myloader, as well as MySQL clients, can be used with TiDB.

However, some MySQL features are not supported. This may be because there is a better way to solve the problem, such as replacing XML functions with JSON, or because demand is currently low compared with the effort required, such as stored procedures or functions. Some features can also be difficult to implement in a distributed system.

• TiDB also does not support the MySQL replication protocol, but it provides specific tools for replicating data from MySQL.
  • Replicating data from MySQL: TiDB Data Migration, or DM, is a tool that supports full data migration and incremental data replication from MySQL/MariaDB to TiDB.
  • Replicating data to MySQL: TiCDC is a tool that pulls TiKV change logs and replicates TiDB incremental data. TiCDC replicates TiDB incremental data to MySQL by using a MySQL sink.

Note:

This page explains general differences between MySQL and TiDB. For compatibility in safe and pessimistic transaction modes, see the dedicated page.

Unsupported Features

• Stored procedures and functions
• Triggers
• Events
• User-defined functions
• FOREIGN KEY constraints #18209
• FULLTEXT syntax and indexes #1793
• SPATIAL, also known as GIS or GEOMETRY, functions, data types, and indexes #6347
• Character sets other than ascii, binary, latin1, utf8, utf8mb4, and gbk
• SYS schema
• Optimizer trace
• XML functions
• X Protocol #1109
• Savepoints #6840
• Column-level privileges #9766
• XA syntax. TiDB internally uses two-phase commit, but this is not exposed through the SQL interface.
• CREATE TABLE tblName AS SELECT stmt syntax #4754
• CHECK TABLE syntax #4673
• CHECKSUM TABLE syntax #1895
• REPAIR TABLE syntax
• OPTIMIZE TABLE syntax
• HANDLER syntax
• CREATE TABLESPACE syntax

Features That Differ from MySQL

Auto-increment IDs

• In TiDB, auto-increment columns are globally unique. They are incremental on a single TiDB server, but they are not necessarily incremental or allocated in order across multiple TiDB servers. It is better not to mix default values and custom values. Otherwise, a Duplicated Error message may appear.

• You can use the tidb_allow_remove_auto_inc system variable to allow or forbid removing the AUTO_INCREMENT column attribute. The syntax for removing the column attribute is ALTER TABLE MODIFY or ALTER TABLE CHANGE.

• TiDB does not support adding the AUTO_INCREMENT column attribute. If this attribute is removed, it cannot be restored.

• For 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, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT _tidb_rowid, id FROM t;
+-------------+------+
| _tidb_rowid | id   |
+-------------+------+
|           4 |    1 |
|           5 |    2 |
|           6 |    3 |
+-------------+------+
3 rows in set (0.01 sec)

Performance Schema

TiDB stores and queries performance monitoring metrics with the combination of Prometheus and Grafana. Performance Schema tables return empty results in TiDB.

Query Execution Plans

The output format, output content, and permission settings of EXPLAIN FOR query execution plans, or EXPLAIN, differ significantly from MySQL.

The MySQL system variable optimizer_switch is read-only in TiDB and does not affect query plans. Optimizer hints can also be used with syntax similar to MySQL, but the available hints and implementations may differ.

For details, see “Understanding Query Execution Plans.”

Built-in Functions

TiDB supports most MySQL built-in functions, but not all of them. The syntax SHOW BUILTINS provides a list of available functions.

Reference: TiDB SQL syntax.

DDL

All DDL changes supported by TiDB are performed online. Compared with MySQL DDL operations, TiDB DDL operations have the following main limitations.

• You cannot complete multiple operations in one ALTER TABLE statement. For example, you cannot add multiple columns or indexes in one statement. Otherwise, an Unsupported multi schema change error message may appear.

• TiDB’s ALTER TABLE does not support some data type changes. For example, TiDB does not support changing from DECIMAL to DATE. If a data type change is not supported, TiDB displays an Unsupported modify column: type %d not match origin %d error. For details, see ALTER TABLE.

• The ALGORITHM={INSTANT,INPLACE,COPY} syntax only works as an assertion in TiDB and does not change the ALTER algorithm. For details, see ALTER TABLE.

• Adding or removing a CLUSTERED primary key is not supported. For details about CLUSTERED primary keys, see clustered indexes.

• Various index types, such as HASH|BTREE|RTREE|FULLTEXT, are not supported. If specified, they are parsed and ignored.

• Table partitioning supports HASH, RANGE, and LIST partitioning types. For unsupported partition types, Warning: Unsupported partition type %s, treat as normal table may be output, where %s is the specific partition type.

• Table partitioning also supports ADD, DROP, and TRUNCATE operations. Other partition operations are ignored. The following table partition syntaxes are not supported:

  • PARTITION BY KEY
  • SUBPARTITION
  • {CHECK|TRUNCATE|OPTIMIZE|REPAIR|IMPORT|DISCARD|REBUILD|REORGANIZE|COALESCE} PARTITION
    For details, see partitioning.

Table Analysis

Statistics collection behaves differently in TiDB and MySQL. In MySQL/InnoDB it is a relatively lightweight and short-lived task, while in TiDB it rebuilds table statistics completely and can take much longer to complete.

These differences are described in detail under ANALYZE TABLE.

SELECT Syntax Limitations

• The syntax SELECT ... INTO @variable is not supported.
• The syntax SELECT ... GROUP BY ... WITH ROLLUP is not supported.
• The syntax SELECT .. GROUP BY expr does not imply GROUP BY expr ORDER BY expr as it does in MySQL 5.7.

For details, see the SELECT statement reference.

UPDATE Statement

See the UPDATE statement reference.

Views

TiDB views cannot be updated. Operations such as INSERT and UPDATE are not supported, and DELETE is also not supported.

Temporary Tables

For details, see compatibility between TiDB local temporary tables and MySQL temporary tables.

Character Sets and Collations

• For details about character sets and collations supported by TiDB, see the character set and collation overview.

• For details about MySQL compatibility for the GBK character set, see GBK compatibility.

• TiDB inherits the character set used in a table as the national character set.

Storage Engines

For compatibility, TiDB supports syntax for creating tables with alternative storage engines. In implementation, TiDB describes metadata as the InnoDB storage engine.

TiDB supports storage engine abstraction similar to MySQL, but when starting the TiDB server, you must specify the storage engine with the --store option.

SQL Modes

TiDB supports most SQL modes.

• Compatibility modes such as Oracle or PostgreSQL are parsed but ignored. Compatibility modes are deprecated in MySQL 5.7 and were removed from MySQL 5.7.
• The ONLY_FULL_GROUP_BY mode has minor semantic differences from MySQL 5.7.
• MySQL’s MySQL NO_DIR_IN_CREATE and NO_ENGINE_SUBSTITUTION modes are accepted for compatibility, but they do not apply to TiDB.

Default Differences

• Default character set:
  • TiDB’s default is utf8mb4.
  • MySQL 5.7’s default is latin1.
  • MySQL 8.0’s default is utf8mb4.
• Default collation:
  • TiDB’s default collation for utf8mb4 is utf8mb4_bin.
  • MySQL 5.7’s default collation for utf8mb4 is utf8mb4_general_ci.
  • MySQL 8.0’s default collation for utf8mb4 is utf8mb4_0900_ai_ci.
• Default value of foreign_key_checks:
  • TiDB’s default is OFF, and currently TiDB supports only OFF.
  • MySQL 5.7’s default is ON.
• Default SQL mode:
  • TiDB’s default SQL mode includes the following 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:
    • MySQL 5.7’s default SQL mode is the same as TiDB’s.
    • MySQL 8.0’s default SQL mode includes the following modes:
      ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION.
• Default value of lower_case_table_names:
  • TiDB’s default is 2, and currently TiDB supports only 2.
  • MySQL defaults:
    • Linux: 0
    • Windows: 1
    • macOS: 2
• Default value of explicit_defaults_for_timestamp:
  • TiDB’s default is ON, and currently TiDB supports only ON.
  • MySQL defaults:
    • MySQL 5.7: OFF.
    • MySQL 8.0: ON.

Date and Time

Named Time Zones

• TiDB currently uses all time zone rules installed on the system, generally packaged as tzdata, for calculations. You can use all time zone names without importing time zone table data. You cannot import time zone table data or change calculation rules.
• MySQL uses the local time zone by default and depends on the current time zone rules built into the system for calculations, such as the start of daylight saving time. Also, a time zone cannot be specified by name without importing time zone table data.

Differences in the Type System

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

• FLOAT4/FLOAT8
• SQL_TSI_*, including SQL_TSI_MONTH, SQL_TSI_WEEK, SQL_TSI_DAY, SQL_TSI_HOUR, SQL_TSI_MINUTE, and SQL_TSI_SECOND, excluding SQL_TSI_YEAR

Incompatibilities Caused by Deprecated Features

TiDB does not implement certain features marked as deprecated in MySQL.

• Specifying precision for floating-point types. This is deprecated in MySQL 8.0, and using the DECIMAL type instead is recommended.
• The ZEROFILL attribute. This is deprecated in MySQL 8.0, and padding numbers in the application instead is recommended.