四、GaussDB 兼容性与高级SQL 学习笔记

# GaussDB 兼容性与高级SQL 学习笔记

## 一、GaussDB 兼容性概述

### 1.1 兼容A数据库（如Oracle）

#### 1.1.1 兼容概览
- 支持SQL2003标准，基础SQL语法完全兼容。
- 兼容大部分A数据库对象：表、视图、索引、序列、同义词、触发器、函数、PL/SQL对象。
- 使用UGO工具可自动转换90%以上数据库对象和SQL/PLSQL语法。

#### 1.1.2 数据类型兼容性

**完全兼容的数据类型：**
- VARCHAR2、CHAR、NUMBER、FLOAT、BINARY_DOUBLE、DATE、INTERVAL、RAW、BLOB、CLOB、JSON、VARRAY、NEST TABLE等。

**存在差异的数据类型：**
| 数据类型 | 差异说明 |
|----------|----------|
| NVARCHAR2/NCHAR | GaussDB不支持国家字符集，使用无差异 |
| TIMESTAMP | GaussDB精度0-6，A数据库精度0-9 |
| TIMESTAMP WITH TIME ZONE | GaussDB存储为UTC，不存储输入时区 |
| XMLTYPE | 不支持XMLQUERY |

**不支持的数据类型：**
| 数据类型 | 替代方案 |
|----------|----------|
| TIMESTAMP WITH LOCAL TIME ZONE | 使用 `timestamp with time zone` |
| BINARY_FLOAT | 使用 `float4` |

#### 1.1.3 操作符与表达式兼容性

**完全兼容的操作符类别：**
- 算术操作符、COLLATE操作符、连接运算符（`||`）、集合运算符（UNION/INTERSECT/MINUS）、多集合运算符、用户自定义操作符、层次查询操作符（PRIOR/CONNECT_BY_ROOT）。

**表达式差异：**
- **占位符表达式**：GaussDB支持 `:var` 形式，不支持 `INDICATOR` 结合两个占位符。
- **类型构造表达式**：不支持 `NEW` 关键字。

#### 1.1.4 条件匹配兼容性

**支持的条件：**
- 比较条件、模式匹配（LIKE/REGEXP_LIKE）、复合条件、EXISTS、IN、BETWEEN、IS NULL、JSON_EQUAL（等价于JSONB_EQ）。

**不支持的条件：**
- 浮点条件（IS NAN/INFINITE）
- JSON条件（IS JSON、JSON_EXISTS）
- XML条件（EQUALS_PATH）

#### 1.1.5 数据对象兼容性

**支持的数据对象：**
- 表、分区表（RANGE/LIST/HASH/INTERVAL）、索引（BTREE/UBTREE/HASH/GIST/GIN/SPGIST）、视图、序列、同义词、DBLINK。

**对象命名差异：**
- GaussDB默认小写存储，不区分大小写（除非加双引号）。
- 命名长度上限63字节（A数据库为128字节）。

#### 1.1.6 语法对比示例

**DDL：**
```sql
-- 添加主键约束（GaussDB使用 USING INDEX）
A数据库：ALTER TABLE tab1 ADD CONSTRAINT pk_tab1 PRIMARY KEY(col1,col2) USE INDEX pk_tab1;
GaussDB：ALTER TABLE tab1 ADD CONSTRAINT pk_tab1 PRIMARY KEY USING INDEX pk_tab1;

-- 表达式索引（GaussDB需双层括号）
A数据库：CREATE INDEX idx ON tbl_expression (a/2);
GaussDB：CREATE INDEX idx ON tbl_expression ((a/2));
```

**DML：**
- **去重删除**：
  - A数据库：`DELETE FROM t WHERE rowid NOT IN (...)`
  - GaussDB：`DELETE FROM t WHERE ctid NOT IN (...)` 或使用主键
- **分页查询**：
  - A数据库：`WHERE rownum <= 20`
  - GaussDB：`LIMIT 20` 或 `WHERE rownum <= 20`（已支持）
