DDL支持度

以DATABASE为对象的DDL

DDL类型 DDL语句示例 全量 增量 备注
CREATE DATABASE basic CREATE DATABASE db_name;

CREATE DATABASE IF NOT EXISTS db_name;

CREATE SCHEMA db_name;

CREATE SCHEMA IF NOT EXISTS db_name;		 支持 支持 1. 支持server级默认字符集不一致情形
character set and collate CREATE SCHEMA db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE SCHEMA db_name DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

CREATE SCHEMA db_name CHARACTER SET=latin1 COLLATE=latin1_swedish_ci;

CREATE DATABASE db_name COLLATE latin2_general_ci;

CREATE DATABASE db_name CHARACTER SET binary;

CREATE DATABASE db_name DEFAULT CHARACTER SET=gbk DEFAULT COLLATE=gbk_chinese_ci;		 支持 支持 1. 支持字符集latin1、latin2、gbk、utf8、utf8mb4、binary
ALTER DATABASE ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci 支持 支持 支持字符集latin1、latin2、gbk、utf8、utf8mb4、binary
DROP DATABASE DROP DATABASE db_name;

DROP DATABASE IF EXISTS db_name;

DROP SCHEMA db_name;

DROP SCHEMA IF EXISTS db_name;		 支持 支持

以TABLE为对象的DDL

DDL类型 DDL语句示例 全量 增量 备注
CREATE TABLE column data types and attributes basic CREATE TABLE [IF NOT EXISTS] tbl_name (col_name column_definition); 支持 支持
character set CREATE TABLE [IF NOT EXISTS] tbl_name (col_name column_definition CHARACTER SET utf8 COLLATE utf8_bin); 支持 支持
null CREATE TABLE [IF NOT EXISTS] tbl_name (col_name column_definition NULL); 支持 支持
not null CREATE TABLE [IF NOT EXISTS] tbl_name (col_name column_definition NOT NULL); 支持 支持
default CREATE TABLE [IF NOT EXISTS] tbl_name (c CHAR(20) DEFAULT ''); 支持 支持
auto_increment CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT PRIMARY KEY); 支持 支持
comment CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) COMMENT ''); 支持 支持
generated always CREATE TABLE [IF NOT EXISTS] tbl_name (sidea DOUBLE,sideb DOUBLE,sidec DOUBLE GENERATED ALWAYS AS (SQRT(sidea * sidea + sideb * sideb))); 支持 支持 建表语句支持，但是数据复制有缺陷
https://github.com/actiontech/dtle/issues/787
indexes and foreign keys check CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) CHECK(expr)); 支持 支持
primary key CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT, PRIMARY KEY (id));

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT, c CHAR(20), PRIMARY KEY(id, c));		 支持 支持
key CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT KEY);

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT, KEY index_name (key_part));

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT, c CHAR(20) DEFAULT 't7', KEY (key_part, key_part));		 支持 支持
index CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT, INDEX index_name (key_part));

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT, c CHAR(20) DEFAULT 't10', INDEX(key_part, key_part));		 支持 支持
unique CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT UNIQUE KEY);

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT, UNIQUE KEY index_name (key_part));

CREATE TABLE [IF NOT EXISTS] tbl_name (c CHAR(20), UNIQUE INDEX (key_part(prefix value)));

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT, c CHAR(20) DEFAULT 't10', CONSTRAINT UNIQUE INDEX (key_part, key_part));"		 支持 支持
fulltext CREATE TABLE [IF NOT EXISTS] tbl_name (c_varchar_1 varchar(255), c_varchar_2 varchar(255), FULLTEXT KEY index_name (key_part, key_part)); 支持 支持
foreign key CREATE TABLE [IF NOT EXISTS] tbl_name (id INT, parent_id INT, CONSTRAINT symbol FOREIGN KEY (col_name) REFERENCES tbl_name(key_part) ON DELETE reference_option ON UPDATE reference_option);

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT, parent_id INT, CONSTRAINT symbol FOREIGN KEY (col_name) REFERENCES tbl_name(key_part) ON DELETE reference_option ON UPDATE reference_option);

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT, parent_id INT, FOREIGN KEY index_name (col_name) REFERENCES tbl_name(key_part) ON DELETE reference_option ON UPDATE reference_option);

