数据库参考手册

一 MySQL 参考手册

数据库配置（文件dbcp.properties）如下

 ########DBCP##########
 driverClassName=com.mysql.cj.jdbc.Driver
 #url
 url=jdbc:mysql://192.168.168.156:3306/iidp?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
 username=root
 password=123456
 initialSize=5
 maxActive=30
 minIdle=5
 maxWait=6000
 filters=stat
 timeBetweenEvictionRunsMillis=60000
 minEvictableIdleTimeMillis=300000
 validationQuery=select 'x'
 testOnBorrow=false
 testOnReturn=false
 testWhileIdle=true
 poolPreparedStatements: true
 maxOpenPreparedStatements: 20

二 Oracle 参考手册

数据库配置（文件dbcp.properties）如下

 ########Oracle########
 driverClassName=oracle.jdbc.OracleDriver
 url=jdbc:oracle:thin:@192.168.175.193:1521:SMOMDB
 username=sie_smom
 password=SIE_SMOM#123
 validationQuery=SELECT 'x' FROM DUAL

 initialSize=5
 maxActive=30
 minIdle=5
 maxWait=6000
 filters=stat
 timeBetweenEvictionRunsMillis=60000
 minEvictableIdleTimeMillis=300000
 validationQuery=select 'x'
 testOnBorrow=false
 testOnReturn=false
 testWhileIdle=true
 poolPreparedStatements: true
 maxOpenPreparedStatements: 20

三 PostgreSQL 参考手册

PostgreSQL 版本: 16.6

下载地址: Open-Source, Enterprise Postgres Database Management

第一节.本地和Docker安装步骤

1. 本地安装 PG

以下是在本地 Windows 系统上安装 PostgreSQL 16.6 的流程：

1. 下载安装包

   • 访问 PostgreSQL 官方下载页面
   • 选择适合您系统的安装包

2. 运行安装程序

   • 双击下载的安装程序文件，启动安装向导
   • 在安装向导中，选择安装组件，包括 PostgreSQL Server、pgAdmin 4 和 Command Line Tools 等

3. 配置安装选项

   • 选择安装目录：默认安装目录为 C:\Program Files\PostgreSQL\16，可根据需要更改
   • 设置数据目录：默认数据目录为 C:\Program Files\PostgreSQL\16\data，可根据需要更改
   • 设置超级用户密码：输入并确认超级用户 postgres 的密码
   • 设置端口号：默认端口号为 5432，可根据需要更改
   • 配置高级选项：如需，可配置其他高级选项，如 locale 和 encoding 等

4. 完成安装

   • 点击 Install 按钮开始安装
   • 安装完成后，可选择是否启动 Stack Builder 来安装其他工具和驱动程序

5. 验证安装

   • 打开 Dbeaver，连接到本地服务器，数据库: postgres，默认端口: 5432，输入超级用户 postgres 的密码，验证连接是否成功
   • 打开命令行工具，输入 psql -U postgres -h localhost -p 5432，验证是否可以成功登录到数据库
   • PostgreSQL 默认的数据库名字是 postgres，默认的模式 schema 是: public，pg 的模式相当于 MySQL 的数据库，可以创建不同的模式

6. DBCP 配置

   ########postgresql########
   driverClassName=org.postgresql.Driver
   url=jdbc:postgresql://localhost:5432/postgres?currentSchema=public
   username=postgres
   password=******

参考资料

• PostgreSQL 官方文档
• PostgreSQL 16 在 Windows 下安装
• Windows 10 上安装 PostgreSQL 16
• PostgreSQL 在 Windows 上的安装与配置

2. Docker 安装 PG

以下是在 120 机器上，使用 Docker 安装 PostgreSQL 16.6 的流程：

1. 拉取镜像

   从 Docker Hub 上拉取 PostgreSQL 16.6 的镜像：

   docker pull swr.cn-north-4.myhuaweicloud.com/ddn-k8s/docker.io/postgres:16.6-alpine3.21
   docker tag swr.cn-north-4.myhuaweicloud.com/ddn-k8s/docker.io/postgres:16.6-alpine3.21 docker.io/postgres:16.6

2. 创建并运行 PostgreSQL 容器

   使用以下命令创建并运行一个 PostgreSQL 容器，并配置数据持久化：

   docker run --name postgres16 -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -v /opt/postgresql/data:/var/lib/postgresql/data -d docker.io/postgres:16.6

   • --name postgres16：指定容器名称为 postgres16
   • -e POSTGRES_PASSWORD=mysecretpassword：设置 PostgreSQL 的超级用户密码
   • -p 5432:5432：将容器的 5432 端口映射到主机的 5432 端口
   • -v /opt/postgresql/data:/var/lib/postgresql/data：将主机的目录（/opt/postgresql/data）挂载到容器的数据目录（/var/lib/postgresql/data），以实现数据持久化

3. 进入容器

   如果需要进入容器内部，可以使用以下命令：

   docker exec -it postgres16 bash

4. 连接到数据库

   在容器内部或宿主机上，可以使用 psql 命令连接到数据库：

   psql -U postgres -h localhost -p 5432

   • -U postgres：指定用户名为 postgres
   • -h localhost：指定主机名为 localhost
   • -p 5432：指定端口号为 5432

5. DBCP 配置

   已经在 120 安装了 PG，测试可以直接使用。PG 默认的模式是：public，可以通过设置 currentSchema 切换模式。

   ########postgresql########
   driverClassName=org.postgresql.Driver
   url=jdbc:postgresql://xxx.xxx.120:5432/postgres?currentSchema=public&encoding=UTF-8&timezone=UTC
   username=postgres
   password=******

参考资料

• Docker 安装 PostgreSQL 16.6
• Docker+PostgreSQL 数据库
• Docker 容器化部署 PostgreSQL

第二节. PostgreSQL 特性

2.0 特别注意:PostgreSQL 和 MySQL 中标识符大小写与引号使用对比

1. PostgreSQL 中的处理方式

表名和字段名大小写

• 默认行为：PostgreSQL 在 SQL 查询时会自动将大写标识符转换为小写

• 双引号影响：如果标识符用双引号括起来，则保留原始大小写

  SELECT * FROM MyTable;    -- 实际查询的是 mytable
  SELECT * FROM "MyTable";  -- 查询的是保留大小写的 MyTable

引号使用

• 单引号 ('')：用于字符串值
• 双引号 ("")：用于标识符（表名、列名等）
• 反引号 (``)：PostgreSQL 不支持使用反引号

2. MySQL 中的处理方式

表名大小写敏感性

• Windows 系统：默认不区分大小写
• Linux 系统：默认区分大小写
• 控制参数：lower_case_table_names 参数控制此行为

列名大小写

• 所有系统：列名不区分大小写

  SELECT mycolumn FROM mytable;  -- 与 SELECT MyColumn FROM MyTable 相同

引号使用

• 单引号 ('') 和 双引号 ("")：都可用于字符串值

• 反引号 (``)：用于标识符（表名、列名等），特别是在标识符与关键字冲突或包含特殊字符时

  SELECT `select` FROM `table`;

标识符大小写敏感性

• 未加引号的标识符：大小写不敏感

  UPDATE MY_TABLE SET A = 5;  -- 等价于 uPDaTE my_TabLE SeT a = 5;

• 加引号的标识符：保留大小写

  UPDATE "my_table" SET "a" = 5;  -- 必须使用相同大小写引用

主要差异总结

特性	PostgreSQL	MySQL
标识符默认大小写处理	大写转小写	取决于系统和配置
保留大小写的方式	使用双引号	使用反引号
字符串引号	仅单引号	单引号或双引号
标识符引号	双引号	反引号
列名大小写敏感性	取决于是否加双引号	所有系统都不区分大小写

2.1 Boolean 值问题

PostgreSQL 支持 boolean 类型，但我们引擎是使用 char(1) 代替 boolean。插入到数据的时候我们插入 char(1)，查询的时候返回 boolean 值。

CREATE TABLE example (
    is_active CHAR(1)
);

2.2 索引问题

在 PostgreSQL 里，索引名称的最大长度受标识符最大长度限制，官方规定标识符最大长度为 63 字节。若标识符（如表名、列名、索引名等）长度超 63 字节，会自动截断并发出警告。

1. PostgreSQL 索引命名规则与实践

1. 基本规则

   • 长度：索引名最长 63 字节，超量自动截断并告警
   • 字符集：可含字母、数字、下划线和特殊字符，建议只用前三者
   • 大小写：默认转小写存储，用双引号包裹可保留大小写
   • 唯一性：在同一个 schema 中，索引名称必须唯一，创建同名索引会引发错误

2. 命名限制

   • 长度：长度超过 63 字节会被截断。

     CREATE INDEX this_is_a_very_long_index_name_that_exceeds_the_maximum_length_of_sixty_three_bytes ON my_table(my_column);
     -- PostgreSQL 会将其截断为
     this_is_a_very_long_index_name_that_exceeds_the_maximum_length_of_sixty_three
     

   • 大小写：默认转小写，用 "MyIndex" 可保留

   • 特殊字符：可含特殊字符，但可能有兼容性问题，如 "my_index$1"

   • 唯一性：同一 schema 内重名报错，如 CREATE INDEX my_index ON my_table(my_column); 后再创建同名索引会冲突。

     CREATE INDEX my_index ON my_table(my_column);
     CREATE INDEX my_index ON my_table(another_column);  -- 错误：索引名称冲突
     

3. 最佳实践

   • 索引名称应反映其用途：如 idx_table_column 表示普通索引，uk_table_column 表示唯一约束索引。例如：

     • idx_table_column：表示在 table 表的 column 列上创建的索引
     • uk_table_column：表示在 table 表的 column 列上创建的唯一约束索引

   • 避保留字：不用 SELECT、INSERT 等保留字命名

   • 使用一致的命名规则：

     • 前缀：使用 idx_ 表示普通索引，uk_ 表示唯一索引，pk_ 表示主键索引
     • 表名和列名：在索引名称中包含表名和列名

     例如：

     CREATE INDEX idx_users_email ON users(email);
     CREATE UNIQUE INDEX uk_users_username ON users(username);
     

   • 长度限制：避免超过 63 字节

4. 修改索引名

   使用 ALTER INDEX old_index_name RENAME TO new_index_name; 修改

5. 查看索引名

   用以下 SQL 查询：

   SELECT schemaname, tablename, indexname
   FROM pg_indexes
   WHERE schemaname NOT LIKE 'pg_%'
   ORDER BY schemaname, tablename, indexname;

2.3 支持Transactional DDL

1. 参考资料

