Use varchar or text for a dynamic field in mysql?

Question

I am building a table that will receive different values in a field, think of a log table that the "value" field can be a number, a tiny string or a big text etc.
So I was wonderig if I should create that "value" field as Text or create two fields, one for small inputs, like date, numbers and some string and another one only for the Texts inputs.

so, my question is this: Should this "value" field be a Varchar along with some other "value2" as Text or create one field Text that the mysql will manage this corretcly?

I am afraid that creating only one Text field can be a bad thing for performance.

EDIT: the number, datetime etc are going to be cast as string before insertion, thats not the point

Thanks,
Joe

Answer 1

Do you know how large the largest input will be? If you impose a limit, or know how large the maximum input will be, then you could use varchar (which caps at 255 characters in versions < 5.0.3, and 65,535 in versions >= 5.0.3). Otherwise, you're probably better off with Text, since it holds significantly more (65,535*2^16-1).

As an alternative, if users are creating things that already have tables (such as adding events to a calendar), you could just put an "is_approved" column on the table and only display approved ones, or search through everything to check for duplicates.

http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

Answer 2

If there is a limit to the length of that the data stored use varchar() (as MySQL 5.0.3 varchar max length can be up to 65,535)

If there is no concrete limit then use a 'text' field type.