mysql type column for very long data?

what's the right type to use for a mysql column in case of very long text? i'm actually using a column to store a serialized array that is very big what开发者_JAVA百科 should i use? LONGBLOB?

I doubt you're storing 4 GB of text. The mediumtext (16 MB) or text (64 KB) types should be sufficient.

Sounds like you've already answered your question; LONGBLOB can hold 4GB; although you won't be able to fully index on this field read the link below to see the syntax on specifying length of BLOB data types. Also note, that I believe the max index length is somewhere around 1000 bytes.

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Use varchar if you can, possibly binary. Avoid the text and blob types unless you have no choice.

If you use a blob or text (as opposed to car or varchar) type you block the use of in-memory temporary tables for sorting results.

However you do it, avoid putting the bulk data in a table with columns that you access frequently, since you don't want to fill the caches with bulk data except for cases when you really need that data. Best to put it in a dedicated table and join with it only when required. Or do it as two queries, one to get the keys of the rows you need, the other to retrieve them.

This is just my opinion. Contact an Oracle public relations person for the official view of the company.

James Day, MySQL Senior Principal Support Engineer, Oracle