Datatype difference

[xploreraj]

Hi,
I have one confusion. What is the difference between varchar and char? I have observed that both work similarly, a varchar won't take more than specified no. of characters.
And one more thing, what should be the data type that can take in varying number of values?

Thanks,
xploreraj

[code green]

It just seems to be down to memory allocation.
VARCHAR (n) will allocate memory for UPTO n characters
CHAR (n) will ALWAYS allocate memory for n characters.
CHAR has a max of 255 characters, VARCHAR has more in later versions.
This may help some more.

http://dev.mysql.com/doc/refman/5.0/en/char.html

I seem to remember it is more efficient to use CHAR.

[Atli]

I seem to remember it is more efficient to use CHAR.

Depends on what you are trying to optimize.

VarChar is a variable size field, whereas Char is fixed size. As such, Char will usually use more disk space. So if you are worried about the size of the database, VarChar would be the better choice.

However, if all the columns in your table are fixed size, it *may* increase the speed at which MySQL can read the table. For each row it reads, MySQL has to detect the total size of the data. If all the rows are fixed, it can do this once for all the rows using only the column definitions. If the table contains a variable size column, it has to do this for each row by inspecting the actual data (or it's meta-data).

So, essentially:
- Use VarChar to conserve disk space
- Use Char to optimize read speed.

That second one is, as I explained, dependent on the other rows not being variable size as well.

[xploreraj]

Thanks Code Green and Atli for the details, just cleared my concept, thanks a lot.