- **COUNT与ORDER BY**：GaussDB中 `COUNT` 与 `ORDER BY` 一起使用会报错，建议去掉 `ORDER BY`。

**DCL：**
- 事务保存点：GaussDB需先执行 `START TRANSACTION`。

#### 1.1.7 系统视图映射

| A数据库视图前缀 | GaussDB对应前缀 |
|-----------------|-----------------|
| DBA_*          | ADM_*          |
| ALL_*          | DB_*           |
| USER_*         | MY_*           |
| V$*            | GV_* / V_*     |

**示例：**
- `v$session` → `v_session`
- `user_jobs` → `my_jobs`
- `all_tab_columns` → `db_tab_columns`

#### 1.1.8 PL/SQL兼容性差异
1. **不支持的数据类型**：STRING、PLS_INTEGER。
2. **自定义类型限制**：不支持OBJECT类型，RECORD不支持默认值，集合类型部分约束不生效。
3. **动态语句绑定**：变量绑定顺序与A数据库不完全一致。
4. **编译执行**：仅支持解释执行，部分SQL预解析。
5. **FORALL语句**：仅为兼容，不支持并发执行。

#### 1.1.9 函数兼容性
支持大部分函数，包括数字、字符串、日期、转换、聚合、分析函数等。详细列表见PDF第20页。

---

### 1.2 兼容M数据库（如MySQL）

#### 1.2.1 兼容概览
- M兼容模式采用新框架，隔离解析器与系统表/视图/函数。
- 支持更多数据类型、语法、函数，便于MySQL迁移。

**兼容对比统计（GaussDB M兼容505.2 vs MySQL 5.7）：**
| 类型         | MySQL 5.7 | GaussDB M兼容505.2 |
|--------------|-----------|--------------------|
| 语法         | 539       | 212                |
| 数据类型     | 40        | 31                 |
| 系统视图     | 294       | 35                 |
| 系统函数     | 451       | 208                |
| 操作符/表达式| 121       | 115                |
| 合计         | 1485      | 600                |

#### 1.2.2 语法差异

**DDL：**
- 对象命名长度上限63字节（MySQL为64字符）。
- 不支持自定义函数、存储过程、可执行注释。
- 创建索引时，索引名/约束名在schema中需唯一（MySQL只需在表中唯一）。
- 不支持前缀索引作为主键。

**分区表：**
- GaussDB不支持表达式作为分区键。
- 仅在LIST/RANGE分区（无二级分区）支持多分区键。

**DML：**
- 需开启转义符和列名回显开关：
  ```sql
  SET m_format_behavior_compat_options='enable_escape_string,select_column_name';
  ```
- UPDATE/REPLACE SET中三段式用法不同：
  - MySQL：`database.table.column`
  - GaussDB：`table.column.field`（不推荐）
- INSERT时，若值少于字段数，GaussDB按顺序补充默认值（MySQL报错）。
- 不支持 `LOW_PRIORITY`/`HIGH_PRIORITY`。

**DCL：**
- 默认隔离级别：GaussDB为 `READ COMMITTED`，MySQL为 `REPEATABLE READ`。
- 不支持设置Global系统参数。

#### 1.2.3 数据类型映射
| MySQL类型           | GaussDB对应类型          | 差异说明                     |
|---------------------|--------------------------|------------------------------|
| DECIMAL/NUMERIC     | DECIMAL/NUMERIC          | 存储格式不同                 |
| VARCHAR             | VARCHAR(n)               | 变长字符串                   |
| LONGBLOB            | LONGBLOB                 | GaussDB最大1GB-1，MySQL最大4GB-1 |
| JSON                | JSON                     | 输入格式需符合JSON标准       |
| ENUM/SET            | ENUM/SET                 | 存储空间差异                 |

#### 1.2.4 系统视图兼容
- 大部分 `information_schema` 视图已兼容，部分字段未实现。
- 新增 `m_schema.*` 视图映射MySQL系统表（如 `m_schema.func`、`m_schema.proc`）。

