MySQL Compatibility

TiDB is highly compatible with the MySQL 5.7 protocol and supports the common features and syntax of MySQL 5.7. This means that ecosystem tools such as PHPMyAdmin, Navicat, MySQL Workbench, mysqldump, and Mydumper/myloader, as well as 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:TiCDCis a tool for replicating the incremental data of TiDB by pulling TiKV change logs. TiCDC uses theMySQL sinkto replicate the incremental data of TiDB to MySQL.

You can try out TiDB features onTiDB Playground.

Unsupported features

• Stored procedures and functions
• Triggers
• Events
• User-defined functions
• FULLTEXTsyntax and indexes#1793
• SPATIAL(also known asGIS/GEOMETRY) functions, data types and indexes#6347
• Character sets other thanascii,latin1,binary,utf8,utf8mb4, andgbk.
• SYS schema
• Optimizer trace
• XML Functions
• X-Protocol#1109
• Column-level privileges#9766
• XAsyntax (TiDB uses a two-phase commit internally, but this is not exposed via an SQL interface)
• CREATE TABLE tblName AS SELECT stmtsyntax#4754
• CHECK TABLEsyntax#4673
• CHECKSUM TABLEsyntax#1895
• REPAIR TABLEsyntax
• OPTIMIZE TABLEsyntax
• HANDLERstatement
• 创建表空间statement
• "Session Tracker: Add GTIDs context to the OK packet"

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 theAUTO_INCREMENTMySQL 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 Errormessage.

• You can use thetidb_allow_remove_auto_incsystem variable to allow or forbid removing theAUTO_INCREMENTcolumn attribute. To remove the column attribute, use theALTER TABLE MODIFYorALTER TABLE CHANGEsyntax.

• TiDB does not support adding theAUTO_INCREMENTcolumn 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, seeAUTO_INCREMENT.

              

               mysql>
               CREATE
               TABLEt(idINT
               UNIQUEKEY AUTO_INCREMENT); Query OK,0
               rowsaffected (0.05sec) mysql>
               INSERT
               INTOtVALUES(); Query OK,1
               rowsaffected (0.00sec) mysql>
               INSERT
               INTOtVALUES(); Query OK,1
               rowsaffected (0.00sec) mysql>
               INSERT
               INTOtVALUES(); Query OK,1
               rowsaffected (0.00sec) mysql>
               SELECT_tidb_rowid, idFROMt;+
               -------------+------+
               |_tidb_rowid|id|
               +
               -------------+------+
               |
               2
               |
               1
               |
               |
               4
               |
               3
               |
               |
               6
               |
               5
               |
               +
               -------------+------+
               3
               rows
               in
               set(0.01sec)

               
               

As shown, because of the shared allocator, theidincrements by 2 each time. This behavior changes inMySQL compatibility mode, where there is no shared allocator and therefore no skipping of numbers.

Performance schema

TiDB utilizes a combination ofPrometheus and Grafanafor storing and querying performance monitoring metrics. In TiDB, performance schema tables do not return any results.

To check performance metrics in TiDB Cloud, you can either check the cluster overview page on the TiDB Cloud console or usethird-party monitoring integrations. Performance schema tables return empty results in TiDB.

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 variableoptimizer_switchis 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 toUnderstand the Query Execution Plan.

Built-in functions

TiDB supports most of the built-in functions in MySQL, but not all. You can use the statementSHOW BUILTINSto get a list of the available functions.

For more information, refer to theTiDB 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 singleALTER TABLEstatement 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 c1command, theUnsupported operate same column/indexerror is output.

• 它不支持修改多个TiDB-specific schema objects using a singleALTER TABLEstatement, such asTIFLASH REPLICA,SHARD_ROW_ID_BITS, andAUTO_ID_CACHE.

• TiDB does not support the changes of some data types usingALTER TABLE. For example, TiDB does not support the change from theDECIMALtype to theDATEtype. If a data type change is unsupported, TiDB reports theUnsupported modify column: type %d not match origin %derror. Refer toALTER TABLEfor more details.

