数据库 Clob 类型详解

在关系型数据库中，除了 int 与 varchar 等基础数据类型外，针对大数据属性提供了 Clob 与 Blob 类型，支持几十 MB 乃至 GB 级的数据存储。

今天就让我们深入剖析 CLOB(character large object) 类型，探究如何实现超长文本存储。

一、MySQL库

1. 基本介绍

在 MySQL 数据库中，字符串内容时通常以 VARCHAR 类型存储，其最大支持为 65535 字节。

但需注意此处指字节而 VARCHAR(n) 中的 n 所指为字符，由于字符集的存在以 utf8mb4 为例，其单个字符占 4 个字节，因此其最大字符数约为 65535/4 ≈ 16383。

因此，在存储大文本时更多的采用 Clob 类型，在 MySQL 中以 TEXT 形式存在，可取范围如下：

类型	长度
TINYTEXT	255 字节
TEXT	65535 字节 (约64KB)
MEDIUMTEXT	16777215 字节 (约16MB)
LONGTEXT	4294967295 字节 (约4GB)

除了在新建表时声明外，也可通过 ALTER 关键字修改字段类型。

ALTER TABLE <table_name> MODIFY COLUMN <column_name> LONGTEXT;

同时在适用 TEXT 字段类型时需注意一点，其相应的查询等操作的索引将无效。

二、Oracle库

1. 定义声明

Oracle 库中类似的，在 11g 版本中 VARCHAR 最大长度限制为 4000，同时编码字符集的存在一个汉字占据多个字节，实际长度不足 2000。

在 Oracle 中可通过下述命令查询当前数据库的字符集。

-- 查询当前字符集
SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';

不同字符集下汉字对应的字节数参考下表：

字符集	汉字限制
AL32UTF8	一个汉字 3 字节
ZHS16GBK	一个汉字 2 字节

因此在若存储大文本内容，则需将字段类型声明为 CLOB 类型。

在插入或更新时，可由 TO_CLOB() 函数执行，但需注意此方式并不支持超过 4000 字符的数据操作。

UPDATE <table_name> SET <column_name> = TO_CLOB(content);

2. 字段修改

但与 MySQL 不同的是，Oracle 中由于底层实现不同，并不支持将 VARCHAR 类型直接修改为 CLOB 类型。因此，对于以存在的表若需要实现 CLOB 类型切换，需分为四步进行。

-- 1. 新增 CLOB 临时字段
ALTER TABLE <table_name> ADD <temp_column> CLOB;

-- 2. 将目标字段内容赋值临时字段
UPDATE <table_name> SET <temp_column> = <origin_column>;

-- 3. 删除目标字段
ALTER TABLE <table_name> DROP COLUMN <origin_column>;

-- 4. 将临时字段改名为原目标字段
ALTER TABLE <table_name> RENAME COLUMN <temp_column> TO <origin_column>;

3. 代码集成

在 JDBC 代码集成中，当字符数少于 4000 时可直接通过 setString() 方式赋值。

但若超过 4000 字符时，由于 Oracle 存在限制不支持超长的数据的直接增改，需创建 Clob 对象或通过 setCharacterStream() 方式保存，相应的代码示例如下：

public void clobDemo1() {
    String sql = "INSERT INTO TB_TEST(ID, CONTENTS) VALUES (?, ?)";
    try (
            Connection conn = ConnectionUtil.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
    ) {
        stmt.setString(1, UUID.randomUUID().toString());
        // 创建 Clob 对象
        Clob clob = conn.createClob();
        clob.setString(1, "Pretent this data is very long.");
        stmt.setClob(2, clob);
        stmt.execute();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

上述示例以 Clob 对象为例，下面让我们看下以 setCharacterStream() 流的方式提交。

public void clobDemo1() {
    String sql = "INSERT INTO TB_TEST(ID, CONTENTS) VALUES (?, ?)";
    try (
            Reader reader = new StringReader("Pretent this data is very long.");
            Connection conn = ConnectionUtil.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
    ) {
        stmt.setString(1, UUID.randomUUID().toString());
        // 以流的方式写入
        stmt.setCharacterStream(2, reader);
        stmt.execute();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

在某些不支持 CLOB 赋值的情况下，可以采用分步取巧的方式实现 CLOB 数据保存。

实现思路上分为两步，手动开启事务并针对非 CLOB 字段执行新增或修改，完成后单独针对 CLOB 字段分批进行更新。在上述提到了 Oracle 不支持超过 4000 的直接增改，因此在考虑字符集的情况下可按照 1000 进行拆分，再分批通过 || 拼接原内容执行更新。

需要注意一点，由于此方式将命令拆解多步骤，因此需要通过 setAutoCommit(false) 关闭自动提交开启事务，当全部成功之后再提交事务，若期间发生错误则需进行回滚。

public void clobDemo() {
    Connection conn = null;
    try {
        conn = ConnectionUtil.getConnection();
        // 关闭自动提交
        conn.setAutoCommit(false);

        // 先执行非 CLOB 字段新增
        String sql = "INSERT INTO TB_TEST(ID, NAME) VALUES (1, 'Alex')";
        conn.prepareStatement(sql).execute();

        int interval = 1000;
        String data = "Pretent this data is very long.";
        int strLength = data.length();
        for (int i = 0; i < strLength; i += interval) {
            // 分批拼接更新
            String updateSql = "UPDATE TB_TEST SET CLOB_FIELD = CLOB_FIELD || ? WHERE ID = ?";
            PreparedStatement stmt = conn.prepareStatement(updateSql);
            // 分配截取
            String batch = data.substring(i, Math.min(strLength, i + interval));
            stmt.setString(1, batch);
            stmt.setString(2, 1);
        }

        // 提交事务
        conn.commit();
    } catch (Exception e) {
        if (Objects.nonNull) {
            // 发生异常时回滚事务
            conn.rollback();
        }
    }
}