List:General Discussion« Previous MessageNext Message »
From:Jay J Date:August 2 1999 6:38am
Subject:store array of integers using a BLOB?
View as plain text  
Hi!

I know the list gets it's fair amount of storing 'binary' questions, but
this _is different_, sort of.

In effect, I want to store a huge array of binary-coded integers in a BLOB
type field. So I could store 0 .. 4294967295 in 4 bytes (plus mysql L+4
bytes for the BLOB column), and avoid storing useless commas.

As opposed to storing "1234567890, ...., 4294967295" as ascii in a TEXT type
field. [No, using seperate rows with an INT column isn't an option as it
would result in millions of rows.]

I've tried every conceivable column type and (I think) every possible
permutation of pack/unpack, as well as using LOAD DATA INFILE to insert a
4-byte packed value from a 'text' file. Yet, upon inserting (see below)
MySQL seems to magically expand it from 4 bytes to 16 (+4 per L+4 in a BLOB
column). I'm lost. If 'abcd' is 4 ascii char's times 2 bytes, that's 8 ..
plus 4 = 12, right? Why 20? I'm lost.

My problem seems to be in how I'm packing the value, as $dbh->quote works
for inserting a .gif ..

Many many thanks,

-Jay Jarvinen

p.s. Due to my limited C knowledge, I was unable to decipher a possible
answer from the list:
http://www.progressive-comp.com/Lists/?l=mysql&m=89074057525005&w=2


#/usr/bin/perl -w
# ... make DBI connection

my $bigint = 4294967295;
print "bigint         : $bigint \n";        # 4294967295

$bigint = pack("N", $bigint);
print "4-byte         : $bigint \n\n";    # ÿÿÿÿ

open (FH, ">bin_num.txt"); print FH $bigint;close FH;

my $tmp = $bigint;
$tmp = unpack("B32", $tmp);
print "bits           : $tmp \n";    # 11111111111111111111111111111111

$tmp = unpack("N", pack("B32", $tmp));
print "back to bigint : $tmp \n";    # 4294967295


my $sql = q!INSERT INTO foo (mynum) VALUES (! . $dbh->quote($bigint) . q!)!;
$dbh->do($sql) ? print "OK\n\n" : print "No dice.\n";

my $sth = $dbh->prepare(q!SELECT mynum FROM foo!); $sth->execute or die
"oops : $dbh->errstr\n";
while (my $row = $sth->fetchrow_hashref) {
    print "selected : ", $row->{mynum}, "\n";    # ÿÿÿÿ

    my $num = unpack("B32",$row->{mynum});
    print "selected : $num\n";    # 11111111111111111111111111111111

    $num = unpack("N", pack("B32", $num));
    print "selected : $num\n";    # 4294967295
}

$dbh->disconnect;

__END__

mysql> describe foo;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| mynum | longblob | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

-rw-rw----   1 mysql    mysql          20 Aug  1 12:14
/home/mysql/data/test/foo.ISD
-rw-rw-r--   1 root     root            4 Aug  1 12:14 bin_num.txt




# for google: blob, text, binary, integer, integers, encode, coded, code,
mysql, pack, unpack, byte, convert, expand, expands, perl, voodoo
conversions

Thread
store array of integers using a BLOB?Jay J2 Aug
  • Re: store array of integers using a BLOB?Martin Ramsch2 Aug
  • Re: store array of integers using a BLOB? [success]Jay J2 Aug
  • Re: store array of integers using a BLOB? .. correctionJay J2 Aug
  • store array of integers using a BLOB?Michael Widenius3 Aug