• TheALGORITHM={INSTANT,INPLACE,COPY}syntax functions only as an assertion in TiDB, and does not modify theALTERalgorithm. SeeALTER TABLEfor further details.

• Adding/Dropping the primary key of theCLUSTEREDtype is unsupported. For more details about the primary key of theCLUSTEREDtype, refer toclustered index.

• Different types of indexes (HASH|BTREE|RTREE|FULLTEXT) are not supported, and will be parsed and ignored when specified.

• TiDB supportsHASH,RANGE,LIST, andKEYpartitioning types. Currently, theKEYpartition 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%sis the specific unsupported partition type.

• Range, Range COLUMNS, List, and List COLUMNS partitioned tables supportADD,DROP,TRUNCATE, andREORGANIZEoperations. Other partition operations are ignored.

• Hash and Key partitioned tables supportADD,COALESCE, andTRUNCATEoperations. 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, seePartitioning.

Analyzing tables

In TiDB,Statistics Collectiondiffers 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 toANALYZE TABLE.

Limitations ofSELECTsyntax

TiDB does not support the followingSELECTsyntax:

• SELECT ... INTO @variable
• SELECT ... GROUP BY ... WITH ROLLUP
• SELECT .. GROUP BY exprdoes not implyGROUP BY expr ORDER BY expras it does in MySQL 5.7.

For more details, see theSELECTstatement reference.

UPDATEstatement

See theUPDATEstatement reference.

Views

Views in TiDB are not updatable and do not support write operations such asUPDATE,INSERT, andDELETE.

Temporary tables

For more information, seeCompatibility between TiDB local temporary tables and MySQL temporary tables.

Character sets and collations

• To learn about the character sets and collations supported by TiDB, seeCharacter Set and Collation Overview.

• For information on the MySQL compatibility of the GBK character set, refer toGBK 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--storeoption, it is necessary to start the TiDB server. This storage engine abstraction feature is similar to MySQL.

SQL modes

TiDB supports mostSQL modes:

• The compatibility modes, such asOracleandPostgreSQLare parsed but ignored. Compatibility modes are deprecated in MySQL 5.7 and removed in MySQL 8.0.
• TheONLY_FULL_GROUP_BYmode has minorsemantic differencesfrom MySQL 5.7.
• TheNO_DIR_IN_CREATEandNO_ENGINE_SUBSTITUTIONSQL 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 isutf8mb4.
  • MySQL 5.7’s default value islatin1.
  • MySQL 8.0’s default value isutf8mb4.
• Default collation:
  • TiDB’s default collation isutf8mb4_bin.
  • MySQL 5.7’s default collation isutf8mb4_general_ci.
  • MySQL 8.0’s default collation isutf8mb4_0900_ai_ci.
• 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.
• Default value oflower_case_table_names:
  • The default value in TiDB is2, and only2is 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 TABLEorCREATE DATABASEstatement. 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 TABLEorCREATE DATABASEstatement, but MySQL converts them to lowercase on lookup. Name comparisons are not case-sensitive.
• Default value ofexplicit_defaults_for_timestamp:
  • The default value in TiDB isON, and onlyONis currently supported.
  • MySQL defaults to the following values:
    • For MySQL 5.7:OFF.
    • For MySQL 8.0:ON.

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 thetzdatapackage. 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. Withoutimporting timezone table data, MySQL cannot specify the timezone by name.

Type system differences

The following column types are supported by MySQL butnotby 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.0deprecatesthis feature, and it is recommended to use theDECIMALtype instead.
• TheZEROFILLattribute. MySQL 8.0deprecatesthis feature, and it is recommended to pad numeric values in your application instead.

CREATE RESOURCE GROUP,DROP RESOURCE GROUP, andALTER RESOURCE GROUPstatements

The following statements for creating, modifying, and dropping resource groups have different supported parameters than MySQL. For details, see the following documents:

• CREATE RESOURCE GROUP
• DROP RESOURCE GROUP
• ALTER RESOURCE GROUP