合 PG数据类型之二进制字符串（bytea）
👉 本文共约3483个字，系统预计阅读时间或需14分钟。
二进制字符串
官网：http://postgres.cn/docs/13/datatype-binary.html
二进制串是一个八位位组（或字节）的序列。 二进制串和字符串的区别有两个： 首先，二进制串明确允许存储零值的字节以及其它“不可打印的”字节（通常是位于十进制范围32到126之外的字节）。 字符串不允许零字节，并且也不允许那些对于数据库的选定字符集编码是非法的任何其它字节值或者字节值序列。 第二，对二进制串的操作会处理实际上的字节，而字符串的处理和取决于区域设置。 简单说，二进制字串适用于存储那些程序员认为是“裸字节”的数据，而字符串适合存储文本。
bytea类型支持两种用于输入和输出的格式：“十六进制”格式和PostgreSQL的历史的“转义”格式。在输入时这两种格式总是会被接受。输出格式则取决于配置参数bytea_output，其默认值为十六进制（注意十六进制格式是在PostgreSQL 9.0中被引入的，早期的版本和某些工具无法理解它）。
SQL标准定义了一种不同的二进制串类型， 叫做BLOB或者BINARY LARGE OBJECT。其输入格式和bytea不同，但是提供的函数和操作符大多一样。
| 名字 | 存储尺寸 | 描述 |
|---|---|---|
bytea | 1或4字节外加真正的二进制串 | 变长二进制串 |
1 | db=# create table demo_bytea(bytea bytea); |
插入文本
1 2 3 4 5 6 7 8 | db=# insert into demo_bytea values('123'); INSERT 0 1 db=# select * from demo_bytea; bytea \x313233 (1 row) |
插入转义类型
这个不是很理解，什么叫转义类型，我的理解，就是将二进制的表示为八进制或者十六进制这种字符串，为什么这么表示，主要是有一些特殊的字符比如\b\n\t，这种需要转义的字符，如果不转义，则作为字符串添加进去的话，就会有问题，所以才需要通过八进制或者十六进制的方式进行表示（因为直接用二进制太长了，虽然也是可以）。插入的时候怎么使用呢
插入八进制数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | db=# insert into demo_bytea values('\001'); INSERT 0 1 db=# insert into demo_bytea values('\002'); INSERT 0 1 db=# insert into demo_bytea values('\003'); INSERT 0 1 db=# select * from demo_bytea; bytea \x01 \x02 \x03 (3 rows) db=# insert into demo_bytea values('\001'); INSERT 0 1 db=# insert into demo_bytea values('\001\002'); INSERT 0 1 db=# insert into demo_bytea values('\001\002\032'); INSERT 0 1 db=# insert into demo_bytea values('\001\002\032\101'); INSERT 0 1 db=# insert into demo_bytea values('\001\002\032\101\021'); INSERT 0 1 db=# select * from demo_bytea; bytea \x01 \x0102 \x01021a \x01021a41 \x01021a4111 (5 rows) |
插入十六进制数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | db=# insert into demo_bytea values(E'\x01'); INSERT 0 1 db=# insert into demo_bytea values(E'\x02'); INSERT 0 1 db=# insert into demo_bytea values(E'\x03'); INSERT 0 1 db=# insert into demo_bytea values(E'\x04'); INSERT 0 1 db=# insert into demo_bytea values(E'\x05'); INSERT 0 1 db=# select * from demo_bytea; bytea \x01 \x02 \x03 \x04 \x05 |
base64编码
通过base64编码将数据插入进去
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | db=# select encode('你好', 'base64'); encode 5L2g5aW9 (1 row) db=# insert into demo_bytea values(decode('5L2g5aW9', 'base64')); INSERT 0 1 db=# select * from demo_bytea; bytea \xe4bda0e5a5bd (1 row) |
本地路径
将本地的文件插入到pg数据库中，通过函数
文本方式读入
1 2 3 4 5 6 7 8 | db=# insert into demo_bytea values(pg_read_file('/var/file/test.txt')::bytea); INSERT 0 1 db=# select * from demo_bytea; bytea -------- \x320a (1 row) |
不转换的话会失败
1 2 3 4 5 6 7 | db=# insert into demo_bytea values(pg_read_file('/var/file/test.txt')) db-# ; ERROR: column "bytea" is of type bytea but expression is of type text LINE 1: insert into demo_bytea values(pg_read_file('/var/file/test.t... ^ HINT: You will need to rewrite or cast the expression. |
二进制文本读入
1 2 3 4 5 6 7 8 9 10 | db=# create table demo_time(time time); CREATE TABLE db=# insert into demo_bytea values(pg_read_binary_file('/var/file/test.txt')); INSERT 0 1 db=# select * from demo_bytea; bytea -------- \x320a (1 row) |
其中文件的内容为
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
