What are the tradeoffs in choosing a TEXT datatype as compared to a BLOB or BINARY datatype? I don't intend to index on the column or use it in a WHERE clause, it's just data in the database, that happens to be textual. If there's a performance or storage advantage to my choice of datatype though, that would be good to know... Thanks!
+4
A:
Short answer: If it's text, use the TEXT datatype.
Long answer: TEXT columns are treated as text, i.e. they have an assigned character set. If you do comparisons between TEXT values, it will use your character set's collation rather than just comparing their numeric values. BLOB columns, on the other hand, are just arrays of bytes; they have no defined character set. If you're storing unicode (or other 'wide character' encodings), you will definitely want to use TEXT, since your data will be basically opaque to the db unless you do.
They can both store the same amount of data (depending on which of the subtypes you're using, of course), but you might see a small performance gain in using BLOB for binary data since there won't be any text-related processing going on with it.
Faisal
2010-07-12 17:32:02