Oracle语法与mysql/pg异同&gorm如何支持oracle&支持达梦？

之前分享过mysql和postgresql差异？gorm如何同时兼容？此篇文章在上次基础上又对oracle进行了调研。目前来看，go对于oracle支持较不友好，文章中的驱动组件请不要轻易用在生产环境。

1. 背景

  接上篇文章背景来讲，除了可能支持postgresql外，oracle目前也是使用场景较多的一个数据库。此时我们需要考虑，如何让go项目在数据库切换为oracle情况下还能正常运作？除此之外，文中还对达梦数据库做了一些小研究，分析了与oracle的异同。另外，gorm目前并没有对oracle原生支持的驱动，只能通过开源的一些gorm-driver来使用。但是这些驱动我在看完源码并使用下来之后发现还是存在不少问题，并不能很好的使用于正常环境。

本文是基于go角度来对oracle数据库展开讨论，包括兼容性、适配等。

本文不讲述两者的选型优劣，仅描述常见的基本差异，如基本类型、语法、DDL/DML、字符集、SDK以及平时使用时存在的问题做举例和总结，避免在业务改造兼容两者时花精力又踩坑。

Mysql 5.7/oracle 19c为例

2. 差异

2.1 基本类型

2.2 字符集

  oracle的字符集默认与数据库有关系，数据库创建后无法更改。所以在创建数据库的时候可以指定字符集和国家字符集。默认为AL32UTF8，通用字符集。对于pg和mysql默认字符集分别为UTF-8和latin1，但是mysql在8.0之后默认为utf8mb4。

2.3 DDL

2.3.1 创建表

  oracle在创建表中与mysql和pg有较多不同，下面内容oracle创建表的相关语法，如果要看mysql和pg相关建表语句，可以看本文引用的文章。

CREATE TABLE T1(
   id NUMBER(19) generated by default as identity,
   a_col NVARCHAR2(32),
   b_col CLOB,
   c_col NUMBER(2,0),
   d_col NVARCHAR2(128),
   e_col TIMESTAMP(0) DEFAULT SYSTIMESTAMP,
   f_col CLOB DEFAULT '{}',
   g_col NUMBER(1) DEFAULT 0,
   CONSTRAINT uniq_a_col UNIQUE (a_col),
   CONSTRAINT pk_id PRIMARY KEY (id),
   CONSTRAINT f_col_json CHECK ( f_col iS JSON )
);

-- 创建索引
CREATE INDEX idx_c_col_d_col ON t1(c_col, d_col);

-- 注释与pg十分相似
COMMENT ON TABLE T1 IS "T1表";
COMMENT ON COLUMN T1.id IS "id列";
COMMENT ON COLUMN T1.a_col IS "a_col列";
COMMENT ON COLUMN T1.b_col IS "b_col列";
COMMENT ON COLUMN T1.c_col IS "c_col列";
COMMENT ON COLUMN T1.d_col IS "d_col列";
COMMENT ON COLUMN T1.e_col IS "e_col列";
COMMENT ON COLUMN T1.f_col IS "f_col列";

注意：可能会要求在创表时判断是否存在，oracle在23版本之前都没有类似CREATE TABLE .. IF NOT EXISTS语法的，所以这里需要用特殊的方式实现：

DECLARE v_cnt NUMBER;
BEGIN
    SELECT COUNT(1) INTO v_cnt FROM USER_TABLES WHERE TABLE_NAME = 'T1';
    IF v_cnt = 0 THEN
        EXECUTE IMMEDIATE
        'CREATE TABLE T1(
            id NUMBER(19) generated by default as identity,
            a_col NVARCHAR2(32),
            b_col CLOB,
            c_col NUMBER(2,0),
            d_col NVARCHAR2(128),
            e_col TIMESTAMP(0) DEFAULT SYSTIMESTAMP,
            f_col CLOB DEFAULT ''{}'', -- 转义
            g_col NUMBER(1) DEFAULT 0,
            CONSTRAINT uniq_a_col UNIQUE (a_col),
            CONSTRAINT pk_id PRIMARY KEY (id),
            CONSTRAINT f_col_json CHECK ( f_col iS JSON )
        )';
    END IF;

    -- 判断索引是否存在
    SELECT COUNT(1) INTO v_cnt FROM USER_IND_COLUMNS WHERE INDEX_NAME = UPPER('idx_c_col_d_col');
    IF v_cnt = 0 THEN
        EXECUTE IMMEDIATE
        'CREATE INDEX idx_c_col_d_col ON T1(c_col, d_col)';
    END IF;
