mysql与TiDB的兼容性对比

与 MySQL 兼容性对比概览

TiDB 100% 兼容 MySQL 5.7 协议、MySQL 5.7 常用的功能及语法。MySQL 5.7 生态中的系统工具&＃xff08;PHPMyAdmin、Navicat、MySQL Workbench、mysqldump、Mydumper/Myloader&＃xff09;、客户端等均用于 TiDB。

由于 TiDB 是一款分布式数据库&＃xff0c;MySQL 5.7 中的部分特性因工程实现难度较大&＃xff0c;投入产出比较低等多种原因在 TiDB 中未能实现或者仅兼容语法但功能并没有实现&＃xff0c;因此使用过程中请特别注意。例如&＃xff1a;CREATE TABLE 语句中 ENGINE&＃xff0c;仅兼容语法&＃xff0c;功能并没有实现&＃xff0c;因此 TiDB 中没有 ENGINE 这类的概念。

注意&＃xff1a;

本页内容仅涉及 MySQL 与 TiDB 的总体差异。关于安全特性、悲观事务模型 相关的兼容信息请查看各自具体页面。

不支持的功能特性

存储过程与函数
触发器
事件
自定义函数
外键约束
全文/空间函数与索引
非 ascii/latin1/binary/utf8/utf8mb4 的字符集
SYS schema
MySQL 追踪优化器
XML 函数
X Protocol
Savepoints
列级权限
XA 语法&＃xff08;TiDB 内部使用两阶段提交&＃xff0c;但并没有通过 SQL 接口公开&＃xff09;
CREATE TABLE tblName AS SELECT stmt 语法
CREATE TEMPORARY TABLE 语法
CHECK TABLE 语法
CHECKSUM TABLE 语法

与 MySQL 有差异的特性详细说明

自增 ID
TiDB 的自增列仅保证自增且唯一、但不保证自动分配的值的连续性&＃xff0c;建议不要将缺省值和自定义值混用&＃xff0c;若混用可能会收 Duplicated Error 的错误信息。

TiDB 在工程实现上会在每一个 tidb-server 实例上缓存一段 ID 的值用于给表的自增列分配值&＃xff0c;缓存 ID 的个数由表的 AUTO_ID_CACHE 确定&＃xff0c;默认值&＃xff1a;30000。请特别注意&＃xff1a;自增列和 _tidb_rowid都会消耗缓存的 ID。如果 INSERT 语句中所要求的连续的 ID 个数大于 AUTO_ID_CACHE 的值时系统会自动调整 AUTO_ID_CACHE 的值以确保该语句能正常执行。

TiDB 可通过 tidb_allow_remove_auto_inc 系统变量开启或者关闭删除列的 AUTO_INCREMENT 属性。删除列属性的语法是&＃xff1a;alter table modify 或 alter table change 。

注意&＃xff1a;

tidb_allow_remove_auto_inc 要求版本号 >&＃61; v2.1.18 或者 >&＃61; v3.0.4。
表的 AUTO_ID_CACHE 属性要求版本号 >&＃61; v3.0.14 或者 >&＃61; v3.1.2 或者 >&＃61; v4.0.rc-2。
若创建表时没有指定主键时&＃xff0c;TiDB 会使用 _tidb_rowid 来标识行&＃xff0c;该数值的分配会和自增列&＃xff08;如果存在的话&＃xff09;共用一个分配器。如果指定了自增列为主键&＃xff0c;则 TiDB 会用该列来标识行。因此会有以下的示例情况&＃xff1a;
Copy
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;
±------------±-----&＃43;
| _tidb_rowid | id |
±------------±-----&＃43;
| 4 | 1 |
| 5 | 2 |
| 6 | 3 |
±------------±-----&＃43;
3 rows in set (0.01 sec)
Performance schema
TiDB 主要使用 Prometheus 和 Grafana 来存储及查询相关的性能监控指标&＃xff0c;所以 Performance schema 部分表是空表。
查询计划
EXPLAIN/EXPLAIN FOR 输出格式、内容、权限设置与 MySQL 有比较大的差别&＃xff0c;参见理解 TiDB 执行计划。
内建函数
支持常用的 MySQL 内建函数&＃xff0c;有部分函数并未支持&＃xff0c;参考 SQL 语法文档。
DDL 的限制
Add Index