CREATE TABLE [IF NOT EXISTS] tbl_name (id INT, parent_id INT, FOREIGN KEY (col_name) REFERENCES tbl_name(key_part) ON DELETE reference_option ON UPDATE reference_option);		 支持 支持 应满足一下配置，否则在开启MTS的情况下不能保证数据一致性

1.目标端数据库@@foreign_key_checks = 1 （默认值为1）

2.dtle job 中ForeignKeyChecks = true （默认值为true）
table options engine CREATE TABLE [IF NOT EXISTS] tbl_name (c CHAR(20)) ENGINE=InnoDB; 支持 支持
auto_increment CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=100; 支持 支持
character set CREATE TABLE [IF NOT EXISTS] tbl_name (c CHAR(20)) CHARACTER SET=utf8; 支持 支持
collate CREATE TABLE [IF NOT EXISTS] tbl_name (c CHAR(20)) DEFAULT COLLATE=utf8_general_ci; 支持 支持
checksum CREATE TABLE [IF NOT EXISTS] tbl_name (c CHAR(20)) CHECKSUM=1; 支持 支持
comment CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT PRIMARY KEY) COMMENT=''; 支持 支持
compression CREATE TABLE [IF NOT EXISTS] tbl_name (id INT(11) AUTO_INCREMENT PRIMARY KEY) COMPRESSION='ZLIB'; 支持 支持
table partitioning hash CREATE TABLE [IF NOT EXISTS] tbl_name (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1);

CREATE TABLE [IF NOT EXISTS] tbl_name (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH ( YEAR(col3) );		 支持 支持
key CREATE TABLE [IF NOT EXISTS] tbl_name (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4; 支持 支持
linear key CREATE TABLE [IF NOT EXISTS] tbl_name (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5; 支持 支持
range CREATE TABLE [IF NOT EXISTS] tbl_name (year_col INT, some_data INT) PARTITION BY RANGE (year_col) (PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (2020), PARTITION p5 VALUES LESS THAN MAXVALUE); 支持 支持
range columns CREATE TABLE [IF NOT EXISTS] tbl_name (a INT NOT NULL, b INT NOT NULL) PARTITION BY RANGE COLUMNS(a,b) (PARTITION p1 VALUES LESS THAN (20,10), PARTITION p2 VALUES LESS THAN (50,MAXVALUE), PARTITION p3 VALUES LESS THAN (65,MAXVALUE), PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)); 支持 支持
list CREATE TABLE [IF NOT EXISTS] tbl_name (id INT, name VARCHAR(35)) PARTITION BY LIST (id) (PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)); 支持 支持
like statement tracked to tracked CREATE TABLE new_1 LIKE old_1; 支持 支持 新表在目标端创建，后续数据传输正常
{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "old_1"
}，
{
"table_name": "new_1"
}
]
}
]
}
tracked to not tracked CREATE TABLE new_2 LIKE old_2; 支持 支持 新表不会在目标端创建

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "old_2"
}
]
}
]
}
not tracked to tracked CREATE TABLE new_3 LIKE old_3; 支持 支持 新表不会在目标端创建，DTLE会报错重启
old_3不在目标端，无法执行该语句

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "new_3"
}
]
}
]
}
not tracked to not tracked CREATE TABLE new_4 LIKE old_4; 支持 支持 新表不会在目标端创建

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables"": [
{
"table_name": "old_1"
}
]
}
]
}
alter table table options engine ALTER TABLE tbl_name ENGINE=InnoDB; / 支持
auto_increment ALTER TABLE tbl_name AUTO_INCREMENT=100; / 支持
character set ALTER TABLE tbl_name CHARACTER SET=utf8; / 支持
collate ALTER TABLE tbl_name COLLATE=utf8_general_ci; / 支持
checksum ALTER TABLE tbl_name CHECKSUM=1; / 支持
comment ALTER TABLE tbl_name COMMENT=''; / 支持
compression ALTER TABLE tbl_name COMPRESSION='ZLIB'; / 支持
adding and dropping columns add ALTER TABLE tbl_name ADD d CHAR(20); / 支持
add after ALTER TABLE tbl_name ADD d CHAR(20) AFTER b; / 支持
add first ALTER TABLE tbl_name ADD d CHAR(20) FIRST; / 支持
drop ALTER TABLE tbl_name DROP b; / 支持
add multiple ALTER TABLE tbl_name ADD (d CHAR(20), e INT(11)); / 支持
drop multiple ALTER TABLE tbl_name DROP b, DROP COLUMN c; / 支持
renaming, redefining, and reordering columns change name ALTER TABLE tbl_name CHANGE COLUMN b d INT(11) DEFAULT NULL; / 支持
change type ALTER TABLE tbl_name CHANGE b b BIGINT DEFAULT NULL; / 支持
modify type ALTER TABLE tbl_name MODIFY COLUMN b BIGINT DEFAULT NULL; / 支持
chang multiple ALTER TABLE tbl_name CHANGE b d INT(11) DEFAULT NULL, CHANGE c e BIGINT DEFAULT NULL; / 支持
modify multiple ALTER TABLE tbl_name MODIFY b BIGINT DEFAULT NULL, MODIFY c BIGINT DEFAULT NULL; / 支持
modify character set ALTER TABLE tbl_name MODIFY b TEXT CHARACTER SET utf8; / 支持
primary keys and indexes add primary key ALTER TABLE tbl_name ADD CONSTRAINT PRIMARY KEY (key_part);

