PG数据类型之二进制字符串（bytea）
二进制字符串
官网：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) |
其中文件的内容为
1 2 3 | # cat test.txt 2 12 |
读取
后端代码中进行读取，这里用到自己的Orm框架Neo（）
1 2 3 4 5 6 7 8 9 10 11 12 13 | @Test public void test3(){ String url="jdbc:postgresql://localhost:54321/db"; String user = "postgres"; String password = "pg.123"; Neo db = Neo.connect(url, user, password); ByteaDO result = db.one(ByteaDO.class, "demo_bytea", NeoMap.of()); // 320a show(HexUtils.toHexString(result.getBytea())); // 2 show(new String(result.getBytea())); } |
其它
bytea的十六进制格式
“十六进制”格式将二进制数据编码为每个字节2个十六进制位，最高有效位在前。整个串以序列\x开头（用以和转义格式区分）。在某些情景中，开头的反斜线可能需要通过双写来转义，详见(see 第 4.1.2.1 节)。 作为输入，十六进制位可以是大写也可以是小写，在位对之间可以有空白（但是在位对内部以及开头的\x序列中不能有空白）。十六进制格式和很多外部应用及协议相兼容，并且其转换速度要比转义格式更快，因此人们更愿意用它。
例子：
1 | SELECT '\xDEADBEEF'; |
bytea的转义格式
“转义”格式是bytea类型的传统PostgreSQL格式。它采用将二进制串表示成ASCII字符序列的方法，而将那些无法用ASCII字符表示的字节转换成特殊的转义语句。从应用的角度来看，如果将字节表示为字符有意义，那么这种表示将很方便。但是在实际中，这常常是令人困扰的，因为它使二进制串和字符串之间的区别变得模糊，并且这种特别的转义机制也有点难于处理。因此这种格式可能会在大部分新应用中避免使用。
在转义模式下输入bytea值时，某些值的字节必须\被转义，而所有的字节值都可以\被转义。通常，要转义一个字节，需要把它转换成与它的三位八进制值， 并且前导一个反斜线。反斜线本身（十进制字节值92）也可以用双写的反斜线表示。下表显示了必须被转义的字符，并给出了可以使用的替代转义序列。
bytea文字转义字节
| 十进制字节值 | 描述 | 转义输入表示 | 例子 | 十六进制表示 |
|---|---|---|---|---|
| 0 | 0字节 | '\000' | '\000'::bytea | \x00 |
| 39 | 单引号 | ''''或'\047' | ''''::bytea | \x27 |
| 92 | 反斜线 | '\\'或'\134' | '\\'::bytea | \x5c |
| 0到31和127到255 | “不可打印的”字节 | '\*xxx'*（八进制值） | '\001'::bytea | \x01 |
转义“不可打印的”字节的要求取决于区域设置。在某些实例中，你可以不理睬它们，让它们保持未转义的状态。
如上表中所示，要求单引号必须写两次的原因对任何SQL命令中的字符串常量都是一样的。 文字解析器消耗最外层的单引号，并缩减成对的单引号为一个普通数据字符。 bytea输入函数看到的只是一个单引号，它将其视为普通数据字符。 但是，bytea输入函数将反斜杠视为特殊字符，上表中显示的其他行为由该函数实现。
在某些情况下，反斜杠必须加倍，如上所示，因为通用的字符串文字解析器也会 将一对反斜杠减少为一个数据字符;请参阅第 4.1.2.1 节。
Bytea字节默认被输出为hex格式。如果你把bytea_output改为escape，“不可打印的”字节会被转换成与之等效的三位八进制值并且前置一个反斜线。大部分“可打印的”字节被输出为它们在客户端字符集中的标准表示形式，例如：
1 2 3 4 5 6 | SET bytea_output = 'escape'; SELECT 'abc \153\154\155 \052\251\124'::bytea; bytea ---------------- abc klm *\251T |
十进制值为92（反斜线）的字节在输出时被双写。详情请见下表。
bytea输出转义字节
| 十进制字节值 | 描述 | 转义的输出表示 | 例子 | 输出结果 |
|---|---|---|---|---|
| 92 | 反斜线 | \\ | '\134'::bytea | \\ |
| 0到31和127到255 | “不可打印的”字节 | \*xxx*（八进制值） | '\001'::bytea | \001 |
| 32到126 | “可打印的”字节 | 客户端字符集表示 | '\176'::bytea | ~ |
根据你使用的PostgreSQL前端，你在转义和未转义bytea串方面可能需要做额外的工作。例如，如果你的接口自动翻译换行和回车，你可能也不得不转义它们。
