MySQL Compatibility
TiDB is highly compatible with the MySQL 5.7 protocol and the common features and syntax of MySQL 5.7. The ecosystem tools for MySQL 5.7 (PHPMyAdmin, Navicat, MySQL Workbench, mysqldump, and Mydumper/myloader) and the MySQL client can be used for TiDB.
However, some features of MySQL are not supported. This could be because there is now a better way to solve the problem (such as XML functions superseded by JSON), or a lack of current demand versus effort required (such as stored procedures and functions). Some features might also be difficult to implement as a distributed system.
- In addition, TiDB does not support the MySQL replication protocol, but provides specific tools to replicate data with MySQL.
- Replicate data from MySQL: TiDB Data Migration (DM) is a tool that supports the full data migration and the incremental data replication from MySQL/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.
Unsupported features
- Stored procedures and functions
- Triggers
- Events
- User-defined functions
FOREIGN KEY
constraints #18209- Temporary tables #1248
FULLTEXT
syntax and indexes #1793SPATIAL
(also known asGIS
/GEOMETRY
) functions, data types and indexes #6347- Character sets other than
utf8
,utf8mb4
,ascii
,latin1
andbinary
- SYS schema
- Optimizer trace
- XML Functions
- X-Protocol #1109
- Savepoints #6840
- 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 #1895GET_LOCK
andRELEASE_LOCK
functions #14994LOAD DATA
with theREPLACE
keyword #24515
Features that are different from MySQL
Auto-increment ID
In TiDB, auto-increment columns are only guaranteed to be unique and incremental on a single TiDB server, but they are not guaranteed to be incremental among multiple TiDB servers or allocated sequentially. It is recommended that you do not mix default values and custom values. Otherwise, you might encounter the
Duplicated Error
error message.You can use the
tidb_allow_remove_auto_inc
system variable to allow or forbid removing theAUTO_INCREMENT
column attribute. The syntax of removing the column attribute isALTER TABLE MODIFY
orALTER TABLE CHANGE
.TiDB does not support adding the
AUTO_INCREMENT
column attribute, and this attribute cannot be recovered once it is removed.See
AUTO_INCREMENT
for more details.
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 uses a combination of Prometheus and Grafana to store and query the performance monitoring metrics. Performance schema tables return empty results in TiDB.
Query Execution Plan
The output format, output content, and the privilege setting of Query Execution Plan (EXPLAIN
/EXPLAIN FOR
) in TiDB is greatly different from those in MySQL.
The MySQL system variable optimizer_switch
is read-only in TiDB and has no effect on query plans. You can also use optimizer hints in similar syntax to MySQL, but the available hints and implementation might differ.
See Understand the Query Execution Plan for more details.
Built-in functions
TiDB supports most of the MySQL built-in functions, but not all. The statement SHOW BUILTINS
provides a list of functions that are available.
See also: TiDB SQL Grammar.
DDL
In TiDB, all supported DDL changes are performed online. Compared with DDL operations in MySQL, the DDL operations in TiDB have the following major restrictions:
- Multiple operations cannot be completed in a single
ALTER TABLE
statement. For example, it is not possible to add multiple columns or indexes in a single statement. Otherwise, theUnsupported multi schema change
error might be output. ALTER TABLE
in TiDB does not support the changes of some data types. 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
, andLIST
partitioning types. For an unsupported partition type, TiDB returnsWarning: Unsupported partition type, treat as normal table
. - Table partitioning supports
ADD
,DROP
, andTRUNCATE
operations. Other partition operations are ignored. The following table partition syntaxes are not supported:PARTITION BY KEY
SUBPARTITION
{CHECK|OPTIMIZE|REPAIR|IMPORT|DISCARD|REBUILD|REORGANIZE|COALESCE} PARTITION
Analyze table
Statistics Collection works differently in TiDB than in MySQL, in that it is a relatively lightweight and short-lived operation in MySQL/InnoDB, while in TiDB it completely rebuilds the statistics for a table and can take much longer to complete.
These differences are documented further in ANALYZE TABLE
.
Limitations of SELECT
syntax
- 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 implyGROUP 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
Views in TiDB are not updatable. They do not support write operations such as UPDATE
, INSERT
, and DELETE
.
Storage engines
For compatibility reasons, TiDB supports the syntax to create tables with alternative storage engines. In implementation, TiDB describes the metadata as the InnoDB storage engine.
TiDB supports storage engine abstraction similar to MySQL, but you need to specify the storage engine using the --store
option when you start the TiDB server.
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
- Default character set:
- The default value in TiDB is
utf8mb4
. - The default value in MySQL 5.7 is
latin1
. - The default value in MySQL 8.0 is
utf8mb4
.
- The default value in TiDB is
- Default collation:
- The default collation of
utf8mb4
in TiDB isutf8mb4_bin
. - The default collation of
utf8mb4
in MySQL 5.7 isutf8mb4_general_ci
. - The default collation of
utf8mb4
in MySQL 8.0 isutf8mb4_0900_ai_ci
.
- The default collation of
- Default value of
foreign_key_checks
:- The default value in TiDB is
OFF
and currently TiDB only supportsOFF
. - The default value in MySQL 5.7 is
ON
.
- The default value in TiDB is
- Default SQL mode:
- The default SQL mode in TiDB 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
. - The default SQL mode in MySQL:
- 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
.
- The default SQL mode in TiDB includes these modes:
- Default value of
lower_case_table_names
:- The default value in TiDB is
2
and currently TiDB only supports2
. - The default value in MySQL:
- On Linux:
0
- On Windows:
1
- On macOS:
2
- On Linux:
- The default value in TiDB is
- Default value of
explicit_defaults_for_timestamp
:- The default value in TiDB is
ON
and currently TiDB only supportsON
. - The default value in MySQL:
- For MySQL 5.7:
OFF
. - For MySQL 8.0:
ON
.
- For MySQL 5.7:
- The default value in TiDB is
Date and Time
Named timezone
- TiDB uses all time zone rules currently installed in the system for calculation (usually the
tzdata
package). You can use all time zone names without importing the time zone table data. You cannot modify the calculation rules by importing the time zone table data. - MySQL uses the local time zone by default and relies on the current time zone rules built into the system (such as when to start daylight saving time) for calculation; and the time zone cannot be specified by the time zone name without importing the time zone table data.
Type system differences
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)
Incompatibility caused by deprecated features
TiDB does not implement certain features that have been marked as 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 instead pad numeric values in your application.