#### 1.2.5 函数兼容
- **时间日期函数**：全部兼容（共60个）。
- **数学函数**：大部分兼容，COT、MOD、RADIANS、ROUND从506.0版本支持。
- **字符串函数**：支持45个常用函数。
- **聚合函数**：支持AVG、COUNT、GROUP_CONCAT等。
- **JSON函数**：支持大部分JSON操作函数。
- **窗口函数**：支持LAG、LEAD、ROW_NUMBER等。

#### 1.2.6 操作符与表达式差异
- **NULL排序**：MySQL默认NULL在前，GaussDB默认NULL在后（可用 `NULLS FIRST/LAST` 调整）。
- **操作符结合性**：MySQL严格要求括号，GaussDB较宽松。
- **比较结果**：MySQL返回1/0，GaussDB返回t/f。

---

## 二、高级SQL功能

### 2.1 DBLINK（数据库链接）

#### 2.1.1 基本概念
- 用于跨数据库访问，分为PUBLIC（所有用户可见）和PRIVATE（仅创建者可用）。
- 信息存储在 `gs_db_links` 系统视图。

#### 2.1.2 语法
```sql
-- 创建
CREATE [PUBLIC] DATABASE LINK dblink_name 
CONNECT TO user IDENTIFIED BY password 
USING (host 'ip', port 'port', dbname 'db');

-- 修改
ALTER DATABASE LINK dblink_name CONNECT TO user IDENTIFIED BY password;

-- 删除
DROP DATABASE LINK dblink_name;

-- 使用（在对象后加 @dblink）
SELECT * FROM remote_table@dblink;
INSERT INTO remote_table@dblink VALUES (...);
CALL remote_proc@dblink();
```

#### 2.1.3 规格约束
- **事务隔离级别映射**：
  | 本地隔离级别       | 远程隔离级别     |
  |--------------------|------------------|
  | Read Uncommitted   | Repeatable Read  |
  | Read Committed     | Repeatable Read  |
  | Repeatable Read    | Repeatable Read  |
  | Serializable       | Serializable     |
- 不支持同义词、HASHBUCKET表、SLICE表、复制表、临时表。
- 视图结构变化可能导致查询异常。

#### 2.1.4 使用注意事项
- 仅A兼容模式可用，远程数据库需为GaussDB 2.23.01.210+。
- 本地与远程参数需一致（如 `behavior_compat_options`）。
- 长事务首次使用DBLINK可能阻塞，建议先执行快速元数据查询（如 `SELECT * FROM t1@dblink WHERE 1=2`）。

---

### 2.2 触发器

#### 2.2.1 基本概念
- 特殊存储过程，与表/视图关联，在特定事件（INSERT/UPDATE/DELETE/TRUNCATE）触发。
- 触发时机：BEFORE、AFTER、INSTEAD OF。
- 类型：行级触发器（ROW）、语句级触发器（STATEMENT）。

#### 2.2.2 语法
```sql
-- 创建
CREATE TRIGGER trigger_name 
BEFORE|AFTER|INSTEAD OF event 
ON table_name 
FOR EACH ROW|STATEMENT 
EXECUTE PROCEDURE function_name();

-- 修改（仅可重命名）
ALTER TRIGGER trigger_name ON table_name RENAME TO new_name;

-- 删除
DROP TRIGGER trigger_name ON table_name;
```

#### 2.2.3 使用示例
```sql
-- 创建备份表触发器
CREATE OR REPLACE FUNCTION tri_backup_func() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO backup_table VALUES (NEW.id, NEW.name);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER backup_trigger 
BEFORE INSERT ON main_table 
FOR EACH ROW 
EXECUTE PROCEDURE tri_backup_func();
```

#### 2.2.4 最佳实践
- **适用场景**：数据完整性、自动化同步、审计日志。
- **潜在风险**：性能影响、维护复杂度增加。
- **建议**：避免复杂逻辑、保持简洁。

---

