List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 2 1999 9:54pm
Subject:store array of integers using a BLOB?
View as plain text  
>>>>> "Jay" == Jay J <3pound@stripped> writes:

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

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

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

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


Jay> +-------+----------+------+-----+---------+-------+
Jay> | Field | Type     | Null | Key | Default | Extra |
Jay> +-------+----------+------+-----+---------+-------+
Jay> | mynum | longblob | YES  |     | NULL    |       |
Jay> +-------+----------+------+-----+---------+-------+

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


ISAM needs some information to know how long each row is, how long
each blob is, how much empty bytes there are after the data and if the
row is linked to some other place in the data file (for example if you
expand a blob).  The ISAM library also needs place to hold a delete
link if you delete the row.

To solve the above, ISAM needs a header of 3-11 bytes before each row.
(The long header is needed if the row is updated later on).  For a
longblob the ISAM also stores 4 byte before each blob for the length

To avoid fragmentation (at least a bit), no block will be less than 20

Hope this answers yours questions.

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