ALTER TABLE tbl_name ADD PRIMARY KEY (key_part, key_part);		 / 支持
drop primary key ALTER TABLE tbl_name DROP PRIMARY KEY; / 支持
add key ALTER TABLE tbl_name ADD KEY (key_part);

ALTER TABLE tbl_name ADD INDEX index_name (key_part, key_part);		 / 支持
drop key ALTER TABLE tbl_name DROP PRIMARY KEY;

ALTER TABLE tbl_name DROP INDEX index_name;		 / 支持
add unique key ALTER TABLE tbl_name ADD UNIQUE INDEX (key_part);

ALTER TABLE tbl_name ADD CONSTRAINT UNIQUE KEY index_name (key_part, key_part);		 / 支持
drop unique key ALTER TABLE tbl_name DROP KEY id;

ALTER TABLE tbl_name DROP INDEX index_name;		 / 支持
foreign keys add foreign key ALTER TABLE tbl_name ADD CONSTRAINT symbol FOREIGN KEY (col_name) REFERENCES tbl_name(key_part) ON DELETE reference_option ON UPDATE reference_option;

ALTER TABLE tbl_name ADD CONSTRAINT symbol FOREIGN KEY (col_name) REFERENCES tbl_name(key_part) ON DELETE reference_option ON UPDATE reference_option;

ALTER TABLE tbl_name ADD CONSTRAINT FOREIGN KEY index_name (col_name) REFERENCES tbl_name(key_part) ON DELETE reference_option ON UPDATE reference_option;

ALTER TABLE tbl_name ADD FOREIGN KEY (col_name) REFERENCES tbl_name(key_part) ON DELETE reference_option ON UPDATE reference_option;

