postgresqlbytea查询的简单介绍

关于postgrsql读取数据库的问题！

PostgreSQL中的bytea字段类型可以以二进制的形式存储数据，这样做的好处就是可以将原本存储在网站目录下的文件存储到数据库中，坏处就是如果文件过多、过大的话，就会导致数据库的数据量大大增加，备份和恢复的时候就会浪费大量的时间，而且数据也有可能会出错。个人觉得，在文件量小的情况下，使用这种存储方式还是很方便的。

言归正传，下面介绍一下使用bytea字段存读取文件的具体实现方法。首先是文件存储于存储于bytea字段的方法，主要用到的就是PHP中的pg_escape_bytea方法，代码如下：

?php

$dbconn = pg_connect("host='localhost' dbname='dbname' user='user' password='password' port='port'")

OR DIE('Could not connect:' . pg_last_error());

$fileUrl = iconv("utf-8", "gbk", $fileUrl);//$fileName为文件路径地址，汉字可能会乱码，此处处理一下

$fileContents = file_get_contents($fileUrl);

$escapeBytea = pg_escape_bytea($fileContents);//转义bytea数据类型的二进制字符串

$insertStr = "INSERT INTO tableName(id, contents)

VALUES(DEFAULT, '{$escapeBytea}')";

pg_query($dbconn, $insertStr);

?

　从bytea字段中还原文件用到的是PHP中的pg_unescape_bytea方法，实现代码如下：

?php

$dbconn = pg_connect("host='localhost' dbname='dbname' user='user' password='password' port='port'")

OR DIE('Could not connect: ' . pg_last_error());

$selectStr = "SELECT contents

FROM tableName

WHERE id = " . $fileId;//$fileId为文件id

$query = pg_query($dbconn, $selectStr);

while($row = pg_fetch_array($query, null, PGSQL_ASSOC)){

$escapeBytea = $row['contents'];

$fileContents = pg_unescape_bytea($escapeBytea); //获得二进制数据

file_put_contents($fileName, $fileContents); //$fileName为带有后缀名的文件名，如hello.pdf

}

?

导出文件后，如果二进制数据转码错误就会出现文件打不开的现象，比如错误的PDF文件打开时弹出错误如下图：

这种错误在数据库迁移时特别容易出现（本人是从PostgreSQL 8.4迁移到9.1），解决的方法是修改PostgreSQL的配置文件

postgresql.conf，将bytea_output的输出类型设置为转义类型（escape）输出，即bytea_output = 'escape'（如果前面有#，删除开启配置），然后reload一下PostgreSQL的配置使修改生效，这样二进制数据就可以正常解码并输出到文件。

相关问答

Q1: 如何向postgreSQL中添加bytea类型的大对象数据

如果想要在PostgreSQL中存储二进制数据，例如存储Word、Excel文档，图片文件等，可以使用bytea类型的列。bytea类型是PostgreSQL特有的存储二进制数据的字段类型，与SQL标准中的BLOB和BINARY

LARGE OBJECT类型异曲同工。这在PostgreSQL文档的bytea类型介绍中有所说明。

接下来先说说如何向表中插入、更新bytea数据。

PostgreSQL允许在SQL命令中包含bytea类型的数据，以便能够使用INSERT向表中插入含有二进制数据的记录，使用UPDATE和调用与bytea类型相关的函数更新和操作bytea类型数据。二进制数据是一个字节序列，然而SQL命令是文本字符串，怎样在SQL中写入二进制数据呢？答案很简单，把每一个字节转换成对应的三位十进制数字的八进制数字符串表示，以双斜线做为前缀，即0x00表示为\\000、0x2C表示为\\02C、0xFF表示为\\377，并按照bytea类型的要求在字符串前端的单引号外注明E。举例如下：

INSERT INTO table1 (fileid, filename, content) VALUES (1, 'filename.doc', E'\\000\\001\\002');

INSERT INTO table1 (fileid, filename, content) VALUES (2, 'anotherfile.jpg', E'\\000\\377');

UPDATE table1 SET content = E'\\000\\000\\000' WHERE fileid

= 1;

UPDATE table1 SET content = content || E'\\377\\377\\377' WHERE fileid

= 2;

可以在INSERT

INTO中包含整个文件的bytea类型字符串，也可以像上面第四行那样，分块追加。对于短小的二进制数据，在命令控制台中编辑SQL命令也未尝不可。但是如果要存储一个图片文件或者Word文档之类的大型二进制数据的时候，就需要借助数据访问借口，或者自己写一个字节转换程序，直接操作SQL语句。

插入bytea数据后可以使用SELECT语句获取它。如下所示：

SELECT content FROM table1;

在命令控制台中，我们会看到以输入时的字符串格式输出二进制数据，这是PostgreSQL做的转换。在Python中使用psycopg2模块，执行上述SELECT语句后能够获得原始的二进制字节字符串，可以直接写入二进制文件。

顺便说明一下。对于字节的转换，PostgreSQL的文档说的非常详细，按照零字节、单引号、斜线，以及字符的可打印性分别作了讨论。原因是需要逃逸单引号和斜线字符，另外可打印字符可以不作转换，直接出现。

Q2: postgresql插入bytea类型数据，sql语句

PostgreSQL的二进制数据类型为bytea，可最多保存2G的数据。在ADO、ODBC等接口，可通过带参数化的插入SQL语句上传二进制。

然而在某些接口、SQL语句无法进行参数绑定，或者某些语言没有二进制的类型，或者接口的不兼容等原因，常无法上传二进制数据。

碰到这种情况下，必须在客户端将二进制转化为一定规则的字符序列，然后插入语句时，来个显示转换cast('字符序列' as bytea).

二进制转化为字符序列的规则：

分析每个字节，当其ASCII值在0到31，127到255，或为39、92，请将这字节转化为3位的八进制字符串，前面加上2个反斜杠，如chr(39)表示为//047. 其他ascii值为可显示字符，不变，当然你也可全部转化为八进制，但这样整个字符串太长，浪费空间。

如二进制序列 chr(0)+'a'+'b'+chr(233)+chr(25)+'c'转化为字符序列为：

//000ab//351//031c

上传的时候再来个显示转换，如 insert into table1 (aa) values (cast('//000ab//351//031c' as bytea))

如果二进制数据太大，可分次上传,用||连接符连接起来,如:

insert into table1 (id,aa) values (1,cast('序列1' as bytea))

update table1 set aa=aa||cast('序列2' as bytea) where id=1

Q3: postgresql数据库怎么查询所有的序列名

1、在查询的时候一般使用*查询代表查询所有字段信息，但表太大就不建议使用这种方式。

2、在查询的时候也可以指定字段进行查询，如下图只查询表中的一部分字段信息。

3、不过最经常使用的查询是指定特殊的条件来进行查询，以便查询结果更为精确。

4、在查询的时候有时也需要对查询结果进行适当的排序，这样可以快速定位要查询数据的结果。

5、在查询的时候也可以指定特殊的查询范围，根据指定的查询范围来查询出特定结果。

postgresqlbytea查询的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于、postgresqlbytea查询的信息别忘了在本站进行查找喔。