如何将存储在sqlite数据库中的文件作为blob转储? [英] How to dump a file stored in a sqlite database as a blob?

问题描述

我有一个sqlite3数据库.一列具有TEXT类型，并且包含我想另存为文件的blob.这些是压缩文件.

I have a sqlite3 database. One column has the TEXT type, and contains blobs which I would like to save as file. Those are gzipped files.

命令sqlite3 db.sqlite3 ".dump"的输出为:

INSERT INTO "data" VALUES(1,'objects','object0.gz',X'1F8B080000000000000 [.. a few thousands of hexadecimal characters ..] F3F5EF')

如何使用命令行将sqlite文件中的二进制数据提取到文件中?

How may I extract the binary data from the sqlite file to a file using the command line ?

推荐答案

sqlite3无法直接输出二进制数据，因此您必须将数据转换为十六进制转储，使用cut从blob文字中提取十六进制数字，并使用xxd(vim软件包的一部分)将hexdump转换回二进制文件:

sqlite3 cannot output binary data directly, so you have to convert the data to a hexdump, use cut to extract the hex digits from the blob literal, and use xxd (part of the vim package) to convert the hexdump back into binary:

sqlite3 my.db "SELECT quote(MyBlob) FROM MyTable WHERE id = 1;"  \
| cut -d\' -f2                                                   \
| xxd -r -p                                                      \
> object0.gz

在SQLite 3.8.6或更高版本中，命令行外壳包含fileio扩展名，该扩展名实现

With SQLite 3.8.6 or later, the command-line shell includes the fileio extension, which implements the writefile function:

sqlite3 my.db "SELECT writefile('object0.gz', MyBlob) FROM MyTable WHERE id = 1"

这篇关于如何将存储在sqlite数据库中的文件作为blob转储?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！