使用 PostgreSQL 的 uuid 字段类型

calendar May 21, 2018   ---   · 2 min read · PostgreSQL  ·
Share on: linkedin copy
上一篇 使用 SQL Server 的 uniqueidentifier 字段类型 了解了 SQL Server 中如何使用 uniqueidentifier 字段类型后，现在来看下 PostgreSQL 中如何使用 uuid 字段类型。在 PostgreSQL 的字段类型是 uuid 了，所以创建一个带有 uuid 字段的表是

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

然后用 SQL 语句来向该表插入记录
1insert into customers(id, name) values('0681757b-5f92-42c2-a4cd-90976f50225f', 'World')
2
3--以下两条语句将会失败
4insert into customers(id, name) values('0681757b-5f92-42c2-a4cd-90976f50225f', 'Upper Case')  --破坏了主键约束
5insert 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，于是我们试一下
1select uuid_generate_v1();
2select uuid_generate_v4();

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

只有安装了 uuid-ossp 之后，上面的那两个函数 uuid_generate_v1() 和 uuid_generate_v4() 才能调用, 而且这时候才能在创建表时为 uuid 字段使用其中一个函数作为默认值
1CREATE TABLE customers (
2    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
3    name VARCHAR(36)
4)

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(...) 方法
 1Class.forName("org.postgresql.Driver");
 2Connection conn = DriverManager.getConnection(
 3    "jdbc:postgresql://localhost:5432/postgres", "postgres", "mysecretpassword");
 4
 5PreparedStatement pstmt = conn.prepareStatement("insert into customers(id, name) values(?, ?)");
 6
 7pstmt.setObject(1, UUID.randomUUID());
 8
 9//以下两种方式调 uuid 类型字段的值均会报错
10//pstmt.setObject(1, UUID.randomUUID().toString());
11//pstmt.setString(1, UUID.randomUUID().toString());
12
13pstmt.setString(2, "hello");
14
15pstmt.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) 更烦琐的操作
1PGobject newUUID = new PGobject();
2newUUID.setType("uuid");
3newUUID.setValue(UUID.randomUUID().toString());
4pstmt.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 对象
1ResultSet rs = conn.createStatement().executeQuery("select * from customers");
2while (rs.next()) {
3    UUID uuid = (UUID)rs.getObject(1);
4}

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


2019-05-16

使用 JdbcTemplate 操作 PostgreSQL 的 UUID 类型字段，需要把字符串转换回 UUID 类型
1postgreSQLJdbcTemplate.update("insert into customers(id, name) values(?, ?)",
2   UUID.fromString("d98ffda8-77ec-11e9-8f9e-2a86e4085a59"), "Yanbin");

不能直接用字符串形式的 UUID, 否则 JdbcTemplate 会尝试用 setString(index, uuid) 来设置参数。 永久链接 https://yanbin.blog/use-postgresql-uuid-field-data-type/, 来自 隔叶黄莺 Yanbin's Blog
[版权声明] 本文采用 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。