同一条 SQL 语句不支持创建多个索引。
仅在语法在支持创建不同类型的索引 (HASH/BTREE/RTREE&＃xff09;&＃xff0c;功能未实现。
Add Column

不支持设置PRIMARY KEY 及 UNIQUE KEY&＃xff0c;不支持设置 AUTO_INCREMENT 属性。可能输出的错误信息&＃xff1a;unsupported add column ‘%s’ constraint PRIMARY/UNIQUE/AUTO_INCREMENT KEY
Drop Column

不支持删除主键列及索引列&＃xff0c;可能输出的错误信息&＃xff1a;Unsupported drop integer primary key/column a with index covered。
Drop Primary Key

仅支持删除建表时启用了 alter-primary-key 配置项的表的主键。可能输出的错误信息: Unsupported drop primary key when alter-primary-key is false。
Order By 忽略所有列排序相关的选项。

Change/Modify Column

不支持有损变更&＃xff0c;比如从 BIGINT 变为 INTEGER&＃xff0c;或者从 VARCHAR(255) 变为 VARCHAR(10)&＃xff0c;可能输出的错误信息&＃xff1a;length %d is less than origin %d。
不支持修改 DECIMAL 类型的精度&＃xff0c;可能输出的错误信息&＃xff1a;can’t change decimal column precision。
不支持更改 UNSIGNED 属性&＃xff0c;可能输出的错误信息&＃xff1a;can’t change unsigned integer to signed or vice versa。
只支持将 CHARACTER SET 属性从 utf8 更改为 utf8mb4
LOCK [&＃61;] {DEFAULT|NONE|SHARED|EXCLUSIVE}

仅在语法上支持&＃xff0c;功能未实现&＃xff0c;故所有的 DDL 都不会锁表。
ALGORITHM [&＃61;] {DEFAULT|INSTANT|INPLACE|COPY}

支持 ALGORITHM&＃61;INSTANT 和 ALGORITHM&＃61;INPLACE 语法&＃xff0c;但行为与 MySQL 有所不同&＃xff0c;MySQL 中的一些 INPLACE 操作在 TiDB 中的 是INSTANT 操作。
仅在语法上支持 ALGORITHM&＃61;COPY&＃xff0c;功能未实现&＃xff0c;会返回警告信息。
单条 ALTER TABLE 语句中无法完成多个操作。例如&＃xff1a;不能用一条语句来添加多个列或多个索引。可能输出的错误信息&＃xff1a;Unsupported multi schema change。

Table Option 仅支持AUTO_INCREMENT、CHARACTER SET、COLLATE、COMMENT&＃xff0c;不支持以下语法:

WITH/WITHOUT VALIDATION
SECONDARY_LOAD/SECONDARY_UNLOAD
CHECK/DROP CHECK
STATS_AUTO_RECALC/STATS_SAMPLE_PAGES
SECONDARY_ENGINE
ENCRYPTION
Table Partition 分区类型支持 Hash、Range&＃xff1b;支持 Add/Drop/Truncate/Coalesce&＃xff1b;忽略其他分区操作&＃xff0c;可能错误信息&＃xff1a;Warning: Unsupported partition type, treat as normal table&＃xff0c;不支持以下语法:

PARTITION BY LIST
PARTITION BY KEY
SUBPARTITION
{CHECK|EXCHANGE|TRUNCATE|OPTIMIZE|REPAIR|IMPORT|DISCARD|REBUILD|REORGANIZE} PARTITION
ANALYZE TABLE
ANALYZE TABLE 语句会完全重构表的统计数据&＃xff0c;语句执行过程较长&＃xff0c;但在 MySQL/InnoDB 中&＃xff0c;它是一个轻量级语句&＃xff0c;执行过程较短。
视图
不支持 UPDATE、INSERT、DELETE 等写入操作。
存储引擎
仅在语法上兼容创建表时指定存储引擎&＃xff0c;实际上 TiDB 会将元信息统一描述为 InnoDB 存储引擎。TiDB 支持类似 MySQL 的存储引擎抽象&＃xff0c;但需要在系统启动时通过–store 配置项来指定存储引擎。
SQL 模式
不支持兼容模式&＃xff0c;例如&＃xff1a;ORACLE 和 POSTGRESQL&＃xff0c;MySQL 5.7 已弃用兼容模式&＃xff0c;MySQL 8.0 已移除兼容模式。

ONLY_FULL_GROUP_BY 与 MySQL 5.7 相比有细微的语义差别。

NO_DIR_IN_CREATE 和 NO_ENGINE_SUBSTITUTION MySQL 用于解决兼容问题&＃xff0c;并不适用于 TiDB。

默认设置
字符集&＃xff1a;

TiDB 默认&＃xff1a;utf8mb4。
MySQL 5.7 默认&＃xff1a;latin1。
MySQL 8.0 默认&＃xff1a; utf8mb4。
排序规则&＃xff1a;

TiDB 中 utf8mb4 字符集默认: utf8mb4_bin。
MySQL 5.7 中 utf8mb4 字符集默认: utf8mb4_general_ci。
MySQL 8.0 中 utf8mb4 字符集默认: utf8mb4_0900_ai_ci。
foreign_key_checks&＃xff1a;

TiDB 默认: OFF&＃xff0c;且仅支持设置该值为 OFF。
MySQL 5.7 默认&＃xff1a; ON。
SQL mode&＃xff1a;

TiDB 默认&＃xff1a; 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 5.7 默认 与 TiDB 相同。
MySQL 8.0 默认 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION。
lower_case_table_names&＃xff1a;

TiDB 默认&＃xff1a;2&＃xff0c;且仅支持设置该值为 2。
MySQL 默认如下&＃xff1a;
Linux 系统中该值为 0
Windows 系统中该值为 1
macOS 系统中该值为 2
explicit_defaults_for_timestamp&＃xff1a;

TiDB 默认&＃xff1a;ON&＃xff0c;且仅支持设置该值为 ON。
MySQL 5.7 默认&＃xff1a;OFF。
MySQL 8.0 默认&＃xff1a;ON。

日期时间处理的区别

时区
TiDB 采用系统当前安装的所有时区规则进行计算&＃xff08;一般为 tzdata 包&＃xff09;, 不需要导入时区表数据就能使用所有时区名称&＃xff0c;无法通过导入时区表数据的形式修改计算规则。

MySQL 默认使用本地时区&＃xff0c;依赖于系统内置的当前的时区规则&＃xff08;例如什么时候开始夏令时等&＃xff09;进行计算&＃xff1b;且在未导入时区表数据的情况下不能通过时区名称来指定时区。

零月和零日

与 MySQL 一样&＃xff0c;TiDB 默认启用了 NO_ZERO_DATE 和 NO_ZERO_IN_DATE 模式&＃xff0c;但是 TiDB 与 MySQL 在处理这两个 SQL 模式有以下不同&＃xff1a;
TiDB 在非严格模式下启用以上两个 SQL 模式&＃xff0c;插入零月/零日/零日期不会给出警告&＃xff0c;MySQL 则会给出对应的警告。
TiDB 在严格模式下&＃xff0c;启用了 NO_ZERO_DATE &＃xff0c;仍然能够插入零日期&＃xff1b;如果启用了 NO_ZERO_IN_DATE 则无法插入零月/零日日期。MySQL 在严格模式下则都无法插入两种类型的日期。

类型系统

不支持 FLOAT4/FLOAT8。

不支持 FIXED (alias for DECIMAL)。

不支持 SERIAL (alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE)。

不支持 SQL_TSI_*&＃xff08;包括 SQL_TSI_YEAR、SQL_TSI_MONTH、SQL_TSI_WEEK、SQL_TSI_DAY、SQL_TSI_HOUR、SQL_TSI_MINUTE 和 SQL_TSI_SECOND&＃xff09;。

参考资料

https://pingcap.com/docs-cn/stable/mysql-compatibility/