12.3. SQL 语法

12.3.1. DDL

本节主要介绍统一SQL支持原生MySQL数据库中的SQL的DDL操作。

12.3.1.1. 创建库

语法

CREATE DATABASE [IF NOT EXISTS] DB_NAME

警告

可通过参数unisql.change.database.to.schema配置来决定通过统一SQL转换后逻辑：

当unisql.change.database.to.schema=1时，统一SQL转换为创建模式；CREATE SCHEMA schema_name;

当unisql.change.database.to.schema=0时，统一SQL转换为创建库；CREATE DATABASE db_name;

示例

-- 转换前MySQL SQL:
CREATE DATABASE IF NOT EXISTS testdb;

-- 统一SQL配置unisql.change.database.to.schema=1，转换后GaussDB-Oracle SQL:
CREATE SCHEMA testdb;

-- 统一SQL配置unisql.change.database.to.schema=0，转换后GaussDB-Oracle SQL:
CREATE DATABASE testdb;

12.3.1.2. 删除库

语法

DROP DATABASE [IF EXISTS] DB_NAME

警告

可通过参数unisql.change.database.to.schema配置决定统一SQL转换后对象为DATABASE或SCHEMA：

当unisql.change.database.to.schema=1时，统一SQL转换为删除模式；DROP SCHEMA [IF EXISTS] schema_name CASCADE;

当unisql.change.database.to.schema=0时，统一SQL转换为删除库；DROP DATABASE [IF EXISTS] db_name;

unisql.change.database.to.schema参数值默认为0。

示例

-- 转换前MySQL SQL:
DROP DATABASE IF EXISTS testdb;

-- 统一SQL配置unisql.change.database.to.schema=1，转换后GaussDB-Oracle SQL:
DROP SCHEMA IF EXISTS testdb CASCADE;

-- 统一SQL配置unisql.change.database.to.schema=0，转换后GaussDB-Oracle SQL:
DROP DATABASE IF EXISTS testdb;

12.3.1.3. 切换库

语法

USE DB_NAME

警告

DB_NAME 不能为mysql或GaussDB-Oracle关键字，否则在执行过程中报错。

示例

-- 转换前MySQL SQL:
   USE DB_NAME

-- 转换后GaussDB-Oracle SQL:
   SET SCHEMA 'DB_NAME'

12.3.1.4. 创建普通表

语法

CREATE TABLE [IF NOT EXISTS] TABLE_NAME(
    COLUMN_NAME1 DATATYPE [AUTO_INCREMENT] [DEFAULT ...] [ON UPDATE CURRENT_TIMESTAMP] [ZEROFILL]
    [CHARSET | CHARACTER SET CHARSET_NAME] [COLLATE COLLATION_NAME] [COMMENT COMMENT_STRING]
    [, ...]
    [, PRIMARY KEY(COLUMN_NAME [, ...] [USING BTREE])]
    [, INDEX INDEX_NAME(COLUMN_NAME [, ...] [USING BTREE])]
)
[ENGINE = ENGINE_NAME]
[[DEFAULT] CHARSET | CHARACTER SET [=] CHARSET_NAME]
[[DEFAULT] COLLATE [=] COLLATION_NAME]
[ROW_FORMAT [=] FORMAT_NAME]
[COMMENT [=] COMMENT_STRING]

警告

对于CHARACTER/CHARACTER SET、COLLATE、ROW_FORMAT可选字段，统一SQL均为语法支持，无实际功能；支持创建前缀索引语法，无实际功能；

对于表中列指定了自增主键，当向主键列中null值时，等同于default会自动生成自增主键值，例如：

– 转换前MySQL SQL（id为自增主键列）: INSERT INTO t1(id, name) VALUES(NULL, ‘test’);

– 转换后GaussDB-Oracle SQL: INSERT INTO t1(id, name) VALUES(DEFAULT, ‘test’);

支持PRIMARY KEY、UNIQUE KEY、KEY、INDEX对象带备注，如对象没有指定名称则根据规则：表名_指定列(多个列间用下划线拼接)_后缀生成对象名称，说明：PRIMARY KEY后缀为 _pkey、UNIQUE KEY后缀为 _key、KEY和INDEX后缀为 _idx；

一个表多个对象的名称超过63个字符，且前63个字符一致，因GAUSSDB_ORACLE会截取前63个字符作为对象名称，会导致后创建的对象失败，这种统一sql不做特殊处理;

GAUSSDB_ORACLE中相同名称的约束只能创建一个，不同表产生相同名称的约束对象统一sql不做特殊处理。

示例

