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
