二进制数据类型是PostgreSQL的另一个有用的数据类型，用于存储二进制字符串数据。字节或八位数的序列被存储在二进制字符串中。零值八位数和不可打印的八位数可以存储在二进制数据类型的字段中。原始字节是由二进制字符串存储的。二进制字符串的输入值可以采用"十六进制 "或 "转义 "格式，输出的格式取决于配置参数bytea_output。默认的输出格式是'十六进制'。BLOB或BINARY LARGE OBJECT被SQL标准定义为二进制字符串类型。不同的格式和二进制数据类型在PostgreSQL中的用途已在本教程中展示。
前提条件
在执行本教程中所示的SQL语句之前，你必须在Linux操作系统上安装最新版本的PostgreSQL软件包。运行下面的命令来安装和启动PostgreSQL。
$ sudo apt-get -y install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service
运行下面的命令，以root权限登录到PostgreSQL。
$ sudo -u postgres psql
Bytea Hex格式
二进制数据以十六进制格式，每个字节有两个十六进制数字编码。二进制字符串的前面有一个序列，即\x。十六进制数字可以是大写或小写。这种格式被广泛的外部应用所支持。
例子
# SELECT E'\\xABC0110' AS hex_format;
Bytea转义格式
转义格式是传统的PostgreSQL格式。一个ASCII字符序列被用来表示转义格式的二进制数据。二进制字符串被转换为一个三位数的八进制值，前面有两个反斜线。
Bytea 字母转义的八位数
| 十进制值 | 说明 | 转义的输入 | 例子 | 输出 |
|---|---|---|---|---|
| 0 | 零八位数 | E'\\000′ | SELECT E'\000′::bytea; | \x00 |
| 45 | 连字符 | '-'或E'\055'。 | SELECT E'\-'::bytea; | \x2d |
| 110 | 'n' | 'n' or E'\156′\ | SELECT E'\n'::bytea; | \x6e |
| 0到31和127到255 | Non-printable octets | E'\\xxx'(八进制值) | SELECT E'\001′::bytea; | \x01 |
Bytea输出转义的八位数
| 十进制值 | 说明 | 逃脱的输出 | 例子 | 输出 |
|---|---|---|---|---|
| 45 | 连字符 | - | SELECT E'\\055′::bytea; | - |
| 32至126 | 可打印的八位数 | 任何可打印的字符 | SELECT E'\156′::bytea; | n |
| 0到31和127到255 | 不可打印的八位数 | \XXX(八进制值) | SELECT E'\001′::bytea; | \001 |
在PostgreSQL中使用二进制数据类型
在创建任何具有布尔数据类型的表之前，你必须先创建一个PostgreSQL数据库。因此，运行下面的命令来创建一个名为 "testdb"的数据库。
# CREATE DATABASE testdb;
创建数据库后会出现下面的输出。
例1：创建一个二进制数据类型的表来存储八进制值
在当前数据库中创建一个名为 "tbl_binary_1"的表，有两个字段。第一个字段名是id，是表的主键。当有新的记录插入时，这个字段的值将会自动增加。第二个字段名是 binary_data，数据类型是BYTEA。
# CREATE TABLE tbl_binary_1 (
Id SERIAL PRIMARY KEY,
binary_data BYTEA);
执行上述查询后会出现以下输出。
运行下面的INSERT查询，将在tbl_binary_1表中插入两个八进制的值。
# INSERT INTO tbl_binary_1 (binary_data)
VALUES
(E'\\055'),
(E'\\156');
执行上述查询后会出现以下输出。
运行以下SELECT查询，将从 tbl_binary_1表中读取所有记录。
# SELECT * FROM tbl_binary_1;
执行上述查询后会出现以下输出。输出显示的是八进制值的十六进制值。
例2：创建一个二进制数据类型的表来存储图像数据
在当前数据库中创建一个名为'tbl_binary_2'的表，有三个字段。第一个字段名是id，它是表的主键，当插入一条新记录时，这个字段的值将自动增加。第二个字段名是image_name，数据类型是VARCHAR（20）。图像名称将被存储在这个字段中。第三个字段名是image_data，该字段的数据类型是BYTEA。图像数据将被存储在这个字段中。
# CREATE TABLE tbl_binary_2 (
Id SERIAL PRIMARY KEY,
image_name VARCHAR(20),
image_data BYTEA);
执行上述查询后会出现以下输出。
使用PHP在表中插入一个图像
创建一个名为insert_image.php的PHP文件，其代码如下，将读取图像文件的内容。然后，将图像转换为二进制数据后存储在PostgreSQL的表中。
<?php
//Display error setting
ini_set('display_errors', 1);
error_reporting(E_ALL);
$host = "localhost";
$user = "postgres";
$pass = "12345";
$db = "testdb";
//Create database connection object
$db_connection = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
$filename = "flower.png";
$image = fopen($filename, 'r') or die("Unable to open the file.");
$data = fread($image, filesize($filename));
$cdata = pg_escape_bytea($data);
fclose($image);
//Insert the image data
$query = "INSERT INTO tbl_binary_2(image_name, image_data) Values('$filename', '$cdata')";
$result = pg_query($db_connection, $query);
if($result) echo "Image data is inserted successfully.";
pg_close($db_connection);
?>
在本地服务器上执行上述脚本，以及在当前位置存在的图像文件后，将出现以下输出。
使用PHP从表中读取图像数据
创建一个名为get_image.php的PHP文件，其代码如下，将读取图像文件的二进制数据。根据二进制数据创建图像，并在浏览器中显示该图像。
<?php
//Display error setting
ini_set('display_errors', 1);
error_reporting(E_ALL);
$host = "localhost";
$user = "postgres";
$pass = "12345";
$db = "testdb";
//Create database connection object
$db_connection = pg_connect("host=$host dbname=$db user=$user password=$pass")
or die ("Could not connect to server\n");
//Read the image data from the table
$query = "SELECT image_data FROM tbl_binary_2 WHERE id=1";
$result = pg_query($db_connection, $query) or die (pg_last_error($db_connection));
$data = pg_fetch_result($result, 'image_data');
$cimage = pg_unescape_bytea($data);
//Create an image file with the image data retrieved from the table
$filename = "myfile.jpg";
$image = fopen($filename, 'wb') or die("Unable to open image.");
fwrite($image, $cimage) or die("Unable to write data.");
fclose($image);
pg_close($db_connection);
//Display the image in the browser
echo "<img src='".$filename."' height=200 width=300 />";
?>
从本地服务器上执行上述脚本后，将出现从图像数据中生成的图像。
总结
本教程已经展示了在PostgreSQL中使用二进制数据类型的目的和二进制数据的不同用途，这将有助于PostgreSQL新用户使用二进制数据类型。