ALTER TABLE tbl_name ADD FOREIGN KEY (col_name) REFERENCES tbl_name(key_part);		 / 支持 应满足一下配置，否则在开启MTS的情况下不能保证数据一致性
1.目标端数据库@@foreign_key_checks = 1 （默认值为1）
2.dtle job 中ForeignKeyChecks = true （默认值为true）
drop foreign key ALTER TABLE tbl_name DROP FOREIGN KEY fk_child_5_1; / 支持 无法和MTS同时使用：
https://github.com/actiontech/dtle/issues/795#issuecomment-961786003
changing the character set modify character set ALTER TABLE tbl_name MODIFY b TEXT CHARACTER SET utf8; / 支持
convert to ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8; / 支持
rename rename index ALTER TABLE tbl_name RENAME INDEX index_name_old to index_name_new; / 支持
rename key ALTER TABLE tbl_name RENAME KEY index_name_old toindex_name_new; / 支持
tracked to tracked ALTER TABLE old_1 RENAME TO new_1; / 支持 重命名成功，后续数据传输正常

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "old_1"
}，
{
"table_name": "new_1"
}
]
}
]
}
tracked to not tracked ALTER TABLE old_2 RENAME AS new_2; / 支持 重命名成功，后续数不应据传到目标端

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "old_2"
}
]
}
]
}
not tracked to tracked ALTER TABLE old_3 RENAME AS new_3; / 支持 因目标端没有改名前的表，会有ERROR log
old_3不在目标端，无法执行该语句

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "new_3"
}
]
}
]
}
not tracked to not tracked ALTER TABLE old_4 RENAME TO new_4; / 支持 目标端不会有重命名后的表

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "old_1"
}
]
}
]
}
table partitioning ADD PARTITION ALTER TABLE tbl_name PARTITION BY HASH(expr) PARTITIONS num;

ALTER TABLE tbl_name ADD PARTITION (PARTITION partition_names VALUES LESS THAN (MAXVALUE));		 / 支持
DROP PARTITION ALTER TABLE tbl_name DROP PARTITION partition_names; / 支持
drop table basic DROP TABLE tbl_name; / 支持
if exists DROP TABLE IF EXISTS tbl_name RESTRICT;

DROP TABLE IF EXISTS not_exists_tbl_name CASCADE;		 / 支持
drop multiple DROP TABLE IF EXISTS not_exists_tbl_name CASCADE;

DROP TABLE IF EXISTS tbl_name_1, tbl_name_2, not_exists_tbl_name;		 / 支持
## 以INDEX为对象的DDL
DDL类型 DDL语句示例 全量 增量 备注
CREATE INDEX basic CREATE INDEX key_t1_1 ON t1 (id); 支持 支持
prefix key CREATE INDEX key_t2_1 ON t2 (a(2));

CREATE UNIQUE INDEX key_t3_1 ON t3 (a(3));		 支持 支持
unique CREATE UNIQUE INDEX key_t4_1 ON t4 (id); 支持 支持
unique not null CREATE UNIQUE INDEX key_t5_1 ON t5 (id); 支持 支持
unique multiple CREATE UNIQUE INDEX key_t6_1 ON t6 (a, b); 支持 支持
fulltext CREATE FULLTEXT INDEX full_t7_1 ON t7 (a); 支持 支持
index_type CREATE INDEX key_t8_1 ON t8 (id) USING BTREE; 支持 支持
comment CREATE INDEX key_t9_1 ON t9 (id) COMMENT 'test comment'; 支持 支持
DROP INDEX DROP INDEX key_t1_1 ON t1; 支持 支持

其他对象DDL

DDL类型 DDL语句示例 全量 增量 备注
RENAME tracked to tracked RENAME TABLE old_1 to new_1; / 支持 重命名成功，后续数据传输正常

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "old_1"
}，
{
"table_name": "new_1"
}
]
}
]
}"
tracked to not tracked RENAME TABLE old_2 to new_2; / 支持 重命名成功，后续数不应据传到目标端

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "old_2"
}
]
}
]
}
not tracked to tracked RENAME TABLE old_3 to new_3; / 支持 因目标端没有改名前的表，会有ERROR log
old_3不在目标端，无法执行该语句

{
"replicate_do_db": [
{
"table_schema": "action_db_1",
"tables": [
{
"table_name": "new_3"
}
]
}
]
}
not tracked to not tracked RENAME TABLE old_4 to new_4; / 支持 目标端不会有重命名后的表

{
""replicate_do_db"": [
{
""table_schema"": ""action_db_1"",
""tables"": [
{
""table_name"": ""old_1""
}
]
}
]
}
rename multiple RENAME TABLE old_5 to new_5, old_6 to new_6; / 支持
TRUNCATE TRUNCATE tbl_name;

TRUNCATE TABLE tbl_name;		 / 支持
VIEW CREATE VIEW CREATE VIEW view_name AS select_statement;