-- 转换前MySQL SQL:
CREATE TABLE IF NOT EXISTS test_table(
    id int AUTO_INCREMENT,
    co1 varchar(20) DEFAULT 'abc' COMMENT 'this is a test column',
    co2 text CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
    co3 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    co4 numeric(5, 3) ZEROFILL,
    co5 varchar(200),
    PRIMARY KEY(id),
    INDEX idx_co12(co1, co2)，
    KEY idx_co5(co5(50))
)
ENGINE=InnoDB
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE=utf8_bin
ROW_FORMAT = COMPACT
COMMENT = 'this is test table';

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE IF NOT EXISTS test_table (
    id serial,
    co1 nvarchar2(20) DEFAULT 'abc' ,
    co2 text NOT NULL,
    co3 timestamp DEFAULT CURRENT_TIMESTAMP(0),
    co4 numeric(5,3),
    PRIMARY KEY(id)
) ;

COMMENT ON TABLE test_table IS 'this is test table';
COMMENT ON COLUMN test_table.co1 IS 'this is a test column';
CREATE INDEX idx_co12 ON test_table(co1,co2);
CREATE INDEX idx_co5 ON test_table(co5);


-- 转换前MySQL SQL:
CREATE TABLE EXAMPLE_TABLE (
    COLUMN1 INT AUTO_INCREMENT COMMENT 'COLUMN1',
    COLUMN2 VARCHAR ( 255 ) NOT NULL COMMENT 'COLUMN2',
    COLUMN3 VARCHAR ( 50 ) NOT NULL COMMENT 'COLUMN3',
    COLUMN4 INT COMMENT 'COLUMN4',
    COLUMN5 INT COMMENT 'COLUMN5',
    COLUMN6 INT COMMENT 'COLUMN6',
    COLUMN7 INT COMMENT 'COLUMN7',
    COLUMN8 INT COMMENT 'COLUMN8',
    COLUMN9 INT COMMENT 'COLUMN9',
    COLUMN10 INT COMMENT 'COLUMN10',
    PRIMARY KEY ( COLUMN1 ) COMMENT '主键 ',
    UNIQUE KEY ( COLUMN2, COLUMN3, COLUMN4 ) COMMENT '唯一索引',
    KEY ( COLUMN5 ) COMMENT 'KEY',
INDEX ( COLUMN6, COLUMN7, COLUMN8, COLUMN9, COLUMN10 ) COMMENT '索引'
) COMMENT = '用户信息表';

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE EXAMPLE_TABLE (
    COLUMN1 serial,
    COLUMN2 nvarchar2 ( 765 ) NOT NULL,
    COLUMN3 nvarchar2 ( 150 ) NOT NULL,
    COLUMN4 INT,
    COLUMN5 INT,
    COLUMN6 INT,
    COLUMN7 INT,
    COLUMN8 INT,
    COLUMN9 INT,
    COLUMN10 INT,
    CONSTRAINT example_table_pkey PRIMARY KEY ( COLUMN1 ),
CONSTRAINT example_table_column2_column3_column4_key UNIQUE ( COLUMN2, COLUMN3, COLUMN4 ));
CREATE INDEX example_table_column5_idx ON EXAMPLE_TABLE ( COLUMN5 );
CREATE INDEX example_table_column6_column7_column8_column9_column10_idx ON EXAMPLE_TABLE ( COLUMN6, COLUMN7, COLUMN8, COLUMN9, COLUMN10 );
COMMENT ON TABLE EXAMPLE_TABLE IS '用户信息表';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN1 IS 'COLUMN1';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN2 IS 'COLUMN2';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN3 IS 'COLUMN3';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN4 IS 'COLUMN4';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN5 IS 'COLUMN5';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN6 IS 'COLUMN6';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN7 IS 'COLUMN7';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN8 IS 'COLUMN8';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN9 IS 'COLUMN9';
COMMENT ON COLUMN EXAMPLE_TABLE.COLUMN10 IS 'COLUMN10';
COMMENT ON CONSTRAINT example_table_pkey ON EXAMPLE_TABLE IS '主键 ';
COMMENT ON CONSTRAINT example_table_column2_column3_column4_key ON EXAMPLE_TABLE IS '唯一索引';
COMMENT ON INDEX example_table_column5_idx IS 'KEY';
COMMENT ON INDEX example_table_column6_column7_column8_column9_column10_idx IS '索引'

12.3.1.5. 修改普通表

语法

