text or varchar?

I have 2 columns containing text one will be max 150 chars long and the other max 700 chars long,

My question is, should I use for both varchar types开发者_开发知识库 or should I use text for the 700 chars long column ? why ?

Thanks,

The varchar data type in MySQL < 5.0.3 cannot hold data longer than 255 characters. While in MySQL >= 5.0.3 it has a maximum of 65,535 characters.

So, it depends on the platform you're targeting, and your deployability requirements. If you want to be sure that it will work on MySQL versions less than 5.0.3, go with a text type data field for your longer column

• http://dev.mysql.com/doc/refman/5.0/en/char.html

An important consideration is that with varchar the database stores the data directly in the table, and with text the database stores a pointer to a separate tablespace in which the data is stored. So, unless you run into the limit of a row length (64K in MySQL, 4-32K in DB2, 8K in SQL Server 2000) I would normally use varchar.

Not sure about mysql specifically, but in MS SQL you definitely should use a VARCHAR for anything under 8000 characters long, if you want to be able to run any sort of comparison on the value in the field. For example this would be possible with a VARCHAR:

select your_column from your_table 
where your_column like '%dogs%'

but not with a TEXT field.

More information regarding TEXT field in mysql 5.4 can be found here and more information about the VARCHAR field can be found here.

I'd pick the option based on the type of data being entered. For example, if it's a (potentially) super long username and a biography, I'd do varchar and text. Sure, you're limiting the bio to 700 chars, but what if you add HTML formatting down the road, keeping the 700 char limit but allowing HTML tags for formatting?

Twitter may use text for their tweets, since it could be quicker to add meta data to the posts (e.g. url href's and @user PK's) to cache the additional data instead of caluclate it every rendering.