### 2.3 高级数据类型与自定义类型

#### 2.3.1 高级数据类型
- **几何类型**：point、lseg、box、path、polygon、circle。
- **XML类型**：支持DOM操作，最大1GB。
- **JSON类型**：JSON（保留输入格式）和JSONB（二进制存储，优化查询）。
- **数组类型**：元素类型相同，最大维度6，总大小≤1GB-1。

#### 2.3.2 自定义类型
1. **复合类型**：类似表结构，用于函数参数/返回类型。
   ```sql
   CREATE TYPE person AS (name varchar, age int);
   ```

2. **基本类型**：自定义标量类型，需定义输入/输出函数。
   ```sql
   CREATE TYPE mytype (
       INPUT = mytype_in,
       OUTPUT = mytype_out,
       INTERNALLENGTH = 4
   );
   ```

3. **Shell类型**：占位符类型，用于前向引用。

4. **枚举类型**：
   ```sql
   CREATE TYPE status AS ENUM ('active', 'inactive');
   ```

5. **集合类型**：类似无长度限制的数组，主要用于存储过程。
   ```sql
   CREATE TYPE mytable AS TABLE OF varchar;
   ```

---

### 2.4 公共表达式（CTE）

#### 2.4.1 基本语法
```sql
WITH cte_name (col1, col2) AS (
    SELECT ...
)
SELECT * FROM cte_name;
```

#### 2.4.2 递归CTE
```sql
WITH RECURSIVE cte AS (
    SELECT 1 AS n  -- 初始查询
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 100  -- 递归查询
)
SELECT SUM(n) FROM cte;
```

#### 2.4.3 在DML中使用
```sql
-- 迁移数据
WITH moved AS (
    DELETE FROM src_table RETURNING *
)
INSERT INTO dst_table SELECT * FROM moved;

-- 递归删除
WITH RECURSIVE sub AS (...)
DELETE FROM departments WHERE id IN (SELECT id FROM sub);
```

---

### 2.5 聚集函数与窗口函数

#### 2.5.1 聚集函数
- **常用函数**：SUM、MIN、MAX、COUNT、AVG、STRING_AGG、GROUP_CONCAT。
- **高级分组**：GROUPING SETS、CUBE、ROLLUP。
- **自定义聚集函数**：
  ```sql
  CREATE AGGREGATE my_sum (int) (
      SFUNC = int_add,
      STYPE = int,
      INITCOND = '0'
  );
  ```

#### 2.5.2 窗口函数
- **语法**：
  ```sql
  SELECT col, 
         SUM(col) OVER (PARTITION BY group_col ORDER BY order_col) 
  FROM table;
  ```
- **常用函数**：
  - 排名：ROW_NUMBER、RANK、DENSE_RANK
  - 取值：FIRST_VALUE、LAST_VALUE、LAG、LEAD
  - 其他：RATIO_TO_REPORT、DELTA

---

### 2.6 自治事务

#### 2.6.1 基本概念
- 独立于主事务的子事务，提交/回滚互不影响。
- 使用 `PRAGMA AUTONOMOUS_TRANSACTION` 声明。

#### 2.6.2 规格约束
- 最大并发数由 `max_concurrent_autonomous_transactions` 控制（默认10）。
- 不支持触发器、非顶层匿名块、修改隔离级别、返回集合类型。

#### 2.6.3 使用示例
```sql
-- 存储过程中使用
CREATE PROCEDURE auto_proc AS
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO log_table VALUES ('autonomous log');
    COMMIT;
END;
```

---

## 三、思考题答案

1. **B** 数组类型（元素必须是相同类型）
2. **C、D**（Repeatable Read、Serializable）
3. **A**（`SELECT * FROM pg_extension;`）
4. **A、C、D**（INSTEAD OF、BEFORE、AFTER）
5. **C**（5种：复合、基本、Shell、枚举、集合）

如果需要我将此内容保存为 `.md` 文件，请告诉我。

FYF

导航1234

最近发表

友情链接