使用 PostgreSQL 的 uuid 字段类型 | 隔叶黄莺 Yanbin Blog

2018-05-21 | 阅读(5,780)

上一篇使用 SQL Server 的 uniqueidentifier 字段类型了解了 SQL Server 中如何使用 uniqueidentifier 字段类型后，现在来看下 PostgreSQL 中如何使用 uuid 字段类型。在 PostgreSQL 的字段类型是 uuid 了，所以创建一个带有 uuid 字段的表是

CREATE TABLE customers (
id uuid PRIMARY KEY,
name VARCHAR(36)
);

我们这里设置 id 字段类型为 uuid, 并且它是一个主键。也可以应用函数指定它的默认值，下面将会讲述到。

然后用 SQL 语句来向该表插入记录

insert into customers(id, name) values('0681757b-5f92-42c2-a4cd-90976f50225f', 'World')

--以下两条语句将会失败
insert into customers(id, name) values('0681757b-5f92-42c2-a4cd-90976f50225f', 'Upper Case')  --破坏了主键约束
insert into customers(id, name) values('x681757b-5f92-42c2-a4cd-90976f50225f', 'Changed first letter to x') --不是一个合法的 uuid

insert into customers(id, name) values('0681757b-5f92-42c2-a4cd-90976f50225f', 'World')

--以下两条语句将会失败

insert into customers(id, name) values('0681757b-5f92-42c2-a4cd-90976f50225f', 'Upper Case') --破坏了主键约束

insert into customers(id, name) values('x681757b-5f92-42c2-a4cd-90976f50225f', 'Changed first letter to x') --不是一个合法的 uuid

uuid 字段值必须为一个合法的 UUID 字符串, 我们可以用 Java 的 java.util.UUID.randomUUID().toString() 来生成。同样 UUID 按模式 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 展示出来也是它的外部表现形式，它内部是以 16 位来存储的，所以改变字符的大小写也是同一个值。插入非法的 uuid 字符串不被接受，也就是该字符串不能用 java.util.UUID.fromString("your-input") 还原为 UUID 对象就是不合法的。

这儿是一个 SQL Server 的 uniqueidentifier 和 PostgreSQL 的 uuid 字段的对比: UNIVERSAL UNIQUE IDENTIFIERS POSTGRESQL SQL SERVER COMPARE

前面还落下一个问题，即如何为 uuid 字段设定默认值及调用 PostgreSQL 函数来生成 uuid 值

从搜索到的资料来看，PostgreSQL 中可以调用uuid_generate_v1() 或 uuid_generate_v4() 等函数生成 uuid，于是我们试一下

select uuid_generate_v1();
select uuid_generate_v4();

1 2	select uuid_generate_v1(); select uuid_generate_v4();

均提示函数不存在，这是因为它们依赖于一个第三方模块 uuid-ossp, 在使用它的函数之前必须用 CREATE EXTENSION 安装该模块

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

1	CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

只有安装了 uuid-ossp 之后，上面的那两个函数 uuid_generate_v1() 和 uuid_generate_v4() 才能调用, 而且这时候才能在创建表时为 uuid 字段使用其中一个函数作为默认值

CREATE TABLE customers (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(36)
)

CREATE TABLE customers (

id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),

name VARCHAR(36)

)

uuid-ossp 模块包含的所有函数，和 uuid_generate_v1() 与 uuid_generate_v4() 之间的区别请查看链接 https://www.postgresql.org/docs/9.4/static/uuid-ossp.html

参观一下插入记录后查询 uuid 字段是怎么显示的，如下图

最后，也来看下 JDBC 中如何操作 uuid 字段

读取 uuid 字段仍然是用 rs.getString(column)，rs.getObject(column) 得到的是一个 java.util.UUID 对象。

JDBC 在应用 PreparedStatement 插入或更新 uuid 字段，只能用 PreparedStatement 的 setObject(...) 方法

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://localhost:5432/postgres", "postgres", "mysecretpassword");

PreparedStatement pstmt = conn.prepareStatement("insert into customers(id, name) values(?, ?)");

pstmt.setObject(1, UUID.randomUUID());

//以下两种方式调 uuid 类型字段的值均会报错
//pstmt.setObject(1, UUID.randomUUID().toString());
//pstmt.setString(1, UUID.randomUUID().toString());
        
pstmt.setString(2, "hello");

pstmt.execute();

Class.forName("org.postgresql.Driver");

