在关系型数据库中，除了 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>;
同时需注意 Oracle 并不支持对 Clob 字段的函数与分组等操作。
如下述语句在执行时将失败返回 ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB。
select
clob_column, count(1)
from
tb_test
group by
clob_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();
}
}
}