END;

PS：

• 在oracle中所有字段默认是大写的，即使创建表时写的是小写字段，创建成功后会转为大写。
• oracle在12c之前，通常使用触发器来实现自增的。12c开始提供自增列。使用generated by default as identity生成自增长序列是由系统自动生成的，如果删除表系统会将与之绑定的sequence放入回收站，我们无法手动删除。此时使用PURGE RECYCLEBIN即可删除掉。

2.3.2 列操作

  由于主要用mysql，所以以mysql进行对比，如果想看postgresql对于列操作的语法，可以看本文引用文章。

2.3.3 约束操作

2.3.4 索引操作

2.3.5 用户操作

2.3.6 小结

• 在表创建上，mysql和oracle有许多语法上的不同。包括类型，设置注释，设置索引等。
• 在列操作上，一般正常使用，语法其实和mysql没多大区别，基本类似。
• 在约束操作上，oracle支持check约束，其余基本一样。
• 在索引操作上，两者比较相似，不过mysql有create和alter两种语法，oracle只有create的方式。
• 在用户操作上，两者是不同的，差异较大。

2.4 DML

2.4.1 插入数据

• 语法

  oracle在插入语句上，和标准的sql语句基本一样，且支持returning语句。但是唯一不同的是，oracle不支持VALUES的批量插入。

-- oracle单条插入
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...) RETURNING ID INTO :xx;

• INSERT ALL批量插入(不推荐)

-- 官方提供的一种批量插入
INSERT ALL
INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)
INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)
INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)
SELECT 1 FROM DUAL;

PS：INSERT ALL用来给多张表插入数据，当然表可以是同一张。但是需要注意，这种批量插入方式需要自己设置自增ID的值，因为INSERT ALL并不会随着插入数据而自增ID，下面举一个例子：

INSERT ALL
    INTO T1 (A_COL,B_COL,C_COL,D_COL) values('1','你好1',1,'很好1')
    INTO T1 (A_COL,B_COL,C_COL,D_COL) values('2','你好2',2,'很好2')
    INTO T1 (A_COL,B_COL,C_COL,D_COL) values('3','你好3',3,'很好3')
SELECT 1 FROM DUAL;

但是报以下错误：

必须指定主键ID才可以插入成功。

• INSERT INTO ... SELECT批量插入

  使用该方式可以支持批量插入并且不需要指定ID，但是如果遇到冲突则无法ignore。此外，对于其他方式的批量插入，还有通过事务的方式一个一个进行插入，但是效率极其低下。

INSERT INTO T1(A_COL, B_COL, C_COL, D_COL, F_COL)
SELECT A_COL, B_COL, C_COL, D_COL, F_COL FROM (
    SELECT '11' A_COL, '11' B_COL, 11 C_COL, '11' D_COL, '{"nzx": 1}' F_COL FROM DUAL
    UNION
    SELECT '22' A_COL, '22' B_COL, 22 C_COL, '22' D_COL, '{"nzx": 2}' F_COL FROM DUAL
);

• MERGE INTO 批量插入时冲突解决

  MERGE INTO用来处理冲突时的结果，如果没有冲突则可以执行插入，根据这个特性可以实现批量插入，但是需要结合关键字UNION。

  冲突中WHEN MATCHED THEN可以不写，这样相当于ON CONFLICT DO NOTHING一样的效果。同理，WHEN NOT MATCHED THEN也可以不写，这样没有冲突也不会插入新数据。

