Flame has asked for the wisdom of the Perl Monks concerning the following question:

This is just a quick question, I have a string of binary values, (yes, i mean I have a string that looks like this: '010010110101001000101001000000100101110000000010') 357 characters long that I have to store in a mysql table. Now I have several posibilities for how to store it, and several problems with most of them.

My first choice would be a varchar field because that can be fixed-width, however those have a max length of 255, leaving Text as my shortest field type available.

Another possibility was the pack command, which seems to work, shortening it down to an easy-to-store 45 characters, however that creates other complications. In order to use this method, I need to find some way of at least simulating a unpack('B*',value) from within mysql.

To summerize, is there a pack/unpack command built in to mysql that could compress and de-compress my string, or is there some other method of making that many pieces of data fit into a 255 char space?

Sorry if this isn't as clear as it could be, if I need to clarify something, feel free to say so.

Thanks!


My code doesn't have bugs, it just develops random features.

Flame ~ Lead Programmer: GMS (DOWN) | GMS (DOWN)

Replies are listed 'Best First'.
Re: MySQL pack?
by Zero_Flop (Pilgrim) on Apr 13, 2003 at 05:32 UTC
    If your string is only 1 and 0 you could simply treat it like a number and convert it to another base. Hex or base10. This would shrink your representation of the data, if you have to query the table, just convert your query string prior to running it.

    This may also make it easier to handle the data.
    You may have to split your data up into several strings, depending on the limitations of the conversion program. so as and example 00111011101010000110 => 0011101+1101010+000110 => 48863+545345+879
[reply]

      At this point MIME's base64 would be attractive as well.

[reply]

        How would base64 improve over pack 'B*'?

        Does MySQL have a mechanism for un-base64ing on the fly?

[reply]
Re: MySQL pack?
by Anonymous Monk on Apr 13, 2003 at 01:53 UTC

    If you need to be able to select rows based on arbitrary combinations of your 357 boolean conditions, then you might consider using 6 x SET type columns.

    This would allow you to select using the FIND_IN_SET() function, the LIKE operator or direct boolean math.

    http://www.mysql.com/documentation/mysql/bychapter/index.html#SET

[reply]
Re: MySQL pack?
by CountZero (Bishop) on Apr 13, 2003 at 15:23 UTC

    In MySQL 4 the length of BIGINT is 64 bit, so I don't think it is possible to store your 357 bit string into a single numeric field by using native SQL-functions.

    Also there is no equivalent to the perl pack-function.

    But you can split the input into 6 parts and convert each of them to a decimal number (as suggested by Zero_flop) and store it in six fields. That would save your 357 bit string into 48 bytes plus some general overhead.

    To retrieve the original data use the CONV(N,from_base,to_base) or BIN(N) function from MySQL and concatenate the results together.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]

Back to Seekers of Perl Wisdom