char or varchar in mysql?

[fj_111]

Hi,

following the 228 tutorial by Kevin Yank i added fields to an existing mysql table by using the command

alter table xyz add zyx char(20) not null;

when i have the table displayed the field's type sometimes comes up as varchar(20), sometimes as char(20).

is there a difference between these two?

Thanks for your help,
Frank

[freakysid]

The CHAR type is a fixed length string. So if you have CHAR(12) and you have the strings:

foo
foobar
foobarzoot

They will each take up the same amount of storage space (12 bytes) in the database because if the column type is CHAR.

The advantage to this is that if you have a table where all of the columns are of fixed length (including CHAR types) then MySQL should be able to perform queries faster. This is because, once MySQL has located the record in the index, it can calculate the possition of that record in the table (as each record will be offset by the same amount of bytes). The downside is that to create a table using all fixed length columns, you will generally end up wasting storage space when you store strings that are in fact shorter than the size of the CHAR column they are going into. So there is a trade-off between gain in performance from having fixed length records and greater stogage requirements, plus a counter-balancing speed hit from having to move around a larger file. Well, that's all rather theoretical and probably doesn't make much difference in most cases. None the less, I would love some MySQL guru to give an algorithm or rule-of-thumb as to how to decide at which point those counter-acting forces tip either way.

All the above is well and good, but here is the trick. If your table has any columns that are not of fixed length (VARCHAR, TEXT, BLOB) then there is no advantage to having a column of CHAR mixed in, because the records will be variable length anyway. In this case, even if you declare a column of CHAR, MySQL will ignore you and save it as VARCHAR to save on storage space. That may explain some of the behaviour you have noticed.

[fj_111]

Great, thanks!

[DR_LaRRY_PEpPeR]

if you make a VARCHAR(3) or less than 3, it will become a CHAR. and, as said, if the rows are variable length a CHAR(4) or greater than 4 will become a VARCHAR.

explained here: www.mysql.com/doc/S/i/Silent_column_changes.html

[ucahg]

CHAR would be useful if you are storing information that you absolutely know will be a certain number of characters long.

Zip codes/Postal Codes (5 and 6, respectedly), md5() (32), etc.