BLOB与VARCHAR在一个MySQL表中存储阵列 [英] BLOB vs. VARCHAR for storing arrays in a MySQL table

问题描述

我有一个设计决策做出和我寻找一些最佳实践的建议。我有需要存储在MySQL数据库中的浮点阵列的大量（几百天）的Java程序。这些数据是固定长度双击长度300阵列，我可以看到三个合理的方案：

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像这样（见下文code为例）。我想，这大概比使用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;
    }

}

编辑：我会希望使用二进制（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屋！