ALTER TABLE TABLE_NAME
[DROP PRIMARY KEY | INDEX INDEX_NAME]
| ADD [COLUMN] [IF NOT EXISTS] COLUMN_NAME DATATYPE [CHARACTER SET CHARSET_NAME] [COLLATE COLLATE_NAME] [DEFAULT ...] [COMMENT COMMENT_STRING]
| MODIFY [COLUMN] COLUMN_NAME DATATYPE [CHARACTER SET CHARSET_NAME] [COLLATE COLLATE_NAME] [DEFAULT ...] [COMMENT COMMENT_STRING]
| CHANGE [COLUMN] COLUMN_OLD_NAME COLUMN_NEW_NAME DATATYPE [CHARACTER SET CHARSET_NAME] [COLLATE COLLATE_NAME] [DEFAULT ...] [COMMENT COMMENT_STRING]
| ADD INDEX | KEY INDEX_NAME(COLUMN_NAME [, ...])
| RENAME INDEX | KEY INDEX_OLD_NAME TO INDEX_NEW_NAME
| CONVERT TO CHARACTER SET CHARSET_NAME
| COMMENT COMMENT_STRING

警告

ALTER TABLE…CONVERT TO CHARACTER SET…仅语法支持，无实际功能，且受配置参数控制，config/unisql.conf中unisql.replace.sql参数可配置需要替换的sql语句。
ALTER TABLE TABLE_NAME change 中 DATATYPE 为 double(p,s) 时转换为decimal(p,s),p、s参考数据类型章节; (numeric和decimal是等价的)。
ALTER TABLE TABLE_NAME change 中 DATATYPE 为 datetime 时转换为timestamp(0);(mysql datetime对应无精度的timestamp)。
ALTER TABLE TABLE_NAME MODIFY|CHANGE 在 GaussDB 中修改类型需要有类型转换逻辑，不是所有类型都支持转换，类型是否可以修改，需要遵循 GaussDB 的约束规则,比如blob类型修改成int不支持。
ALTER TABLE TABLE_NAME ADD 的IF NOT EXISTS 转化到GaussDB-Oracle后被忽略，所以添加表中已经存在的列，在GaussDB-Oracle执行会报错。

示例

-- 转换前MySQL SQL:
ALTER TABLE test_table DROP PRIMARY KEY;
ALTER TABLE test_table DROP INDEX idx_co12;
ALTER TABLE test_table ADD COLUMN IF NOT EXISTS co5 VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'this is a comment';
ALTER TABLE test_table MODIFY COLUMN co5 text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'test column' COMMENT 'this is another comment';
ALTER TABLE test_table CHANGE COLUMN co5 co5_new blob DEFAULT NULL COMMENT 'this is a new comment';
ALTER TABLE test_table ADD INDEX idx_co5(co5);
ALTER TABLE test_table ADD KEY idx_co5_another(co5);
ALTER TABLE test_table RENAME INDEX idx_co5 TO idx_co5_new;
ALTER TABLE test_table CONVERT TO CHARACTER SET utf8;
ALTER TABLE test_table COMMENT 'this is a new table comment';
ALTER TABLE test_table ADD COLUMN mediumintColumn mediumint DEFAULT 0 ;

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE test_table DROP CONSTRAINT test_table_pkey;
DROP INDEX idx_co12;

ALTER TABLE test_table ADD co5 nvarchar2(20) DEFAULT NULL;
COMMENT ON COLUMN test_table.co5 IS 'this is a comment';

ALTER TABLE test_table ALTER COLUMN co5 TYPE text;
ALTER TABLE test_table ALTER COLUMN co5 SET DEFAULT 'test column';
COMMENT ON COLUMN test_table.co5 IS 'this is another comment';

ALTER TABLE test_table RENAME co5 TO co5_new;
ALTER TABLE test_table ALTER COLUMN co5_new TYPE bytea;
ALTER TABLE test_table ALTER COLUMN co5_new SET DEFAULT NULL;
COMMENT ON COLUMN test_table.co5_new IS 'this is a new comment';

CREATE INDEX idx_co5 ON test_table (co5);

CREATE INDEX idx_co5_another ON test_table (co5);

ALTER INDEX idx_co5 RENAME TO idx_co5_new;

-- unisql.replace.sql = 'select 1'
select 1;

COMMENT ON TABLE test_table IS 'this is a new table comment';

ALTER TABLE unisql_mediumint_test ADD mediumintColumn int DEFAULT 0;

12.3.1.6. 创建分区表

语法

CREATE TABLE [IF NOT EXISTS] TABLE_NAME(
    COLUMN_NAME1 DATATYPE [AUTO_INCREMENT] [DEFAULT ...] [ON UPDATE CURRENT_TIMESTAMP] [ZEROFILL]
    [CHARACTER SET CHARSET_NAME] [COLLATE COLLATION_NAME] [COMMENT COMMENT_STRING]
    [, ...]
    [, PRIMARY KEY(COLUMN_NAME [, ...] [USING BTREE])]
    [, INDEX INDEX_NAME(COLUMN_NAME [, ...] [USING BTREE])]
)
PARTITION BY RANGE [COLUMNS](COLUMN_NAME)(
    PARTITION PARTITION_NAME VALUES LESS THAN (VALUE_1)
    [, ...]
)

