1.3.3.3. DML
1.3.3.3.1. GaussDB-Oracle
1.3.3.3.1.1. 主键或唯一约束冲突时更新该条数据，否则插入数据
语法
REPLACE INTO table_name(column_name [, …])
VALUES(…) [,(…)]
| SELECT_SUB_QUERY
警告
由于GaussDB-Oracle 505.2版本已支持replace into语句，因此不进行转换。 非505.2版本，统一SQL会将REPLACE INTO转为INSERT INTO…ON DUPLICATE KEY UPDATE，限制如下：
ON DUPLICATE KEY UPDATE执行更新操作，而REPLACE INTO执行删除和插入操作；
ON DUPLICATE KEY UPDATE只更新重复键所在行的列值，而REPLACE INTO插入完全新的数据；
ON DUPLICATE KEY UPDATE保留了非唯一键、主键的原始值，而REPLACE INTO将完全替换整行数据；
ON DUPLICATE KEY UPDATE不会重置自增字段的值，而REPLACE INTO会重置自增字段的值；
ON DUPLICATE KEY UPDATE 依赖REPLACE INTO语句中明确的列名。
示例
-- 转换前MySQL SQL:
replace into user_test(id,name) value (1,'zhangsan');
replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2;
-- 转换后GaussDB-Oracle SQL:
INSERT INTO user_test (id,name) VALUES (1,'zhangsan') ON DUPLICATE KEY UPDATE id=VALUES(id),name=VALUES(name);
INSERT INTO tb1 (name,title,mood) SELECT rname,rtitle,rmood FROM tb2 ON DUPLICATE KEY UPDATE name=VALUES(name),title=VALUES(title),mood=VALUES(mood);
-- 505.2版本转换前MySQL SQL
REPLACE INTO students_1 (id,name,age) VALUES (4,'yodo',18);
REPLACE INTO students_1 (id,name,age) VALUES (1,'tom',18),(2,'bob',19),(3,'nick',22);
REPLACE INTO orders_summary (customer_id,total_amount) SELECT customer_id AS customer_id,sum(amount) AS total_amount FROM orders_1 WHERE order_date=2025 GROUP BY customer_id HAVING total_amount>1000 ORDER BY total_amount DESC;
-- 505.2版本转换后GaussDB-Oracle SQL
REPLACE INTO students_1 (id,name,age) VALUES (4,'yodo',18);
REPLACE INTO students_1 (id,name,age) VALUES (1,'tom',18),(2,'bob',19),(3,'nick',22);
REPLACE INTO orders_summary (customer_id,total_amount) SELECT customer_id AS customer_id,sum(amount) AS total_amount FROM orders_1 WHERE order_date=2025 GROUP BY customer_id HAVING total_amount>1000 ORDER BY total_amount DESC;
1.3.3.3.1.2. 主键或唯一约束冲突时更新该条数据，否则插入数据
语法
INSERT INTO table_name [(column1, ...)] VALUES (value1, ...) ON DUPLICATE KEY UPDATE column1=value2 [, ...];
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
);
INSERT INTO test_table (id, name, age) VALUES (1, 'John', 25);
-- 转换前MySQL SQL:
INSERT INTO test_table (id, name, age) values (1, 'Make', 30) ON DUPLICATE KEY UPDATE name = 'Jam', age = 20;
-- 转换后GaussDB-Oracle SQL:
INSERT INTO test_table (id, name, age) VALUES (1, 'Make', 30) ON DUPLICATE KEY UPDATE name = 'Jam', age = 20;
1.3.3.3.1.3. 主键或唯一约束冲突时忽略不插入数据
语法
INSERT IGNORE INTO table_name [(column1, ...)] VALUES (value1, ...) [, ...];
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
);
INSERT INTO test_table (id, name, age) VALUES (1, 'John', 25);
-- 转换前MySQL SQL:
INSERT IGNORE INTO test_table (id, name, age) values (1, 'Make', 30);
-- 转换后GaussDB-Oracle SQL:
INSERT INTO test_table (id, name, age) VALUES (1, 'Make', 30) ON DUPLICATE KEY UPDATE NOTHING;
1.3.3.3.1.4. 联表更新
语法
UPDATE [IGNORE] table1 [, table2] SET column1=value1 [, column2=value2] [where ...];
警告
不支持with子句组合；
联表更新时最多支持两张表更新；
当两张表更新时，SET列值时，列名必须带表名或表别名，例如 update…set a.id=1 或 update…set test_table.id=2。
UPDATE IGNORE xxx，转化时候可通过配置项[unisql.remove.ignore]的配置决定是否移除IGNORE
对于gaussdb-oracle 505.2 版本，不对多表更新进行转换，因为此版本已支持多表更新（需要设置unisql.target.database.version = 5050200）
对于gaussdb-oracle 505.2 版本，配置项unisql.remove.ignore=0，则 update ignore 多表更新转换不支持
对于gaussdb-oracle 505.2 版本，配置项unisql.remove.ignore=0，update ignore仅支持唯一约束，check约束、非空约束、外键约束等均不支持
对于gaussdb-oracle 505.2 版本，配置项unisql.remove.ignore=0，update ignore将唯一约束列批量修改为相同值时，行为和mysql不一致，mysql会修改第一行数据，其他行数据由于唯一约束冲突，则不修改；统一SQL转换实现不会修改任何行记录。
对于gaussdb-oracle 505.2 版本，配置项unisql.remove.ignore=0，update ignore修改唯一约束列，自己和自己冲突也不会更新，如 update ignore ignore_test set a=1,b=2 where a=1，a列为唯一约束，则b列也不更新。
对于gaussdb-oracle 505.2 版本，配置项unisql.remove.ignore=0，update ignore不支持将列值设置为default，如 update ignore ignore_test set a=1,b=default where id=1
示例
-- 转换前MySQL SQL:
update t1 a, t2 b set a.name = 'join', b.name = 'make' where a.id = 5 and b.id = 6;
update t1 set name = 'join', age = '20' where id = 5;
-- 转换后GaussDB-Oracle SQL:
WITH sub_update_t2 AS (UPDATE t2 AS b SET b.name='make' FROM t1 AS a WHERE a.id=5 AND b.id=6 RETURNING b.name) UPDATE t1 AS a SET a.name='join' FROM t2 AS b WHERE a.id=5 AND b.id=6;
UPDATE t1 SET name='join', age='20' WHERE id=5;
-- 转换后GaussDB-Oracle505.2 SQL:
-- 配置项：unisql.target.database.version = 5050200
update t1 a, t2 b set a.name = 'join', b.name = 'make' where a.id = 5 and b.id = 6;
update t1 set name = 'join', age = '20' where id = 5;
-- 转换前MySQL SQL:
UPDATE IGNORE test_update_ignore
SET unique_column = 20, value_column = 'Updated Value'
WHERE id = 3;
-- 转换后GaussDB-Oracle SQL:
-- 配置项：unisql.remove.ignore=1
UPDATE test_update_ignore SET unique_column=20, value_column='Updated Value' WHERE id=3
-- 配置项：unisql.remove.ignore=0
UPDATE IGNORE test_update_ignore SET unique_column=20, value_column='Updated Value' WHERE id=3
-- 转换前MySQL SQL:列 a 为唯一约束
update ignore update_ignore_34513 set a1=4 where id =3
-- 转换后GaussDB-Oracle505.2 SQL:
-- 配置项：unisql.remove.ignore=0
-- 配置项：unisql.target.database.version = 5050200
WITH source_table AS (
SELECT
update_ignore_34513.ctid AS rid,
4 AS v0,
row_number() OVER (PARTITION BY update_ignore_34513.ctid
ORDER BY
update_ignore_34513.ctid) AS rn
FROM
update_ignore_34513
WHERE
id = 3)
UPDATE
update_ignore_34513 AS target_table SET
target_table.a1 = source_table.v0
FROM
source_table
WHERE
id = 3
AND NOT EXISTS (
SELECT
1
FROM
update_ignore_34513 filter_table
WHERE
filter_table.a1 = source_table.v0)
1.3.3.3.1.5. 表删除
语法
-- 单表删除
DELETE FROM tbl_name [[AS] tbl_alias]
[WHERE where_condition]
[ORDER BY expr_col [ DESC | ASC ][,expr_col [ DESC | ASC ]]...]
[LIMIT row_count]
-- 多表删除
DELETE tbl_name [, tbl_name] ...
FROM table_references
[WHERE where_condition]
警告
- 多表删除目前限制：
where 中不支持order by、limit子句
不支持USING
联表删除单表时，delete 后面的名称必须和被删除的表名或别名完全一致
联表数量小于3张时，联表删除，必须是单表，不能是子查询
关联表为2张时，联表条件必须满足唯一性，不支持删除的表数量大于2；只支持cross join 和left join(left join时删除的表必须是第一个表的别名、连接的表必须是2个且都带有别名、必须是第一个表左连接第二个表)；删除一张表时，删除的表必须是关联的第一张表；转换成with时with目前不支持双引号，因此删除的表名不能是关键字
关联表超过3张时，暂不支持
关联表为3张时，第一张表应为单表，只支持cross join，删除的表必须是关联的第一张表，子查询只能是一层
不支持.*
示例
-- 转换前MySQL SQL:
DELETE from test_table limit 10;
delete a from test_table a where a.id = 1;
delete a,b from test_table a,test_table b where a.id = b.id;
DELETE a FROM tb_ci_base_object a, tb_ci_communication_endpoint b WHERE a.instance_id = b.instance_id AND b.instance_id = '1';
DELETE tb_ci_base_object, tb_ci_communication_endpoint FROM tb_ci_base_object , tb_ci_communication_endpoint WHERE tb_ci_base_object.instance_id = tb_ci_communication_endpoint.instance_id AND tb_ci_communication_endpoint.instance_id = '1';
delete tb_rpa_flow_reference from tb_rpa_flow_reference,(select distinct to_flow_id from tb_rpa_flow_reference where root_flow_id = '1' union select '1' from dual) as b, (select distinct root_flow_id from tb_rpa_flow_reference where to_flow_id = '1' union select '1' from dual) as c where tb_rpa_flow_reference.from_flow_id = b.to_flow_id and tb_rpa_flow_reference.root_flow_id = c.root_flow_id;
delete from delete_test where id=1 order by id limit 1;
-- 转换后GaussDB-Oracle SQL:
DELETE from test_table limit 10;
DELETE FROM test_table AS a WHERE a.id=1;
delete a,b from test_table a,test_table b where a.id = b.id;
DELETE FROM tb_ci_base_object AS a USING tb_ci_communication_endpoint AS b WHERE a.instance_id=b.instance_id AND b.instance_id='1';
DELETE tb_ci_base_object, tb_ci_communication_endpoint FROM tb_ci_base_object , tb_ci_communication_endpoint WHERE tb_ci_base_object.instance_id = tb_ci_communication_endpoint.instance_id AND tb_ci_communication_endpoint.instance_id = '1';
DELETE FROM tb_rpa_flow_reference USING (SELECT DISTINCT to_flow_id FROM tb_rpa_flow_reference WHERE root_flow_id='1' UNION SELECT '1' FROM sys_dummy) AS b CROSS JOIN (SELECT DISTINCT root_flow_id FROM tb_rpa_flow_reference WHERE to_flow_id='1' UNION SELECT '1' FROM sys_dummy) AS c WHERE tb_rpa_flow_reference.from_flow_id=b.to_flow_id AND tb_rpa_flow_reference.root_flow_id=c.root_flow_id;
DELETE FROM delete_test WHERE id=1 ORDER BY id LIMIT 1;
-- 转换前MySQL SQL:
DELETE FROM mysql.user WHERE user = 'xxx';
-- 目前GaussDB-Oracle不支持删除数据库用户，转换报错(CONVERT ERROR)，不支持(not support)，具体如下：
(CONVERT ERROR)
[ [00002]not support error: not support ]
1.3.3.3.1.6. 分页查询
语法
SELECT ... FROM ... LIMIT [offset,] row_count;
警告
offset取值从0开始，表从offset+1条记录行开始检索，rows代表取出rows条数据； 当offset不指定时，默认从第一条记录开始，取出rows行。
示例
-- 转换前MySQL SQL:
SELECT * FROM test_table LIMIT 5, 10;
SELECT * FROM test_table LIMIT 10;
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM test_table LIMIT 5, 10;
SELECT * FROM test_table LIMIT 10;
1.3.3.3.1.7. 强制索引查询
语法
SELECT ... FROM ... FORCE INDEX(index_name [,index_name]...) WHERE ...;
警告
对于gaussdb自身不支持的SQL语法，统一SQL不做转换，例如gaussdb中cross join无需带on条件，inner join 必须带on条件，而mysql中无此限制。
示例
-- 转换前MySQL SQL:
SELECT * FROM employees FORCE INDEX (department_idx) WHERE department_id = 10;
select
tb.name,
tb.instance_id,
f.dest_instance_id,
f.source_instance_id
from
tb_ci_concrete_collection f force index(idx_source_instance)
left join tb_ci_base_object tb on
tb.instance_id = f.instance_id;
-- 转换后GaussDB-Oracle SQL:
SELECT /*+ IndexScan(employees department_idx)*/ * FROM employees WHERE department_id = 10;
SELECT /*+ IndexScan(f idx_source_instance)*/ tb.name,tb.instance_id,f.dest_instance_id,f.source_instance_id
FROM tb_ci_concrete_collection AS f LEFT JOIN tb_ci_base_object AS tb ON tb.instance_id = f.instance_id;
-- 转换前MySQL SQL，force index 支持多个索引
select
tb.name,
tb.instance_id,
f.dest_instance_id,
f.source_instance_id
from
tb_ci_concrete_collection f force index(idx_source_instance,idx_instance_id)
left join tb_ci_base_object tb on
tb.instance_id = f.instance_id;
-- 转换后GaussDB-Oracle SQL:
SELECT /*+ IndexScan(f idx_source_instance) IndexScan(f idx_instance_id)*/ tb.name,tb.instance_id,f.dest_instance_id,f.source_instance_id
FROM tb_ci_concrete_collection AS f LEFT JOIN tb_ci_base_object AS tb ON tb.instance_id=f.instance_id
1.3.3.3.1.8. 查询分区表信息
语法
SELECT ... FROM INFORMATION_SCHEMA.PARTITIONS WHERE ...;
示例
-- 转换前MySQL SQL:
SELECT MAX(partition_description) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'test_table';
-- 转换后GaussDB-Oracle SQL:
SELECT MAX(partition_description) FROM unisql.information_schema_partitions WHERE TABLE_SCHEMA='public' AND TABLE_NAME='test_table';
1.3.3.3.1.9. 空值判断
语法
警告
对于update、delete、select的where条件中对于列的判断条件，如果列值为空，则转化为IS NULL，如果列值不为空，则转化为IS NOT NULL。 因为在gaussdb-oracle中，null与空值’’等价，此行为与mysql有差异（在mysql中，null与空值’’不等价）；
对于select的where条件中对于列等于的判断条件，如果列值等于绑定变量，如绑定变量的值为空，在配置文件unisql.conf中，配置unisql.null.table.columns表名和列名。在Java应用中，会将 where columnName = ? 转化成 where (columnName =? or (cast ? as dataType) is null and columnName is null)；这里的dataType指字符的数据类型，本功能结合元数据来推导 列的数据类型，如果不同表名中有相同的列，且列的数据类型不同、不同表名在sql中有相同的别名，且列前面有这个相同的别名，可能会出现误转。 具体使用请参考 unisql.conf文件说明。
对于select的where条件中对于列不等于的判断条件，列值不等于绑定变量的转化功能暂不支持。
示例
-- 转换前MySQL SQL:
select * from tb_sys_param where status>0 and field !='' and field is not null;
select * from tb_sys_param where status>0 and field ='' and field is null;
-- 转换后GaussDB-Oracle SQL:
SELECT * FROM tb_sys_param WHERE status>0 AND field IS NOT NULL AND field IS NOT NULL;
SELECT * FROM tb_sys_param WHERE status>0 AND field IS NULL AND field IS NULL;
-- 准备数据：
CREATE TABLE table2 (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100),
content longtext,
created_at TIMESTAMP
);
INSERT INTO table2 (title, content, created_at) VALUES
(null, '', CURRENT_TIMESTAMP),
('', null, CURRENT_TIMESTAMP),
('', 'Content3', CURRENT_TIMESTAMP),
('Title4', null, CURRENT_TIMESTAMP),
('Title5', 'Content5', CURRENT_TIMESTAMP),
('Title6', 'Content6', CURRENT_TIMESTAMP),
('Title7', 'Content7', CURRENT_TIMESTAMP),
('Title8', 'Content8', CURRENT_TIMESTAMP),
('Title9', 'Content9', CURRENT_TIMESTAMP),
('Title10', 'Content10', CURRENT_TIMESTAMP);
-- 配置文件unisql.conf中，配置unisql.null.table.columns=table2:title;转换前MySQL SQL:
select
id,
title,
content,
created_at
from
table2
where
title = ? or content = ? order by id;
-- 转换后GaussDB-Oracle SQL:
SELECT id,title,content,created_at FROM table2 WHERE (title=? OR (CAST(? AS varchar) IS NULL AND title IS NULL)) OR content=? ORDER BY id
1.3.3.3.1.10. 删除用户
语法
DELETE FROM mysql.user WHERE user = 'user_name';
警告
受配置参数控制，config/unisql.conf中unisql.replace.sql参数可配置需要替换的sql语句。
示例
-- 转换前MySQL SQL:
DELETE FROM mysql.user WHERE user = '' or user = null;
-- 转换后GaussDB-Oracle SQL（unisql.replace.sql = 'select 1'）:
select 1;
1.3.3.3.2. GoldenDB-Mysql
1.3.3.3.2.1. REPLACE INTO
语法
REPLACE INTO table_name(column_name [, …])
VALUES(…) [,(…)]
| SELECT_SUB_QUERY
警告
- 统一SQL会将REPLACE INTO转为MERGE INTO，限制如下：
需要开启参数
unisql.transform.replace.into并配合 元数据管理 功能使用;目前仅支持源数据库为Mysql，目标数据库为GoldenDB-Mysql的场景;
目标表至少有一个唯一约束，且table_name(column_name [, …]) 的column_name包含至少一个唯一约束的所有列;
优先使用第一个约束名称为PRIMARY的约束，GoldenDB-Mysql中主键约束默认名称为PRIMARY;
当merge into 有多个唯一约束时，可能出现因其他约束冲突导致转化后的sql执行失败的问题;
values表达式只支持字面值，不支持子查询、函数、别名等。
示例
-- 转换前MySQL SQL:
REPLACE INTO B (id,name, value)
SELECT id as dd, 1, 'f' as ff FROM C;
-- 转换后GoldenDB-Mysql SQL:
MERGE INTO `B` AS `target_table`
USING (SELECT `id` AS `dd`,1 AS `name`,'f' AS `ff` FROM `C`) AS `source_table`
ON
(`target_table`.`id`=`source_table`.`dd`)
WHEN MATCHED THEN UPDATE SET
`name`=`source_table`.`name`,
`value`=`source_table`.`ff`
WHEN NOT MATCHED THEN
INSERT (`id`,`name`,`value`)
VALUES (`source_table`.`dd`,`source_table`.`name`,`source_table`.`ff`)
-- 转换前MySQL SQL:
REPLACE INTO B (id,name, value)
SELECT id, name, value FROM A WHERE A.ID=21 union
SELECT id, name, value FROM C WHERE C.ID=31;
-- 转换后GoldenDB-Mysql SQL:
MERGE INTO `B` AS `target_table`
USING
(SELECT `id` AS `id`,`name` AS `name`,`value` AS `value` FROM `A` WHERE `A`.`ID`=21
UNION
SELECT `id` AS `id`,`name` AS `name`,`value` AS `value` FROM `C` WHERE `C`.`ID`=31)
AS `source_table`
ON (`target_table`.`id`=`source_table`.`id`)
WHEN MATCHED THEN UPDATE
SET `name`=`source_table`.`name`,
`value`=`source_table`.`value`
WHEN NOT MATCHED THEN INSERT (`id`,`name`,`value`)
VALUES (`source_table`.`id`,`source_table`.`name`,`source_table`.`value`)
1.3.3.3.2.2. INSERT INTO … ON DUPLICATE KEY UPDATE
语法
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ SELECT ...
| table_name
}
[ON DUPLICATE KEY UPDATE assignment_list]
警告
- 统一SQL会将INSERT INTO … ON DUPLICATE KEY UPDATE 转为MERGE INTO，限制如下：
需要开启参数
unisql.transform.replace.into并配合 元数据管理 功能使用;目前仅支持源数据库为Mysql，目标数据库为GoldenDB-Mysql的场景;
主键自增问题,mysql中冲突后修改，自增键会跳跃增长。merge into只是修改，自增键不会增加。两边库的自增键会对应不上
插入的列必须有主键或唯一键（如果是联合主键或联合唯一键，联合的列需要全部写出；多个唯一键时所有唯一键都要列出），否则merge into 中on关联条件无法获取
mysql多个唯一键冲突时（不同的行数据）只会更新第一个冲突的唯一键所在行。merge into 中如果存在多个唯一键用or连接，冲突的行都会更新。两边数据结果不一致
insert into SELECT 子句查询出来的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）
merge into 中 on 关联条件组成：联合主键中的列之间、联合唯一键中的列之间用and连接，主键和唯一键之间、唯一键和唯一键之间用or连接;（用or连接目标库冲突的行都会更新，mysql只会更新第一个冲突所在行）
不支持更新多列， update （column,cloumn）=expr,expr
values表达式只支持字面值，不支持子查询、函数、别名等。
示例
-- 数据准备，使用前要加载元数据
CREATE TABLE `unisql_insert_duplicate_key_update_1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`key` varchar(63) unique NOT null DEFAULT 'null' COMMENT '属性英文名',
`name` varchar(63) DEFAULT NULL COMMENT '属性描述',
`type` varchar(31) DEFAULT NULL COMMENT '类型',
`length` int(11) DEFAULT NULL COMMENT '长度',
`class_id` varchar(63) DEFAULT NULL COMMENT '所属类',
`mark_as_deleted` tinyint(1) DEFAULT NULL COMMENT '是否标记为删除',
`store_place` int(11) DEFAULT NULL COMMENT '存储位置',
`editable` tinyint(1) DEFAULT NULL COMMENT '是否可编辑',
`validate` varchar(512) DEFAULT NULL COMMENT '检验规则',
`user_defined` tinyint(1) DEFAULT NULL COMMENT '是否是自定义字段',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `unisql_insert_duplicate_key_update_1` (`key`, `name`, `type`, `length`, `class_id`, `mark_as_deleted`, `store_place`, `editable`, `validate`) VALUES
( 'encode', '编码', 'enum', '63', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL),
( 'content', '内容', 'text', '0', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL),
( 'file_path', '路径', 'varchar', '1023', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL),
( 'file_extend', '格式', 'enum', '63', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL),
( 'file_live_host', '宿主主机', 'varchar', '63', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL),
( 'file_size', '文件大小', 'double', '0', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL),
( 'keyword', '关键字', 'varchar', '1023', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL),
( 'file_nature', '文件属性', 'enum', '31', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL),
( 'file_system', '文件系统', 'varchar', '31', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL);
-- 转换前MySQL SQL:
INSERT INTO `unisql_insert_duplicate_key_update_1` (`key`, `name`, `type`, `length`, `class_id`, `mark_as_deleted`, `store_place`, `editable`, `validate`) VALUES
( 'encode', '编码1', 'enum', '63', 'SEE.SYSTEM_FILE', '0', '1', '1', NULL)
ON DUPLICATE KEY UPDATE name=VALUES(name),`type`=VALUES(`type`),`length`=VALUES(`length`);
-- 转换后GoldenDB-Mysql SQL:
MERGE INTO `unisql_insert_duplicate_key_update_1` AS `unisql_insert_duplicate_key_update_1`
USING (SELECT 'encode' AS `key`,'编码1' AS `name`,'enum' AS `type`,'63' AS `length`,'SEE.SYSTEM_FILE' AS `class_id`,'0' AS `mark_as_deleted`,'1' AS `store_place`,'1' AS `editable`,NULL AS `validate` FROM DUAL)
AS `source_table`
ON (`unisql_insert_duplicate_key_update_1`.`key`=`source_table`.`key`)
WHEN MATCHED THEN
UPDATE SET `unisql_insert_duplicate_key_update_1`.`name`=`source_table`.`name`,`unisql_insert_duplicate_key_update_1`.`type`=`source_table`.`type`,
`unisql_insert_duplicate_key_update_1`.`length`=`source_table`.`length`
WHEN NOT MATCHED THEN
INSERT (`key`,`name`,`type`,`length`,`class_id`,`mark_as_deleted`,`store_place`,`editable`,`validate`)
VALUES (`source_table`.`key`,`source_table`.`name`,`source_table`.`type`,`source_table`.`length`,`source_table`.`class_id`,`source_table`.`mark_as_deleted`,`source_table`.`store_place`,
`source_table`.`editable`,`source_table`.`validate`)
-- 转换前MySQL SQL:
INSERT INTO `unisql_insert_duplicate_key_update_1` (`key`,`name`, `type`, `length`, `class_id`, `mark_as_deleted`, `store_place`, `editable`, `validate`)
select 'encode','编码' as name, 'enum1', '63', 'SEE.SYSTEM_FILE', '1', '0', '0', null from dual union
select 'encode','编码' as name, 'enum1', '64', 'SEE.SYSTEM_FILE', '1', '0', '0', null from dual
ON DUPLICATE KEY UPDATE name=VALUES(name),`type`=VALUES(`type`),`length`=VALUES(`length`),mark_as_deleted=unisql_insert_duplicate_key_update_1.mark_as_deleted,store_place= VALUES(store_place),validate= VALUES(validate);
-- 转换后GoldenDB-Mysql SQL:
MERGE INTO `unisql_insert_duplicate_key_update_1` AS `unisql_insert_duplicate_key_update_1`
USING (SELECT 'encode' AS `key`,'编码' AS `name`,'enum1' AS `type`,'63' AS `length`,'SEE.SYSTEM_FILE' AS `class_id`,'1' AS `mark_as_deleted`,'0' AS `store_place`,'0' AS `editable`,NULL AS `validate` FROM dual
UNION
SELECT 'encode' AS `key`,'编码' AS `name`,'enum1' AS `type`,'64' AS `length`,'SEE.SYSTEM_FILE' AS `class_id`,'1' AS `mark_as_deleted`,'0' AS `store_place`,'0' AS `editable`,NULL AS `validate` FROM dual)
AS `source_table`
ON (`unisql_insert_duplicate_key_update_1`.`key`=`source_table`.`key`)
WHEN MATCHED THEN
UPDATE SET `unisql_insert_duplicate_key_update_1`.`name`=`source_table`.`name`,`unisql_insert_duplicate_key_update_1`.`type`=`source_table`.`type`,`unisql_insert_duplicate_key_update_1`.`length`=`source_table`.`length`,
`unisql_insert_duplicate_key_update_1`.`mark_as_deleted`=`unisql_insert_duplicate_key_update_1`.`mark_as_deleted`,`unisql_insert_duplicate_key_update_1`.`store_place`=`source_table`.`store_place`,
`unisql_insert_duplicate_key_update_1`.`validate`=`source_table`.`validate`
WHEN NOT MATCHED THEN
INSERT (`key`,`name`,`type`,`length`,`class_id`,`mark_as_deleted`,`store_place`,`editable`,`validate`)
VALUES (`source_table`.`key`,`source_table`.`name`,`source_table`.`type`,`source_table`.`length`,`source_table`.`class_id`,`source_table`.`mark_as_deleted`,
`source_table`.`store_place`,`source_table`.`editable`,`source_table`.`validate`)
1.3.3.3.3. LightDB-Mysql
1.3.3.3.3.1. INSERT INTO
语法
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} [ROW] ( value_list) [, [ROW] (value_list)] ... }
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
INSERT INTO test_table (id, name, age) VALUES row (1, 'John', 25),row (2,'Tom',18);
-- 转换后LightDB-Mysql SQL:
INSERT INTO "test_table" ("id","name","age") VALUES (1,'John',25),(2,'Tom',18)
1.3.3.3.3.2. INSERT INTO … ON DUPLICATE KEY UPDATE
语法
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ SELECT ...
| table_name
}
[ON DUPLICATE KEY UPDATE assignment_list]
警告
- 统一SQL会将INSERT INTO … ON DUPLICATE KEY UPDATE 转为INSERT INTO … ON CONFLICT …，限制如下：
需要配合 元数据管理 功能使用;
目前仅支持源数据库为Mysql，目标数据库为LightDB-Mysql的场景;
插入的表必须有主键或唯一键，否则ON CONFLICT 中冲突列无法获取;
冲突列会把主键和唯一键都会包含，所以当插入的组合列发生冲突时，更新现有记录;
assignment_list表达式右边支持vlaues函数转化为EXCLUDED相关列，其他表达式透传。
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
age INT
);
INSERT INTO test_table (id, name, age) VALUES (1, 'John', 25);
-- 转换前MySQL SQL:
INSERT INTO test_table (id, name, age) values (1, 'John', 30) ON DUPLICATE KEY UPDATE name = values(name), age = values(age);
-- 转换后LightDB-Mysql SQL:
INSERT INTO "t1" ("id","name","age") VALUES (1,'John', 30) ON CONFLICT ("id","name") DO UPDATE SET "name"=EXCLUDED."name","age"=EXCLUDED."age"
1.3.3.3.3.3. INSERT IGNORE INTO
语法
INSERT IGNORE INTO table_name [(column1, ...)] VALUES (value1, ...) [, ...];
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
);
INSERT INTO test_table (id, name, age) VALUES (1, 'John', 25);
-- 转换前MySQL SQL:
INSERT IGNORE INTO test_table (id, name, age) values (1, 'Make', 30);
-- 转换后LightDB-Mysql SQL:
INSERT INTO "test_table" ("id","name","age") VALUES (1,'Make',30) ON CONFLICT DO NOTHING
1.3.3.3.3.4. REPLACE INTO
语法
统一SQL会将REPLACE INTO 转为INSERT INTO … ON CONFLICT DO UPDATE SET…
REPLACE [INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
|
VALUES row_constructor_list
}
REPLACE
[INTO] tbl_name
SET assignment_list
REPLACE
[INTO] tbl_name
[(col_name [, col_name] ...)]
{SELECT ... | TABLE table_name}
value:
{expr | DEFAULT}
value_list:
value [, value] ...
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
警告
- 统一SQL会将REPLACE INTO 转为INSERT INTO … ON CONFLICT DO UPDATE SET…，限制如下：
需要配合 元数据管理 功能使用;
目前仅支持源数据库为MySQL，目标数据库为LightDB-MySQL的场景;
插入的表必须有主键或唯一键，不支持主键和唯一键同时存在，否则ON CONFLICT 中冲突列无法获取;
主键自增问题，MySQL中冲突后是先删除再插入，而LightDB-MySQL冲突后是更新列内容，所以两边库自增列会对应不上。
REPLACE INTO … select XXX … 时SELECT的列必须写明确，不能SELECT * ，并且SELECT 出来的列类型需要和插入表的列保持一致。
示例
CREATE TABLE tb_rolepower (
role_id varchar(32) NOT NULL,
asset_type varchar(64) DEFAULT NULL,
power_id varchar(64) NOT NULL,
sys_key varchar(32) NOT NULL DEFAULT 'acm',
PRIMARY KEY (role_id, power_id, sys_key)
);
INSERT INTO tb_rolepower (role_id, asset_type, power_id, sys_key) VALUES ('see_default_read_write_role', 'menu', 'POWER_PAASMANAGE_SYSTEM_SET', 'paasmanage');
-- 转换前MySQL SQL:
REPLACE INTO tb_rolepower(role_id, asset_type, power_id, sys_key) VALUES ('see_default_read_role', 'menu', 'AUTO_JOBNET', 'auto');
-- 转换后LightDB-Mysql SQL:
INSERT INTO "tb_rolepower" ("role_id","asset_type","power_id","sys_key") VALUES ('see_default_read_role','menu','AUTO_JOBNET','auto')
ON CONFLICT ("role_id","power_id","sys_key")
DO UPDATE SET "role_id"=EXCLUDED."role_id","asset_type"=EXCLUDED."asset_type","power_id"=EXCLUDED."power_id","sys_key"=EXCLUDED."sys_key"
-- 转换前MySQL SQL:
REPLACE INTO tb_rolepower(role_id, asset_type, power_id, sys_key) VALUES ROW('see_default_read_role', 'menu', 'AUTO_JOBNET', 'auto');
-- 转换后LightDB-Mysql SQL:
INSERT INTO "tb_rolepower" ("role_id","asset_type","power_id","sys_key") VALUES ('see_default_read_role','menu','AUTO_JOBNET','auto')
ON CONFLICT ("role_id","power_id","sys_key")
DO UPDATE SET "role_id"=EXCLUDED."role_id","asset_type"=EXCLUDED."asset_type","power_id"=EXCLUDED."power_id","sys_key"=EXCLUDED."sys_key"
-- 转换前MySQL SQL:
replace into tb_rolepower (role_id, asset_type, power_id, sys_key) select DISTINCT role_id, asset_type, 'POWER_MYSQL_CANCLE', sys_key from tb_rolepower where power_id = 'POWER_PAASMANAGE_SYSTEM_SET';
-- 转换后LightDB-Mysql SQL:
INSERT INTO "tb_rolepower" ("role_id","asset_type","power_id","sys_key") SELECT DISTINCT "role_id","asset_type",'POWER_MYSQL_CANCLE',"sys_key" FROM "tb_rolepower" WHERE "power_id"='POWER_PAASMANAGE_SYSTEM_SET'
ON CONFLICT ("role_id","power_id","sys_key")
DO UPDATE SET "role_id"=EXCLUDED."role_id","asset_type"=EXCLUDED."asset_type","power_id"=EXCLUDED."power_id","sys_key"=EXCLUDED."sys_key"
-- 转换前MySQL SQL:
REPLACE tb_rolepower SET role_id = 'see_default_read_write_role', asset_type = 'menu', power_id = 'AUTO_JOBNET', sys_key = 'auto';
-- 转换后LightDB-Mysql SQL:
INSERT INTO "tb_rolepower" ("role_id","asset_type","power_id","sys_key") VALUES ('see_default_read_write_role','menu','AUTO_JOBNET','auto')
ON CONFLICT ("role_id","power_id","sys_key")
DO UPDATE SET "role_id"=EXCLUDED."role_id","asset_type"=EXCLUDED."asset_type","power_id"=EXCLUDED."power_id","sys_key"=EXCLUDED."sys_key"
1.3.3.3.4. OceanBase-Mysql
1.3.3.3.4.1. INSERT INTO
语法
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} [ROW] ( value_list) [, [ROW] (value_list)] ... }
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
INSERT INTO test_table (id, name, age) VALUES row (1, 'John', 25),row (2,'Tom',18);
-- 转换后LightDB-Mysql SQL:
INSERT INTO test_table (id,name,age) VALUES (1,'John',25),(2,'Tom',18)
1.3.3.3.4.2. REPLACE INTO
语法
REPLACE [INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
|
VALUES row_constructor_list
}
REPLACE
[INTO] tbl_name
SET assignment_list
REPLACE
[INTO] tbl_name
[(col_name [, col_name] ...)]
{SELECT ... | TABLE table_name}
value:
{expr | DEFAULT}
value_list:
value [, value] ...
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
示例
CREATE TABLE tb_rolepower (
role_id varchar(32) NOT NULL,
asset_type varchar(64) DEFAULT NULL,
power_id varchar(64) NOT NULL,
sys_key varchar(32) NOT NULL DEFAULT 'acm',
PRIMARY KEY (role_id, power_id, sys_key)
);
INSERT INTO tb_rolepower (role_id, asset_type, power_id, sys_key) VALUES ('see_default_read_write_role', 'menu', 'POWER_PAASMANAGE_SYSTEM_SET', 'paasmanage');
-- 转换前MySQL SQL:
REPLACE INTO tb_rolepower(role_id, asset_type, power_id, sys_key) VALUES ('see_default_read_role', 'menu', 'AUTO_JOBNET', 'auto');
-- 转换后OceanBase-Mysql SQL:
REPLACE INTO `tb_rolepower` (`role_id`,`asset_type`,`power_id`,`sys_key`) VALUES ('see_default_read_role','menu','AUTO_JOBNET','auto')
-- 转换前MySQL SQL:
REPLACE INTO tb_rolepower(role_id, asset_type, power_id, sys_key) VALUES ROW('see_default_read_role', 'menu', 'AUTO_JOBNET', 'auto');
-- 转换后OceanBase-Mysql SQL:
REPLACE INTO `tb_rolepower` (`role_id`,`asset_type`,`power_id`,`sys_key`) VALUES ('see_default_read_role','menu','AUTO_JOBNET','auto')
-- 转换前MySQL SQL:
replace into tb_rolepower (role_id, asset_type, power_id, sys_key) select DISTINCT role_id, asset_type, 'POWER_MYSQL_CANCLE', sys_key from tb_rolepower where power_id = 'POWER_PAASMANAGE_SYSTEM_SET';
-- 转换后OceanBase-Mysql SQL:
REPLACE INTO `tb_rolepower` (`role_id`,`asset_type`,`power_id`,`sys_key`) SELECT DISTINCT `role_id`,`asset_type`,'POWER_MYSQL_CANCLE',`sys_key` FROM `tb_rolepower` WHERE `power_id`='POWER_PAASMANAGE_SYSTEM_SET'
-- 转换前MySQL SQL:
REPLACE tb_rolepower SET role_id = 'see_default_read_write_role', asset_type = 'menu', power_id = 'AUTO_JOBNET', sys_key = 'auto';
-- 转换后OceanBase-Mysql SQL:
REPLACE INTO `tb_rolepower` SET `role_id`='see_default_read_write_role',`asset_type`='menu',`power_id`='AUTO_JOBNET',`sys_key`='auto'
1.3.3.3.4.3. DELETE 单表
语法
DELETE [IGNORE]
FROM tbl_name [[AS] table_alias]
[WHERE where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT row_count]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
DELETE IGNORE FROM test_table as t WHERE t.id = 1 ORDER BY t.id limit 10;
-- 转换后OceanBase-Mysql SQL:
DELETE FROM `test_table` AS `t` WHERE `t`.`id`=1 ORDER BY `t`.`id` LIMIT 10;
1.3.3.3.4.4. DELETE 多表
语法
DELETE [IGNORE]
[tbl_name|table_alias][.*] [, [tbl_name|table_alias][.*]]...
FROM tbl_name [[AS] table_alias] [, tbl_name[AS] table_alias]...
[WHERE where_condition]
示例
CREATE TABLE person (
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
);
CREATE TABLE job (
id INT PRIMARY KEY,
job VARCHAR(20)
);
-- 转换前MySQL SQL:
DELETE IGNORE p, j FROM person as p, job as j WHERE p.id = j.id;
-- 转换后OceanBase-Mysql SQL:
DELETE `p`,`j` FROM `person` AS `p`, `job` AS `j` WHERE `p`.`id`=`j`.`id`;
1.3.3.3.5. Oracle_19C
1.3.3.3.5.1. INSERT INTO
语法
INSERT [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ SELECT ... }
[ON DUPLICATE KEY UPDATE assignment_list]
value_list:
value [, value] ...
assignment:
col_name = values(col_name)
assignment_list:
assignment [, assignment] ...
示例
CREATE TABLE test_table (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
INSERT INTO test_table (name, age) VALUES ('John', 25);
INSERT INTO test_table (id,name, age) VALUES (10,'Tom', 26);
INSERT INTO test_table (name, age) VALUES ('Jerry', 27);
select id,name, age from test_table;
id|name |age|
--+-----+---+
1|John | 25|
10|Tom | 26|
11|Jerry| 27|
-- 转换后Oracle_19C SQL:
INSERT INTO test_table (name,age) VALUES ('John',25);
INSERT INTO test_table (id,name,age) VALUES (10,'Tom',26);
INSERT INTO test_table (name,age) VALUES ('Jerry',27);
SELECT id,name,age FROM test_table
ID|NAME |AGE|
--+-----+---+
1|John | 25|
10|Tom | 26|
2|Jerry| 27|
--插入多行、子查询
CREATE TABLE test_cases (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
age INT,
is_active BOOLEAN
);
-- 转换前MySQL SQL:
INSERT INTO test_cases (name, age, is_active) VALUES
('Multi1', 18, TRUE),
('Multi2', 19, FALSE);
INSERT INTO test_cases (name, age, is_active) with tmp as (select name, age, is_active from test_cases) select name, age+1, is_active from tmp;
-- 转换后Oracle_19C SQL:
INSERT INTO test_cases (name,age,is_active) SELECT 'Multi1',18,1 FROM DUAL UNION ALL SELECT 'Multi2',19,0 FROM DUAL;
INSERT INTO test_cases (name,age,is_active) WITH tmp AS (SELECT name,age,is_active FROM test_cases) SELECT name,age+1,is_active FROM tmp;
--insert ignore ... 、insert ... on duplicate key update
CREATE TABLE insert_ignore_unisql (
id int PRIMARY KEY NOT NULL,
key_id varchar(64) unique NOT null,
class_id varchar(63) DEFAULT NULL ,
mark_id varchar(31) DEFAULT NULL,
store_id int DEFAULT NULL ,
user_defined int DEFAULT NULL);
INSERT INTO insert_ignore_unisql (id, key_id, class_id, mark_id, store_id, user_defined) VALUES
(1, 'key_001', 'class_A', 'mark_1', 100, 1),
(2, 'key_002', 'class_B', 'mark_2', 101, 2),
(3, 'key_003', 'class_C', 'mark_3', 102, 3),
(4, 'key_004', 'class_D', 'mark_4', 103, 4),
(5, 'key_005', 'class_E', 'mark_5', 104, 5);
-- 转换前MySQL SQL:
INSERT ignore INTO insert_ignore_unisql (id, key_id, class_id, mark_id, store_id, user_defined) VALUES
(1, 'key_001', 'class_A', 'mark_1', 100, 1),
(7, 'key_001', 'class_H', 'mark_7', 107, 7);
INSERT IGNORE INTO insert_ignore_unisql (id, key_id, class_id, mark_id, store_id, user_defined) VALUES
(5, 'key_005_1', 'class_E_1', 'mark_5_1', 1041, 51),
(8, 'key_008', 'class_H', 'mark_8',108, 8)
ON DUPLICATE KEY UPDATE
class_id = VALUES(class_id),
mark_id = VALUES(mark_id),
store_id = VALUES(store_id),
user_defined = VALUES(user_defined);
-- 转换后Oracle_19C SQL:
MERGE INTO insert_ignore_unisql target_table USING
(SELECT 1 AS id,'key_001' AS key_id,'class_A' AS class_id,'mark_1' AS mark_id,100 AS store_id,1 AS user_defined FROM DUAL UNION
SELECT 7 AS id,'key_001' AS key_id,'class_H' AS class_id,'mark_7' AS mark_id,107 AS store_id,7 AS user_defined FROM DUAL)
source_table ON ((target_table.id=source_table.id) OR (target_table.key_id=source_table.key_id))
WHEN NOT MATCHED THEN INSERT (id,key_id,class_id,mark_id,store_id,user_defined) VALUES
(source_table.id,source_table.key_id,source_table.class_id,source_table.mark_id,source_table.store_id,source_table.user_defined);
MERGE INTO insert_ignore_unisql target_table USING
(SELECT 5 AS id,'key_005_1' AS key_id,'class_E_1' AS class_id,'mark_5_1' AS mark_id,1041 AS store_id,51 AS user_defined FROM DUAL UNION
SELECT 8 AS id,'key_008' AS key_id,'class_H' AS class_id,'mark_8' AS mark_id,108 AS store_id,8 AS user_defined FROM DUAL) source_table
ON ((target_table.id=source_table.id) OR (target_table.key_id=source_table.key_id))
WHEN MATCHED THEN UPDATE SET
target_table.class_id=source_table.class_id,target_table.mark_id=source_table.mark_id,
target_table.store_id=source_table.store_id,target_table.user_defined=source_table.user_defined
WHEN NOT MATCHED THEN INSERT (id,key_id,class_id,mark_id,store_id,user_defined)
VALUES (source_table.id,source_table.key_id,source_table.class_id,source_table.mark_id,source_table.store_id,source_table.user_defined)
警告
在 MySQL 中，如果表的主键是自增列（AUTO_INCREMENT），即使手动指定了 id 的值，后续的自动分配仍会基于手动指定的最大值递增，确保连续性。 而在 Oracle 19C 中手动指定 id 后，SEQUENCE 的下一个值仍然基于其自身的递增逻辑，与手动插入的最大 id 无关。自增序列插入null 不支持，oracle中序列不支持null; 对于boolean类型的插入not true , not false不支持，空插不支持（insert into tableName values()[,()]）
- 统一SQL会将INSERT INTO批量插入转化为 INSERT INTO … SELECT ..，插入的值存在Default，限制如下：
需要配合 元数据管理 功能使用；
不支持主键自增的列插入default的值；
如果创建表没有指定DEFAULT值，插入DEFAULT时会直接插入NULL；
DEFAULT值依赖于创建表的列DEFAULT值的转化，功能、限制保持一致
- 统一SQL会将INSERT IGNORE 转为 MERGE INTO，限制如下：
需要配合 元数据管理 功能使用；
主键自增问题，MySQL中冲突后是自增键会跳跃增长，出现跳号，所以两边库自增列会对应不上；
插入的列必须有主键或唯一键（如果是联合主键或联合唯一键，联合的列需要全部写出；多个唯一键时所有唯一键都要列出），否则转化为普通insert into语句；
INSERT IGNORE SELECT 子句不支持使用 select * ；
mysql插入的数据中的对应的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）
INSERT IGNORE 转为 MERGE INTO只支持了主键或唯一键冲突，其他check约束、外键等暂不支持。
不支持插入的值存在default
- 统一SQL会将INSERT … ON DUPLICATE KEY UPDATE 转为 MERGE INTO，限制如下：
需要配合 元数据管理 功能使用；
主键自增问题，MySQL中冲突后是自增键会跳跃增长，出现跳号，所以两边库自增列会对应不上；
mysql有冲突时所有列都支持更新，MERGE INTO 中UPDATE SET不支持更新主键和唯一键；
插入的列必须有主键或唯一键（如果是联合主键或联合唯一键，联合的列需要全部写出；多个唯一键时所有唯一键都要列出），否则转化为普通insert into语句；
insert into SELECT 子句不支持使用 select * ；
mysql插入语句中一行数据冲突表中多行数据，只会更新第一个冲突的唯一键所在行。MERGE INTO 中如果存在多个唯一键用or连接，冲突的行都会更新。两边数据结果不一致；
mysql插入的数据中的对应的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）；
ON DUPLICATE KEY UPDATE不支持更新主键，唯一键的列（merge into 中不支持更新ON 子句中引用的列）；
INSERT … ON DUPLICATE KEY UPDATE 转为 MERGE INTO只支持了主键或唯一键冲突，其他check约束、外键等暂不支持。
on duplicate update仅考虑 colName= values(colName)这种情况
不支持插入的值存在default
- 统一SQL多发比对在如上转换的限制上还有如下限制：
不支持列只有自增键一个字段的情况，不支持空和null问题
mysql多个唯一键冲突时（不同的行数据）只会更新第一个冲突的唯一键所在行。merge into 中如果存在多个唯一键用or连接，冲突的行都会更新。两边数据结果不一致
insert into SELECT 子句查询出来的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）
values表达式只支持字面值，不支持子查询、函数、别名等
ON DUPLICATE KEY UPDATE不支持更新主键，唯一键的列（merge into 中不支持更新ON 子句中引用的列）；更新的列在插入列中必须存在
boolean类型 not true , not false不支持
不支持DEFAULT关键字；以及values中没有值的情况
日期时间字面量表达式不支持：’%Y/%m/%d’ 例如’2023/10/01’、’%Y/%m/%d %H:%i:%s’ 例如’2025/04/14 11:36:06’、’%Y/%m/%d %H:%i:%s.%f’ 例如’2025/04/14 11:36:06.136’、’%Y-%m-%d’ 例如’2023-10-01’、’%Y-%m-%d %H:%i:%s’ 例如’2025-04-14 11:36:06’、’%Y-%m-%d %H:%i:%s.%f’ 例如’2025-04-14 11:36:06.136’、’%Y%m%d’ 例如’20231001’、’%Y%m%d%H%i%s’ 例如’20231001123456’、%Y%m%d 例如20231001、%Y%m%d%H%i%s 例如20231001123456。可通过STR_TO_DATE替代，例如STR_TO_DATE(‘2025-04-14 11:36:35’, ‘%Y-%m-%d %H:%i:%s’)
自增序列插入null 不支持，oracle中序列不支持null
mysql和oracle两库之间null的排序规则不一致
不支持limit；mysql中LIMIT不支持和IN/ALL/ANY/SOME subquery 一起使用
1.3.3.3.5.2. REPLACE INTO
语法
统一SQL会将REPLACE INTO 转为 MERGE INTO 语句，支持语法如下：
REPLACE [INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...}
REPLACE
[INTO] tbl_name
[(col_name [, col_name] ...)]
{SELECT ...}
value:
{expr | DEFAULT}
value_list:
value [, value] ...
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
警告
- 统一SQL会将REPLACE INTO 转为 MERGE INTO，限制如下：
需要配合 元数据管理 功能使用;
主键自增问题，MySQL中冲突后是先删除再插入，而ORACLE冲突后是更新列内容，所以两边库自增列会对应不上。
mysql有冲突时所有列都支持更新，MERGE INTO 中on关联条件字段不支持更新(主键和唯一键不支持更新)
REPLACE INTO … select XXX … 时SELECT的列必须写明确，不能SELECT * ，并且SELECT 出来的列类型需要和插入表的列保持一致。
mysql多个唯一键冲突时（不同的行数据）只会更新第一个冲突的唯一键所在行。MERGE INTO 中如果存在多个唯一键用or连接，冲突的行都会更新。两边数据结果不一致
REPLACE INTO 指定的插入列中必须包含至少一个完整的唯一约束。
示例
create table students_1(
id int primary key,
name varchar(10),
age int);
-- 转换前MySQL SQL:
replace into students_1(id,name,age) values(4,'yodo',18);
-- 转换后Oracle SQL:
MERGE INTO students_1 insert_table USING (SELECT 4 AS id,'yodo' AS name,18 AS age FROM DUAL) source_table ON (insert_table.id=source_table.id) WHEN MATCHED THEN UPDATE SET name=source_table.name,age=source_table.age WHEN NOT MATCHED THEN INSERT (id,name,age) VALUES (source_table.id,source_table.name,source_table.age);
-- 转换前MySQL SQL:
replace into students_1(id,name,age) values(5,'yodo',18),(6, 'mike', 20);
-- 转换后Oracle SQL:
MERGE INTO students_1 insert_table USING (SELECT 5 AS id,'yodo' AS name,18 AS age FROM DUAL UNION ALL SELECT 6 AS id,'mike' AS name,20 AS age FROM DUAL) source_table ON (insert_table.id=source_table.id) WHEN MATCHED THEN UPDATE SET name=source_table.name,age=source_table.age WHEN NOT MATCHED THEN INSERT (id,name,age) VALUES (source_table.id,source_table.name,source_table.age);
-- 转换前MySQL SQL:
replace into students_4(id, name, age) select id, name, age from students_5 where id<3;
-- 转换后Oracle SQL:
MERGE INTO students_4 insert_table USING (SELECT id AS id,name AS name,age AS age FROM students_5 WHERE id<3) source_table ON (insert_table.id=source_table.id AND insert_table.name=source_table.name) WHEN MATCHED THEN UPDATE SET age=source_table.age WHEN NOT MATCHED THEN INSERT (id,name,age) VALUES (source_table.id,source_table.name,source_table.age);
1.3.3.3.5.3. UPDATE 单表
语法
UPDATE [IGNORE] tbl_name
SET assignment_list
[WHERE where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT row_count]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
update test_table set name = 'John' where id = 1;
update test_table set name = (select 'John' from dual) where id = 1;
update test_table set name = 'John' order by id limit 1;
--- 更新非唯一键
update ignore test_table set name = 'John' where id = 1;
--- 更新唯一键
update ignore test_table set id =2 where id=1;
-- 转换后Oracle_19C SQL:
UPDATE test_table SET name='John' WHERE id=1
UPDATE test_table SET name=(SELECT 'John' FROM dual) WHERE id=1
UPDATE test_table SET name='John' WHERE rowid IN (SELECT test_table.rowid AS rid FROM test_table ORDER BY id FETCH NEXT 1 ROWS ONLY );
--- 更新非唯一键
UPDATE test_table SET name='John' WHERE id=1;
--- 更新唯一键
MERGE INTO test_table target_table
USING (SELECT test_table.rowid AS rid,2 AS v0,row_number() OVER (PARTITION BY test_table.rowid ORDER BY test_table.rowid) AS rn FROM test_table WHERE id=1) source_table
ON (source_table.rid=target_table.rowid AND source_table.rn=1)
WHEN MATCHED THEN
UPDATE SET target_table.id=source_table.v0
WHERE NOT EXISTS (SELECT 1 FROM test_table filter_table WHERE filter_table.ID=source_table.v0)
警告
- 统一SQL对单表update可能转为merge into，限制如下：
不支持在一条SQL中把两行记录的唯一键更新为相同值，转换会成功，在oracle 执行会失败，比如：1,2 都更新为3，在mysql 其中某一条数据会被更新为3，在oracle 会更新失败
mysql 的update的ignore是对字段起效，同一行修改中冲突的字段修改会忽略，其他字段修改会成功，转为oracle后是对行起效，有冲突的字段，整行修改都会被忽略
1.3.3.3.5.4. UPDATE 多表
语法
UPDATE [IGNORE] table_references
SET assignment_list
[WHERE where_condition]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
CREATE TABLE test_table1 (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
update test_table a, test_table1 b set a.name = b.name where a.id= b.id and b.name = 'John';
update ignore test_table a, test_table1 b set a.id = 100 where a.id= b.id and b.name = 'John';
update test_table a, test_table1 b set a.age=15, b.age=15 where a.id= b.id and b.name = 'John';
update ignore test_table a, test_table1 b set a.id=100, b.name='jake' where a.id= b.id and b.name = 'John';
-- 转换后Oracle_19C SQL:
--- 多表更新转为merge into
MERGE INTO test_table target_table
USING (
SELECT
a.rowid AS rid,
b.name AS v0,
ROW_NUMBER() OVER (PARTITION BY a.rowid
ORDER BY
a.rowid) AS rn
FROM
test_table a ,
test_table1 b
WHERE
a.id = b.id
AND b.name = 'John') source_table
ON(source_table.rid = target_table.rowid AND source_table.rn = 1)
WHEN MATCHED THEN
UPDATE SET target_table.name = source_table.v0;
--- 多表更新带ignore转为merge into + not exists
MERGE INTO test_table target_table
USING (
SELECT
a.rowid AS rid,
100 AS v0,
row_number() OVER (PARTITION BY a.rowid
ORDER BY
a.rowid) AS rn
FROM
test_table a ,
test_table1 b
WHERE
a.id = b.id
AND b.name = 'John') source_table
ON(source_table.rid = target_table.rowid AND source_table.rn = 1)
WHEN MATCHED THEN
UPDATE SET target_table.id = source_table.v0
WHERE NOT EXISTS (
SELECT
1
FROM
test_table filter_table
WHERE
filter_table.ID = source_table.v0);
--- 更新多表转为匿名块
DECLARE
CURSOR cur_date IS SELECT a.rowid AS a_rid,b.rowid AS b_rid,15 AS a_v0,15 AS b_v1 FROM test_table a , test_table1 b WHERE a.id=b.id AND b.name='John';
BEGIN
FOR rec IN cur_date LOOP
UPDATE test_table a SET a.age=rec.a_v0 WHERE rowid = rec.a_rid;
UPDATE test_table1 b SET b.age=rec.b_v1 WHERE rowid = rec.b_rid;
END LOOP;
END;
--- 更新多表带ignore转为匿名块 + not exists
DECLARE
CURSOR cur_date IS SELECT a.rowid AS a_rid,b.rowid AS b_rid,100 AS a_v0,100 AS b_v1 FROM test_table a , test_table1 b WHERE a.id=b.id AND b.name='John';
BEGIN
FOR rec IN cur_date LOOP
UPDATE test_table a SET a.id=rec.a_v0 WHERE rowid = rec.a_rid AND NOT EXISTS (SELECT 1 FROM test_table filter_table WHERE filter_table.ID=rec.b_v0);
UPDATE test_table1 b SET b.id=rec.b_v1 WHERE rowid = rec.b_rid;
END LOOP;
END;
警告
- 统一SQL会将多表update转为merge into 或匿名块，限制如下：
更新字段必须指定表名，不指定不支持转换
不支持在一条SQL中把两行记录的唯一键更新为相同值，转换会成功，在oracle 执行会失败，比如：1,2 都更新为3，在mysql 其中某一条数据会被更新为3，在oracle 会更新失败
mysql 的update的ignore是对字段起效，同一行修改中冲突的字段修改会忽略，其他字段修改会成功，转为oracle后是对行起效，有冲突的字段，整行修改都会被忽略
1.3.3.3.5.5. DELETE 单表
语法
DELETE [IGNORE]
FROM tbl_name [[AS] table_alias]
[WHERE where_condition]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
DELETE IGNORE FROM test_table as t WHERE t.id = 1;
-- 转换后Oracle_19C SQL:
DELETE FROM test_table t WHERE t.id = 1;
1.3.3.3.5.6. DELETE 多表
语法
DELETE tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
警告
- 多表删除目前限制：
where 中不支持order by、limit子句
不支持USING
联表删除单表时，delete 后面的名称必须和被删除的表名或别名完全一致
示例
-- 转换前MySQL SQL:
delete a,b from test_table a,test_table b where a.id = b.id;
DELETE a FROM tb_ci_base_object a, tb_ci_communication_endpoint b WHERE a.instance_id = b.instance_id AND b.instance_id = '1';
DELETE tb_ci_base_object, tb_ci_communication_endpoint FROM tb_ci_base_object , tb_ci_communication_endpoint WHERE tb_ci_base_object.instance_id = tb_ci_communication_endpoint.instance_id AND tb_ci_communication_endpoint.instance_id = '1';
-- 转换后Oracle_19C SQL:
DECLARE CURSOR cur_data IS SELECT DISTINCT a.rowid as a, b.rowid as b FROM test_table a , test_table b WHERE a.id=b.id;BEGIN FOR rec IN cur_data LOOP DELETE FROM test_table WHERE rowid=rec.a; DELETE FROM test_table WHERE rowid=rec.b; END LOOP; END;
DECLARE CURSOR cur_data IS SELECT DISTINCT a.rowid as a FROM tb_ci_base_object a , tb_ci_communication_endpoint b WHERE a.instance_id=b.instance_id AND b.instance_id='1';BEGIN FOR rec IN cur_data LOOP DELETE FROM tb_ci_base_object WHERE rowid=rec.a; END LOOP; END;
DECLARE CURSOR cur_data IS SELECT DISTINCT tb_ci_base_object.rowid as tb_ci_base_object, tb_ci_communication_endpoint.rowid as tb_ci_communication_endpoint FROM tb_ci_base_object , tb_ci_communication_endpoint WHERE tb_ci_base_object.instance_id=tb_ci_communication_endpoint.instance_id AND tb_ci_communication_endpoint.instance_id='1';BEGIN FOR rec IN cur_data LOOP DELETE FROM tb_ci_base_object WHERE rowid=rec.tb_ci_base_object; DELETE FROM tb_ci_communication_endpoint WHERE rowid=rec.tb_ci_communication_endpoint; END LOOP; END;
1.3.3.3.6. OceanBase-Oracle
1.3.3.3.6.1. INSERT INTO
语法
INSERT [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ SELECT ... }
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
with [RECURSIVE]
{new_tbl_name AS ({ SELECT ... }) [, new_tbl_name AS ({ SELECT ... })}
{ SELECT ... }
value_list:
value [, value] ...
assignment:
col_name = values(col_name)
assignment_list:
assignment [, assignment] ...
示例
CREATE TABLE test_table (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
INSERT INTO test_table (name, age) VALUES ('John', 25);
INSERT INTO test_table (id,name, age) VALUES (10,'Tom', 26);
INSERT INTO test_table (name, age) VALUES ('Jerry', 27);
select id,name, age from test_table;
id|name |age|
--+-----+---+
1|John | 25|
10|Tom | 26|
11|Jerry| 27|
-- 转换后OceanBase-Oracle SQL:
INSERT INTO test_table (name,age) VALUES ('John',25);
INSERT INTO test_table (id,name,age) VALUES (10,'Tom',26);
INSERT INTO test_table (name,age) VALUES ('Jerry',27);
SELECT id,name,age FROM test_table
ID|NAME |AGE|
--+-----+---+
1|John | 25|
10|Tom | 26|
2|Jerry| 27|
--插入多行、子查询
CREATE TABLE test_cases (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
age INT,
is_active BOOLEAN
);
-- 转换前MySQL SQL:
INSERT INTO test_cases (name,age,is_active) VALUES ('Multi1',18,1),('Multi2',19,0);
INSERT INTO test_cases (name, age, is_active) with tmp as (select name, age, is_active from test_cases) select name, age+1, is_active from tmp;
-- 转换后OceanBase-Oracle SQL:
INSERT INTO test_cases (name,age,is_active) SELECT 'Multi1',18,1 FROM DUAL UNION ALL SELECT 'Multi2',19,0 FROM DUAL;
INSERT INTO test_cases (name,age,is_active) WITH tmp AS (SELECT name,age,is_active FROM test_cases) SELECT name,age+1,is_active FROM tmp;
--insert ignore ... 、insert ... on duplicate key update
CREATE TABLE insert_ignore_unisql (
id int PRIMARY KEY NOT NULL,
key_id varchar(64) unique NOT null,
class_id varchar(63) DEFAULT NULL ,
mark_id varchar(31) DEFAULT NULL,
store_id int DEFAULT NULL ,
user_defined int DEFAULT NULL);
INSERT INTO insert_ignore_unisql (id, key_id, class_id, mark_id, store_id, user_defined) VALUES
(1, 'key_001', 'class_A', 'mark_1', 100, 1),
(2, 'key_002', 'class_B', 'mark_2', 101, 2),
(3, 'key_003', 'class_C', 'mark_3', 102, 3),
(4, 'key_004', 'class_D', 'mark_4', 103, 4),
(5, 'key_005', 'class_E', 'mark_5', 104, 5);
-- 转换前MySQL SQL:
INSERT ignore INTO insert_ignore_unisql (id, key_id, class_id, mark_id, store_id, user_defined) VALUES
(1, 'key_001', 'class_A', 'mark_1', 100, 1),
(7, 'key_001', 'class_H', 'mark_7', 107, 7);
INSERT IGNORE INTO insert_ignore_unisql (id, key_id, class_id, mark_id, store_id, user_defined) VALUES
(5, 'key_005_1', 'class_E_1', 'mark_5_1', 1041, 51),
(8, 'key_008', 'class_H', 'mark_8',108, 8)
ON DUPLICATE KEY UPDATE
class_id = VALUES(class_id),
mark_id = VALUES(mark_id),
store_id = VALUES(store_id),
user_defined = VALUES(user_defined);
-- 转换后OceanBase-Oracle SQL:
MERGE INTO insert_ignore_unisql target_table USING
(SELECT 1 AS id,'key_001' AS key_id,'class_A' AS class_id,'mark_1' AS mark_id,100 AS store_id,1 AS user_defined FROM DUAL UNION
SELECT 7 AS id,'key_001' AS key_id,'class_H' AS class_id,'mark_7' AS mark_id,107 AS store_id,7 AS user_defined FROM DUAL)
source_table ON ((target_table.id=source_table.id) OR (target_table.key_id=source_table.key_id))
WHEN NOT MATCHED THEN INSERT (id,key_id,class_id,mark_id,store_id,user_defined) VALUES
(source_table.id,source_table.key_id,source_table.class_id,source_table.mark_id,source_table.store_id,source_table.user_defined);
MERGE INTO insert_ignore_unisql target_table USING
(SELECT 5 AS id,'key_005_1' AS key_id,'class_E_1' AS class_id,'mark_5_1' AS mark_id,1041 AS store_id,51 AS user_defined FROM DUAL UNION
SELECT 8 AS id,'key_008' AS key_id,'class_H' AS class_id,'mark_8' AS mark_id,108 AS store_id,8 AS user_defined FROM DUAL) source_table
ON ((target_table.id=source_table.id) OR (target_table.key_id=source_table.key_id))
WHEN MATCHED THEN UPDATE SET
target_table.class_id=source_table.class_id,target_table.mark_id=source_table.mark_id,
target_table.store_id=source_table.store_id,target_table.user_defined=source_table.user_defined
WHEN NOT MATCHED THEN INSERT (id,key_id,class_id,mark_id,store_id,user_defined)
VALUES (source_table.id,source_table.key_id,source_table.class_id,source_table.mark_id,source_table.store_id,source_table.user_defined)
警告
在 MySQL 中，如果表的主键是自增列（AUTO_INCREMENT），即使手动指定了 id 的值，后续的自动分配仍会基于手动指定的最大值递增，确保连续性。 而在 OceanBase-Oracle 中手动指定 id 后，SEQUENCE 的下一个值仍然基于其自身的递增逻辑，与手动插入的最大 id 无关。自增序列插入null 不支持，OceanBase-Oracle中序列不支持null; 对于boolean类型的插入not true , not false不支持;空插不支持（insert into tableName values()[,()]）
- 统一SQL会将INSERT IGNORE 转为 MERGE INTO，限制如下：
需要配合 元数据管理 功能使用；
主键自增问题，MySQL中冲突后是自增键会跳跃增长，出现跳号，所以两边库自增列会对应不上；
插入的列必须有主键或唯一键（如果是联合主键或联合唯一键，联合的列需要全部写出；多个唯一键时所有唯一键都要列出），否则转化为普通insert into语句；
不支持使用 select * ；
mysql插入的数据中的对应的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）
INSERT IGNORE 转为 MERGE INTO只支持了主键或唯一键冲突，其他check约束、外键等暂不支持。
不支持插入的值存在default
- 统一SQL会将INSERT … ON DUPLICATE KEY UPDATE 转为 MERGE INTO，限制如下：
需要配合 元数据管理 功能使用；
主键自增问题，MySQL中冲突后是自增键会跳跃增长，出现跳号，所以两边库自增列会对应不上；
mysql有冲突时所有列都支持更新，MERGE INTO 中UPDATE SET不支持更新主键和唯一键；
插入的列必须有主键或唯一键（如果是联合主键或联合唯一键，联合的列需要全部写出；多个唯一键时所有唯一键都要列出），否则转化为普通insert into语句；
不支持使用 select * ；
mysql插入语句中一行数据冲突表中多行数据，只会更新第一个冲突的唯一键所在行。MERGE INTO 中如果存在多个唯一键用or连接，冲突的行都会更新。两边数据结果不一致；
mysql插入的数据中的对应的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）；
ON DUPLICATE KEY UPDATE不支持更新主键，唯一键的列（merge into 中不支持更新ON 子句中引用的列）；
INSERT … ON DUPLICATE KEY UPDATE 转为 MERGE INTO只支持了主键或唯一键冲突，其他check约束、外键等暂不支持。
on duplicate update仅考虑 colName= values(colName)这种情况
不支持插入的值存在default
- 统一SQL支持 INSERT INTO WITH，限制如下：
不支持 values row 语法，转换报错
Mysql CTE 中 select 语句包含时间类型字段的字面值，以及 Mysql 的函数。转换成功，执行失败
只包含普通 inset with select,如包含 ignore 和 on duplicate key update 等语法不支持
Group by 不支持表达式或者计算列的别名(如 case when 表达式)
CTE 不是递归性质的，但包含 union all，此时会转换成递归
Mysql WITH RECURSIVE 转换成功，执行失败，OceanBase-Oracle 不支持 WITH RECURSIVE 语法
Mysql CTE 中 select 仅使用order by 进行排序时，与OceanBase-Oracle 结果不一致, Mysql中未进行排序处理
OceanBase-Oracle 中会将无意义的0去掉，DECIMAL(10,2）这个类型，插入的值是28.00，返回的值是28
- 统一SQL多发比对在如上转换的限制上还有如下限制：
不支持列只有自增键一个字段的情况，不支持空和null问题
mysql多个唯一键冲突时（不同的行数据）只会更新第一个冲突的唯一键所在行。merge into 中如果存在多个唯一键用or连接，冲突的行都会更新。两边数据结果不一致
insert into SELECT 子句查询出来的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）
values表达式只支持字面值，不支持子查询、函数、别名等
ON DUPLICATE KEY UPDATE不支持更新主键，唯一键的列（merge into 中不支持更新ON 子句中引用的列）；更新的列在插入列中必须存在
boolean类型 not true , not false不支持
不支持DEFAULT关键字；以及values中没有值的情况
日期时间字面量表达式不支持：’%Y/%m/%d’ 例如’2023/10/01’、’%Y/%m/%d %H:%i:%s’ 例如’2025/04/14 11:36:06’、’%Y/%m/%d %H:%i:%s.%f’ 例如’2025/04/14 11:36:06.136’、’%Y-%m-%d’ 例如’2023-10-01’、’%Y-%m-%d %H:%i:%s’ 例如’2025-04-14 11:36:06’、’%Y-%m-%d %H:%i:%s.%f’ 例如’2025-04-14 11:36:06.136’、’%Y%m%d’ 例如’20231001’、’%Y%m%d%H%i%s’ 例如’20231001123456’、%Y%m%d 例如20231001、%Y%m%d%H%i%s 例如20231001123456。可通过STR_TO_DATE替代，例如STR_TO_DATE(‘2025-04-14 11:36:35’, ‘%Y-%m-%d %H:%i:%s’)
自增序列插入null 不支持，oracle中序列不支持null
mysql和ocean_base_oracle两库之间null的排序规则不一致
不支持limit；mysql中LIMIT不支持和IN/ALL/ANY/SOME subquery 一起使用
不支持空串和null、default关键字以及values中没有值的情况
不支持select *
表名列名使用时需要和创建时一致
因为源库mysql的AUTO_INCREMENT在目标库ocean_base_oracle中在转换后用的是GENERATED BY DEFAULT AS IDENTITY，序列会存在cahce，插入会存在序列跳跃问题
1.3.3.3.6.2. REPLACE INTO
语法
统一SQL会将REPLACE INTO 转为 MERGE INTO 语句，支持语法如下：
REPLACE [INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...}
REPLACE
[INTO] tbl_name
[(col_name [, col_name] ...)]
{SELECT ...}
value:
{expr | DEFAULT}
value_list:
value [, value] ...
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
警告
- 统一SQL会将REPLACE INTO 转为 MERGE INTO，限制如下：
需要配合 元数据管理 功能使用;
主键自增问题，MySQL 中冲突后是先删除再插入，而 OceanBase-Oracle 冲突后是更新列内容，所以两边库自增列会对应不上。
MySQL 有冲突时所有列都支持更新，MERGE INTO 中 ON 关联条件字段不支持更新(即主键和唯一键不支持更新)。
MySQL 多个唯一键冲突时（不同的行数据）只会更新第一个冲突的唯一键所在行。MERGE INTO 中如果存在多个唯一键用 OR 连接，冲突的行都会更新。两边数据结果不一致。
REPLACE INTO 指定的插入列中必须包含至少一个完整的唯一约束。
插入两条重复的记录，统一 SQL 转换成功，OceanBase-Oracle 执行报错。
如果插入的表中含有 DEFAULT 默认字段的，REPLACE INTO 插值时，该字段值为空，会导致两个数据库之间的值不匹配。
REPLACE INTO … SELECT XXX … 时，SELEC 语句中有 VALUES ROW 子句，统一 SQL 转换失败。
REPLACE INTO … SELECT XXX … 时，SELECT 出来的列类型需要和插入表的列保持一致。
REPLACE INTO 插入数据匹配多个唯一约束时，将转换为 delete+insert 两句SQL进行处理，对于存在绑定变量的情况，绑定变量的位置和个数可能会发生变化，此种情况仅在配合sql-convert-runtime使用时会自动处理，c接口和通过统一SQL对外接口转换后的情况需要手动计算并设置绑定变量的值。
示例
create table students_1(
id int primary key,
name varchar(10),
age int);
-- 转换前MySQL SQL:
replace into students_1(id,name,age) values(4,'yodo',18);
-- 转换后OceanBase-Oracle SQL:
MERGE INTO students_1 insert_table USING (SELECT 4 AS id,'yodo' AS name,18 AS age FROM DUAL) source_table ON (insert_table.id=source_table.id) WHEN MATCHED THEN UPDATE SET name=source_table.name,age=source_table.age WHEN NOT MATCHED THEN INSERT (id,name,age) VALUES (source_table.id,source_table.name,source_table.age);
-- 转换前MySQL SQL:
replace into students_1(id,name,age) values(5,'yodo',18),(6, 'mike', 20);
-- 转换后OceanBase-Oracle SQL:
MERGE INTO students_1 insert_table USING (SELECT 5 AS id,'yodo' AS name,18 AS age FROM DUAL UNION ALL SELECT 6 AS id,'mike' AS name,20 AS age FROM DUAL) source_table ON (insert_table.id=source_table.id) WHEN MATCHED THEN UPDATE SET name=source_table.name,age=source_table.age WHEN NOT MATCHED THEN INSERT (id,name,age) VALUES (source_table.id,source_table.name,source_table.age);
-- 转换前MySQL SQL:
replace into students_4(id, name, age) select id, name, age from students_5 where id<3;
-- 转换后OceanBase-Oracle SQL:
MERGE INTO students_4 insert_table USING (SELECT id AS id,name AS name,age AS age FROM students_5 WHERE id<3) source_table ON (insert_table.id=source_table.id AND insert_table.name=source_table.name) WHEN MATCHED THEN UPDATE SET age=source_table.age WHEN NOT MATCHED THEN INSERT (id,name,age) VALUES (source_table.id,source_table.name,source_table.age);
CREATE TABLEreplace_into_8_20250824_34513 (
c1 int,
c2 int,
c3 int,
c4 int,
c5 int,
c6 int,
CONSTRAINT replace_into_8_20250824_34513_pkey PRIMARY KEY(c1),
CONSTRAINT c2c3 UNIQUE(c2, c3)
);
-- 转换前MySQL SQL:
replace into replace_into_8_20250824_34513(c1, c2, c3, c4, c5, c6) values (?, ?, 3, 3, 3, 3)
-- 转换后OceanBase-Oracle SQL:
begin
DELETE FROM replace_into_8_20250824_34513 WHERE rowid IN (SELECT insert_table.rowid FROM replace_into_8_20250824_34513 insert_table JOIN (SELECT ? AS c1,? AS c2,3 AS c3,3 AS c4,3 AS c5,3 AS c6 FROM DUAL) source_table ON (insert_table.c1=source_table.c1) OR (insert_table.c2=source_table.c2 AND insert_table.c3=source_table.c3));
INSERT INTO replace_into_8_20250824_34513 (c1,c2,c3,c4,c5,c6) VALUES (?,?,3,3,3,3);
end;
/
1.3.3.3.6.3. UPDATE 单表
语法
UPDATE [IGNORE] tbl_name
SET assignment_list
[WHERE where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT row_count]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
update test_table set name = 'John' where id = 1;
update test_table set name = (select 'John' from dual) where id = 1;
update test_table set name = 'John' order by id limit 1;
--- 更新非唯一键
update ignore test_table set name = 'John' where id = 1;
--- 更新唯一键
update ignore test_table set id =2 where id=1;
-- 转换后Oracle_19C SQL:
UPDATE test_table SET name='John' WHERE id=1
UPDATE test_table SET name=(SELECT 'John' FROM dual) WHERE id=1
UPDATE test_table SET name='John' WHERE rowid IN (SELECT test_table.rowid AS rid FROM test_table ORDER BY id FETCH NEXT 1 ROWS ONLY );
--- 更新非唯一键
UPDATE test_table SET name='John' WHERE id=1;
--- 更新唯一键
MERGE INTO test_table target_table
USING (SELECT test_table.rowid AS rid,2 AS v0,row_number() OVER (PARTITION BY test_table.rowid ORDER BY test_table.rowid) AS rn FROM test_table WHERE id=1) source_table
ON (source_table.rid=target_table.rowid AND source_table.rn=1)
WHEN MATCHED THEN
UPDATE SET target_table.id=source_table.v0
WHERE NOT EXISTS (SELECT 1 FROM test_table filter_table WHERE filter_table.ID=source_table.v0)
警告
- 统一SQL对单表update可能转为merge into，限制如下：
不支持在一条SQL中把两行记录的唯一键更新为相同值，转换会成功，在ob oracle 执行会失败，比如：1,2 都更新为3，在mysql 其中某一条数据会被更新为3，在ob oracle 会更新失败
mysql 的update的ignore是对字段起效，同一行修改中冲突的字段修改会忽略，其他字段修改会成功，转为oracle后是对行起效，有冲突的字段，整行修改都会被忽略
1.3.3.3.6.4. UPDATE 多表
语法
UPDATE [IGNORE] table_references
SET assignment_list
[WHERE where_condition]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
CREATE TABLE test_table1 (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
update test_table a, test_table1 b set a.name = b.name where a.id= b.id and b.name = 'John';
update ignore test_table a, test_table1 b set a.id = 100 where a.id= b.id and b.name = 'John';
update test_table a, test_table1 b set a.age=15, b.age=15 where a.id= b.id and b.name = 'John';
update ignore test_table a, test_table1 b set a.id=100, b.name='jake' where a.id= b.id and b.name = 'John';
-- 转换后Oracle_19C SQL:
--- 多表更新转为merge into
MERGE INTO test_table target_table
USING (
SELECT
a.rowid AS rid,
b.name AS v0,
ROW_NUMBER() OVER (PARTITION BY a.rowid
ORDER BY
a.rowid) AS rn
FROM
test_table a ,
test_table1 b
WHERE
a.id = b.id
AND b.name = 'John') source_table
ON(source_table.rid = target_table.rowid AND source_table.rn = 1)
WHEN MATCHED THEN
UPDATE SET target_table.name = source_table.v0;
--- 多表更新带ignore转为merge into + not exists
MERGE INTO test_table target_table
USING (
SELECT
a.rowid AS rid,
100 AS v0,
row_number() OVER (PARTITION BY a.rowid
ORDER BY
a.rowid) AS rn
FROM
test_table a ,
test_table1 b
WHERE
a.id = b.id
AND b.name = 'John') source_table
ON(source_table.rid = target_table.rowid AND source_table.rn = 1)
WHEN MATCHED THEN
UPDATE SET target_table.id = source_table.v0
WHERE NOT EXISTS (
SELECT
1
FROM
test_table filter_table
WHERE
filter_table.ID = source_table.v0);
--- 更新多表转为匿名块
DECLARE
CURSOR cur_date IS SELECT a.rowid AS a_rid,b.rowid AS b_rid,15 AS a_v0,15 AS b_v1 FROM test_table a , test_table1 b WHERE a.id=b.id AND b.name='John';
BEGIN
FOR rec IN cur_date LOOP
UPDATE test_table a SET a.age=rec.a_v0 WHERE rowid = rec.a_rid;
UPDATE test_table1 b SET b.age=rec.b_v1 WHERE rowid = rec.b_rid;
END LOOP;
END;
--- 更新多表带ignore转为匿名块 + not exists
DECLARE
CURSOR cur_date IS SELECT a.rowid AS a_rid,b.rowid AS b_rid,100 AS a_v0,100 AS b_v1 FROM test_table a , test_table1 b WHERE a.id=b.id AND b.name='John';
BEGIN
FOR rec IN cur_date LOOP
UPDATE test_table a SET a.id=rec.a_v0 WHERE rowid = rec.a_rid AND NOT EXISTS (SELECT 1 FROM test_table filter_table WHERE filter_table.ID=rec.b_v0);
UPDATE test_table1 b SET b.id=rec.b_v1 WHERE rowid = rec.b_rid;
END LOOP;
END;
警告
- 统一SQL会将多表update转为merge into 或匿名块，限制如下：
更新字段必须指定表名，不指定不支持转换
不支持在一条SQL中把两行记录的唯一键更新为相同值，转换会成功，在ob oracle 执行会失败，比如：1,2 都更新为3，在mysql 其中某一条数据会被更新为3，在ob oracle 会更新失败
mysql 的update的ignore是对字段起效，同一行修改中冲突的字段修改会忽略，其他字段修改会成功，转为ob oracle后是对行起效，有冲突的字段，整行修改都会被忽略
1.3.3.3.6.5. DELETE 单表
语法
DELETE [IGNORE]
FROM tbl_name [[AS] table_alias]
[WHERE where_condition]
[ORDER BY {col_name | expr} [ASC | DESC], ...]
[LIMIT row_count]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
DELETE IGNORE FROM test_table as t WHERE t.id = 1;
DELETE FROM test_table as t WHERE t.id = 1 ORDER BY name LIMIT 1;
-- 转换后OceanBase-Oracle SQL:
DELETE FROM test_table t WHERE t.id = 1;
DELETE FROM test_table t WHERE rowid IN (SELECT rowid FROM test_table t WHERE t.id=1 ORDER BY name NULLS FIRST FETCH NEXT 1 ROWS ONLY )
1.3.3.3.6.6. DELETE 多表
语法
DELETE tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
警告
- 多表删除目前限制：
where 中不支持order by、limit子句
不支持USING
联表删除单表时，delete 后面的名称必须和被删除的表名或别名完全一致
示例
-- 转换前MySQL SQL:
delete a,b from test_table a,test_table b where a.id = b.id;
DELETE a FROM tb_ci_base_object a, tb_ci_communication_endpoint b WHERE a.instance_id = b.instance_id AND b.instance_id = '1';
DELETE tb_ci_base_object, tb_ci_communication_endpoint FROM tb_ci_base_object , tb_ci_communication_endpoint WHERE tb_ci_base_object.instance_id = tb_ci_communication_endpoint.instance_id AND tb_ci_communication_endpoint.instance_id = '1';
-- 转换后OceanBase-Oracle SQL:
DECLARE CURSOR cur_data IS SELECT DISTINCT a.rowid as a, b.rowid as b FROM test_table a , test_table b WHERE a.id=b.id;BEGIN FOR rec IN cur_data LOOP DELETE FROM test_table WHERE rowid=rec.a; DELETE FROM test_table WHERE rowid=rec.b; END LOOP; END;
DECLARE CURSOR cur_data IS SELECT DISTINCT a.rowid as a FROM tb_ci_base_object a , tb_ci_communication_endpoint b WHERE a.instance_id=b.instance_id AND b.instance_id='1';BEGIN FOR rec IN cur_data LOOP DELETE FROM tb_ci_base_object WHERE rowid=rec.a; END LOOP; END;
DECLARE CURSOR cur_data IS SELECT DISTINCT tb_ci_base_object.rowid as tb_ci_base_object, tb_ci_communication_endpoint.rowid as tb_ci_communication_endpoint FROM tb_ci_base_object , tb_ci_communication_endpoint WHERE tb_ci_base_object.instance_id=tb_ci_communication_endpoint.instance_id AND tb_ci_communication_endpoint.instance_id='1';BEGIN FOR rec IN cur_data LOOP DELETE FROM tb_ci_base_object WHERE rowid=rec.tb_ci_base_object; DELETE FROM tb_ci_communication_endpoint WHERE rowid=rec.tb_ci_communication_endpoint; END LOOP; END;
1.3.3.3.7. GaussDB-Mysql
1.3.3.3.7.1. 表更新
语法
UPDATE [IGNORE] table1 [, table2] SET column1=value1 [, column2=value2] [where ...];
警告
目标库为GaussDB-Mysql B 模式时，UPDATE IGNORE xxx，转化时候可通过配置项[unisql.remove.ignore]的配置决定是否移除IGNORE,具体参考 unisql.conf文件说明
示例
-- 转换前MySQL SQL:
UPDATE IGNORE test_update_ignore
SET unique_column = 20, value_column = 'Updated Value'
WHERE id = 3;
-- 转换后GaussDB-Mysql B 模式 SQL:
-- 配置项：unisql.remove.ignore=1
UPDATE test_update_ignore SET unique_column=20, value_column='Updated Value' WHERE id=3
-- 配置项：unisql.remove.ignore=0
UPDATE IGNORE test_update_ignore SET unique_column=20, value_column='Updated Value' WHERE id=3
1.3.3.3.8. DM
1.3.3.3.8.1. INSERT IGNORE INTO
语法
INSERT IGNORE INTO table_name [(column1, ...)] VALUES (value1, ...) [, ...];
INSERT IGNORE INTO table_name [(column1, ...)] SELECT ...;
警告
使用该转换功能时，需要预先加载目标库的元数据，否则转换不生效(依赖的唯一约束需要通过 ALTER TABLE XX ADD PRIMARY KEY/CONSTRAINT UNIQUE 添加)。
当前不支持 insert ignore into select * 语法
自增主键行为有差异：mysql 自增主键冲突后会增长，而 merge 自增主键冲突后不会增长
插入的列必须至少覆盖一个完整的唯一约束，不满足条件时会转换为常规的 insert，可能导致唯一约束冲突
插入SQL中不支持 default 列值语法，如 INSERT IGNORE INTO tbl(col) values (default);
示例
create table insert_ignore_1_20250804_34513(
c1 int,
c2 int,
c3 int,
c4 int,
c5 int,
c6 int,
primary key(c1)
);
-- 转换前 MySQL SQL:
insert ignore into insert_ignore_1_20250804_34513(c1,c2,c3,c4,c5,c6) values (1,1,1,1,1,1);
insert ignore into insert_ignore_1_20250804_34513(c1,c2,c3,c4,c5,c6) values (5,5,5,5,5,5),(6,6,6,6,6,6),(7,7,7,7,7,7);
insert ignore into insert_ignore_1_20250804_34513(c1,c2,c3,c4,c5,c6) select c1,c2,c3,c4,c5,c6 from insert_ignore_1_20250804_34513 where c1=1 union select 17,17,17,17,17,17 from dual;
-- 转换后 DM SQL:
MERGE INTO insert_ignore_1_20250804_34513 AS target_table USING (SELECT 1 AS c1,1 AS c2,1 AS c3,1 AS c4,1 AS c5,1 AS c6 FROM DUAL) source_table ON (target_table.c1=source_table.c1) WHEN NOT MATCHED THEN INSERT (c1,c2,c3,c4,c5,c6) VALUES (source_table.c1,source_table.c2,source_table.c3,source_table.c4,source_table.c5,source_table.c6);
MERGE INTO insert_ignore_1_20250804_34513 AS target_table USING (SELECT 5 AS c1,5 AS c2,5 AS c3,5 AS c4,5 AS c5,5 AS c6 FROM DUAL UNION SELECT 6 AS c1,6 AS c2,6 AS c3,6 AS c4,6 AS c5,6 AS c6 FROM DUAL UNION SELECT 7 AS c1,7 AS c2,7 AS c3,7 AS c4,7 AS c5,7 AS c6 FROM DUAL) source_table ON (target_table.c1=source_table.c1) WHEN NOT MATCHED THEN INSERT (c1,c2,c3,c4,c5,c6) VALUES (source_table.c1,source_table.c2,source_table.c3,source_table.c4,source_table.c5,source_table.c6);
MERGE INTO insert_ignore_1_20250804_34513 AS target_table USING (SELECT 9 AS c1,9 AS c2,9 AS c3,9 AS c4,9 AS c5,9 AS c6 FROM DUAL UNION SELECT 10 AS c1,10 AS c2,10 AS c3,10 AS c4,10 AS c5,10 AS c6 FROM DUAL UNION SELECT 11 AS c1,11 AS c2,11 AS c3,11 AS c4,11 AS c5,11 AS c6 FROM DUAL) source_table ON (target_table.c1=source_table.c1) WHEN NOT MATCHED THEN INSERT (c1,c2,c3,c4,c5,c6) VALUES (source_table.c1,source_table.c2,source_table.c3,source_table.c4,source_table.c5,source_table.c6);
1.3.3.3.8.2. REPLACE INTO
语法
REPLACE INTO table_name [(column1, ...)] VALUES (value1, ...) [, ...];
REPLACE INTO table_name [(column1, ...)] SELECT ...;
警告
使用该转换功能时，需要预先加载目标库的元数据，否则转换不生效(依赖的唯一约束需要通过 ALTER TABLE XX ADD PRIMARY KEY/CONSTRAINT UNIQUE 添加)；加载和匹配元数据时不区分表名和列名的大小写，因此不支持同时加载两个或多个除大小写之外一样的表或列元数据，否则可能导致转换异常。
当前不支持 replace into select * 语法
自增主键行为有差异：mysql 自增主键冲突后会增长，而 merge 自增主键冲突后不会增长
插入的列必须至少覆盖一个完整的唯一约束，除非表本身无唯一约束，则将其转换为 insert into 语句，否则报错。
插入SQL中不支持 default 列值语法，如 REPLACE INTO tbl(col) values (default);
插入SQL匹配目标表的多个唯一约束时，将转换为 delete+insert 两句SQL进行处理，对于存在绑定变量的情况，绑定变量的位置和个数可能会发生变化，此种情况仅在配合sql-convert-runtime使用时会自动处理，c接口和通过统一SQL对外接口转换后的情况需要手动计算并设置绑定变量的值。
replace into 语法中，如果待插入数据集本身存在冲突，则转换为 merge 时在目标库执行报错。
示例
create table replace_into_1_20250811_34513(
c1 int,
c2 int,
c3 int,
c4 int,
c5 int,
c6 int,
primary key(c1)
);
-- 转换前 MySQL SQL:
replace into replace_into_1_20250811_34513(c1,c2,c3,c4,c5,c6) values (1,1,1,1,1,1);
replace into replace_into_1_20250811_34513(c1,c2,c3,c4,c5,c6) values (5,5,5,5,5,5),(6,6,6,6,6,6),(7,7,7,7,7,7);
replace into replace_into_1_20250811_34513(c1,c2,c3,c4,c5,c6) select c1,c2,c3,c4,c5,c6 from replace_into_1_20250811_34513 where c1=1 union select 17,17,17,17,17,17 from dual;
-- 转换后 DM SQL:
MERGE INTO replace_into_1_20250811_34513 AS insert_table USING (SELECT 1 AS c1,1 AS c2,1 AS c3,1 AS c4,1 AS c5,1 AS c6 FROM DUAL) source_table ON (insert_table.c1=source_table.c1) WHEN MATCHED THEN UPDATE SET c2=source_table.c2,c3=source_table.c3,c4=source_table.c4,c5=source_table.c5,c6=source_table.c6 WHEN NOT MATCHED THEN INSERT (c1,c2,c3,c4,c5,c6) VALUES (source_table.c1,source_table.c2,source_table.c3,source_table.c4,source_table.c5,source_table.c6);
MERGE INTO replace_into_1_20250811_34513 AS insert_table USING (SELECT 5 AS c1,5 AS c2,5 AS c3,5 AS c4,5 AS c5,5 AS c6 FROM DUAL UNION ALL SELECT 6 AS c1,6 AS c2,6 AS c3,6 AS c4,6 AS c5,6 AS c6 FROM DUAL UNION ALL SELECT 7 AS c1,7 AS c2,7 AS c3,7 AS c4,7 AS c5,7 AS c6 FROM DUAL) source_table ON (insert_table.c1=source_table.c1) WHEN MATCHED THEN UPDATE SET c2=source_table.c2,c3=source_table.c3,c4=source_table.c4,c5=source_table.c5,c6=source_table.c6 WHEN NOT MATCHED THEN INSERT (c1,c2,c3,c4,c5,c6) VALUES (source_table.c1,source_table.c2,source_table.c3,source_table.c4,source_table.c5,source_table.c6);
MERGE INTO replace_into_1_20250811_34513 AS insert_table USING (SELECT c1 AS c1,c2 AS c2,c3 AS c3,c4 AS c4,c5 AS c5,c6 AS c6 FROM replace_into_1_20250811_34513 WHERE c1=1 UNION SELECT 17 AS c1,17 AS c2,17 AS c3,17 AS c4,17 AS c5,17 AS c6 FROM dual) source_table ON (insert_table.c1=source_table.c1) WHEN MATCHED THEN UPDATE SET c2=source_table.c2,c3=source_table.c3,c4=source_table.c4,c5=source_table.c5,c6=source_table.c6 WHEN NOT MATCHED THEN INSERT (c1,c2,c3,c4,c5,c6) VALUES (source_table.c1,source_table.c2,source_table.c3,source_table.c4,source_table.c5,source_table.c6);
1.3.3.3.8.3. DELETE 单表
语法
DELETE FROM tbl_name
[WHERE where_condition]
[ORDER BY col_name [ASC | DESC]]
[LIMIT row_count]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
DELETE FROM test_table as t WHERE t.id = 1;
DELETE FROM test_table as t WHERE t.id = 1 ORDER BY name LIMIT 2;
-- 转换后DM SQL:
DELETE FROM test_table AS t WHERE t.id=1;
DELETE FROM test_table AS t WHERE rowid IN (SELECT rowid FROM test_table AS t WHERE t.id=1 ORDER BY name NULLS FIRST FETCH NEXT 2 ROWS ONLY )
1.3.3.3.8.4. DELETE 多表
语法
DELETE tbl_name [, tbl_name] ...
FROM table_references
[WHERE where_condition]
警告
- 多表删除目前限制：
where 中不支持order by、limit子句
不支持USING
联表删除单表时，delete 后面的名称必须和被删除的表名或别名完全一致
不支持 DELETE tbl_name.*
转换后的sql默认带/, 可由配置文件[unisql.anonymous.block.with.slash]来配置是否带/, 默认带/
示例
-- 转换前 MySQL SQL:
delete a,b from test_table a,test_table b where a.id = b.id;
DELETE a FROM tb_ci_base_object a, tb_ci_communication_endpoint b WHERE a.instance_id = b.instance_id AND b.instance_id = '1';
DELETE tb_ci_base_object, tb_ci_communication_endpoint FROM tb_ci_base_object , tb_ci_communication_endpoint WHERE tb_ci_base_object.instance_id = tb_ci_communication_endpoint.instance_id AND tb_ci_communication_endpoint.instance_id = '1';
-- 转换后 DM SQL:
DECLARE CURSOR cur_data IS SELECT DISTINCT a.rowid as a, b.rowid as b FROM test_table a , test_table b WHERE a.id=b.id;BEGIN FOR rec IN cur_data LOOP DELETE FROM test_table WHERE rowid=rec.a; DELETE FROM test_table WHERE rowid=rec.b; END LOOP; END;
/
DECLARE CURSOR cur_data IS SELECT DISTINCT a.rowid as a FROM tb_ci_base_object a , tb_ci_communication_endpoint b WHERE a.instance_id=b.instance_id AND b.instance_id='1';BEGIN FOR rec IN cur_data LOOP DELETE FROM tb_ci_base_object WHERE rowid=rec.a; END LOOP; END;
/
DECLARE CURSOR cur_data IS SELECT DISTINCT tb_ci_base_object.rowid as tb_ci_base_object, tb_ci_communication_endpoint.rowid as tb_ci_communication_endpoint FROM tb_ci_base_object , tb_ci_communication_endpoint WHERE tb_ci_base_object.instance_id=tb_ci_communication_endpoint.instance_id AND tb_ci_communication_endpoint.instance_id='1';BEGIN FOR rec IN cur_data LOOP DELETE FROM tb_ci_base_object WHERE rowid=rec.tb_ci_base_object; DELETE FROM tb_ci_communication_endpoint WHERE rowid=rec.tb_ci_communication_endpoint; END LOOP; END;
/
1.3.3.3.8.5. INSERT INTO … ON DUPLICATE KEY UPDATE
语法
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ SELECT ...
| table_name
}
[ON DUPLICATE KEY UPDATE assignment_list]
警告
- 统一SQL会将INSERT INTO … ON DUPLICATE KEY UPDATE 转为MERGE INTO 或匿名块（匿名块中使用游标和MERGE INTO），限制如下：
依赖元数据，无元数据会报错
主键自增问题,mysql中冲突后修改，自增键会跳跃增长。merge into或匿名块只是修改，自增键不会增加。两边库的自增键会对应不上
- 单条插入多键冲突，若插入的一条数据与表中不同行的多个唯一键分别冲突：
mysql 只会更新第一个检测到冲突的行（“第一个冲突行” 无确定规则，具有随机性）
merge into或匿名块所有冲突的行都会被更新
示例：表中
a=1和b=2分别在两行，执行INSERT INTO test VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;时，仅a=1的行会被更新（c 值 +1），b=2的行不变化
insert into SELECT 子不支持有 多表的
select *，只支持单表的select *- 对于更新主键的场景，转换后的merge inot 在DM中执行结果和mysql不同（oracle和mysql相同），通过参数
unisql.transform.insert.duplicate.update.unique控制是否支持更新主键，详情参考：unisql.conf文件说明 DM 中实际应该更新一行，但最终会更新一行，插入一行。 可以通过把DM的参数MERGE_OPT_FLAG设置为1来避免此问题（DM8.1）
- 对于更新主键的场景，转换后的merge inot 在DM中执行结果和mysql不同（oracle和mysql相同），通过参数
多行values和insert select 转换为匿名块执行，匿名块是否加斜杠通过参数unisql.anonymous.block.with.slash控制，具体请参考 unisql.conf文件说明
示例
-- 数据准备-mysql sql，使用前要加载元数据
CREATE TABLE unisql_test_insert_duplicate (
id INT NOT NULL,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
country VARCHAR(50),
last_updated DATETIME,
remarks VARCHAR(255),
PRIMARY KEY (id)
);
ALTER TABLE unisql_test_insert_duplicate add unique key(username);
ALTER TABLE unisql_test_insert_duplicate add unique key(email, country);
INSERT INTO unisql_test_insert_duplicate_43211 (id, username, email, country, last_updated, remarks)
VALUES (1, 'user1', 'user1@example.com', 'USA', '2025-04-15 11:36:35', 'Initial row');
-- 转换前MySQL SQL insert values:
INSERT INTO unisql_test_insert_duplicate (id, username, email, country, last_updated, remarks)
VALUES (1, 'other_user', 'other@example.com', 'UK', '2025-04-15 11:36:35', 'PK conflict test1')
ON DUPLICATE KEY UPDATE last_updated = VALUES(last_updated), remarks = concat(remarks, values(remarks));
-- 转换后:
MERGE INTO unisql_test_insert_duplicate AS target_table
USING (SELECT
target_table.rowid AS _unisql_rid,
source_table._insert_val_0,
source_table._insert_val_1,
source_table._insert_val_2,
source_table._insert_val_3,
source_table._insert_val_4,
source_table._insert_val_5
FROM
unisql_test_insert_duplicate target_table
RIGHT JOIN (SELECT
1 AS _insert_val_0,
'other_user' AS _insert_val_1,
'other@example.com' AS _insert_val_2,
'UK' AS _insert_val_3,
'2025-04-15 11:36:35' AS _insert_val_4,
'PK conflict test1' AS _insert_val_5
FROM
DUAL) source_table ON source_table._insert_val_0 = target_table.id OR (source_table._insert_val_1 = target_table.username) OR (source_table._insert_val_2 = target_table.email AND source_table._insert_val_3 = target_table.country)) source_table ON (source_table._unisql_rid = target_table.rowid)
WHEN MATCHED THEN
UPDATE
SET
target_table.last_updated = source_table._insert_val_4,
target_table.remarks = concat(target_table.remarks, source_table._insert_val_5)
WHEN NOT MATCHED THEN
INSERT (id, username, email, country, last_updated, remarks)
VALUES (source_table._insert_val_0, source_table._insert_val_1, source_table._insert_val_2, source_table._insert_val_3, source_table._insert_val_4, source_table._insert_val_5)
-- 转换前MySQL SQL insert select:
INSERT INTO unisql_test_insert_duplicate (id, username, email, country, last_updated, remarks)
select 1 as id, 'conflict1' as username, 'conflict1@example.com' as email, 'USA' as country, '2025-04-15 11:36:35' as last_updated, 'Updated by CUK multi' as remarks from dual
ON DUPLICATE KEY UPDATE last_updated = VALUES(last_updated), remarks = VALUES(remarks);
-- 转换后SQL，多行values 或select 转换为匿名块:
DECLARE
CURSOR cur_date IS SELECT 1 AS _insert_val_0,'conflict1' AS _insert_val_1,'conflict1@example.com' AS _insert_val_2,'USA' AS _insert_val_3,'2025-04-15 11:36:35' AS _insert_val_4,'Updated by CUK multi' AS _insert_val_5 FROM dual;
BEGIN
FOR rec IN cur_date LOOP
MERGE INTO unisql_test_insert_duplicate AS target_table USING (SELECT target_table.rowid AS _unisql_rid,source_table._insert_val_0,source_table._insert_val_1,source_table._insert_val_2,source_table._insert_val_3,source_table._insert_val_4,source_table._insert_val_5 FROM unisql_test_insert_duplicate target_table RIGHT JOIN (SELECT rec._insert_val_0 AS _insert_val_0,rec._insert_val_1 AS _insert_val_1,rec._insert_val_2 AS _insert_val_2,rec._insert_val_3 AS _insert_val_3,rec._insert_val_4 AS _insert_val_4,rec._insert_val_5 AS _insert_val_5 FROM DUAL) source_table ON source_table._insert_val_0=target_table.id OR (source_table._insert_val_1=target_table.username) OR (source_table._insert_val_2=target_table.email AND source_table._insert_val_3=target_table.country)) source_table ON (source_table._unisql_rid=target_table.rowid) WHEN MATCHED THEN UPDATE SET target_table.last_updated=source_table._insert_val_4,target_table.remarks=source_table._insert_val_5 WHEN NOT MATCHED THEN INSERT (id,username,email,country,last_updated,remarks) VALUES (source_table._insert_val_0,source_table._insert_val_1,source_table._insert_val_2,source_table._insert_val_3,source_table._insert_val_4,source_table._insert_val_5);
END LOOP;
END;
/
1.3.3.3.8.6. UPDATE 单表
语法
UPDATE [IGNORE] tbl_name
SET assignment_list
[WHERE where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT row_count]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
update test_table set name = 'John' where id = 1;
update test_table set name = (select 'John' from dual) where id = 1;
update test_table set name = 'John' order by id limit 1;
--- 更新非唯一键
update ignore test_table set name = 'John' where id = 1;
--- 更新唯一键
update ignore test_table set id =2 where id=1;
-- 转换后 SQL:
UPDATE test_table SET name='John' WHERE id=1
UPDATE test_table SET name=(SELECT 'John' FROM dual) WHERE id=1
UPDATE test_table SET name='John' WHERE rowid IN (SELECT test_table.rowid AS rid FROM test_table ORDER BY id FETCH NEXT 1 ROWS ONLY );
--- 更新非唯一键
UPDATE test_table SET name='John' WHERE id=1;
--- 更新唯一键
MERGE INTO test_table target_table
USING (SELECT test_table.rowid AS rid,2 AS v0,row_number() OVER (PARTITION BY test_table.rowid ORDER BY test_table.rowid) AS rn FROM test_table WHERE id=1) source_table
ON (source_table.rid=target_table.rowid AND source_table.rn=1)
WHEN MATCHED THEN
UPDATE SET target_table.id=source_table.v0
WHERE NOT EXISTS (SELECT 1 FROM test_table filter_table WHERE filter_table.ID=source_table.v0)
警告
- 统一SQL对单表update可能转为merge into，限制如下：
有ignore时，需要依赖元数据，没有则报错
有ignore时，不支持在一条SQL中把两行记录的唯一键更新为相同值，转换会成功，在DM中执行会失败，比如：1,2 都更新为3，在mysql中其中某一条数据会被更新为3，在DM中会更新失败
mysql 的update的ignore是对字段起效，同一行修改中冲突的字段修改会忽略，其他字段修改会成功，转为DM后是对行起效，有冲突的字段，整行修改都会被忽略
ignore 只支持忽略主键唯一键冲突
1.3.3.3.8.7. UPDATE 多表
语法
UPDATE [IGNORE] table_references
SET assignment_list
[WHERE where_condition]
示例
CREATE TABLE test_table (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
CREATE TABLE test_table1 (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20) UNIQUE,
age INT
);
-- 转换前MySQL SQL:
update test_table a, test_table1 b set a.name = b.name where a.id= b.id and b.name = 'John';
update ignore test_table a, test_table1 b set a.id = 100 where a.id= b.id and b.name = 'John';
update test_table a, test_table1 b set a.age=15, b.age=15 where a.id= b.id and b.name = 'John';
update ignore test_table a, test_table1 b set a.id=100, b.name='jake' where a.id= b.id and b.name = 'John';
-- 转换后 SQL:
--- 多表更新只更新一个表不转换
update test_table a, test_table1 b set a.name = b.name where a.id= b.id and b.name = 'John';
--- 多表更新带ignore转为merge into + not exists
MERGE INTO test_table target_table
USING (
SELECT
a.rowid AS rid,
100 AS v0,
row_number() OVER (PARTITION BY a.rowid
ORDER BY
a.rowid) AS rn
FROM
test_table a ,
test_table1 b
WHERE
a.id = b.id
AND b.name = 'John') source_table
ON(source_table.rid = target_table.rowid AND source_table.rn = 1)
WHEN MATCHED THEN
UPDATE SET target_table.id = source_table.v0
WHERE NOT EXISTS (
SELECT
1
FROM
test_table filter_table
WHERE
filter_table.ID = source_table.v0);
--- 更新多表转为匿名块
DECLARE
CURSOR cur_date IS SELECT a.rowid AS a_rid,b.rowid AS b_rid,15 AS a_v0,15 AS b_v1 FROM test_table a , test_table1 b WHERE a.id=b.id AND b.name='John';
BEGIN
FOR rec IN cur_date LOOP
UPDATE test_table a SET a.age=rec.a_v0 WHERE rowid = rec.a_rid;
UPDATE test_table1 b SET b.age=rec.b_v1 WHERE rowid = rec.b_rid;
END LOOP;
END;
--- 更新多表带ignore转为匿名块 + not exists
DECLARE
CURSOR cur_date IS SELECT a.rowid AS a_rid,b.rowid AS b_rid,100 AS a_v0,100 AS b_v1 FROM test_table a , test_table1 b WHERE a.id=b.id AND b.name='John';
BEGIN
FOR rec IN cur_date LOOP
UPDATE test_table a SET a.id=rec.a_v0 WHERE rowid = rec.a_rid AND NOT EXISTS (SELECT 1 FROM test_table filter_table WHERE filter_table.ID=rec.b_v0);
UPDATE test_table1 b SET b.id=rec.b_v1 WHERE rowid = rec.b_rid;
END LOOP;
END;
警告
- 统一SQL会将多表update转为merge into 或匿名块，限制如下：
更新字段必须指定表名或表别名，不指定不支持转换
有ignore时，需要依赖元数据，没有则报错
有ignore时，不支持在一条SQL中把两行记录的唯一键更新为相同值，转换会成功，在DM执行会失败，比如：1,2 都更新为3，在mysql 其中某一条数据会被更新为3，在DM会更新失败
mysql 的update的ignore是对字段起效，同一行修改中冲突的字段修改会忽略，其他字段修改会成功，转为DM后是对行起效，有冲突的字段，整行修改都会被忽略
ignore 只支持忽略主键唯一键冲突
转换为匿名块或MERGE INTO时绑定参数顺序可能改变，会返回绑定参数的新旧映射关系，使用提供的JDBC可以自动处理此问题
1.3.3.3.8.8. 查询分区表信息
语法
SELECT ... FROM INFORMATION_SCHEMA.PARTITIONS WHERE ...;
示例
-- 转换前MySQL SQL:
SELECT MAX(partition_description) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'TEST_TABLE';
-- 转换后DM SQL:
SELECT MAX(partition_description) FROM unisql.information_schema_partitions WHERE TABLE_SCHEMA='PUBLIC' AND TABLE_NAME='TEST_TABLE';