-- 解决冲突
MERGE INTO T1
USING (SELECT
           0            AS ID,
           '11'         AS A_COL,
           '11'         AS B_COL,
           11           AS C_COL,
           '11'         AS D_COL,
           '{"nzx": 1}' AS F_COL
       FROM DUAL) excluded
ON (T1.ID = excluded.ID)
WHEN MATCHED THEN
    UPDATE
    SET
        B_COL=excluded.B_COL,
        C_COL=excluded.C_COL,
        D_COL=excluded.D_COL
WHEN NOT MATCHED THEN
    INSERT (A_COL, B_COL, C_COL, D_COL, F_COL)
    VALUES
        (excluded.A_COL, excluded.B_COL, excluded.C_COL, excluded.D_COL, excluded.F_COL);

-- 批量插入
MERGE INTO T1
USING (SELECT
           0            AS ID,
           '11'         AS A_COL,
           '11'         AS B_COL,
           11           AS C_COL,
           '11'         AS D_COL,
           '{"nzx": 1}' AS F_COL
       FROM
           DUAL
       UNION
       SELECT
           0            AS ID,
           '22'         AS A_COL,
           '22'         AS B_COL,
           22           AS C_COL,
           '22'         AS D_COL,
           '{"nzx": 2}' AS F_COL
       FROM
           DUAL) excluded
ON (T1.ID = excluded.ID)
WHEN MATCHED THEN
    UPDATE
    SET
        B_COL=excluded.B_COL,
        C_COL=excluded.C_COL,
        D_COL=excluded.D_COL
WHEN NOT MATCHED THEN
    INSERT (A_COL, B_COL, C_COL, D_COL, F_COL)
    VALUES
        (excluded.A_COL, excluded.B_COL, excluded.C_COL, excluded.D_COL, excluded.F_COL);

• 对于 bool 类型问题

  在oracle中没有bool/boolean类型，只能通过NUMBER代替。

• 批量插入性能对比

  目前插入有2种方式，一种通过一个一个插入方式，另一种为通过INSERT INTO ... SELECT方式插入。两者之间性能对比如下：

结果简直天差地别。

BenchmarkBatchCreate-12            1000000000                 0.1309 ns/op
BenchmarkSingleCreate-12                    1            16524782331 ns/op

2.4.2 更新数据

• 语法

  更新数据的语法与mysql以及postgresql大同小异。

UPDATE table_name SET column1=value1, column2=value2 WHERE some_column=some_value;

• RETURNING语法

  oracle在更新数据时也支持return语句，但是需要定义一个变量并赋值，比如：

UPDATE T1 SET B_COL='11' WHERE 1=1 RETURNING ID INTO :ID;

  其中上述:ID就是所谓变量，需要给其赋值才行，在PL / SQL 块中可以对其进行处理。在go中则需要使用sql.Out去接收，这一点与pg/mysql完全不同。pg本质是在执行更新后返回对应列，类似于查询结果返回；而oracle则是赋值，另外如果是多行更新，在go中无法有合适的类型接收，只能在PL/SQL 块中处理。所以，多行操作在go中是不支持RETURNING。

2.4.3 删除数据

• 语法

  oracle删除的语法类似，如下：

DELETE FROM table_name WHERE some_column=some_value;

  同时删除也支持RETURNING，与update一样的情况。在go中无法支持多行删除returning，只能在PL / SQL 块中处理

DELETE FROM table_name WHERE some_column=some_value RETURNING *;

2.4.4 查询数据

  查询语句oracle与mysql和pg其实是类似的，这里主要简单介绍下连接查询的区别。

• 分页

  oracle的分页与mysql和postgresql完全不同，使用rownum控制。但是使用该隐藏字段，必须只能用