BLOB 与 VARCHAR 用于在 MySQL 表中存储数组 [英] BLOB vs. VARCHAR for storing arrays in a MySQL table

问题描述

我要做出一个设计决定，正在寻找一些最佳实践建议.我有一个 Java 程序，它需要在 MySQL 数据库中存储大量(每天几百个)浮点数组.数据是一个长度为300的定长Double数组.我可以看到三个合理的选择:

I've got a design decision to make and am looking for some best practice advice. I have a java program which needs to store a large number (few hundred a day) of floating point arrays in a MySQL database. The data is a fixed length Double array of length 300. I can see three reasonable options:

1. 将数据存储为 BLOB.
2. 序列化数据并将其存储为 VARCHAR.
3. 将数据作为二进制文件写入磁盘并存储对其的引用.

我还应该提到，这些数据会被频繁读取和更新.

I should also mention that this data will be read from and updated frequently.

我想使用 BLOB，因为这是我过去所做的，它似乎是最有效的方法(例如，保持固定宽度且无需转换为逗号分隔的字符串).然而，我的同事坚持认为我们应该序列化并使用 varchar，原因似乎大多是教条的.

I want to use a BLOB since that is what I have done in the past and it seems like the most efficient method (e.g., maintains fixed width & no need to convert to a comma separated string). However my coworker is insisting that we should serialize and use varchar for reasons which seem mostly dogmatic.

如果这些方法中的一种比另一种更好，原因是 Java 还是 MySQL 特定的?

If one of these methods is better than the other, are the reasons Java or MySQL specific?

推荐答案

像这样存储为 BLOB(参见下面的代码示例).我认为这可能比使用 java 序列化更好，因为 java 的内置序列化将需要 2427 个字节，而非 java 应用程序将更难处理数据.也就是说，将来是否应该有任何非 Java 应用程序查询数据库......如果没有，那么内置序列化就少几行.

Store as a BLOB like so (see code example below). I think this is probably better than using java serialization since java's builtin serialization will need 2427 bytes, and non-java applications will have a harder time dealing with the data. That is, should there ever be any non-java applications querying the database in the future.... if not then the builtin serialization is a few less lines.

public static void storeInDB() throws IOException, SQLException {

    double[] dubs = new double[300];

    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    DataOutputStream dout = new DataOutputStream(bout);
    for (double d : dubs) {
        dout.writeDouble(d);
    }
    dout.close();
    byte[] asBytes = bout.toByteArray();

    PreparedStatement stmt = null;  // however we normally get this...
    stmt.setBytes(1, asBytes);

}

public static double[] readFromDB() throws IOException, SQLException {

    ResultSet rs = null;  // however we normally get this...
    while (rs.next()) {
        double[] dubs = new double[300];
        byte[] asBytes = rs.getBytes("myDoubles");
        ByteArrayInputStream bin = new ByteArrayInputStream(asBytes);
        DataInputStream din = new DataInputStream(bin);
        for (int i = 0; i < dubs.length; i++) {
            dubs[i] = din.readDouble();
        }
        return dubs;
    }

}

我希望使用 BINARY(2400)，但 MySQL 说:

I'd hoped to use BINARY(2400), but MySQL says:

mysql> create table t (a binary(2400)) ;
ERROR 1074 (42000): Column length too big for column 'a' (max = 255);
use BLOB or TEXT instead

这篇关于BLOB 与 VARCHAR 用于在 MySQL 表中存储数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！