3.3.4. DDL

3.3.4.1. GaussDB-Oracle

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

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

3.3.4.1.3. 切换库

语法

USE DB_NAME

警告

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

示例

-- 转换前MySQL SQL:
   USE DB_NAME

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

3.3.4.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 '索引'

-- 转换前MySQL SQL:
create table GROUPS (
    id int,
    name varchar(100),
    name_desc varchar(100),
    trans_name varchar(100),
    test_values varchar(100),
    values1 varchar(100),
    GROUPS int
);

-- 转换后GaussDB-Oracle SQL:
create table GROUPS (id int,
    name nvarchar2(300),
    name_desc nvarchar2(300),
    trans_name nvarchar2(300),
    test_values nvarchar2(300),
    values1 nvarchar2(300),
    GROUPS int
)

3.3.4.1.5. 修改普通表

语法

ALTER TABLE [IF EXISTS] 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执行会报错。
ALTER TABLE IF EXISTS TABLE_NAME 后仅支持 DROP PRIMARY KEY 、ADD [COLUMN] xxx、MODIFY [COLUMN] xxx、CHANGE [COLUMN] xxx 语法。

示例

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

-- 转换前MySQL SQL:
ALTER TABLE if exists unisql_table_alter_table DROP PRIMARY KEY;
ALTER TABLE if exists unisql_table_alter_table ADD COLUMN IF NOT EXISTS co6 VARCHAR(20) CHARACTER SET utf8 COLLATE  utf8_general_ci DEFAULT NULL COMMENT 'this is a comment';
ALTER TABLE if exists unisql_table_alter_table MODIFY COLUMN co5 text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'test column' COMMENT 'this is another comment';
ALTER TABLE if exists unisql_table_alter_table CHANGE COLUMN co5 co51 text DEFAULT NULL COMMENT 'this is a new comment';

-- 转换后GaussDB-Oracle SQL:
ALTER TABLE IF EXISTS unisql_table_alter_table DROP CONSTRAINT unisql_table_alter_table_pkey;

ALTER TABLE IF EXISTS unisql_table_alter_table ADD co6 nvarchar2(60) DEFAULT NULL;
COMMENT ON COLUMN unisql_table_alter_table.co6 IS 'this is a comment';

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

ALTER TABLE IF EXISTS unisql_table_alter_table RENAME co5 TO co51;
ALTER TABLE IF EXISTS unisql_table_alter_table ALTER COLUMN co51 TYPE text;
ALTER TABLE IF EXISTS unisql_table_alter_table ALTER COLUMN co51 SET DEFAULT NULL;
COMMENT ON COLUMN unisql_table_alter_table.co51 IS 'this is a new comment';

3.3.4.1.6. 创建分区表

语法

CREATE TABLE [IF NOT EXISTS] TABLE_NAME(
    COLUMN_NAME1 DATATYPE [AUTO_INCREMENT] [DEFAULT ...] [ON UPDATE CURRENT_TIMESTAMP] [ZEROFILL]
    [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 COLLATE 'utf8_bin',
        co3 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        co4 numeric(5, 3) ZEROFILL,
        PRIMARY KEY(id),
        INDEX idx_co12(co1)
    )
    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_partition (
    id serial,
    co1 nvarchar2(60) DEFAULT 'abc' ,
    co2 text NOT NULL,
    co3 timestamp DEFAULT CURRENT_TIMESTAMP(0),
    co4 numeric(5,3),
    CONSTRAINT test_table_partition_pkey 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)
    );
CREATE INDEX idx_co12 ON test_table_partition (co1);
COMMENT ON COLUMN test_table_partition.co1 IS 'this is a test column';

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

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

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

3.3.4.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)

3.3.4.1.11. 优化存储空间

语法

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...

警告

GaussDB-Oracle(V500)不支持OPTIMIZE TABLE语法，对应的语法为 VACUUM tbl_name 且VACUUM只支持单表。

示例

--转化前转换前MySQL SQL:
OPTIMIZE LOCAL TABLE  students,courses;
OPTIMIZE NO_WRITE_TO_BINLOG TABLE  students,courses,enrollments;

--转化后GaussDB-Oracle SQL:
VACUUM students; VACUUM courses;
VACUUM students; VACUUM courses; VACUUM enrollments;