Field/Data Type to Store Articles in MySQL Database

I can't help but believe this topic has been written about over and over again but I'm having trouble finding any good, solid information.

What data type should I use to store 200 to 400 words of text? What about longer articles that could approach two or three thousand words?

What options should affect my decision? I don't plan to search this data but I can't completely rule out the possibility that I may want to do that later.

Unfortunately my background is MS Access where the only option for this was a memo field. It doesn't appear to be quite so开发者_开发知识库 simple with MySQL.

---

If you're using MySQL 5.0.3 or later, go VARCHAR. It can hold 65k bytes. As long as you have only 1 long VARCHAR per row, you should be fine.

Otherwise go with text.

From the mysql manual:

BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:

There is no trailing-space removal for BLOB and TEXT columns when values are stored or retrieved. Before MySQL 5.0.3, this differs from VARBINARY and VARCHAR, for which trailing spaces are removed when values are stored.

On comparisons, TEXT is space extended to fit the compared object, exactly like CHAR and VARCHAR.

For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 7.5.1, “Column Indexes”.

BLOB and TEXT columns cannot have DEFAULT values.

Also nice to know (from the manual):

Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types

which you really should take into account when formulating queries which use TEXT.

---

A TEXT field should be big enough to store most articles. Seems to be about equivalent to Access's Memo type. It can hold up to 65535 chars, which would be somewhere around...i dunno...10-12,000 words, on average?

---

The TEXT data type is a safe bet for your situation, VARCHARs are usually used when they need to be indexed or there is a well-defined value to be stored (IP address, zip code, etc).