CODEWITHSUNDEEP
mysqltexttypescharvarchar
Bret
Bret

Reputation: 35

Which is the correct text datatype to use in MySQL?

There's VARCHAR, CHAR, TEXT, BIGTEXT, BLOB...

Which is the correct datatype to use when storing small text fields (ie. something like first_name)?

Upvotes: 3

Views: 299

Answers (2)

Lèse majesté
Lèse majesté

Reputation: 8045

"Correct" is a strong word, but most people would probably use VARCHAR. CHAR is also an option, but it's not as frequently used in my experience. This page explains the difference.

One of the notable differences between CHAR and VARCHAR in the latest versions of MySQL is that CHAR is incapable of storing trailing spaces (because the column is automatically padded to its max length by spaces). So if you store something like 'abc ', it will be retrieved as 'abc'. This might not matter for most applications, but it's something to keep in mind. Similarly, prior to 5.03, trailing whitespaces are stripped from VARCHAR fields before insertion. So if you need to store arbitrary byte values and don't want to worry about different behaviors between MySQL versions, you should use one of the BLOB types.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

I usually go with VARCHAR for most small text fields, unless I know the field is going to be a fixed size, like a U.S. state abbreviation, where I'd use CHAR(2).

Upvotes: 3

Related Questions