DBI, BLOBS, MySQL

[Tim Sweetman]

On 9 Jan 2009, at 09:00, Richard Huxton wrote:

> Paul Makepeace wrote:
>> What does 'show table files;' give you? You might have the wrong type
>> of blob/text.
>>
>> http://dev.mysql.com/doc/refman/5.0/en/blob.html
>
> "If a TEXT column is indexed, index entry comparisons are space-padded
> at the end. This means that, if the index requires unique values,
> duplicate-key errors will occur for values that differ only in the
> number of trailing spaces. For example, if a table contains 'a', an
> attempt to store 'a ' causes a duplicate-key error"
>
> Genius. True genius.

Isn't this generally true for VARCHAR fields? Certainly MySQL does  
that, and so does Informix, and I think Oracle does, too. If you're  
storing human-readable strings, storing trailing spaces tends to be  
more of a cause of bugs than a feature (though, if you're thinking  
along those lines, you'd probably want out-of-the-box indexed  
matching that neglects punctuation, or at the very least, which is  
case insensitive, which MySQL, unusually, does by default).

Or sorting that suits humans[1].

(More concerningly, I seem to recall that if you tell MySQL to index  
a TEXT field, the portion it indexes is only the first ~255 bytes).

Moreover, BLOBs in databases appear to be something that was the  
feature du jour in about 199x, and all the database vendors promptly  
built in some sort of BLOB support. However, hardly any of them work  
very well, and the general advice about using blobs seems to be  
"don't", for most cases, or at least "use with extreme caution". I've  
seen them used to store various serialised data structures, which  
works no better than the serialisation mechanism does, with the added  
risk of hitting MySQL's hard limit on size (when it silently  
truncates for you).

tim

[1] http://www.codinghorror.com/blog/archives/001018.html