Sqlite:如何为 BLOB 转换(数据为文本)

2021-12-31 00:00:00 casting sqlite

我有一个 sqlite 数据库，我想从中提取数据类型为 BLOB 的信息列.我正在尝试这个:

I have a sqlite database from which I want to extract a column of information with the datatype BLOB. I am trying this:

SELECT cast(data as TEXT) FROM content

这显然行不通.输出是这样的乱码:

This is obviously not working. The output is garbled text like this:

x Uak 0? > 8 0Ff;I . .i׮% A s M

内容列中的数据主要是文本，但也可能有图像(我认识到如果我将其转换为文本可能会导致问题).我只是想将该数据提取为可用格式.有什么想法吗?

The data in the content column is mostly text, but may also have images (which I recognized could cause a problem if I cast as TEXT). I simply want to extract that data into a usable format. Any ideas?

推荐答案

可以使用

SELECT hex(data) FROM content

或

SELECT quote(data) FROM content

第一个将返回一个十六进制字符串(ABCD)，第二个将作为 SQL 文字引用(X'ABCD').

The first will return a hex string (ABCD), the second quoted as an SQL literal (X'ABCD').

请注意，(目前)无法将十六进制列信息转换回 SQLite 中的 BLOB.您将不得不使用 C/Perl/Python/... 绑定来转换和导入它们.

Note that there's (currently) no way of converting hexadecimal column information back to a BLOB in SQLite. You will have to use C/Perl/Python/… bindings to convert and import those.

相关文章