There's VARCHAR, CHAR, TEXT, BIGTEXT, BLOB...
Which is the correct datatype to use when storing small text fields (ie. something like first_name)?
Add a comment
|
2 Answers
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).
answered Feb 9, 2011 at 14:23
Comments
"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.
answered Feb 9, 2011 at 14:38
