Home >Backend Development >PHP Tutorial >Using PostgreSQL's bytea field to store and read files and handle reading errors_PHP tutorial
Using PostgreSQL's bytea field to store and read files and handle reading errors_PHP tutorial
- WBOYOriginal
- 2016-07-20 11:13:571381browse
The bytea field type in PostgreSQL can store data in binary form. The advantage of this is that files originally stored in the website directory can be stored in the database. The disadvantage is that if there are too many or too large files , will cause the database data volume to greatly increase, a lot of time will be wasted during backup and recovery, and data may also be wrong. Personally, I think it is very convenient to use this storage method when the file size is small.
Returning to the subject, let’s introduce the specific implementation method of using bytea field to store and read files. The first is the method of storing files in the bytea field. The main method used is the pg_escape_bytea method in PHP. The code is as follows:
<?<span php
</span><span $dbconn</span> = pg_connect("host='localhost' dbname='dbname' user='user' password='password' port='port'"<span )
OR </span><span DIE</span>('Could not connect:' .<span pg_last_error());
</span><span $fileUrl</span> = <span iconv</span>("utf-8", "gbk", <span $fileUrl</span>);<span //</span><span $fileName为文件路径地址，汉字可能会乱码，此处处理一下</span>
<span $fileContents</span> = <span file_get_contents</span>(<span $fileUrl</span><span );
</span><span $escapeBytea</span> = pg_escape_bytea(<span $fileContents</span>);<span //</span><span 转义bytea数据类型的二进制字符串</span>
<span $insertStr</span> = "<span INSERT INTO tableName(id, contents)
VALUES(DEFAULT, '{</span><span $escapeBytea</span>}')"<span ;
pg_query(</span><span $dbconn</span>, <span $insertStr</span><span );
</span>?>
The pg_unescape_bytea method in PHP is used to restore files from the bytea field. The implementation code is as follows:
<?<span php
</span><span $dbconn</span> = pg_connect("host='localhost' dbname='dbname' user='user' password='password' port='port'"<span )
OR </span><span DIE</span>('Could not connect: ' .<span pg_last_error());
</span><span $selectStr</span> = "<span SELECT contents
FROM tableName
WHERE id = </span>" . <span $fileId</span>;<span //</span><span $fileId为文件id</span>
<span $query</span> = pg_query(<span $dbconn</span>, <span $selectStr</span><span );
</span><span while</span>(<span $row</span> = pg_fetch_array(<span $query</span>, <span null</span>,<span PGSQL_ASSOC)){
</span><span $escapeBytea</span> = <span $row</span>['contents'<span ];
</span><span $fileContents</span> = pg_unescape_bytea(<span $escapeBytea</span>); <span //</span><span 获得二进制数据</span>
<span file_put_contents</span>(<span $fileName</span>, <span $fileContents</span>); <span //</span><span $fileName为带有后缀名的文件名，如hello.pdf</span>
<span }
</span>?>
After exporting the file, if the binary data is transcoded incorrectly, the file will not be opened. For example, when an incorrect PDF file is opened, an error will pop up as shown below:
This kind of error is especially likely to occur during database migration (I migrated from PostgreSQL 8.4 to 9.1). The solution is to modify the PostgreSQL configuration file
postgresql.conf and set the output type of bytea_output to the escape type. (escape) output, that is, bytea_output = 'escape' (if there is a # in front, delete and enable the configuration), and then reload the PostgreSQL configuration to make the modification effective, so that the binary data can be decoded and output to the file normally.
Blog Statement:
Except for the word "reprinted" in the title, all articles in this blog are original or summarized after consulting the information. Please indicate this statement when quoting non-reprinted articles. —— Blog Garden-pallee