DBI, BLOBS, MySQL
ti at lemonia.org
Sat Jan 10 13:26:36 GMT 2009
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.
> "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.
(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).
More information about the london.pm