示例

-- 转换前MySQL SQL:
CREATE TABLE IF NOT EXISTS test_table_partition(
        id int AUTO_INCREMENT,
        co1 varchar(20) DEFAULT 'abc' COMMENT 'this is a test column',
        co2 text NOT NULL CHARSET 'utf8' COLLATE 'utf8_bin',
        co3 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        co4 numeric(5, 3) ZEROFILL,
        PRIMARY KEY(id),
        INDEX idx_co12(co1, co2)
    )
    PARTITION BY RANGE COLUMNS(id)
    (
        PARTITION p0 VALUES LESS THAN (100),
        PARTITION p1 VALUES LESS THAN (200),
        PARTITION p2 VALUES LESS THAN (300)
    );

-- 转换后GaussDB-Oracle SQL:
CREATE TABLE IF NOT EXISTS test_table (
    id serial,
    co1 nvarchar2(20) DEFAULT 'abc' ,
    co2 text NOT NULL,
    co3 timestamp DEFAULT CURRENT_TIMESTAMP(0),
    co4 NUMERIC(5, 3),
    PRIMARY KEY(id)
    ) PARTITION BY RANGE (id) (
        PARTITION p0
        VALUES LESS THAN (100),
        PARTITION p1
        VALUES LESS THAN (200),
        PARTITION p2
        VALUES LESS THAN (300)
    );

COMMENT ON COLUMN test_table.co1 IS 'this is a test column';
CREATE INDEX idx_co12 ON test_table (co1, co2);

12.3.1.7. 创建用户

语法

CREATE USER USER_NAME@HOST_NAME IDENTIFIED BY PASSWORD

示例

-- 转换前MySQL SQL:
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';

-- 转换后GaussDB-Oracle SQL:
CREATE USER "user1" IDENTIFIED BY '123456';

12.3.1.8. 修改用户

语法

ALTER USER USER_NAME@HOST_NAME
[IDENTIFIED BY NEW_PASSWORD]
| [ACCOUNT LOCK | UNLOCK]

示例

-- 转换前MySQL SQL:
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'abc567890';
ALTER USER 'user1'@'localhost' ACCOUNT LOCK;
ALTER USER 'user1'@'localhost' ACCOUNT UNLOCK;

-- 转换后GaussDB-Oracle SQL:
ALTER USER "user1" IDENTIFIED BY 'abc567890';
ALTER USER "user1" ACCOUNT LOCK;
ALTER USER "user1" ACCOUNT UNLOCK;

12.3.1.9. 设置外键约束

语法

SET FOREIGN_KEY_CHECKS = {0 | 1}

警告

其中 0 表示禁用外键约束，1 表示启用外键约束

示例

-- 转换前MySQL SQL:
SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;

-- 转换后GaussDB-Oracle SQL:
seelct 1;

12.3.1.10. 修改自增列的起始值

语法

ALTER TABLE table_name AUTO_INCREMENT = number

警告

修改自增列的起始值目前限制：

依赖unisql.conf文件中配置unisql.auto.increment.column的相关内容；
GaussDB-Oracle默认的序列名称为：表名_列名_seq,本次转化使用GaussDB-Oracle默认的序列名称，用户需要保证模式下序列名称唯一；
MySQL的修改自增列的起始值是DDL语句，转化后变成DQL语句；
对于MySQL的Bigint类型自增列，转化到GaussDB-Oracle后类型为Bigserial,所以起始值范围：1到2^63 - 1；MySQL的其他整形类型，转化到GaussDB-Oracle后类型为serial,所以起始值范围：1到2^31 - 1。

示例

CREATE TABLE test_table_auto_increment (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY (id)
) ENGINE=InnoDB;

-- 转换前MySQL SQL:
ALTER TABLE test_table_auto_increment AUTO_INCREMENT = 100;

-- 转换后GaussDB-Oracle SQL:
SELECT setval('test_table_auto_increment_id_seq', 100, FALSE)

12.3.2. DML

12.3.2.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);

12.3.2.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;

12.3.2.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;

12.3.2.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;

12.3.2.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 ]

12.3.2.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;

12.3.2.7. 强制索引查询

语法

SELECT ... FROM ... FORCE INDEX(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;

12.3.2.8. 查看当前库所有表

语法

SHOW TABLES

示例

-- 转换前MySQL SQL:
SHOW TABLES;

-- 转换后GaussDB-Oracle SQL:
SELECT tablename FROM pg_tables WHERE schemaname=current_schema();

12.3.2.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';

12.3.2.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文件说明。

示例

-- 转换前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

12.3.2.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;

12.3.3. 其他

12.3.3.1. 权限加载生效

语法

FLUSH PRIVILEGES

警告