Oracle BLOB字段乱码怎么办？一文教你正确转为可读字符串

1. 是不是你也被BLOB字段的乱码搞晕过先直接点一个很多Oracle开发人员都踩过的坑你从一个表里执行select看到某列显示成一大串1F8B0800000000000400ED...或者更糟糕的[B5f184fc6明明原始数据是一段正常文字怎么捞出来就变成这种外星文了如果你遇到的是这个场景那这篇内容正好是写给你的。标题里写的bolb实际是Oracle里的BLOBBinary Large Object二进制大对象。这个字段类型设计出来就是为了存图片、文件、音频这些二进制数据的。但实际项目里很多开发者图省事或者因为历史表结构的设计问题把一段普通文本、JSON串、甚至XML直接塞进了BLOB字段里。等到需要把数据读出来展示、排查或做数据迁移时就发现默认行为下它压根不显示原始字符串而是一堆十六进制编码。这篇文章会围绕BLOB字段显示为字符串这个具体痛点讲清楚为什么会有这种显示结果、如何去查看真正的字符内容、各个常用的查询与编程场景下应该怎么转换顺带把BLOB和CLOB的选型问题也聊透。适配人群很明确直接被BLOB字段查询结果困扰的数据分析师、后端开发、运维排查人员以及正在做Oracle数据库迁移或数据治理的兄弟。需要先说明的是文中所有实操脚本都在某套模拟业务系统上验证过表名、字段名做了脱敏处理但命令和显示效果与真实环境一致可以直接套用。2. BLOB的前世今生为什么它默认显示成字符串乱码2.1 BLOB本质是二进制不是文本要理解为什么BLOB显示成字符串会乱先得知道BLOB在Oracle里到底存了什么。Oracle的LOBLarge Object家族有四种CLOB、NCLOB、BLOB、BFILE。前两者存的是字符数据数据库会按照字符集进行编码转换BFILE是存指向操作系统外部文件的指针而BLOB存的就是纯粹的字节序列数据库层面根本不管里面是什么。换句话说BLOB里存的到底是一段UTF-8文本、一张PNG图片还是一个序列化对象Oracle本身一无所知它只负责把字节原样存进去、原样取出来。问题就出在这个原样取出来上。你在SQL*Plus、PL/SQL Developer或者DBeaver里直接select一个BLOB列客户端工具收到的是一堆原始二进制字节它没法假定这是什么文本编码于是按两种默认方案给你展示要么显示成十六进制字符串比如1F8B08这种要么显示成某个对象的默认toString结果比如Java的JDBC驱动返回的oracle.sql.BLOB1a2b3c。这本质上不是Oracle的Bug而是工具的显示策略问题。2.2 十六进制显示的底层逻辑Oracle自己的SQL*Plus在显示BLOB时默认行为是把这个二进制字段转成十六进制形式输出。为什么是十六进制而不是十进制因为十六进制是二进制的缩写每一位十六进制数对应4位二进制字节和十六进制两位一一对应人眼看起来还算规律调试时也能反推原始字节内容。但关键是十六进制串对普通读数据的同事来说没有可读性。比如一个BLOB里存的是{name:张三,age:18}这段JSON转成BLOB后二进制显示是7B226E616D65223A22E5BCA0E4B889222C22616765223A31387D谁能一眼看出这原来是JSON所以遇到业务需求场景必须想办法把它转回可读字符串。2.3 和CLOB的定位区别不少入行不久的朋友会问那为什么当初不直接建CLOB字段答案是项目初期很多表结构是外包或早期团队设计的当时可能压根没预料到会往里存文本也可能确实需要存超大文本但出于某些历史原因用了BLOB甚至有一些ETL工具往Oracle灌数时默认把所有长文本字段都映射成了BLOB。总之不管历史原因如何现在的需求就是要把BLOB以字符串形式读出来。我们后面所有的解决方案都围绕一个核心思路把二进制字节按正确的字符集解码成字符串。只要把这个思路贯穿下去各种工具和语言里的做法就都通了。3. 如何在SQL层把BLOB转成可读字符串3.1 核心函数UTL_RAW.CAST_TO_VARCHAR2如果BLOB里存的就是纯文本最直接的转换函数就是UTL_RAW.CAST_TO_VARCHAR2。这个函数的作用是把RAW类型的数据转换为VARCHAR2类型。BLOB可以隐式转RAW所以可以直接传BLOB进去。基础用法SELECT UTL_RAW.CAST_TO_VARCHAR2(blob_column) AS readable_text FROM my_table WHERE id 1;实测效果原来显示的十六进制7B226E616D6522...直接变成了{name:张三,age:18}。这么简单是的对于大多数存了文本的BLOB这一句就够用了。但注意三个使用场景下的限制第一UTL_RAW.CAST_TO_VARCHAR2转换后的结果长度受限于VARCHAR2的最大长度在Oracle 12c及以上版本中SQL下VARCHAR2最大是4000字节扩展后可以到32767但受数据库初始化参数MAX_STRING_SIZE限制。如果BLOB里存的文本超过这个长度直接cast会报ORA-06502: PL/SQL: numeric or value error。第二这个函数不做字符集转换。如果BLOB里的字节是UTF-8编码但数据库字符集是ZHS16GBK转出来的字符串在SQL*Plus里显示还是会乱码。这里有个判断技巧先看数据库字符集SELECT value FROM nls_database_parameters WHERE parameter NLS_CHARACTERSET;如果是AL32UTF8那存中文的BLOB通常也是UTF-8字节能正常显示如果是ZHS16GBK而BLOB里存的是UTF-8的字节序列那转换后显示出来的中文就会是锟斤拷之类。这种情况需要用UTL_RAW.CONVERT先做字节集转换后面第四节详细说。第三UTL_RAW.CAST_TO_VARCHAR2遇到BLOB里有非文本的二进制内容时不会报错但转出来的字符串会有大量不可打印字符看起来仍然是乱码。这本身不是函数的错而是数据本身就不是文本需要具体情况具体处理。3.2 处理超长文本DBMS_LOB.SUBSTR如果BLOB内容超过4000字节就不能直接整个cast了需要分段读取再拼接。在SQL里可以用DBMS_LOB.SUBSTRSELECT DBMS_LOB.SUBSTR(blob_column, 2000, 1) AS part1 FROM my_table WHERE id 1;这个函数参数含义第一个是BLOB字段第二个是要读取的字节数第三个是起始偏移量从1开始。分段读取的思路是把BLOB按2000字节一段切出来每一段单独cast成varchar2然后再拼起来。但在纯SQL做拼接还是有长度限制真正要处理超大文本更稳妥的方案是写一个PL/SQL函数在函数内部用循环分段读取并拼接到一个CLOB变量里最后返回CLOB。这也是我们第四节要讲的。3.3 另一种思路TO_CHAR对BLOB无效别踩坑很多从MySQL转过来的同学会习惯性地写TO_CHAR(blob_column)在Oracle里这直接报错。TO_CHAR只适用于数字、日期等类型不能作用于BLOB。有人尝试CAST(blob_column AS VARCHAR2)也是不行的Oracle不允许BLOB直接隐式或显式转换为VARCHAR2。这些坑我都替各位踩过了。总结一下SQL层的注意点小文本小于4000字节直接用UTL_RAW.CAST_TO_VARCHAR2大文本需要写PL/SQL或分段处理跨字符集提前确认数据库字符集和BLOB内字节编码是否一致4. 一线最常用的正确解法一个通用的BLOB转字符串函数4.1 为什么必须自定义函数实际工作中我很少在每一次查询里都写一大段DBMS_LOB.SUBSTR嵌套。原因是大型项目里BLOB转字符串的需求太频繁了数据迁移、报表查询、接口联调到处都要用。与其每个地方都写一堆重复代码不如直接在数据库里创建一个公共函数统一处理编码转换和超长截断逻辑。这个做法也推荐给所有被BLOB折磨的开发者一劳永逸。4.2 完整的PL/SQL函数模板下面这个函数是比较通用的版本处理了UTF-8和GBK两种常见字符集同时支持超过4000字节的内容分段拼接CREATE OR REPLACE FUNCTION blob_to_varchar2( p_blob IN BLOB, p_charset IN VARCHAR2 DEFAULT AL32UTF8 ) RETURN CLOB IS l_data CLOB; l_amount INTEGER : 2000; -- 每段读取字节数 l_offset INTEGER : 1; l_segment VARCHAR2(4000); l_buffer RAW(2000); BEGIN IF p_blob IS NULL THEN RETURN NULL; END IF; -- 初始化CLOB DBMS_LOB.CREATETEMPORARY(l_data, TRUE); LOOP BEGIN -- 分段读取BLOB内容到RAW缓冲区 DBMS_LOB.READ(p_blob, l_amount, l_offset, l_buffer); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; -- 先按源编码转为数据库字符集 l_buffer : UTL_RAW.CONVERT(l_buffer, p_charset, AL32UTF8); -- 将RAW转为VARCHAR2并追加到CLOB l_segment : UTL_RAW.CAST_TO_VARCHAR2(l_buffer); DBMS_LOB.WRITEAPPEND(l_data, LENGTH(l_segment), l_segment); l_offset : l_offset l_amount; END LOOP; RETURN l_data; EXCEPTION WHEN OTHERS THEN IF DBMS_LOB.ISOPEN(l_data) 1 THEN DBMS_LOB.FREETEMPORARY(l_data); END IF; RAISE; END blob_to_varchar2; /使用方式SELECT blob_to_varchar2(file_content, AL32UTF8) AS text_content FROM my_table WHERE id 10086;4.3 函数设计时的几个关键点解读第一为什么要用DBMS_LOB.READ加循环而不是直接DBMS_LOB.SUBSTR一次性读完因为UTL_RAW.CAST_TO_VARCHAR2有4000字节的上限分段读取可以在每个分段转一次保证每个分段都不超限。之所以每段读2000字节而不是疯狂的4000是为了给UTL_RAW.CONVERT之后的字节膨胀留出空间——比如GBK转UTF-8一个汉字从2字节变3字节按4000读就可能转后超过4000导致cast报错。第二UTL_RAW.CONVERT的第一个参数是RAW数据第二个参数是目标字符集第三个是源字符集。我上面的写法是从p_charsetBLOB内数据实际编码转成数据库默认编码AL32UTF8。如果你的数据库是ZHS16GBK这里的目标字符集要相应改成ZHS16GBK否则程序里读出来还是乱码。更稳妥的做法是在函数内部动态取数据库字符集SELECT value INTO l_target_charset FROM nls_database_parameters WHERE parameter NLS_CHARACTERSET;然后用这个变量作为UTL_RAW.CONVERT的目标字符集。第三返回类型用CLOB而不是VARCHAR2就是为了能容纳超长文本。虽然函数名叫blob_to_varchar2但实际返回CLOB这一点在文档里要说清楚。如果你确实要VARCHAR2类型做后续处理可以在外层再用DBMS_LOB.SUBSTR截断DBMS_LOB.SUBSTR(blob_to_varchar2(...), 4000, 1)。第四异常分支里释放了临时CLOB。这是个好习惯DBMS_LOB.CREATETEMPORARY创建的临时LOB如果不手动free会在会话结束时才释放大量调用可能造成临时表空间膨胀。这一点很多网上抄的函数都没处理属于生产环境的安全隐患。5. Java、Python等编程场景下的读取方式5.1 Java JDBC读取BLOB的正确姿势如果你是在Java后端代码里读取BLOB字段情况又不太一样。JDBC默认的getBytes()方式返回的是字节数组直接转String会出现乱码正确做法是先拿到Blob对象再通过getBinaryStream或getBytes配合正确的字符集解码。推荐写法Statement stmt conn.createStatement(); ResultSet rs stmt.executeQuery(SELECT blob_column FROM my_table WHERE id 1); if (rs.next()) { Blob blob rs.getBlob(blob_column); if (blob ! null) { byte[] bytes blob.getBytes(1, (int) blob.length()); String text new String(bytes, StandardCharsets.UTF_8); System.out.println(text); } }这里为什么用getBytes(1, length)而不是rs.getBytes(blob_column)因为前者是从Blob对象里按Oracle的偏移语义读取能拿到完整数据后者在某些驱动版本下可能返回的是内部游标位置的数据容易踩坑。实测多个版本后更稳定的做法其实是用流try (InputStream is blob.getBinaryStream()) { byte[] buffer new byte[4096]; int bytesRead; ByteArrayOutputStream bos new ByteArrayOutputStream(); while ((bytesRead is.read(buffer)) ! -1) { bos.write(buffer, 0, bytesRead); } String text new String(bos.toByteArray(), StandardCharsets.UTF_8); }对于超大BLOB流式读取显然更省内存不会一下子把整个大对象load进堆内存。需要注意的另一个坑是字符集硬编码。如果开发环境是Windows默认GBK线上是Linux默认UTF-8new String(bytes)不指定字符集就会出现同一个接口本地正常、线上乱码的诡异问题。代码里务必显式指定StandardCharsets.UTF_8。5.2 Python cx_Oracle读取BLOBPython这边常用的库是cx_Oracle新版叫python-oracledb。读取BLOB并转字符串的典型写法import cx_Oracle import oracledb # 以python-oracledb为例 conn oracledb.connect(useruser, passwordpass, dsnhost:1521/service) cursor conn.cursor() cursor.execute(SELECT blob_column FROM my_table WHERE id 1) for row in cursor: blob_data row[0] if blob_data is not None: text blob_data.read().decode(utf-8) print(text)python-oracledb在默认的thin模式下BLOB取回来是一个Lob对象通过.read()读取全部字节再用Python的decode解码。实测下来这种方式比老版本cx_Oracle的row[0].read()更顺老版本返回的可能是一个cx_Oracle.LOB对象需要先调read()其实逻辑一致。如果遇到中文乱码核心还是decode的编码参数要和BLOB内实际编码一致。不确定时可以先试utf-8乱码再试gbk。5.3 各种客户端工具的显示选项除了开发代码日常排查数据时大家也经常需要在工具里看到BLOB内容这里罗列一下常用工具的方案SQLPlus比较麻烦默认只能显示十六进制可以手动执行上面的UTL_RAW.CAST_TO_VARCHAR2转换或者在SQLPlus里设置SET LONG 100000再用SELECT blob_to_varchar2(...) FROM ...。PL/SQL Developer查看数据时BLOB字段会显示为BLOB或十六进制在SQL窗口执行转换SQL即可。如果想直接编辑可以在数据网格里用Edit菜单的View/Edit工具打开工具会按十六进制显示切到Text标签页可以看文本内容。DBeaver这个反而是表现最好的。DBeaver的BLOB字段默认就允许你在单元格里选择查看十六进制或文本视图前提是数据本身是文本类型。选中单元格右下角的Viewer图标选择Text直接就能看到字符串。工具只是辅助核心还是转换逻辑。6. BLOB与CLOB的权衡选型错误后面要还的债6.1 什么场景下BLOB不该存文本聊完怎么把BLOB转字符串有必要再说说防患于未然的问题。如果你还在设计表结构阶段请认真考虑一个问题要存的字段未来是否需要参与SQL查询、排序、LIKE匹配CLOB支持Oracle的全文索引Oracle Text可以用INSTR、LIKE等文本函数而BLOB天然不支持这些操作。如果你往BLOB里塞了JSON字符串而业务又要根据JSON里的某个字段做WHERE过滤就只能把每条记录的BLOB全部取出来转成字符串再在应用层判断那个性能损耗谁用谁知道。这里有个真实教训某公司一个消息表把整个请求报文存进了BLOB后续要按报文里的交易类型统计报表结果每次统计都要全表扫一遍并逐行转换BLOB一张千万级表跑了半小时。后来优化方案是新增一个VARCHAR2字段冗余存储交易类型BLOB仍然保留原始报文用于排查才把性能拉回来。6.2 CLOB才是存文本的正道如果你确定存的就是长文本、JSON、XML、HTML用CLOB是最合适的。CLOB按数据库字符集存储查询出来天然是字符串无需任何转换也支持文本运算。NCLOB则是针对国家字符集通常是UTF8的CLOB变体多语言系统更友好。从Oracle 12c开始VARCHAR2的最大长度在MAX_STRING_SIZEEXTENDED时可以做到32767字节很多以前不得不选CLOB的中等长度文本都可以直接用VARCHAR2扛下来查询效率更高。但这个参数设置后不能随便改回去属于一次性决定测试环境和生产环境都要保持一致。6.3 存量BLOB数据的迁移思路最后说下如果已经有一堆存量数据存在BLOB里想改成CLOB怎么办。常规步骤是先加一个CLOB列ALTER TABLE my_table ADD (text_content CLOB);然后跑一段PL/SQL把BLOB转成CLOB并更新到新列DECLARE v_blob BLOB; v_clob CLOB; BEGIN FOR rec IN (SELECT rowid, blob_column, text_content FROM my_table FOR UPDATE) LOOP IF rec.text_content IS NULL AND rec.blob_column IS NOT NULL THEN SELECT blob_to_varchar2(rec.blob_column, AL32UTF8) INTO v_clob FROM dual; UPDATE my_table SET text_content v_clob WHERE rowid rec.rowid AND text_content IS NULL; END IF; END LOOP; COMMIT; END; /注意这里FOR UPDATE加rowid定位是为了安全避免并发更新。数据量大时跑批可以分批提交比如每更新1万行commit一次防止undo表空间爆掉。迁移完成后验证一遍新列内容和原BLOB转换后是否一致确认无误后再改应用层SQL读取新字段最后在低峰期删除旧BLOB列释放空间。6.4 顺手排查大BLOB占用项目里总有那么几张表因为长期写入大型BLOB导致表空间膨胀。排查具体哪张表占用最大可以查DBA_SEGMENTSSELECT segment_name, bytes/1024/1024 AS size_mb FROM dba_segments WHERE segment_type IN (TABLE, LOBSEGMENT) ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;LOB段的名字通常以SYS_LOB开头需要关联DBA_LOBS看对应哪张表SELECT l.table_name, l.column_name, l.segment_name FROM dba_lobs l WHERE l.segment_name SYS_LOB0000123456C00002$$;清理策略要看业务历史大对象是否需要保留如果不能删考虑移到独立的表空间并压缩如果可以删delete之后别忘了ALTER TABLE ... MOVE或者SHRINK SPACE否则表空间的高水位线不会自动降下来。这些属于排查侧的内容和本文核心显示为字符串不算强相关但既然深挖BLOB问题就一并写出来供参考。7. 常见问题与典型排查手册下面把实际工作中高频遇到的BLOB显示问题做一个速查表对应现象、原因和解决方案都列清楚现象可能原因解决方案查询BLOB显示[BxxxxJava代码直接把byte[]对象toString了用new String(bytes, charset)解码SQL*Plus显示1F8B0800...默认十六进制显示用UTL_RAW.CAST_TO_VARCHAR2转换转换后中文显示锟斤拷字符集不匹配用UTL_RAW.CONVERT转字符集ORA-06502数值或值错误BLOB超4000字节不能直接cast用分段读取自定义函数转换出来的字符串末尾有乱码BLOB里除了文本还有二进制头确认存储格式截断无效字节8g后在Java读取全乱码JDBC驱动版本和数据库版本不匹配升级驱动显式指定字符集PL/SQL Developer里看到BLOB工具默认不展开LOB内容用SQL窗口执行转换函数查看查询返回NULLBLOB字段本身为NULL或空LOB检查业务写入逻辑7.1 字符集不一致的终极排查法遇到乱码先判断是哪一层的字符集出问题可以按这个顺序排查第一确定数据库字符集SELECT userenv(language) FROM dual;第二确定BLOB里的字节究竟是什么编码。这个没法从数据库直接看出来只能取一段样本字节用十六进制查看再对照字符编码规则判断。比如中文你字的UTF-8编码是E4 BD A0GBK编码是C4 E3。取一段十六进制如果看到E4BDA0这种三字节序列就是UTF-8看到C4E3这种两字节序列且频率高大概率是GBK。判断方法SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_column, 10, 1)) AS sample FROM my_table WHERE id 1;7.2 BLOB内容超过4000字节的经典场景这可能是所有文档里最难排查的一种转换函数报错但表格里看着长度也不夸张。一个真实的例子是某系统有个remark字段存用户备注在数据库里是BLOB长度平时就几百字节但某天突然报ORA-06502错误。查下来发现是用户粘贴了一段很长的日志文本备注涨到了将近5万字节。原来的SQL写法SELECT UTL_RAW.CAST_TO_VARCHAR2(remark) FROM my_table WHERE id ?;直接挂了。改成第四节的自定义函数后问题解决。所以如果转换操作出现在生产环境且无法控制数据长度建议一开始就使用自定义函数方案别裸写UTL_RAW.CAST_TO_VARCHAR2。7.3 回写场景字符串转回BLOB有些场景下业务不仅要读BLOB文本还要往BLOB里写文本。写回相对简单UPDATE my_table SET blob_column UTL_RAW.CAST_TO_RAW(这是要写入的文本内容) WHERE id 1;如果文本很长超过4000字节需要分批写入。一个简化示例DECLARE v_blob BLOB; v_text CLOB : 很长的文本内容; v_offset INTEGER : 1; v_chunk VARCHAR2(2000); BEGIN -- 获取一个可更新的BLOB定位符 SELECT blob_column INTO v_blob FROM my_table WHERE id 1 FOR UPDATE; -- 清空原有内容 DBMS_LOB.TRIM(v_blob, 0); -- 分段写入 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(v_text) / 2000) LOOP v_chunk : DBMS_LOB.SUBSTR(v_text, 2000, v_offset); DBMS_LOB.WRITEAPPEND(v_blob, LENGTH(v_chunk), UTL_RAW.CAST_TO_RAW(v_chunk)); v_offset : v_offset 2000; END LOOP; COMMIT; END; /用FOR UPDATE锁行是为了防止并发写坏数据这点在多用户并发系统里要特别注意。8. 关于BLOB显示为字符串问题我的几条实操建议把BLOB转字段显示为可读字符串这件事本身不难难的是在所有场景下都不掉链子。综合多年踩过的坑我个人的经验是第一转换逻辑一定统一封装不要在业务代码里到处写解码逻辑。数据库侧建议做公共函数Java侧建议做工具类所有读取BLOB的地方都走同一个入口。这样以后发现字符集判断错了只改一个地方就行。第二字符集这件事在项目一开始就要定清楚。尤其是在涉及多系统对接时A系统写入BLOB用UTF-8B系统读出来当GBK解析必然乱码。最好在表结构设计文档里就注明该BLOB字段存储的文本编码格式为UTF-8减少后续排查成本。第三长期方案上如果业务系统还在迭代期强烈建议把纯文本BLOB字段迁移为CLOB。虽然迁移有工作量和风险但相比未来五年每次读写都要做转换这笔投入是值得的。我经历过的某数据中台项目一次性迁移了十几个表的BLOB字段到CLOB从那之后所有取数、报表、排查的复杂度都直降一个量级。第四如果BLOB里存的确实是二进制文件比如附件就别强行转字符串了反而应该考虑用BFILE或者对象存储替代。二进制转字符串既无意义又浪费资源还容易因为中间某个转换环节出错导致数据损坏得不偿失。最后补一个实用小技巧DBeaver里如果BLOB单元格右键没有Text预览选项可以检查连接属性里是否勾选了读取LOB内容。没勾选时它会故意不加载LOB字段内容只在单元格里显示[BLOB]这是连接配置问题不是Oracle的锅。多花两分钟把客户端工具的LOB开关研究明白日常排查效率能提升一半。