Connection conn = DriverManager.getConnection(

"jdbc:postgresql://localhost:5432/postgres", "postgres", "mysecretpassword");

PreparedStatement pstmt = conn.prepareStatement("insert into customers(id, name) values(?, ?)");

pstmt.setObject(1, UUID.randomUUID());

//以下两种方式调 uuid 类型字段的值均会报错

//pstmt.setObject(1, UUID.randomUUID().toString());

//pstmt.setString(1, UUID.randomUUID().toString());

pstmt.setString(2, "hello");

pstmt.execute();

以上用 pstmt.setObject(1, UUID.randomUUID()) 可以正常插入一个 uuid 值。而如果尝试把 uuid 类型字段当作一个 VARCHAR 字段类型用

pstmt.setObject(1, UUID.randomUUID().toString());
pstmt.setString(1, UUID.randomUUID().toString());

将会得到错误信息

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "id" is of type uuid but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 40
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)

而从前一篇使用 SQL Server 的 uniqueidentifier 字段类型看到的是 SQL Server 用 JDBC 操作时是可以把 uniqueidentifier 字段当作 VARCHAR 类型来对待，这与 PostgreSQL 的 uuid 字段类型是不同的。

JDBC 插入 PostgreSQL 的 uuid 字段还有一种比之 setObject(1, uuid) 更烦琐的操作

PGobject newUUID = new PGobject();
newUUID.setType("uuid");
newUUID.setValue(UUID.randomUUID().toString());
pstmt.setObject(1, newUUID);

PGobject newUUID = new PGobject();

newUUID.setType("uuid");

newUUID.setValue(UUID.randomUUID().toString());

pstmt.setObject(1, newUUID);

在 PostgreSQL 的 PreparedStatement 的实现类 PgPreparedStatement (该类的可见度为默认，非 public) 有一个私有方法

private void setUuid(int parameterIndex, UUID uuid) throws SQLException { ... }

该方法只被 setObject(...) 在判定字段类型为 uuid 后调用，基本确定 uuid 操作时最简单的方式还是用 setObject(...)

pstmt.setObject(index, UUID.randomUUID());
pstmt.setObject(index, UUID.fromString("<uuid-string>"));

由此想到另一个问题，查询 uuid 字段后如果 getObject(index) 得到的是什么类型了，由 setObject(index, value) 和 getObject(index) 这么一对反操作，你应该猜到了，getObject(index) 得到的是一个真真切切的 UUID 对象

ResultSet rs = conn.createStatement().executeQuery("select * from customers");
while (rs.next()) {
    UUID uuid = (UUID)rs.getObject(1);
}

ResultSet rs = conn.createStatement().executeQuery("select * from customers");

while (rs.next()) {

UUID uuid = (UUID)rs.getObject(1);

}

这与 SQL Server 的又一个不同之处，SQL Server 的 uniqueidentifer 字段查询出来 getObject(index) 获得的也是一个字符串。

2019-05-16

使用 JdbcTemplate 操作 PostgreSQL 的 UUID 类型字段，需要把字符串转换回 UUID 类型

postgreSQLJdbcTemplate.update("insert into customers(id, name) values(?, ?)",
   UUID.fromString("d98ffda8-77ec-11e9-8f9e-2a86e4085a59"), "Yanbin");

1 2	postgreSQLJdbcTemplate.update("insert into customers(id, name) values(?, ?)", UUID.fromString("d98ffda8-77ec-11e9-8f9e-2a86e4085a59"), "Yanbin");

不能直接用字符串形式的 UUID, 否则 JdbcTemplate 会尝试用 setString(index, uuid) 来设置参数。

本文链接 https://yanbin.blog/use-postgresql-uuid-field-data-type/, 来自隔叶黄莺 Yanbin Blog

seetimee on 体验 Python FastAPI 的并发能力及线, 进程模型感谢
Yanbin on Mockito 3.4.0 开始可 Mock 静态方法有一个补救，新写了一篇 https://yanbin.blog/mockito-mock-static-method-in-multiple...
Yanbin on 升级到 Spring Boot 3 后 javax.inject.Named 不可用怎么，被抄袭了！算是被机器翻译引用的？
oleksi on 升级到 Spring Boot 3 后 javax.inject.Named 不可用https://www.springcloud.io/post/2023-02/springboot-3-javax-inject-name...
Zhe on 体验 Python FastAPI 的并发能力及线, 进程模型你在trio下得用 trio.sleep()，调用asyncio.sleep() 就会报错。