4.3.3. DML
4.3.3.1. GaussDB-Oracle
4.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);
4.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;
4.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;
4.3.3.1.4. 联表更新
语法
UPDATE table1 [, table2] SET column1=value1 [, column2=value2] [where ...];
警告
不支持with子句组合； 联表更新时最多支持两张表更新； 当两张表更新时，SET列值时，列名必须带表名或表别名，例如 update…set a.id=1 或 update…set test_table.id=2。
示例
-- 转换前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;
4.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 ]
4.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;
4.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
4.3.3.1.8. 查看当前库所有表
语法
SHOW TABLES
示例
-- 转换前MySQL SQL:
SHOW TABLES;
-- 转换后GaussDB-Oracle SQL:
SELECT tablename FROM pg_tables WHERE schemaname=current_schema();
4.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';
4.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
4.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;
4.3.3.2. GoldenDB-Mysql
4.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`)