3.3.3. DML
3.3.3.1. GaussDB-Oracle
3.3.3.1.1. 主键或唯一约束冲突时更新该条数据，否则插入数据
语法
REPLACE INTO table_name(column_name [, …])
VALUES(…) [,(…)]
| SELECT_SUB_QUERY
警告
- 统一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);
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;
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;
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
示例
-- 转换前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;
-- 转换前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
3.3.3.1.5. 表删除
语法
-- 单表删除
DELETE FROM tbl_name [[AS] tbl_alias]
[WHERE where_condition]
[LIMIT row_count]
-- 多表删除
DELETE tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
警告
- 单表删除目前限制：
where 中不支持order by、limit子句
- 多表删除目前限制：
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;
-- 转换后GaussDB-Oracle SQL:
DELETE from test_table limit 10;
DELETE FROM test_table AS a WHERE a.id=1;
WITH a AS (DELETE FROM test_table AS a USING test_table AS b WHERE a.id=b.id RETURNING a.id) DELETE FROM test_table AS b USING a 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';
WITH tb_ci_base_object AS (DELETE FROM tb_ci_base_object USING 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' RETURNING tb_ci_base_object.instance_id) DELETE FROM tb_ci_communication_endpoint USING tb_ci_base_object WHERE tb_ci_base_object.instance_id=tb_ci_communication_endpoint.instance_id;
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;
-- 转换前MySQL SQL:
DELETE FROM mysql.user WHERE user = 'xxx';
-- 目前GaussDB-Oracle不支持删除数据库用户，转换报错(CONVERT ERROR)，不支持(not support)，具体如下：
(CONVERT ERROR)
[ [00002]not support error: not support ]
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;
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
3.3.3.1.8. 查看当前库所有表
语法
SHOW TABLES
示例
-- 转换前MySQL SQL:
SHOW TABLES;
-- 转换后GaussDB-Oracle SQL:
SELECT tablename FROM pg_tables WHERE schemaname=current_schema();
3.3.3.1.9. 查询分区表信息
语法
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';
3.3.3.1.10. 空值判断
语法
警告
对于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 c (
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
3.3.3.1.11. 删除用户
语法
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;
3.3.3.2. GoldenDB-Mysql
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`)
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`)
3.3.3.3. LightDB-Mysql
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)
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"
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
3.3.3.3.4. update
语法
[ WITH with_query [, ...] ]
UPDATE table_references
[FROM table_references]
SET assignment_list
[WHERE where_condition]
[RETURNING ReturningClause]
警告
- 统一SQL会将UPDATE多表更新语句转为WITH … UPDATE语句，限制如下：
目前仅支持源数据库为Mysql，目标数据库为LightDB-Mysql的场景;
更新表对象必须是2个以上;
示例
-- 转换前MySQL SQL:
UPDATE
tb_ci_base_object a, my_table c
SET
a.instance_id = a.instance_id,
a.name = 'John',
a.age = 30,
a.last_modified = '2025-01-01',
a.version = round(a.version + 0.01, 2),
c.instance_id = c.instance_id,
c.name = 'John',
c.age = 11,
c.last_modified = '2025-01-01',
c.version = round(c.version + 0.01, 2)
WHERE
a.instance_id = c.instance_id AND c.instance_id = 123;
-- 转换后Lightdb-Mysql SQL:
WITH `c` AS (
UPDATE
`my_table` AS `c`
SET
`c`.`instance_id` = `c`.`instance_id`,
`c`.`name` = 'John',
`c`.`age` = 11,
`c`.`last_modified` = '2025-01-01',
`c`.`version` = round(`c`.`version` + 0.01, 2)
FROM
`tb_ci_base_object` AS `a`
WHERE
`a`.`instance_id` = `c`.`instance_id`
AND `c`.`instance_id` = 123
RETURNING `c`.*
)
UPDATE
`tb_ci_base_object` AS `a`
SET
`a`.`instance_id` = `a`.`instance_id`,
`a`.`name` = 'John',
`a`.`age` = 30,
`a`.`last_modified` = '2025-01-01',
`a`.`version` = round(`a`.`version` + 0.01, 2)
FROM
`c`
WHERE
`a`.`instance_id` = `c`.`instance_id`
AND `c`.`instance_id` = 123;
3.3.3.3.5. 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"
3.3.3.4. OceanBase-Mysql
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)
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'
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;
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`;
3.3.3.4.5. 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;
-- 转换后Lightdb-Mysql SQL:
DELETE FROM "test_table" AS "t" WHERE "t"."id"=1 ORDER BY "t"."id" LIMIT 10;
3.3.3.4.6. 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;
-- 转换后Lightdb-Mysql SQL:
WITH "j" AS (DELETE FROM "job" AS "j" USING "person" AS "p" WHERE "p"."id"="j"."id" RETURNING "j".*) DELETE FROM "person" AS "p" USING "job" AS "j" WHERE "p"."id"="j"."id";
3.3.3.5. Oracle_19C
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不支持;
- 统一SQL会将INSERT IGNORE 转为 MERGE INTO，限制如下：
需要配合 元数据管理 功能使用；
主键自增问题，MySQL中冲突后是自增键会跳跃增长，出现跳号，所以两边库自增列会对应不上；
插入的列必须有主键或唯一键（如果是联合主键或联合唯一键，联合的列需要全部写出；多个唯一键时所有唯一键都要列出），否则转化为普通insert into语句；
INSERT IGNORE SELECT 子句不支持使用 select * ；
mysql插入的数据中的对应的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）
INSERT IGNORE 转为 MERGE INTO只支持了主键或唯一键冲突，其他check约束、外键等暂不支持。
- 统一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)这种情况
- 统一SQL多发比对在如上转换的限制上还有如下限制：
不支持列只有自增键一个字段的情况，不支持空和null问题
插入的列必须有主键或唯一键（如果是联合主键或联合唯一键，联合的列需要全部写出；多个唯一键时所有唯一键都要列出），否则merge into 中on关联条件无法获取
mysql多个唯一键冲突时（不同的行数据）只会更新第一个冲突的唯一键所在行。merge into 中如果存在多个唯一键用or连接，冲突的行都会更新。两边数据结果不一致
insert into SELECT 子句查询出来的主键或唯一键存在重复行数据时，mysql可以执行，merge into执行报错（主键或唯一键重复）
values表达式只支持字面值，不支持子查询、函数、别名等
ON DUPLICATE KEY UPDATE不支持更新主键，唯一键的列（merge into 中不支持更新ON 子句中引用的列）；更新的列在插入列中必须存在
boolean类型 not true , not false不支持
自增序列插入null 不支持，oracle中序列不支持null
mysql和oracle两库之间null的排序规则不一致
不支持limit；mysql中LIMIT不支持和IN/ALL/ANY/SOME subquery 一起使用
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);
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 会更新失败
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_v0 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_v0 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_v0 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_v0 WHERE rowid = rec.b_rid;
END LOOP;
END;
警告
- 统一SQL会将多表update转为merge into 或匿名块，限制如下：
更新字段必须指定表名，不指定不支持转换
不支持在一条SQL中把两行记录的唯一键更新为相同值，转换会成功，在oracle 执行会失败，比如：1,2 都更新为3，在mysql 其中某一条数据会被更新为3，在oracle 会更新失败
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;
3.3.3.6. OceanBase-Oracle
3.3.3.6.1. INSERT INTO
语法
INSERT
[INTO] tbl_name
[(col_name [, col_name] ...)]
VALUES ( value_expr )
示例
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|
警告
在 MySQL 中，如果表的主键是自增列（AUTO_INCREMENT），即使手动指定了 id 的值，后续的自动分配仍会基于手动指定的最大值递增，确保连续性。 而在 OceanBase-Oracle 中手动指定 id 后，SEQUENCE 的下一个值仍然基于其自身的递增逻辑，与手动插入的最大 id 无关。
3.3.3.6.2. 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 会更新失败
3.3.3.6.3. 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_v0 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_v0 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_v0 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_v0 WHERE rowid = rec.b_rid;
END LOOP;
END;
警告
- 统一SQL会将多表update转为merge into 或匿名块，限制如下：
更新字段必须指定表名，不指定不支持转换
不支持在一条SQL中把两行记录的唯一键更新为相同值，转换会成功，在oracle 执行会失败，比如：1,2 都更新为3，在mysql 其中某一条数据会被更新为3，在oracle 会更新失败
3.3.3.6.4. 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;
-- 转换后OceanBase-Oracle SQL:
DELETE FROM test_table t WHERE t.id = 1;