CREATE OR REPLACE VIEW view_name AS select_statement;"		 不支持 不支持
ALTER VIEW ALTER VIEW view_name (column_list) AS select_statement; / 不支持
DROP VIEW DROP VIEW view_name RESTRICT;

DROP VIEW IF EXISTS view_name RESTRICT;

DROP VIEW IF EXISTS view_name CASCADE;

DROP VIEW view_name_1, view_name_2;		 / 不支持
FUNCTION CREATE FUNCTION CREATE FUNCTION sp_name() RETURNS type characteristic routine_body;

CREATE FUNCTION sp_name(func_parameters) RETURNS type characteristic RETURN routine_body;		 不支持 支持 1. [MySQL 5]目标端账户需要CREATE ROUTINE, SUPER权限
2. [MySQL 8] 目标端账户需要CREATE ROUTINE, SET_USER_ID, SYSTEM_USER权限, SET GLOBAL log_bin_trust_function_creators = ON;
3. job配置ExpandSyntaxSupport=true
4. 全量不复制function
ALTER FUNCTION ALTER FUNCTION sp_name SQL SECURITY DEFINER;

ALTER FUNCTION sp_name COMMENT '';		 / 支持
DROP FUNCTION DROP FUNCTION sp_name;

DROP FUNCTION IF EXISTS sp_name;

DROP FUNCTION IF EXISTS not_exists_sp_name;		 / 支持
PROCEDURE CREATE PROCEDURE CREATE PROCEDURE sp_name() characteristic routine_body;

CREATE PROCEDURE sp_name(proc_parameters) characteristic routine_body;		 不支持 支持 1. [MySQL 5]目标端账户需要CREATE ROUTINE, SUPER权限
2. [MySQL 8] 目标端账户需要CREATE ROUTINE, SET_USER_ID, SYSTEM_USER权限, SET GLOBAL log_bin_trust_function_creators = ON;
3. job配置ExpandSyntaxSupport=true
4. 全量不复制创建procedure
ALTER PROCEDURE ALTER PROCEDURE sp_name SQL SECURITY DEFINER;

ALTER PROCEDURE sp_name COMMENT '';		 / 支持
DROP PROCEDURE DROP PROCEDURE sp_name;

DROP PROCEDURE IF EXISTS sp_name;

DROP PROCEDURE IF EXISTS not_exists_sp_name;		 / 支持
EVENT CREATE EVENT CREATE EVENT event_name ON SCHEDULE schedule COMMENT '' DO event_body; 不支持 不支持 1.不支持复制event
2.源端event产生的数据会复制到目标端
ALTER EVENT ALTER EVENT event_name RENAME TO event_name_new; / 不支持
DROP EVENT DROP EVENT IF EXISTS not_exists_event_name;

DROP EVENT IF EXISTS event_name;

DROP EVENT event_name;		 / 不支持
TRIGGER CREATE TRIGGER CREATE TRIGGER trigger_name BEFORE INSERT on old FOR EACH ROW trigger_body;

CREATE TRIGGER trigger_name AFTER INSERT on old FOR EACH ROW trigger_body;

CREATE TRIGGER trigger_name BEFORE UPDATE on old FOR EACH ROW trigger_body;

CREATE TRIGGER trigger_name AFTER UPDATE on old FOR EACH ROW trigger_body;

CREATE TRIGGER trigger_name BEFORE DELETE on old FOR EACH ROW trigger_body;

CREATE TRIGGER trigger_name AFTER DELETE on old FOR EACH ROW trigger_body;		 不支持 不支持 1.不支持复制trigger
2. 源端trigger产生的数据会复制到目标端
DROP TRIGGER DROP TRIGGER trigger_name;

DROP TRIGGER IF EXISTS trigger_name;

DROP TRIGGER schema_name.trigger_name;		 / 不支持

关于不支持 Trigger、Event

由于Trigger或Event可能会更改表数据，目标端存在Trigger或Event时，存在二次触发的问题（源端已经触发过一次），会引起数据不一致，故dtle不复制Trigger和Event。源端Trigger/Event变更的数据，会被写进binlog，并由dtle复制到目标端。

results matching ""

    No results matching ""