• PostgreSQL 中的事务性 DDL: 数据库对比
• Transactional DDL in PostgreSQL: A Competitive Analysis

2. 什么是 Transactional DDL？

Transactional（事务）在关系型数据库是指一组 SQL 语句，要么提交，要么全部回滚。事务中包含的语句通常是 DML 语句，如 INSERT、UPDATE、DELETE 等。但对于 DDL 语句呢？是否可以在事务中包含诸如 CREATE、ALTER、DROP 等 DDL 命令？

所谓 Transactional DDL 就是我们可以把 DDL 放到事务中，做到事务中的 DDL 语句要么全部提交，要么全部回滚。

看个 PG 的例子

postgres=# begin;
BEGIN
postgres=*# create table a_test(id int);
CREATE TABLE
postgres=*# insert into a_test values(1);
INSERT 0 1
postgres=*# rollback;
ROLLBACK
postgres=# select * from a_test;
ERROR: relation "a_test" does not exist
LINE 1: select * from a_test;
                      ^
postgres=#

可见，在 PostgreSQL 中，是支持 Transactional DDL 的，在上例中，create table 语句被回滚掉了。

并不是所有数据库都支持 Transactional DDL，比如 MySQL。

看个 MySQL 的例子

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a_test (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into a_test values(1);
Query OK, 1 row affected (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a_test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

可以看到 MySQL 这个例子里，不仅 create 语句没有回滚掉，insert 语句也没有回滚掉。这是因为：在 MySQL 中，当执行 DDL 语句时，会隐式地将当前会话的事务进行一次提交操作。所以我们应该严格地将 DDL 和 DML 完全分开，不能混合在一起执行。

一些特例

需要注意的是在 PG 中并不是所有的 DDL 都支持 Transactional DDL。比如 CREATE INDEX CONCURRENTLY、CREATE DATABASE、CREATE TABLESPACE 等等。

postgres=# begin;
BEGIN
postgres=*# CREATE INDEX CONCURRENTLY idx_id ON a_test (id);
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

3. Transactional DDL 的好处

在进行一些模式升级等复杂工作时，可以利用此功能保护数据库。我们可以将所有更改都放入事务块中，确保它们都以原子方式应用，或者根本不应用。这大大降低了数据库因模式更改中的输入错误或其他此类错误而损坏数据库的可能性。

4. PG 中的事务性 DDL: 数据库对比

事务性 DDL

与一些商业数据库一样，PostgreSQL 中有一个比较高级的功能，它能够通过其 预写式日志 的设计执行事务性 DDL。该设计支持回退 DDL 产生的重大更改，例如表创建。您无法恢复添加和删除数据库或表空间的操作，但所有其他的对象操作都是可逆的。

PostgreSQL

下面是一个示例，显示了 PostgreSQL 的设计在这方面的处理能力：

DROP TABLE IF EXISTS foo;
NOTICE: table "foo" does not exist

BEGIN;
CREATE TABLE foo (bar int);
INSERT INTO foo VALUES (1);
ROLLBACK;

SELECT * FROM foo;
ERROR: relation "foo" does not exist

有经验的 PostgreSQL DBA 知道，在执行表结构升级等复杂工作时，可利用此功能来保护自己。如果你把所有这些变更都放到一个事务块中，你可以确保它们都以原子方式进行应用，或者完全不应用。这大大降低了数据库因架构变更中的拼写错误或其他此类错误而损坏的可能性，这在修改多个相关表时尤为重要，因为错误可能会破坏关系键。

MySQL

如果您在使用 MySQL，则无法以这种方式撤消 DDL 和一些类似的更改。如果您使用的是 MyISAM 存储引擎，那它根本不支持事务。对于 InnoDB 存储引擎，服务端有一种 隐式提交，即使关闭了正常的自动提交行为，DDL 命令也会导致当前事务发生隐式提交。

set autocommit = 0;

drop table foo;

create table foo (bar int) engine=InnoDB;

insert into foo values (1);

rollback;

select * from foo;
Empty set (0.00 sec)

Oracle

一个事务从第一个可执行的 SQL 语句开始。事务在提交或回滚时结束，无论是显式使用 COMMIT 或 ROLLBACK 语句，还是在发出 DDL 语句时隐式提交。

Oracle 数据库在以下情况下会发生隐式 COMMIT：

• 在任何语法上有效的数据定义语言（DDL）的语句之前，即使该语句发生了错误
• 在任何没有发生错误执行完成的数据定义语言（DDL）语句之后

让我们在 Oracle 中创建一个表，并插入一行：

-- Create a table and insert a row
CREATE TABLE states
(
    abbr CHAR(2),
    name VARCHAR2(90)
);

-- Transaction will be in progress after this insert
INSERT INTO states VALUES ('CA', 'California');

现在，让我们创建另一个表，并执行 ROLLBACK 操作：

-- Create another table table and insert a row
CREATE TABLE cities
(
    name VARCHAR2(90),
    state CHAR(2)
);

INSERT INTO cities VALUES ('San Francisco', 'CA');

ROLLBACK;

您可以看到，即使在 ROLLBACK 之后，表 states 和表中的行仍然存在，因为 CREATE TABLE cities 语句提交了事务。

表 cities 也存在，但插入的行已回滚：

-- Table states exists and contains 1 row
SELECT COUNT(*) FROM states;
-- Result: 1

-- Table cities also exists, but the inserted row was rolled back
SELECT COUNT(*) FROM cities;
-- Result: 0

2.4 数据类型转换规则

PostgreSQL 的自动类型转换规则是比较严格的，尤其是从字符串到其他类型的转换，通常需要显式指定 PostgreSQL 要求显式的数据类型转换。

1. 示例：数据类型转换错误

INSERT INTO a_test (age) VALUES ('123'); -- 错误：字段 "age" 的类型为 integer, 但表达式的类型为 character varying

2. 解决方法

在插入或更新数据时，确保数据类型匹配，或使用显式类型转换：

INSERT INTO a_test (age) VALUES (CAST('123' AS INTEGER));

3. 自动类型转换（Implicit Casting）

PostgreSQL 会在某些情况下自动进行数据类型转换，以确保操作的正确性。这些情况主要是为了匹配操作符或函数的参数类型。

1. 字符串到数字的隐式转换（受限）：

   • PostgreSQL 不会自动将字符串转换为数字类型（如 integer 或 numeric）。例如：

     SELECT '123' + 456; -- 报错：类型不匹配
     

     如果要进行这种操作，必须显式转换类型。

2. 字符串与字符串的连接：

   • 如果两个操作数都是字符串类型，可以自动进行连接操作：

     SELECT 'Hello' || 'World'; -- 结果为 'HelloWorld'
     

3. 字符串与其他类型的隐式转字符串：

   • 如果一个操作符或函数期望一个字符串类型的参数，而提供的是一个非字符串类型，PostgreSQL 会尝试将其隐式转换为字符串：

     SELECT 42 || ' apples'; -- 结果为 '42 apples'
     

4. 显式类型转换（Explicit Casting）

显式类型转换是 PostgreSQL 中推荐的做法，用于明确地将数据从一种类型转换为另一种类型。以下是几种常用的显式类型转换方法：

1. 使用 CAST 函数：

   • CAST 是标准 SQL 提供的类型转换函数。例如：

     SELECT CAST('123' AS INTEGER); -- 结果为 123
     

     你也可以指定其他目标类型，如 DOUBLE PRECISION、DATE 等：

     SELECT CAST('3.1415' AS NUMERIC(5,2)); -- 结果为 3.14
     

2. 使用列定义语法：

   • 使用列定义语法也可以进行类型转换。例如：

     SELECT '2024-01-01'::DATE; -- 结果为 2024-01-01
     

     这种方法与 CAST 功能相同，但语法更简洁。

3. 使用 :: 运算符：

   • :: 是 PostgreSQL 提供的快捷方式，用于类型转换。例如：

     SELECT '25'::integer; -- 结果为 25
     

4. 类型转换的注意事项

5. 避免非显式转换：

   • 尽量避免依赖隐式类型转换，因为它可能导致意外的错误。例如：

     INSERT INTO users (age) VALUES ('25'); -- 报错，除非 `age` 是 `VARCHAR` 类型
     

     如果 age 是 BOOLEAN 类型，而你尝试插入 TRUE，必须显式转换为布尔值：

     INSERT INTO users (age) VALUES (TRUE::BOOLEAN);
     

6. 处理文本与日期/时间：

   • 当将字符串转换为日期或时间类型时，必须遵循正确的格式。例如：

     SELECT CAST('2023-10-05' AS DATE); -- 正确
     SELECT CAST('10/05/2023' AS DATE); -- 抛出错误，因为格式不匹配
     

     如果需要将日期以特定格式存储或解析，可以使用 TO_DATE 函数：

     SELECT TO_DATE('05-OCT-2023', 'DD-MON-YYYY'); -- 结果为 2023-10-05
     

7. 数组类型的转换：

   • 数组之间的类型转换通常比标量类型更复杂。例如，将 {1,2,3} 转换为 int[] 是可以直接的：

     SELECT '{1,2,3}'::int[]; -- 正确
     

     但是，将字符串数组转换为其他类型数组可能需要额外的处理。

8. 类型转换失败：

   • 如果转换无法完成，PostgreSQL 会抛出错误。例如：

     SELECT CAST('text' AS INTEGER); -- 抛出错误：invalid input syntax for type integer
     

9. 示例

以下是一些常见的类型转换示例：

1. 将字符串转换为整数：

   SELECT CAST('42' AS INTEGER); -- 结果为 42
   SELECT '42'::integer; -- 同上

2. 将数字转换为字符串：

   SELECT CAST(42 AS VARCHAR); -- 结果为 '42'

3. 将布尔值转换为整数（逻辑上不推荐，但支持）：

   SELECT CAST(TRUE AS INTEGER); -- 结果为 1
   SELECT CAST(FALSE AS INTEGER); -- 结果为 0

4. 将字符串转换为布尔值：

   SELECT CAST('t' AS BOOLEAN); -- 结果为 TRUE
   SELECT CAST('false' AS BOOLEAN); -- 结果为 FALSE

2.5 PostgreSQL 错误：current transaction is aborted, commands ignored until end of transaction block

1. 错误概述

当 PostgreSQL 事务中的某条 SQL 语句执行失败时，整个事务会进入 中止状态（Aborted）。所有后续的 SQL 命令都会被忽略，直到事务被回滚或关闭。此时任何后续操作都会触发以下错误：current transaction is aborted, commands ignored until end of transaction block。 核心问题：事务未正确终止（未提交或回滚），导致数据库连接处于不可用状态。

错误信息:

caused by: org.postgresql.util.PSQLException: 错误: 当前事务被终止, 事务块结束之前的查询被忽略
current transaction is aborted, commands ignored until end of transaction block

2. 事务块中语句错误的回滚行为:数据库对比

摘要：在本文中，我们将学习事务块中语句错误的回滚行为，以及 PostgreSQL 和 Oracle 之间的区别。

参考文章

• PostgreSQL - ERROR: current transaction is aborted
• Oracle 语句级回滚

通常，您制造的任何错误都会引发异常，并导致当前事务被标记为中止。这是理智和预期的行为，但如果它发生在您运行大型事务并输入了错误内容时，这可能会非常的烦人！此时，您唯一能做的就是回滚事务并丢弃所有工作。

当您在 PostgreSQL 中遇到错误时，事务是无法继续的。例如：

DROP TABLE IF EXISTS demo;

START TRANSACTION;
CREATE TABLE demo(n, t) AS SELECT 1 n, current_timestamp t;
ALTER TABLE demo ADD UNIQUE(n);

SELECT * FROM demo;
 n |               t
---+-------------------------------
 1 | 2021-06-26 18:15:07.207671+08
(1 row)

INSERT INTO demo VALUES (2, current_timestamp);
SELECT * FROM demo;
 n |               t
---+-------------------------------
 1 | 2021-06-26 18:15:07.207671+08
 2 | 2021-06-26 18:15:07.207671+08
(2 rows)

INSERT INTO demo VALUES (1, current_timestamp);
ERROR: duplicate key value violates unique constraint "demo_n_key"
DETAIL: Key (n)=(1) already exists.

SELECT * FROM demo;
ERROR: current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;

SELECT * FROM demo;
ERROR: relation "demo" does not exist
LINE 1: SELECT * FROM demo;

在这里我们进行了回滚。但其实也可以提交来终止事务，但它无论如何都会回滚：

COMMIT;

SELECT * FROM demo;
ERROR: relation "demo" does not exist
LINE 1: SELECT * FROM demo;

Oracle 语句级回滚

如果在执行过程中的任何时候，SQL 语句发生错误，则该语句的所有更改都将回滚。回滚的效果就好像该语句从未执行过一样。这是一种语句级的回滚。

在 SQL 语句执行过程中发生的错误会导致语句级回滚。（此类错误的一个示例是，尝试在主键中插入重复值。在 SQL 语句解析过程中发生的错误（如语法错误）尚未执行，因此不会导致语句级回滚。涉及死锁（争用相同数据）的单个 SQL 语句也可能导致语句级回滚。

失败的 SQL 语句只会导致丢失它自己执行的任何工作；它不会导致在当前事务中丢失之前的任何工作。如果该语句是 DDL 语句，则不会撤消紧接在它前面的隐式提交。

在 Oracle 中，在一次用户调用中有一个语句失败时，将会回滚该用户调用所做的修改，但不会回滚之前的修改。事务还可以继续（如重试，或执行替代的更改），例如：

CREATE TABLE DEMO AS SELECT 1 n, current_timestamp t FROM dual;
ALTER TABLE DEMO ADD UNIQUE(n);

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00

INSERT INTO DEMO VALUES (2, current_timestamp);

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00
         2 02-AUG-21 11.04.16.601105 PM +00:00

INSERT INTO DEMO VALUES (1, current_timestamp);
ERROR at line 1:
ORA-00001: unique constraint (DEMO.SYS_C007847) violated

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00
         2 02-AUG-21 11.04.16.601105 PM +00:00

3. 错误原因

3.1 直接原因

• 事务中的 SQL 语句执行失败：如违反唯一约束、主键冲突、语法错误、权限不足等
• 事务未显式终止：失败后未执行 ROLLBACK 或 COMMIT，事务块未关闭

3.2 常见触发场景

场景	示例
插入重复的主键或唯一键	INSERT INTO users (id) VALUES (1);（若 id=1 已存在）
语法错误	INSERT INTO table (invalid_column) VALUES (1);（列名错误）
权限不足	普通用户尝试执行 DROP TABLE
隐式事务未处理	ORM 框架或客户端工具默认开启事务，但未捕获异常并回滚

4. 解决方法

1. 立即终止事务

通过 ROLLBACK 显式回滚事务，恢复数据库连接的可用状态：

ROLLBACK; -- 终止当前事务，清除错误状态

2. 定位错误原因

1. 查看客户端返回的错误信息

   例如：duplicate key value violates unique constraint "users_pkey"

2. 查询 PostgreSQL 日志

   • 日志默认路径：/var/log/postgresql/postgresql-<版本>-main.log
   • 搜索关键词：ERROR, ROLLBACK, 或事务 ID（如 process 12345）

3. 通过 SQL 获取最近错误

   SELECT pg_last_error(); -- 部分客户端支持（如 psql）

3. 修复并重试事务

根据错误原因调整 SQL 逻辑：

• 主键冲突：检查重复数据，或使用 ON CONFLICT 处理冲突。

  INSERT INTO users (id) VALUES (1)
  ON CONFLICT (id) DO NOTHING;

• 权限问题：授予用户权限。

  GRANT INSERT, UPDATE ON table_name TO user_name;

5. 高级场景与优化

1. 使用保存点（SAVEPOINT）

在长事务中分割操作，允许部分回滚：

BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint; -- 设置保存点
INSERT INTO table2 VALUES (1); -- 假设此处失败
ROLLBACK TO my_savepoint; -- 回滚到保存点，继续后续操作
INSERT INTO table3 VALUES (1);
COMMIT;

6. 预防措施

措施	说明
短事务原则	减少事务执行时间，避免长事务占用锁资源。
自动提交模式	在客户端工具中开启自动提交（SET AUTOCOMMIT = ON）。
异常捕获与回滚	在所有数据库操作中强制捕获异常并回滚。
测试与监控	定期模拟错误场景（如主键冲突），验证回滚逻辑是否生效。
日志分析	使用工具（如 pgBadger）分析 PostgreSQL 日志，识别高频错误。

2.6 PostgreSQL JSON数据类型

PostgreSQL 提供了强大的 JSON 支持，能够灵活地存储和处理 JSON 数据，同时结合了关系型数据库的可靠性和 JSON 数据的灵活性。

[JSON 数据类型]

1. JSON 数据类型

PostgreSQL提供了两种主要的JSON数据类型：

• JSON：以文本格式存储JSON数据，保留输入时的所有细节（如空格和重复键）。适合需要保留原始文档格式的情况。
• JSONB：将JSON数据解析并以二进制格式存储，不保留格式信息（如空格）和重复键。尽管在插入时可能稍慢，但读取和查询性能通常优于JSON类型，因此推荐用于大多数场景。

2. 查询JSON数据

PostgreSQL为JSON数据提供了丰富的操作符和函数，使得查询变得简单而强大。以下是一些常用的查询方法：

• 提取数据：

  • ->：从JSON对象中获取指定键的值（返回JSON类型）。

  • ->>：类似->，但返回的是文本类型。

  • #> 和 #>>：用于根据提供的路径数组提取嵌套的JSON数据。

    由于->运算符返回 JSON 对象，因此您可以将其与运算符->>串接起来以检索特定节点。例如，以下语句返回所有已售出的产品：

    SELECT info -> 'items' ->> 'product' as product
    FROM orders
    ORDER BY product;
    

• 检查包含关系：

  • @>：检查左侧的JSON是否包含右侧的JSON。
  • <@：与@>相反，检查左侧的JSON是否被右侧的JSON所包含。

• 键存在性检查：

  • ?：检查JSON对象中是否存在某个键。

  • ?| 和 ?&：分别用于检查一组键中是否有任意一个或全部存在于JSON对象中。

3. 创建索引提高性能

为了优化JSON数据的查询性能，特别是对于复杂查询条件，PostgreSQL允许为JSONB字段创建GIN（Generalized Inverted Indexes）索引。GIN索引特别适用于处理复杂的查询，例如查找包含特定键值对的记录。

• 创建GIN索引的例子：

  CREATE INDEX idx_jsonb_data ON your_table USING GIN (jsonb_column);

使用GIN索引可以显著加快涉及@>、?等操作符的查询速度，这对于需要频繁进行这类查询的应用来说是非常重要的。

2.7 PostgreSQL UPDATE 语句中,更新列名时不能带别名

请注意，在 PostgreSQL 中，虽然可以在 UPDATE 语句中为表指定别名，但在实际使用这些列名时，并不需要（也不应该）通过别名来引用它们. 条件可以带别名,但是列名不能带别名.

UPDATE tenant_user_instance t
SET t."update_date" = TO_TIMESTAMP('2025-02-18 11:00:27.296', 'YYYY-MM-DD HH24:MI:SS.FF')
WHERE t."action_dimension_id" = '04me8wsxbgrqt'
AND t."user_id" = '04me9sszlk54b'
AND t."instance_id" = '04me86gy87lxe'
AND t."source" IN ('USER', 'BUSINESS_APP');

错误 [42703]: ERROR: column "t" of relation "tenant_user_instance" does not exist 表示在 tenant_user_instance 表中找不到名为 "t" 的列。实际上，这个错误通常是由表别名的误用引起的。在 PostgreSQL 中，更新语句可以使用表别名，但不需要像在 SELECT 语句中那样引用别名作为表名的一部分。

以下是修正后的 SQL 语句，移除了对别名 "t" 的不正确引用：

UPDATE tenant_user_instance t
SET update_date = TO_TIMESTAMP('2025-02-18 11:00:27.296', 'YYYY-MM-DD HH24:MI:SS.FF')
WHERE t.action_dimension_id = '04me8wsxbgrqt'
AND t.user_id = '04me9sszlk54b'
AND t.instance_id = '04me86gy87lxe'
AND t.source IN ('USER', 'BUSINESS_APP');

2.8 select as 别名问题,如果别名不带标识符``或者"",PostgreSQL/gaussdb会将别名转成小写

请注意，在 PostgreSQL/GaussDB 中，关键词和不被引号修饰的标识符是大小写不敏感的。因此,如果字段不带修饰符双引号,会转换成小写.

如果自定义SQL列别名不带修饰符,这样就会导致代码获取displayName值为NULL, get("displayName")=NULL.就会导致数据库迁移的时候,不兼容.

正确的SQL写法,列别名如果是驼峰或者包含大小写的,一定要用 ""引起来.

参考 4.1.1. 标识符和关键词

select display_name as displayName from meta_app;

--MySQL
displayName   |
-----------------+
基础模块      |



--PostgreSQL/GaussDB
displayname   |
-----------------+
基础模块  


正确的SQL写法,列别名如果是驼峰或者包含大小写的
MYSQL: select display_name as `displayName` from meta_app;
PG/GAUSSDB/ORACLE: select display_name as "displayName" from meta_app;

项目自定义SQL错误的示范:
SELECT param_name paramName, param_value paramValue FROM base_process_spec bps;

标准的SQL语法:
SELECT param_name "paramName", param_value "paramValue" FROM base_process_spec bps

2.9 PostgreSQL 的 schema

PostgreSQL和MySQL比较大的区别是引入了schema的概念,使用的时候需要切换schema. 默认 schema,新建库自带 public；search_path 控制查找顺序。

维度	PostgreSQL	MySQL	一句话差异
1. 层级结构	集群 → 数据库 → schema → 表/函数/序列…	实例 → database → 表…	PG 比 MySQL 多一层真正的 “schema” 命名空间。
2. schema≈database?	❌ 不是一回事；一个db里可建N个schema。	✅ CREATE DATABASE 与 CREATE SCHEMA 几乎等价；一个 db 就是一个 schema 空间。	MySQL 里换 db 就是换 schema；PG 里换 schema 仍在同一个 db 内。
3. 默认 schema	新建库自带 public；search_path 控制查找顺序。	每个 db 自带 sys/mysql/information_schema，但用户表都在 db 根下；无 “默认 schema” 概念。	PG 靠 search_path 实现“跨 schema 透明访问”，MySQL 靠 USE db。
4. 命名空间隔离	同一db内不同schema可存在同名对象(hr.emp, sales.emp)。	同一实例内不同 db 对象名可重复；一个 db 内部表名必须唯一。	PG 的细粒度隔离粒度是 schema，MySQL 是 database。
5. 跨空间访问	只要 GRANT 就可以 hr.emp 访问 sales 的表，仍在一个连接里。	跨库访问需 db.table 语法，但事务/外键/临时表不能跨库；往往要开多个连接。	PG 一个连接即可跨 schema；MySQL 跨 db 容易，跨库逻辑隔离重。
6. 代码/ORM 差异	需写 SET search_path = tenant1, public; 或在连接串里配 `

一句话概括： 在 PostgreSQL 里，schema 只是“数据库内部的一个文件夹”，拿来把表/函数/序列等对象分门别类；而在 MySQL 里并没有这层中间结构，它的“数据库”概念同时扮演了 PostgreSQL 的 database + schema 两个角色。

第三节. PostgreSQL 基础教程

来源: PostgreSQL 教程

本 PostgreSQL 教程可帮助您快速了解 PostgreSQL。您将通过许多实际示例快速掌握 PostgreSQL，并将这些知识应用于使用 PostgreSQL 开发应用程序。

如果你是 …

• 寻求快速学习 PostgreSQL。
• 使用 PostgreSQL 作为后端数据库管理系统开发应用程序。
• 从其他数据库管理系统（例如 MySQL、Oracle 和 Microsoft SQL Server）迁移到 PostgreSQL。

您将在此网站上找到快速有效地开始使用 PostgreSQL 所需的所有信息。

PostgreSQL 教程演示了 PostgreSQL 的许多独特功能，这些功能使其成为最先进的开源数据库管理系统。

PostgreSQL 入门

本部分向您展示如何在 Windows、Linux 和 macOS 上安装 PostgreSQL，帮助您开始使用 PostgreSQL。您还将学习如何使用 psql 工具连接到 PostgreSQL，以及如何将示例数据库加载到 PostgreSQL 中进行练习。

PostgreSQL 基础教程

首先，您将学习如何使用基本数据查询技术从单个表中查询数据，包括查询数据、对结果集进行排序和过滤行。然后，您将了解高级查询，例如连接多个表、使用集合操作以及构造子查询。最后，您将学习如何管理数据库表，例如创建新表或修改现有表的结构。

第 1 节. 查询数据

• 简单查询 – 向您展示如何从单个表中查询数据。
• 列别名 – 了解如何为查询中的列或表达式分配临时名称。
• 排序 – 指导您如何对查询返回的结果集进行排序。
• 去重查询 – 为您提供一个删除结果集中重复行的子句。
• 分页查询 – 指导您如何对查询返回的结果集进行分页。

第 2 节. 过滤数据

• WHERE – 根据指定条件过滤行。
• LIMIT – 获取查询生成的行的子集。
• FETCH – 限制查询返回的行数。
• IN – 选择与值列表中的任何值匹配的数据。
• BETWEEN – 选择值范围内的数据。
• LIKE – 基于模式匹配过滤数据。
• IS NULL – 检查值是否为空。

第 3 节. 连接多个表

• 连接 – 向您展示 PostgreSQL 中连接的简要概述。
• 表别名 – 描述如何在查询中使用表别名。
• 内连接 – 从一个表中选择在其他表中具有相应行的行。
• 左连接 – 从一个表中选择行，这些行在其他表中可能有也可能没有对应的行。
• 自连接 – 通过将表与自身进行比较来将表与其自身连接。
• 完全外连接 – 使用完全连接查找一个表中在另一个表中没有匹配行的行。
• 交叉连接 – 生成两个或多个表中的行的笛卡尔积。
• 自然连接 – 根据连接表中的公共列名称，使用隐式连接条件连接两个或多个表。
• 横向连接 – 通过连接交叉引用子查询中的行，和构建复合结果集。

第 4 节. 数据分组

• GROUP BY – 将行分成组并对每个组应用聚合函数。
• HAVING – 对组应用条件。
• PARTITION BY – 将行分成组并对每个组应用窗口函数。

第 5 节. 集合运算

• UNION – 将多个查询的结果集合并为一个结果集。
• INTERSECT – 组合两个或多个查询的结果集并返回一个结果集，该结果集的行都出现在两个结果集中。
• EXCEPT – 返回第一个查询中未出现在第二个查询的输出中的行。

第 6 节. 分组集、多维分组和汇总

• 分组集 – 在报告中生成多个分组集。
• CUBE – 定义多个分组集，其中包括所有可能的维度组合。
• ROLLUP – 生成包含总计和小计的报告。

第 7 节. 子查询

• 子查询 – 编写一个嵌套在另一个查询中的查询。
• 相关子查询 – 向您展示如何使用相关子查询，来执行依赖于正在处理的当前行的值的查询。
• ANY – 通过将某个值与子查询返回的一组值进行比较来检索数据。
• ALL – 通过将值与子查询返回的值列表进行比较来查询数据。
• EXISTS – 检查子查询返回的行是否存在。

第 8 节. 公共表表达式

• PostgreSQL CTE – 向您介绍 PostgreSQL 公共表表达式或 CTE。
• 使用 CTE 的递归查询 – 讨论递归查询并学习如何在各种上下文中应用它。

第 9 节. 修改数据

在本节中，您将学习如何使用INSERT语句向表中插入数据、使用UPDATE语句修改现有数据以及使用DELETE语句删除数据。此外，您还将学习如何使用 UPSERT 语句来合并数据。

• 插入 – 指导您如何将单行插入表中。
• 插入多行 – 向您展示如何在表中插入多行。
• 更新 – 更新表中的现有数据。
• 连接更新 – 根据另一个表中的值更新表中的值。
• 删除 – 删除表中的数据。
• 连接删除 – 根据另一个表中的值删除表中的行。
• 级联删除 – 在删除父表中的行时级联删除子表中的相关行。
• UPSERT – 如果新行已存在于表中，则插入或更新数据。
• 合并 – 有条件地插入、更新和删除一个表中的行。

第 10 节. 事务

• PostgreSQL 事务 – 向您展示如何使用BEGIN、COMMIT和ROLLBACK语句，来处理 PostgreSQL 中的事务。
• PostgreSQL 子事务 – 向您展示如何使用SAVEPOINT、ROLLBACK TO SAVEPOINT和RELEASE SAVEPOINT语句，来处理 PostgreSQL 子事务。
• 并发锁定和组合事务 – 向您介绍什么是组合事务，它为什么会存在，以及会在什么情况下出现。
• SKIP LOCKED 跳过锁定 – 向您介绍如何在 PostgreSQL 中使用SKIP LOCKED，以避免和解决死锁的问题。

第 11 节. 导入和导出数据

您将学习如何使用COPY命令、DBeaver 工具，以 CSV、SQL 文件格式对 PostgreSQL 数据进行导入和导出。

• 将 CSV 文件导入表中 – 向您展示如何将 CSV 文件导入表中。
• 导出表到 CSV 文件 – 向您展示如何将表导出到 CSV 文件。
• 使用 DBeaver 导入数据 – 向您展示如何使用 DBeaver 将数据从文件导入到表中。
• 使用 DBeaver 导出表 – 向您展示如何使用 DBeaver 将表导出到不同类型和格式的文件。
• 使用 DBeaver 合并数据 – 向您展示如何使用 DBeaver 将文件中的数据合并到表中。

第 12 节. 管理表

在本节中，您将开始探索 PostgreSQL 数据类型，并向您展示如何创建新表和修改现有表的结构。

• 数据类型 – 涵盖最常用的 PostgreSQL 数据类型。
• 创建表 – 指导您如何在数据库中创建新表。
• SELECT INTO 和 CREATE TABLE AS – 向您展示如何从查询的结果集创建新表。
• 使用 SERIAL 自增列 – 使用 SERIAL 将自动增量列添加到表中。
• 序列 – 向您介绍序列并描述如何使用序列生成数字序列。
• 标识列 – 向您展示如何使用标识列。
• 生成列 – 向您展示如何使用生成列。
• 更改表 – 修改现有表的结构。
• 重命名表 – 将表的名称更改为新名称。
• 添加列 – 向您展示如何向现有表添加一列或多列。
• 删除列 – 演示如何删除表的列。
• 更改列数据类型 – 向您展示如何更改列的数据。
• 重命名列 – 说明如何重命名表中的一列或多列。
• 删除表 – 删除现有表及其所有依赖对象。
• 截断表 – 快速有效地删除大表中的所有数据。
• 临时表 – 向您展示如何使用临时表。
• 复制表 – 向您展示如何将表格复制到新表格。
• 表分区 – 向您展示如何使用表分区。
• 管理分区表 – 向您展示如何管理分区表。
• 引发表重写的 DDL 命令 – 向您介绍哪些 DDL 命令会导致表的重写。
• 以最短的停机时间更改列 – 向您介绍如何以最短的停机时间更改列。

第 13 节. 了解 PostgreSQL 约束

• 主键 – 说明在创建表或向现有表添加主键时如何定义主键。
• 外键 – 展示如何在创建新表时定义外键约束或为现有表添加外键约束。
• 检查约束 – 添加逻辑以基于布尔表达式检查值。
• 唯一约束 – 确保一列或一组列中的值在整个表中是唯一的。
• 非空约束 – 确保列中的值不是NULL。

第 14 节. 深入了解 PostgreSQL 数据类型

• 布尔型 – 使用布尔数据类型存储TRUE和FALSE值。
• 字符型 – 了解如何使用各种字符类型，包括char、varchar和text。
• numeric – 向您展示如何使用numeric类型来存储需要精度的值。
• double precision – 了解如何在数据库中存储不准确的可变精度数字。double precision 类型也称为 float 类型。
• real – 指导您如何在数据库中使用单精度浮点数。
• 整型 – 向您介绍 PostgreSQL 中的各种整数类型，包括smallint、int和bigint。
• date – 引入date用于存储日期值的数据类型。
• 时间戳 – 快速了解时间戳数据类型。
• 间隔 – 向您展示如何使用间隔数据类型有效地处理一段时间。
• time – 使用time数据类型来管理一天中的时间值。
• UUID – 指导您如何使用UUID数据类型以及如何使用提供的模块生成UUID值。
• 数组 – 向您展示如何使用数组，并向您介绍一些用于数组操作的方便函数。
• hstore – 向您介绍数据类型，它是存储在 PostgreSQL 中单个值中的一组键/值对。
• JSON – 说明如何使用 JSON 数据类型，并向您展示如何使用一些最重要的 JSON 运算符和函数。
• 用户定义的数据类型 – 向您展示如何使用CREATE DOMAIN和CREATE TYPE语句创建用户定义的数据类型。
• bytea – 了解如何在数据库中存储二进制字节串。

第 15 节. 条件表达式和运算符

• CASE – 向您展示如何使用CASE表达式构成条件查询。
• COALESCE – 返回第一个非空参数。您可以使用它将NULL替换为一个默认值。
• NULLIF – 如果第一个参数等于第二个参数则返回NULL。
• CAST – 从一种数据类型转换为另一种数据类型，例如，从字符串转换为整数，从字符串转换为日期。

第 16 节. PostgreSQL 实用程序

• psql 命令 – 向您展示最常见的 psql 命令，帮助您更快、更有效地与 psql 交互。

第 17 节. 故障处理

• 应对死锁 – 指导您如何在 PostgreSQL 中处理死锁的问题。
• 处理数据块损坏 – 指导您如何处理 PostgreSQL 中损坏的数据块。
• 处理 TOAST 数据损坏 – 指导您如何处理 PostgreSQL 中损坏的 TOAST 数据。
• 处理统计信息损坏 – 指导您如何处理 PostgreSQL 中的统计信息损坏。
• 处理 PL/pgSQL 运行时错误 – 指导您如何对 PostgreSQL 中的 PL/pgSQL 运行时错误进行处理。
• 页面缓存如何影响查询性能？ – 指导您如何分析页面缓存对 PostgreSQL 性能的影响。
• 处理事务 ID 回卷的故障 – 指导您如何处理 PostgreSQL 中事务 ID 回卷的故障。
• 检查后端进程的内存使用情况 – 指导您如何检查 PostgreSQL 中后端进程的内存使用情况，和进行故障处理。
• 记录函数内错误的调用栈 – 指导您如何记录在指定函数内发生错误时的调用栈。
• 分区剪枝不起作用的原因 – 向您介绍在 PostgreSQL 中分区剪枝不起作用的主要原因。
• 处理 pg_wal 目录中的 WAL 积压 – 指导您如何在 PostgreSQL 中排查 pg_wal 目录中的 WAL 积压问题。
• 处理服务器可用内存不足的问题 – 指导您如何在 PostgreSQL 服务器上处理可用内存不足的问题。
• 处理缓存命中率低的问题 – 指导您如何在 PostgreSQL 中处理缓存命中率低的问题。

第 18 节. PostgreSQL 技巧

• 如何比较两个表 – 描述如何比较数据库中两个表中的数据。
• 使用 pgAdmin 4 对比数据架构 – 指导您如何使用 pgAdmin 4 提供的架构差异功能，比较两个数据库或两个模式之间的对象。
• 使用 DBeaver 迁移表数据 – 向您介绍使用 DBeaver 工具，在不同数据库之间或同一数据库内的表之间传输数据。
• 如何在 PostgreSQL 中删除重复行 – 向您展示从表中删除重复行的各种方法。
• 如何生成某个范围内的随机数 – 说明如何生成特定范围内的随机数。
• 查询 JSON 列中内嵌的数组 – 描述如何在 PostgreSQL 中查询 JSON 列中内嵌的数组。
• 更改 JSON 列中内嵌的数组 – 描述如何在 PostgreSQL 中修改 JSON 列中内嵌的数组。
• PostGIS 基础用法 – 向您介绍 PostGIS 的一些基础用法。
• 使用 PostGIS 进行基础的地理空间数据查询 – 向您演示用于处理地理空间数据的基础 PostGIS 查询。

第四节. PostgreSQL 高级教程

这个 PostgreSQL 高级教程涵盖了高级概念，包括存储过程、索引、视图、触发器和数据库管理。

PostgreSQL 函数

PostgreSQL 为内置数据类型提供了大量的函数。本节向您展示如何使用一些最常用的 PostgreSQL 函数。

PostgreSQL PL/pgSQL

此 PostgreSQL 存储过程部分将逐步向您展示如何使用 PL/pgSQL 过程语言开发 PostgreSQL 用户定义函数。

PostgreSQL 触发器

本节向您介绍 PostgreSQL 触发器概念，并展示如何在 PostgreSQL 中管理触发器。

PostgreSQL 视图

我们将向您介绍数据库视图概念，并向您展示如何管理视图，例如在数据库中创建、更改和删除视图。

PostgreSQL 索引

PostgreSQL 索引是增强数据库性能的有效工具。索引可以帮助数据库服务器比没有索引时更快地找到特定行。

PostgreSQL 优化

本节向您介绍 PostgreSQL 性能优化，并展示如何在 PostgreSQL 中优化各种场景的性能问题。

PostgreSQL 管理

PostgreSQL 管理涵盖 PostgreSQL 数据库服务器最重要的活动，包括角色和数据库管理、备份和恢复。

PostgreSQL 监控

PostgreSQL 监控涵盖 PostgreSQL 数据库服务器最重要的监控和运维活动。

应用程序编程接口

本节向您展示，如何从使用流行编程语言（例如 Java、Python 和 PHP）的应用程序，与 PostgreSQL 数据库进行交互。

• PostgreSQL Java 教程 – 此 PostgreSQL JDBC 部分向您展示，如何使用 Java JDBC 驱动程序与 PostgreSQL 数据库进行交互。
• PostgreSQL Python 教程 – 此 PostgreSQL Python 部分向您展示，如何使用 Python 编程语言与 PostgreSQL 数据库进行交互。
• 使用 Golang 连接到 PostgreSQL – 向您介绍如何使用 Go 编程语言与 PostgreSQL 数据库进行交互。

四 OceanBase 参考手册

1. OceanBase 与 MySQL 兼容性总结及 JDBC 配置指南

1.1 兼容性概述

OceanBase 数据库在以下方面高度兼容 MySQL（接近 100%）：

• SQL 语法
• 数据读取和写入
• 事务处理机制
• 系统表结构

1.2 JDBC 连接配置调整

从 MySQL 迁移到 OceanBase 时，需要修改 dbcp.properties 的 JDBC 连接 URL，添加以下两个关键参数：

• rewriteBatchedStatements=TRUE
• allowMultiQueries=TRUE

MySQL 模式下的 JDBC 参数配置建议：

url=jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=3000000&connectTimeout=60000&zeroDateTimeBehavior=convertToNull&allowPublicKeyRetrieval=true

• allowMultiQueries：默认为 FALSE，必须设置为 TRUE，以允许使用分号连接的多语句文本格式。对使用文本协议的场景，只需开启此配置即可实现批量优化。
• rewriteBatchedStatements：默认为 FALSE，若使用 PS 协议，则必须设置为 TRUE，以在执行 executeBatch() 时将多条语句改写为分号连接的多语句格式。

特例说明：开启 rewriteBatchedStatements 后，对于多条 multi queries 插入语句（无论使用文本协议或 PS 协议），JDBC 驱动会将其改写为一条 Multi Values Insert 语句。

参考文档：

• JDBC 配置示例
• 数据库 URL-V2.4.13-OceanBase JDBC 驱动程序文档
• 配置 JDBC 和 OBServer 实现 Batch DML 最佳性能的最佳实践

---

2. OceanBase 概述

OceanBase 数据库是一款完全自研的企业级原生分布式数据库，具有以下特性：

• 在普通硬件上实现金融级高可用
• 首创“三地五中心”城市级故障自动无损容灾新标准
• 单集群规模超过 1500 节点
• 支持云原生、强一致性、高度兼容 Oracle/MySQL

官方文档：OceanBase 概述

---

3. 与 MySQL 兼容性对比

OceanBase 数据库的 MySQL 模式兼容 MySQL 5.7/8.0 的绝大部分功能和语法。由于产品架构不同，或者客户需求不大，有些功能并没有被支持。以下是 OceanBase 数据库的 MySQL 模式与原生 MySQL 数据库的不同之处：

• 数据类型
• SQL 语法
• 过程性语言
• 系统视图
• 字符集
• 字符序
• 函数与表达式
• 分区支持
• 备份恢复
• 存储引擎
• 优化器

数据类型：

• 数值类型：BOOL/BOOLEAN/TINYINT、SMALLINT、MEDIUMINT、INT/INTEGER、BIGINT、DECIMAL、NUMERIC、FLOAT、DOUBLE、BIT
• 日期时间类型：DATETIME、TIMESTAMP、DATE、TIME、YEAR
• 字符类型：CHAR、VARCHAR、BINARY、VARBINARY
• 大对象类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
• 文本类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、STRING

---

4. MySQL 模式使用限制

4.1 集群名长度限制

数据项	最大长度
集群名	128 字节

4.2 标识符长度限制

数据项	最大长度
用户名	64 字节
租户名	63 字节
数据库名	128 字节
表名	64 字符
列名	128 字节
索引名	64 字节
视图名	64 字节
别名	255 字节
表组名	127 字节

4.3 单个表的限制

类型	最大限制
行长度	1.5M 字节
列数	4096 列
索引个数	128 个
索引总列数	512 列
索引长度	16K
主键总列数	64 列
主键长度	16K
分区个数	Oracle 模式：65536 个，MySQL 模式：8192 个（从 V4.2.1 BP3 版本开始，MySQL 模式下单个表允许的最大分区数由租户级配置项 max_partition_num 控制，默认为 8192 个）

4.4 单列的限制

类型	最大限制
索引单个列长度	16K

4.5 字符串类型限制

类型	最大长度
CHAR	256 字符
VARCHAR	262144 字符
BINARY	256 字节
VARBINARY	1048576 字节
TINYBLOB	255 字节
BLOB	65535 字节
MEDIUMBLOB	16777215 字节
LONGBLOB	536870910 字节
TINYTEXT	255 字节
TEXT	65535 字节
MEDIUMTEXT	16777215 字节
LONGTEXT	536870910 字节

---

5. 创建索引

参考文档：创建索引

5.1 创建索引的限制

• 索引名称必须在数据库范围内唯一，长度不能超过 64 字节。
• 唯一索引的列值必须保持唯一，局部唯一索引必须包含表的分区函数中的所有列。
• 全局索引的分区规则不一定需要与表的分区规则完全相同。

---

6. 数据写入

参考文档：

• 插入数据
• 更新数据
• 删除数据
• 替换数据

---

7. 数据读取

参考文档：

• 单表查询
• 多表关联查询
• 在查询中使用操作符和函数

---

8. 事务

事务机制和 MySQL 一样，完全兼容 MySQL。

事务控制语句：

• BEGIN：显式开启一个事务。
• SAVEPOINT：在事务过程中标记一个“保存点”。
• COMMIT：提交并结束当前事务。
• ROLLBACK：回滚整个事务或回滚到某个保存点。

相关文档：

• 开启事务
• 事务保存点
• 提交事务
• 回滚事务

---

9. 租户管理

OceanBase 数据库采用了多租户架构。集群是 Zone 和节点的集合，租户则是资源层面的逻辑概念，通过资源池与资源关联。

相关文档：

• 租户介绍
• 租户容量
• 租户类型
• 用户租户介绍
• 租户系统变量
• 租户常见操作

---

10. 批量操作配置

异常 SQL 示例：

SQL出错：DELETE FROM ui_file_seed WHERE id = 'mbm-mdm.master.mbm_mdm_wip_resource_template',异常信息:Not supported feature or function
2025-03-27 14:52:05.083 [main] ERROR c.s.snest.engine.db.relationdb.RelationDBAccessor -数据源:main,批量执行SQL出错：DELETE FROM ui_file_seed WHERE id = 'mbm-mdm.master.whm_storage_location_template',异常信息:批量执行SQL出错

遇到批量执行 SQL 出错时，需要添加参数到 JDBC URL：rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE

参考文档：JDBC 配置示例

JDBC 连接示例：

url=jdbc:oceanbase://xxx.xxx.xxx.xxx:3306/test?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=10000&connectTimeout=30000

相关参数说明：

• rewriteBatchedStatements：建议设置为 TRUE，以提高批量插入的性能。
• allowMultiQueries：建议设置为 TRUE，允许使用分号连接的多语句文本格式。
• useLocalSessionState：建议设置为 TRUE，避免频繁向 OB 数据库发送 session 变量查询 SQL。
• socketTimeout：执行 SQL 时，socket 等待 SQL 返回的时间。
• connectTimeout：建立连接时，等待连接的时间。
• useCursorFetch：建议设置为 TRUE，适用于大数据量查询。
• useServerPrepStms：控制是否使用 PS 协议发送 SQL。
• cachePrepStmts：控制是否开启 PS cache，避免重复执行 prepare。
• prepStmtCacheSQLLimit：可放入 PS cache 的 SQL 长度限制。
• prepStmtCacheSize：PS cache 可保存的 SQL 数量。
• maxBatchTotalParamsNum：针对 batch 操作，一条 SQL 最多支持的参数个数。

---

11. 数据类型转换规则

数据类型转换和 MySQL 一致。

---

12. Docker 部署 OceanBase

安装参考文档：Docker 部署 OceanBase

部署步骤：

sudo docker pull quay.io/oceanbase/oceanbase-ce
sudo docker run -p 2881:2881 --name oceanbase -e MODE=MINI -e OB_TENANT_PASSWORD=****** -d quay.io/oceanbase/oceanbase-ce
sudo docker logs oceanbase | tail -1
sudo docker exec -it oceanbase bash
docker logs oceanbase | tail -1

obclient -uroot@sys -h127.0.0.1 -P2881 -p
-- 默认密码为空，直接回车

-- 修改密码
ALTER USER 'root' IDENTIFIED BY 'your_password';

obclient -uroot@sys -h127.0.0.1 -P2881 -p your_password

CREATE DATABASE snest_changwen DEFAULT CHARACTER SET utf8mb4 READ WRITE;

show databases;

13. 本地 JDBC连接 OceanBase

dbcp.properties添加oceanbase配置

########OceanBase########
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://xxx.xxx.xxx.xxx:2881/snest?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=10000&connectTimeout=30000&zeroDateTimeBehavior=convertToNull&allowPublicKeyRetrieval=true
username=root
password=******
validationQuery=SELECT 1

五 GaussDB 参考手册

1. 产品概述

GaussDB 是华为云推出的一款高性能、高可用、可扩展的云数据库产品，支持分布式和集中式两种部署模式。它适用于多种应用场景，包括企业级应用、数据分析和物联网等。

更多版本信息请参考：GaussDB 文档中心

高斯数据库支持的版本:

SELECT VERSION();
gaussdb (GaussDB Kernel 505.2.0 build 5a40f15c) compiled at 2024-09-20 00:15:19 commit 9967 last mr 19883 release

引擎版本: v2.8.1-RELEASE

引擎支持的高斯数据库默认是使用的 MySQL 兼容模式,所以应用程序的SQL语法和MySQL基本兼容.

IIDP目前支持高斯数据库的MySQL数据库兼容MySQL模式

<dependency>
 <groupId>com.sie.meta</groupId>
 <artifactId>sie-snest-engine</artifactId>
 <version>v2.8.1-RELEASE</version>
</dependency>

SqlProvider实现: com.sie.snest.engine.db.relationdb.provider.GaussDBProvider

返回数据库类型DBType: GaussDB

 com.sie.snest.engine.db.relationdb.provider.GaussDBProvider
/**
 * return GaussDB
 */
@Override
public String getDBType() {
	return DBType.GaussDB.getName();
}

2. 部署方案

• 分布式版部署方案

• 集中式版部署方案

3. GaussDB 数据库兼容性说明

GaussDB 支持多种兼容性模式，具体如下：

• 集中式部署：
  • A：兼容 Oracle。
  • B：兼容 MySQL。
  • PG：兼容 PostgreSQL。
• 分布式部署：
  • ORA：兼容 Oracle。
  • MYSQL：兼容 MySQL。
  • PG：兼容 PostgreSQL。

执行以下 SQL 命令，可以查看所有数据库的兼容性模式：

SELECT datname, datcompatibility FROM pg_database;

datname    |datcompatibility|
-----------+----------------+
templatea  |ORA             |
snest_test3|MySQL           |
test       |MYSQL           |
template1  |MYSQL           |
template0  |MYSQL           |
templatem  |M               |
postgres   |MYSQL           |
tpcc       |MYSQL           |

1. MySQL 兼容模式

IIDP只支持高斯数据库的MySQL数据库兼容MySQL模式

GaussDB 提供了对 MySQL 5.7 的兼容模式，通过设置特定的参数，可以实现与 MySQL 5.7 数据库的高度兼容。以下是具体的兼容性说明：

由于GaussDB数据库与MySQL数据库底层框架实现存在差异，GaussDB数据库与MySQL数据库仍存在部分差异。

分布式版MySQL兼容性说明

• 概述
• MySQL数据库兼容MySQL模式
• MySQL数据库兼容性M-Compatibility模式

参考文档: https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.7.30.10/compatibility-description/gaussdb-20-0002.html

1.1 参数设置

为了启用 MySQL 兼容模式，需要在 GaussDB 数据库中设置以下参数：

• sql_compatibility：设置为 'MYSQL'，以启用 MySQL 兼容模式。

• b_format_version：设置为 '5.7'，以指定 MySQL 的版本兼容性。

• b_format_dev_version：设置为 's1'，以指定开发版本的兼容性。

  SHOW enable_gtm_free;
  SHOW gtm_option;
  show b_format_version;
  show b_format_dev_version;

1.2 数据类型兼容性

• GaussDB 支持 MySQL 5.7 中定义的大多数数据类型，包括但不限于：
  • 数值类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、DECIMAL、FLOAT、DOUBLE 等。
  • 字符串类型：CHAR、VARCHAR、TEXT、BLOB 等。
  • 日期和时间类型：DATE、TIME、DATETIME、TIMESTAMP 等。
• 在某些情况下，GaussDB 可能对某些数据类型的行为或限制进行了优化或调整，以适应其自身的架构和性能要求。

1.3 SQL 功能兼容性

• SQL 语法：GaussDB 支持 MySQL 5.7 的大多数 SQL 语法，包括：
  • 数据定义语言（DDL）：如 CREATE TABLE、ALTER TABLE、DROP TABLE 等。
  • 数据操纵语言（DML）：如 INSERT、UPDATE、DELETE、SELECT 等。
  • 数据控制语言（DCL）：如 GRANT、REVOKE 等。
• 存储过程和函数：支持 MySQL 5.7 的存储过程和函数语法，用户可以创建和使用存储过程来实现复杂的业务逻辑。
• 视图和索引：支持视图和索引的创建和使用，用户可以通过视图简化复杂的查询操作，并利用索引提高查询性能。
• 事务支持：支持事务操作，确保数据的一致性和完整性。

1.4 数据库对象兼容性

• 表和表空间：支持 MySQL 5.7 中的表和表空间概念，用户可以创建、修改和删除表，以及管理表空间。
• 用户和权限管理：支持 MySQL 5.7 的用户和权限管理机制，用户可以创建和管理用户账户，并授予或撤销用户权限。
• 字符集和校对规则：支持 MySQL 5.7 的字符集和校对规则，用户可以根据需要选择合适的字符集和校对规则。

1.5 特殊兼容性说明

• 语法差异：尽管 GaussDB 努力实现与 MySQL 5.7 的高度兼容，但在某些复杂的 SQL 语法或特定功能上可能存在差异。例如，某些特定的 MySQL 函数或存储过程可能在 GaussDB 中表现略有不同。
• 性能优化：GaussDB 在某些场景下可能需要额外的性能优化配置，以充分发挥其性能优势。
• 功能限制：某些 MySQL 特性可能在 GaussDB 中不完全支持，或者需要通过其他方式实现。

2. 其他兼容模式

GaussDB 还支持其他数据库的兼容模式，例如 Oracle、PostgreSQL 和 Teradata 等。以下是简要说明：

2.1 Oracle 兼容模式

• 参数设置：使用参数 c='A' 或 dbcompatibility='ORA' 创建兼容 Oracle 的数据库。
• 功能支持：支持 Oracle 的数据类型、SQL 语法、系统函数、日期时间类型等。

2.2 PostgreSQL 兼容模式

• 参数设置：使用参数 dbcompatibility='PG' 创建兼容 PostgreSQL 的数据库。
• 功能支持：支持 PostgreSQL 的数据类型、SQL 语法和对象定义。

更多详细信息和完整兼容性说明，请参考 GaussDB 官方文档：

• GaussDB 文档中心 - MySQL 兼容性概述

4. 高斯数据库不兼容项

1. 不支持唯一索引,不支持复合唯一索引

在华为GaussDB中，当出现“Cannot create index whose evaluation cannot be enforced to remote nodes”错误时，通常是因为在分布式数据库系统中创建索引时，没有包含分布列（即决定数据如何在各个节点间分布的列）。 之前的MySQL唯一索引就失效了.

主键约束的本质就是一个非空约束加一个唯一约束，重点关注这个唯一约束（通过一个唯一索引实现）。

对于hash分布表，分布列决定了这条数据将在CN(CoordinateNode协调节点)被hash分布到哪个DN（DataNode数据节点）上，所以唯一约束本质上是确保分布到这个DN的数据的唯一性。如果允许唯一约束不包含分布列，那么就会出现在不同DN上查询到相同唯一键的情况，这肯定不是期望内的。

对于复制表（replicated table）, 由于每个数据在每个DN上都被存有一份，所以不存在这个问题。

所以出现这个报错的时候要么使主键的定义包含分布列，要么改用复制表,要么使用复合唯一索引,要么使用全局二级索引。

不支持原因:

1. 使用唯一索引时: 唯一列必须包含分布式列,导致复合唯一索引失效了,因为必须带ID字段加唯一字段,就违背了复合唯一索引的定义.

2. 使用全局二级索引:遇到下面3个错误:

   ERROR: INSERT/UPDATE/DELETE/MERGE contains multiple remote queries under GTM-free mode
   
   解决方式:开启GTM-free 模式

   解决完第一个错误后,然后又遇到下面的错误:

   INSERT ... SELECT ... WHERE NOT EXISTS 不支持, INSERT  INTO IGNORE不支持, 改为INSERT  INTO " + sql +" ON DUPLICATE KEY UPDATE NOTHING
   解决方式:使用流算子或 Hint
   SET enable_stream_operator=on;
   或者在 SQL 语句中添加 `/*+ multinode */` Hint：

   修复完上面的2个错误后,又遇到往数据库插入或更新数据时报错，提示分布键不能被更新，错误信息如下所示：

   ERROR: Distributed key column can't be updated in current version

这个错误的意思是,如果列是分布式列,就不能更新该字段,这种就会导致应用程序更新失败.咨询了华为的同事.没有参数可以跳过.

1. 因为引擎插入和更新的时候会做唯一校验,如果不支持唯一校验,不影响业务的插入和更新时的唯一校验.

2. SELECT AS 别名问题,如果别名不带标识符``或者"",PostgreSQL/GaussDB会将别名转成小写

请注意，在 PostgreSQL/GaussDB 中，关键词和不被引号修饰的标识符是大小写不敏感的。因此,如果字段不带修饰符双引号,会转换成小写.

如果自定义SQL列别名不带修饰符,这样就会导致代码获取displayName值为NULL, get("displayName")=NULL.就会导致数据库迁移的时候,不兼容.

正确的SQL写法,列别名如果是驼峰或者包含大小写的,一定要用 ``或者 ""引起来.

select display_name as displayName from meta_app;

--MySQL
displayName   |
-----------------+
基础模块      |


--PostgreSQL/GaussDB
displayname   |
-----------------+
基础模块  


正确的SQL写法,列别名如果是驼峰或者包含大小写的
MYSQL: select display_name as `displayName` from meta_app;
GAUSSDB select display_name as `displayName` from meta_app;
PostgreSQL: select display_name as "displayName" from meta_app;

项目自定义SQL错误的示范:
SELECT param_name paramName, param_value paramValue FROM base_process_spec bps;

标准的SQL语法:
SELECT param_name "paramName", param_value `paramValue` FROM base_process_spec bps
SELECT param_name "paramName", param_value "paramValue" FROM base_process_spec bps

3. ERROR: INSERT/UPDATE/DELETE/MERGE contains multiple remote queries under GTM-free mode

-数据源:main,执行SQL失败:INSERT INTO ui_file_seed (`bucket`, `path`, `file_archive_path`, `name`, `attachment_id` , `download_path`, `id`, `create_user`, `create_date`, `app_id` , `url`, `md5`) SELECT 'apps' , '/file/document/doc/定制权限-导入模板.xlsx' , 'BOOT-INF/classes/com/sie/app/tenant/file/document/doc/定制权限-导入模板.xlsx', '下载定制权限导入模板', '04pebasn0auvo' , '/apps/2025-03-20/619344493502774957.xlsx' , 'sie-snest-tenant.master.custom_permission_import_template', 'rbac_user_superuser' , '2025-03-20 17:40:54.917', '04peack6huebq' , '/fileSystem/apps/2025-03-20/619344493502774957.xlsx' , '38ea78b3e59ef8cfbfa77ada296a71a0' FROM dual WHERE NOT EXISTS ( SELECT 1 FROM ui_file_seed WHERE id = 'sie-snest-tenant.master.custom_permission_import_template' ),异常信息:[2.0.0.1:55959/192.168.96.11:8000] ERROR: INSERT/UPDATE/DELETE/MERGE contains multiple remote queries under GTM-free mode
  建议：modify your SQL to generate light-proxy or fast-query-shipping plan

问题分析

• GTM-free 模式：在这种模式下，GaussDB 对分布式事务的管理方式有所限制，尤其是对涉及多个远程节点（如跨节点的子查询）的复杂查询支持较弱。
• 错误原因：你的 SQL 语句中使用了 INSERT ... SELECT ... WHERE NOT EXISTS，这种结构会被解析为多个远程查询，导致 GTM-free 模式下无法执行。
• 参考: https://support.huaweicloud.com/distributed-devg-v2-gaussdb/gaussdb-12-1095.html

解决方案

根据错误信息中的建议，你需要修改 SQL，使其生成 light-proxy 或 fast-query-shipping 计划。

4. INSERT … SELECT … WHERE NOT EXISTS 不支持, INSERT INTO IGNORE不支持, 改为INSERT INTO " + sql +" ON DUPLICATE KEY UPDATE NOTHING

5. 不支持修改分布式列的字段,比如修改主键的长度.

6. 事物回滚机制和MySQL不同,事物机制和PostgreSQL一致.

4. 用户指南

数据库管理员应具备以下能力：拥有极强的数据库技术理解能力；能够安装数据库、执行相关操作并排除故障；能够进行云数据库GaussDB管理平台（TPOPS）的日常维护。

工程师应具备以下能力：了解数据库技术常识；能够执行数据库相关的操作并排除故障。

请参考:https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.7.30.10/usermanual/qlh_02_0001.html

5. 数据库基本概念

数据库（Database）

数据库是存储在一起的相关数据的集合，这些数据可以被访问，管理以及更新。

数据库用于管理各类数据对象，与其他数据库隔离。创建数据对象时可以指定对应的表空间，如果不指定相应的表空间，相关的对象会默认保存在PG_DEFAULT空间中。数据库管理的对象可分布在多个表空间上。

数据块（Block）

数据块是数据库管理的基本单位，默认大小为8KB。

行（Row）

一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

列（Cloumn）

每一列被当作是一个字段。每个字段中的值代表一种类型的数据。例如，一个表可能有3个字段，姓名、城市和国家。这个表就会有3列，一列代表姓名，一列代表城市，一列代表国家。表中的每一行包含3个字段的内容，姓名字段包含姓名，城市字段包含城市，国家字段包含国家。

表（Table）

表是由行与列组合成的，是数据库中用来存储数据的对象，是整个数据库系统的基础。

每张表只能属于一个数据库，也只能对应到一个表空间。每张表对应的数据文件必须在同一个表空间中。

数据文件（Datafile Segment）

通常每张表只对应一个数据文件。如果某张表的数据大于1GB，则会分为多个数据文件存储。

表空间（Tablespace）

在Gauss中，表空间是一个目录，在物理数据和逻辑数据间提供了抽象的一层，为所有的数据库对象分配存储空间，里面存储的是它所包含的数据库的各种物理文件。由于表空间是一个目录，仅是起到了物理隔离的作用，其管理功能依赖于文件系统。

表空间可以存在多个，创建好之后，创建数据库对象时可以指定该对象所属的表空间。

模式（Schema）

数据库对象集，包括逻辑结构，例如表、视图、序、存储过程、同义名、索引及数据库链接。

事务（Transaction）

数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

同时，事务也是恢复和并发控制的基本单位，必须具备ACID特性，即：

• 原子性（Atomicity）：一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
• 一致性（Consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
• 隔离性（Isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（Durability）：持久性也称永久性（Permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

请参考: https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.7.30.10/devg-dist/gaussdb-12-0002.html

6. 分布式开发指南

请参考文档:https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/25.1.30/devg-dist/gaussdb-12-0078.html

6.1 数据库系统概述

• 数据库逻辑结构图
• 数据查询请求处理过程
• 管理事务

6.2 数据库安全

• 用户及权限
• 数据库审计

6.3 操作数据库

本节描述使用数据库的基本操作。通过此节您可以完成创建数据库用户、创建数据库、创建表及向表中插入数据和查询表中数据等操作。

• 创建数据库用户
• 管理数据库
• 管理表空间
• 管理表
• 查看系统表
• 其他操作

6.5 数据库设计规范

• 基本规范
• 部署规范
• 数据库对象命名规范
• Database和Schema设计规范
• 权限设计规范
• 字符集设计规范
• 表设计规范
• 字段设计规范
• 索引设计规范
• 函数/存储过程设计规范
• 约束设计
• 视图和关联表设计

6.6 应用程序开发教程

• GaussDB应用程序开发教程概述
• 开发规范
• 基于JDBC开发

本地JDBC配置:

1. dbcp.properties 示例:

########DBCP##########
initialSize=5
maxActive=2000
minIdle=5
filters=stat
maxWait=6000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=1800000
testOnBorrow=false
testOnReturn=false
testWhileIdle=true
poolPreparedStatements: true
maxOpenPreparedStatements: 20
connectionProperties:druid.stat.sql.MaxSize=100;
keepAlive=true


########gaussdb########
driverClassName=com.huawei.gaussdb.jdbc.Driver
url=jdbc:gaussdb://127.0.0.1:8000,127.0.0.1:8000,127.0.0.1:8000/tpcc?currentSchema=snest_jichen&autoBalance=true&refreshCNIpListTime=3&prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=OFF
username=******
password=******
validationQuery=SELECT 1
gaussdbDeployment=Distributed

2. spring配置文件添加参数适配高斯自定义SQL列字段别名转换

正常不需要添加这个配置,除非项目特殊需要.

application-dev.properties添加:

orm.sql.wrap_camelcase_alias=true

6.7 SQL调优指南

SQL调优的唯一目的是“资源利用最大化”，即CPU、内存、磁盘I/O、网络IO四种资源利用最大化。所有调优手段都是围绕资源使用开展的。所谓资源利用最大化是指SQL语句尽量高效，节省资源开销，以最小的代价实现最大的效益。比如做典型点查询的时候，可以用seqscan+filter（即读取每一条元组和点查询条件进行匹配）实现，也可以通过indexscan实现，显然indexscan可以以更小的代价实现相同的效果。

• Query执行流程
• SQL执行计划介绍
• 调优流程
• 更新统计信息
• 审视和修改表定义
• 典型SQL调优点
• 经验总结：SQL语句改写规则
• SQL调优关键参数调整
• 使用Plan Hint进行调优
• 检查隐式转换的性能问题
• 使用向量化执行引擎进行调优
• 使用SQL PATCH进行调优
• 实际调优案例

6.8 SQL参考

• GaussDB SQL
• 关键字
• 数据类型
• 字符集与字符序
• 常量与宏
• 函数和操作符
• 表达式
• 伪列
• 类型转换
• 系统操作
• 事务控制
• 全文检索
• SQL语法
• 附录

6.9 最佳实践

• SQL查询最佳实践
• 权限配置最佳实践
• 数据倾斜查询最佳实践
• 存储过程最佳实践

6.10 工具指南

用DBeaver客户端连接高斯数据库

分布式工具指南

1. 工具介绍：GaussDB 提供了多种工具支持分布式开发，如 GaussDB 数据库管理工具、备份工具等。
2. 使用方法：参考 GaussDB 分布式工具指南 获取工具的使用方法和示例。

集中式工具指南

1. 工具介绍：GaussDB 提供了多种工具支持集中式开发，如 GaussDB 数据库管理工具、性能监控工具等。
2. 使用方法：参考 GaussDB 集中式工具指南 获取工具的使用方法和示例。

7. 特性描述

7.1 分布式特性描述

• 弹性扩展：支持水平扩展，可根据业务需求动态调整实例数量。
• 高可用性：支持多副本存储和自动故障切换，确保数据的高可用性。
• 高性能：采用分布式架构，提供高性能处理能力。

7.2 集中式特性描述

• 性能优化：针对集中式场景进行优化，提供高性能处理能力。
• 数据安全：支持数据加密、访问控制等安全功能。
• 易用性：提供简单的管理界面和操作方式。

8. 常见问题

8.1 性能问题

• Q：如何优化 GaussDB 的性能？
  • A：可以通过调整数据库参数、优化 SQL 查询、增加实例规格等方式提升性能。具体优化方法请参考 性能优化指南。

8.2 安全问题

• Q：如何确保 GaussDB 的数据安全？

  • A：可以通过设置访问控制、数据加密、备份与恢复等方式确保数据安全。具体安全措施请参考 安全指南。

9. 资源链接

• 官方文档中心

• 分布式版MySQL兼容性说明

• SQL参考

• M-Compatibility数据库系统概述

六 Vastbase G100 参考手册

Vastbase 海量数据库基本上兼容Postgres SQL,所以SQL语法和Postgres一样,可以直接把Vastbase作为Postgres使用.

SQL语法可以直接参考: PostgreSQL 特性

1.参考指南

https://docs.vastdata.com.cn/zh_CN/VastbaseG100/V2.2.15/1/6fffc868878a49e3902ba0a6f68d087e

2. SQL语法参考

https://docs.vastdata.com.cn/zh_CN/VastbaseG100/V2.2.15/1/65ccc3f66f1a413eb169bb5f514e5e83

3.兼容性手册

https://docs.vastdata.com.cn/zh_CN/VastbaseG100/V2.2.15/1/5fda827143144783b9958728321c467c

4.MySQL兼容性

https://docs.vastdata.com.cn/zh_CN/VastbaseG100/V2.2.15/1/9363aeb3a4264c468b93079db61cdf6e

5.JAVA语言应用开发

https://docs.vastdata.com.cn/zh_CN/VastbaseG100/V2.2.15/1/e4b9ce00d5244d4786dcd26a20508f57

6.快速入门

https://docs.vastdata.com.cn/zh_CN/VastbaseG100/V2.2.15/1/0095ee60bd934df9b7a0fcafdf9b5361

本章节对 Vastbase G100 进行了简单的产品介绍以及安装和使用指导，帮助用户快速上手使用。

若用户希望了解更多 Vastbase G100 的详细特性以及参考信息，请查阅具体的文档。例如，《安装升级指南》提供了有关安装要求和过程的完整信息，《开发者指南》为基于 Vastbase G100 进行C/Java应用程序开发的程序员提供了必要的参考信息，《管理员指南》介绍了 Vastbase G100 数据库常用的管理员操作，方便管理员阅读参考。

• 产品概述
• 新手指引
• 安装数据库
• 升级说明
• 初始化数据库
• 启停数据库
• 使用数据库

6.管理员指南/数据库使用

本章节介绍使用数据库的相关操作，帮助使用本手册的管理员获取到使用和管理数据库的相关信息。包括如下内容：

• 从这里开始
• 创建和管理分区表
• 创建和管理视图
• 启停Vastbase
• 状态查询
• 连接数据库
• 创建和管理数据库
• 规划存储模型
• 创建和管理表空间
• 创建和管理表
• 查看系统表
• 触发器
• 查询缓存
• 其它操作

7.Docker 单机镜像安装

https://docs.vastdata.com.cn/zh_CN/VastbaseG100/V2.2.15/1/d9d69078b93448bbb219ec6407c4c0f2

参数名称	含义
-e VB_USERNAME	自动创建用户名称，和初始用户共享密码。
-e VB_DBCOMPATIBILITY	指定数据库初始化兼容模式，取值范围：A、B、C、PG、MSSQL。分别表示兼容Oracle、MySQL、PostgreSQL、SQL Server。
–name	运行容器名称。

七 Dameng(国产达梦数据库) 参考手册

1.数据库版本：DM8

2.Docker安装

参考：https://eco.dameng.com/document/dm/zh-cn/start/dm-install-docker.html 安装前准备 | 软硬件 | 版本 | | ——————— | ———————————————————— | | 终端 | X86-64 架构 | | Docker | 2023 年 6 月版 |

下载 Docker 安装包 请在达梦数据库官网下载 https://eco.dameng.com/download/ 。 导入安装包 拷贝安装包到 /opt 目录下，执行以下命令导入安装包： docker load -i dm8_20230808_rev197096_x86_rh6_64_single.tar 结果显示如下： 导入完成后，可以使用 docker images 查看导入的镜像。

启动容器 镜像导入后，使用 docker run 启动容器，启动命令如下： docker run -d -p 30236:5236 –restart=always –name dm8_test –privileged=true -e PAGE_SIZE=16 -e LD_LIBRARY_PATH=/opt/dmdbms/bin -e EXTENT_SIZE=32 -e BLANK_PAD_MODE=1 -e LOG_SIZE=1024 -e UNICODE_FLAG=1 -e LENGTH_IN_CHAR=1 -e INSTANCE_NAME=dm8_test -v /data/dm8_test:/opt/dmdbms/data dm8_single:dm8_20230808_rev197096_x86_rh6_64 注：挂载的磁盘/data/dm8_test要足够大，否则会启动失败。

容器启动完成后，使用 docker ps 查看镜像的启动情况，结果显示如下： 启动完成后，可通过日志检查启动情况，命令如下： docker logs -f dm8_test 或 docker logs -f 58deb28d1209

启动/停止数据库 停止数据库命令如下： docker stop dm8_test

启动数据库命令如下： docker start dm8_test

重启命令如下： docker restart dm8_test

注意1.如果使用 docker 容器里面的 disql，进入容器后，先执行 source /etc/profile 防止中文乱码。

2.新版本 Docker 镜像中数据库默认用户名/密码为 SYSDBA/SYSDBA001。

测试环境安装位置：

数据库连接信息： 服务器：192.168.175.193 docker 镜像：dm8_test

3.安装DM 管理工具

参考文档：https://eco.dameng.com/document/dm/zh-cn/start/tool-dm-manager.html

1.先去https://eco.dameng.com/download/?_blank 下载 windows DM8

2.安装的时候如果不需要安装数据库，只勾选DM 管理工具

4.测试连接DM数据库

参考链接：https://eco.dameng.com/document/dm/zh-cn/start/tool-dm-manager.html

数据库连接信息： 192.168.175.193:30236 SYSDBA/SYSDBA001

5.DM添加数据库驱动包，添加maven依赖

添加Maven依赖(DmJdbcDriver18已经上传到maven仓库)

<dependency>
		<groupId>com.dameng</groupId>
		<artifactId>    </artifactId>
		<version>8.1.3.111</version>
</dependency>

配置数据库连接信息 在 dbcp.properties 配置连接信息如下：

########dameng##########
driverClassName=dm.jdbc.driver.DmDriver
url=jdbc:dm://192.168.175.193:30236/IIDP_DEMO
username=SYSDBA
password=SYSDBA001

6.DM数据库兼容性测试

6.1 修改 dbcp.properties 配置连接信息如下：

########dameng########## 
driverClassName=dm.jdbc.driver.DmDriver 
url=jdbc:dm://192.168.175.193:30236/SNEST_TEST 
username=SNEST_TEST
password=SNEST_TEST#123

使用dbwaver客户端连接,连接方式选择ODBC,编辑ODBC模板 dm.jdbc.driver.DmDriver jdbc:dm://192.168.175.193:30236/SNEST_TEST 添加以下库为驱动：

https://drive.weixin.qq.com/s?k=AAIAKAcJAAcPnHMZYSAS8ATAaiAIk

6.2 因为DM数据库是兼容oracle的，所以测试用例和测试App 和oracle一样。