CODEWITHSUNDEEP
mysqltextvarchar
Jonathan
Jonathan

Reputation: 4928

Use varchar or text for a dynamic field in mysql?

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

Upvotes: 7

Views: 6334

Answers (3)

programmer
programmer

Reputation: 916

The varchar field can handle the different input's that you are mentioning but as a string, not as an integer or datetime.

Upvotes: 0

RDL
RDL

Reputation: 7961

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.

Upvotes: 1

Shauna
Shauna

Reputation: 9596

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

Upvotes: 4

Related Questions