如何在Postgres中使用Blob数据类型

Question

我在我的 Rails 应用程序中使用了 Postgresql 数据库。为了将大文件或数据存储在数据库中，我在 MySql 中使用了 blob 数据类型。

在 Postgres 中，我需要使用哪种数据类型来替代 MySql 中的 blob？

- Jayashri

---

我建议先阅读这个链接：https://wiki.postgresql.org/wiki/BinaryFilesInDB - guettli

你看过https://dev59.com/DWEh5IYBdhLWcg3wjEDn#46314519?noredirect=1#comment93152073_46314519吗？ - Ravi

Answer 1

使用bytea（或者如果你非常需要的话，使用大对象）

- user533832

---

为什么你说“如果你绝对必须”的话？你认为使用大对象会有很大的缺点吗？ - avernet

@avernet - 是的，它们更难处理并且有一些安全问题。 - user533832

严格来说，bytea 不是一个 blob 类型，而是一个被编码的字符串。它会将文件在存储中的大小加倍，并且转换需要时间。 - baash05

@dave 不是的，bytea 是二进制数据 - 它肯定不会使大小翻倍 - "十六进制"格式用于输入和输出而不是存储。当然，关于客户端如何选择显示二进制数据是另一回事... - user533832

当行被获取时，bytea 数据会被加载。因此要注意内存使用。当需要通过 STDOUT 实际读取数据时，Blob 数据才会被加载。 - Akshay Rawat

我发现bytea需要额外的客户端内存进行转换，但@daveatflow关于存储的说法是错误的。与所有PostgreSQL类型一样，它在SQL接口中以文本字符串形式公开，但就像时间戳、几何图形或inet地址一样，文本表示不是内部二进制表示。Pg文档中有很多关于如何编写二进制时间的示例，这使得这一点很清楚。 - Chris Travers

Answer 2

我认为这是关于PostgreSQL二进制文件的最全面的回答，它来自PostgreSQL维基本身：https://wiki.postgresql.org/wiki/BinaryFilesInDB

请阅读标题为“在数据库中存储文件的最佳方法”部分。

- frankhommers

Answer 3

在数据库中存储文件将导致巨大的数据库大小。您可能不喜欢这样做，因为这会影响开发、测试、备份等方面。相反，您可以使用FileStream（SQL-Server）或BFILE（Oracle）。

Postgres没有BFILE / FileStream的默认实现，但您可以添加它：https://github.com/darold/external_file 更多信息（法语）可在此处获得：
http://blog.dalibo.com/2015/01/26/Extension_BFILE_pour_PostgreSQL.html

---

回答实际问题：
除了bytea，对于非常大的文件，您可以使用LOBS:

// http://stackoverflow.com/questions/14509747/inserting-large-object-into-postgresql-returns-53200-out-of-memory-error
// https://github.com/npgsql/Npgsql/wiki/User-Manual
public int InsertLargeObject()
{
    int noid;
    byte[] BinaryData = new byte[123];

    // Npgsql.NpgsqlCommand cmd ;
    // long lng = cmd.LastInsertedOID;

    using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
    {
        using (Npgsql.NpgsqlTransaction transaction = connection.BeginTransaction())
        {
            try
            {
                NpgsqlTypes.LargeObjectManager manager = new NpgsqlTypes.LargeObjectManager(connection);
                noid = manager.Create(NpgsqlTypes.LargeObjectManager.READWRITE);
                NpgsqlTypes.LargeObject lo = manager.Open(noid, NpgsqlTypes.LargeObjectManager.READWRITE);

                // lo.Write(BinaryData);
                int i = 0;
                do
                {
                    int length = 1000;
                    if (i + length > BinaryData.Length)
                        length = BinaryData.Length - i;

                    byte[] chunk = new byte[length];
                    System.Array.Copy(BinaryData, i, chunk, 0, length);
                    lo.Write(chunk, 0, length);
                    i += length;
                } while (i < BinaryData.Length);

                lo.Close();
                transaction.Commit();
            } // End Try
            catch
            {
                transaction.Rollback();
                throw;
            } // End Catch

            return noid;
        } // End Using transaction 

    } // End using connection

} // End Function InsertLargeObject 



public System.Drawing.Image GetLargeDrawing(int idOfOID)
{
    System.Drawing.Image img;

    using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
    {
        lock (connection)
        {
            if (connection.State != System.Data.ConnectionState.Open)
                connection.Open();

            using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction())
            {
                NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection);
                NpgsqlTypes.LargeObject lo = lbm.Open(takeOID(idOfOID), NpgsqlTypes.LargeObjectManager.READWRITE); //take picture oid from metod takeOID
                byte[] buffer = new byte[32768];

                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    int read;
                    while ((read = lo.Read(buffer, 0, buffer.Length)) > 0)
                    {
                        ms.Write(buffer, 0, read);
                    } // Whend

                    img = System.Drawing.Image.FromStream(ms);
                } // End Using ms

                lo.Close();
                trans.Commit();

                if (connection.State != System.Data.ConnectionState.Closed)
                    connection.Close();
            } // End Using trans

        } // End lock connection

    } // End Using connection

    return img;
} // End Function GetLargeDrawing



public void DeleteLargeObject(int noid)
{
    using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
    {
        if (connection.State != System.Data.ConnectionState.Open)
            connection.Open();

        using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction())
        {
            NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection);
            lbm.Delete(noid);

            trans.Commit();

            if (connection.State != System.Data.ConnectionState.Closed)
                connection.Close();
        } // End Using trans 

    } // End Using connection

} // End Sub DeleteLargeObject 

- Stefan Steiger

---

但这不是问题的答案（它与问题有关，但不是答案）。 - Bruno Ranschaert

@Bruno Ranschaert：没错-已经修复了。但是，得票最多的答案也不是。但实际问题不是“如何使用blob/bytea/lobs”，而是“如何在数据库中存储大文件或数据”（如问题所述）-答案是：大文件不应该放入数据库中-我指的是既不是bytea也不是blob。我的回答解决了如何做到这一点（正确地）。不需要Blob。 - Stefan Steiger