[sql] What's the difference between VARCHAR and CHAR?

What's the difference between VARCHAR and CHAR in MySQL?

I am trying to store MD5 hashes.

CHAR is a fixed length field; VARCHAR is a variable length field. If you are storing strings with a wildly variable length such as names, then use a VARCHAR, if the length is always the same, then use a CHAR because it is slightly more size-efficient, and also slightly faster.

CHAR :

• Supports both Character & Numbers.
• Supports 2000 characters.
• Fixed Length.

VARCHAR :

• Supports both Character & Numbers.
• Supports 4000 characters.
• Variable Length.

any comments......!!!!

CHAR is fixed length and VARCHAR is variable length. CHAR always uses the same amount of storage space per entry, while VARCHAR only uses the amount necessary to store the actual text.

In most RDBMSs today, they are synonyms. However for those systems that still have a distinction, a CHAR field is stored as a fixed-width column. If you define it as CHAR(10), then 10 characters are written to the table, where "padding" (typically spaces) is used to fill in any space that the data does not use up. For example, saving "bob" would be saved as ("bob"+7 spaces). A VARCHAR (variable character) column is meant to store data without wasting the extra space that a CHAR column does.

As always, Wikipedia speaks louder.

CHAR Vs VARCHAR

CHAR is used for Fixed Length Size Variable
VARCHAR is used for Variable Length Size Variable.

E.g.

Create table temp
(City CHAR(10),
Street VARCHAR(10));

Insert into temp
values('Pune','Oxford');

select length(city), length(street) from temp;

Output will be

length(City)          Length(street)
10                    6

Conclusion: To use storage space efficiently must use VARCHAR Instead CHAR if variable length is variable

A CHAR(x) column can only have exactly x characters.
A VARCHAR(x) column can have up to x characters.

Since your MD5 hashes will always be the same size, you should probably use a CHAR.

However, you shouldn't be using MD5 in the first place; it has known weaknesses.
Use SHA2 instead.
If you're hashing passwords, you should use bcrypt.

What's the difference between VARCHAR and CHAR in MySQL?

To already given answers I would like to add that in OLTP systems or in systems with frequent updates consider using CHAR even for variable size columns because of possible VARCHAR column fragmentation during updates.

I am trying to store MD5 hashes.

MD5 hash is not the best choice if security really matters. However, if you will use any hash function, consider BINARY type for it instead (e.g. MD5 will produce 16-byte hash, so BINARY(16) would be enough instead of CHAR(32) for 32 characters representing hex digits. This would save more space and be performance effective.

according to High Performance MySQL book:

VARCHAR stores variable-length character strings and is the most common string data type. It can require less storage space than fixed-length types, because it uses only as much space as it needs (i.e., less space is used to store shorter values). The exception is a MyISAM table created with ROW_FORMAT=FIXED, which uses a fixed amount of space on disk for each row and can thus waste space. VARCHAR helps performance because it saves space.

CHAR is fixed-length: MySQL always allocates enough space for the specified number of characters. When storing a CHAR value, MySQL removes any trailing spaces. (This was also true of VARCHAR in MySQL 4.1 and older versions—CHAR and VAR CHAR were logically identical and differed only in storage format.) Values are padded with spaces as needed for comparisons.

Char or varchar- it is used to enter texual data where the length can be indicated in brackets Eg- name char (20)

Char has a fixed length (supports 2000 characters), it is stand for character is a data type

Varchar has a variable length (supports 4000 characters)

CHAR

1. Used to store character string value of fixed length.
2. The maximum no. of characters the data type can hold is 255 characters.
3. It's 50% faster than VARCHAR.
4. Uses static memory allocation.

VARCHAR

1. Used to store variable length alphanumeric data.
2. The maximum this data type can hold is up to
   • Pre-MySQL 5.0.3: 255 characters.
   • Post-MySQL 5.0.3: 65,535 characters shared for the row.
3. It's slower than CHAR.
4. Uses dynamic memory allocation.

Varchar cuts off trailing spaces if the entered characters is shorter than the declared length, while char will not. Char will pad spaces and will always be the length of the declared length. In terms of efficiency, varchar is more adept as it trims characters to allow more adjustment. However, if you know the exact length of char, char will execute with a bit more speed.

The char is a fixed-length character data type, the varchar is a variable-length character data type.

Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.

You can use char when the data entries in a column are expected to be the same size. You can use varchar when the data entries in a column are expected to vary considerably in size.