Restrictions
- TiDB is 100% compatible with MySQL 5.7 protocol, functions and syntax commonly used in MySQL 5.7. The system tools (PHPMyAdmin, Navicat, MySQL Workbench, mysqldump and Mydumper/myloader) and clients in the MySQL5.7 ecosystem are all used for TiDB.
- TiDB is a distributed database. Some features in MySQL 5.7 are not implemented in TiDB or only compatible with syntax but function is not implemented due to many reasons, such as great difficulty in engineering implementation and low input and output. Therefore, please pay special attention during use. For example: ENGINE in the CREATE TABLE statement is only compatible with the syntax and the function has not been implemented, so there is no such concept as ENGINE in TiDB.
For detailed compatibility instructions, please refer to the official TiDB documentation: https://docs.pingcap.com/zh/tidb/v4.0/mysql-compatibility
Features unsupported
- Stored procedures and functions
- Trigger
- Event
- Customized functions
- Foreign key constraints
- Full-text/spatial functions and indexes
- Character set other than ascii/latin1/binary/utf8/utf8mb4
- SYS schema
- MySQL trace optimizer
- XML functions
- X Protocol
- Savepoints
- Column-level permissions
- XA syntax (two-phase commit is used internally by TiDB, but is not exposed through SQL APIs)
- CREATE TABLE tblName AS SELECT stmt syntax
- CREATE TEMPORARY TABLE Syntax
- CHECK TABLE syntax
- CHECKSUM TABLE syntax
- SELECT INTO FILE syntax
Features different from MySQL
Auto increment ID
The auto increment ID of TiDB only guarantees auto increment and uniqueness, and does not guarantee continuous assignment. TiDB currently adopts batch assignment, so if data is inserted on multiple TiDBs at the same time, the assigned auto increment IDs will be discontinuous.
Performance schema
TiDB mainly uses Prometheus and Grafana to store and query relevant performance monitoring indicators, so some tables in the Performance schema are empty tables.
Query plan
The output format, content, and permission settings of EXPLAIN/EXPLAIN FOR are quite different from those of MySQL. See Understanding TiDB Execution Plans.
Built-in function
TiDB supports commonly used MySQL built-in functions, but not all functions are already supported. For details, please refer to Official syntax documentation
View
Write operations such as UPDATE, INSERT and DELETE are not supported.
Store Engine
The storage engine is specified only when creating a table grammatically compatible. In fact, TiDB will uniformly describe the meta information as an InnoDB storage engine. TiDB supports the storage engine abstraction similar to MySQL, but the storage engine needs to be specified through --store configuration item at system startup.
DDL restrictions
-
Add Index
- Creating multiple indexes is not supported for the same SQL statement.
- Creating different types of indexes (HASH/BTREE/RTREE) is only grammatically supported, and the function is not implemented.
-
Add Column
- It is not supported to set PRIMARY KEY and UNIQUE KEY and set AUTO_INCREMENT attributes. Error messages that may be output: unsupported add column '%s' constraint PRIMARY/UNIQUE/AUTO_INCREMENT KEY
-
Drop Column
- It is not supported to delete primary key columns and index columns, error messages that may be output: Unsupported drop integer primary key/column a with index covered.
-
Drop Primary Key
- Only support deleting the primary key of the table with alter-primary-key configuration item enabled when the table was created, error messages that may be output: Unsupported drop primary key when alter-primary-key is false.
-
Order By ignores all options related to column sorting.
-
Change/Modify Column
- Lossy changes is unsupported, such as change from BIGINT to INTEGER, or change from VARCHAR(255) to VARCHAR(10); error messages that may be output: length %d is less than origin %d
- It is not supported to modify the precision of the DECIMAL type; error messages that may be output: can't change decimal column precision.
- It is not supported to change the UNSIGNED attribute; error messages that may be output: can't change unsigned integer to signed or vice versa.
- Only support changing the CHARACTER SET attribute from utf8 to utf8mb4.
-
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
- Only supported in syntax, and the function is not implemented, so all DDLs will not lock table.
-
ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
- Support ALGORITHM=INSTANT and ALGORITHM=INPLACE syntax, but the behavior is different from MySQL. Some INPLACE operations in MySQL are INSTANT operations in TiDB.
Only support ALGORITHM=COPY in syntax, and the function is not implemented; a warning message will be returned.
- Multiple operations cannot be completed in a single ALTER TABLE statement. For example: You cannot add multiple columns or indexes with a single statement. Error messages that may be output: Unsupported multi schema change.
-
Table Option only supports AUTO_INCREMENT, CHARACTER SET, COLLATE and COMMENT; the following syntax is not supported:
- WITH/WITHOUT VALIDATION
- SECONDARY_LOAD/SECONDARY_UNLOAD
- CHECK/DROP CHECK
- STATS_AUTO_RECALC/STATS_SAMPLE_PAGES
- SECONDARY_ENGINE
- ENCRYPTION
-
Table Partition’s partition type supports Hash and Range; supports Add/Drop/Truncate/Coalese; ignores other partition operations, possible error messages: Warning: Unsupported partition type, treat as normal table, the following syntax is unsupported:
- PARTITION BY LIST
- PARTITION BY KEY
- SUBPARTITION
- {CHECK|EXCHANGE|TRUNCATE|OPTIMIZE|REPAIR|IMPORT|DISCARD|REBUILD|REORGANIZE} PARTITION
-
ANALYZE TABLE
- ANALYZE TABLE statement will completely reconstruct the statistical data of the table. The statement execution process is quite long, but in MySQL/InnoDB, it is a lightweight statement with a short execution process.
View
SQL mode
- Compatible mode is not supported, for example: ORACLE and POSTGRESQL, MySQL 5.7 has abandoned the compatible mode, and MySQL 8.0 has removed the compatible mode.
- ONLY_FULL_GROUP_BY has subtle semantic differences compared with MySQL 5.7.
- NO_DIR_IN_CREATE and NO_ENGINE_SUBSTITUTION MySQL are used to resolve compatibility problems and are not applicable to TiDB.
Time zone
- TiDB uses all the time zone rules currently installed in the system (usually tzdata packet) for calculation. All time zone names can be used without importing time zone table data. The calculation rules cannot be modified by importing time zone table data.
- MySQL uses the local time zone by default, which depends on the current time zone rules built in the system (such as when does daylight saving time start, etc.) for calculation; and the time zone cannot be specified by the time zone name without importing the time zone table data.
Zero month and zero day
- Like MySQL, TiDB enables NO_ZERO_DATE and NO_ZERO_IN_DATE modes by default, but TiDB and MySQL have the following differences in processing these two SQL modes:
- TiDB enables the above two SQL modes in non-strict mode. Inserting zero month/zero day/zero date will not give a warning, while MySQL will give corresponding warnings.
- In strict mode, TiDB can still insert zero date if NO_ZERO_DATE is enabled; if NO_ZERO_IN_DATE is enabled, zero month/zero date cannot be inserted. MySQL cannot insert both types of dates in strict mode.
Type system
- FLOAT4/FLOAT8 unsupported.
- FIXED (alias for DECIMAL) unsupported.
- SERIAL (alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE) unsupported.
- SQLTSI* unsupported (including SQL_TSI_YEAR, SQL_TSI_MONTH, SQL_TSI_WEEK, SQL_TSI_DAY, SQL_TSI_HOUR, SQL_TSI_MINUTE and SQL_TSI_SECOND).