CODEWITHSUNDEEP
sqlmysql
steven
steven

Reputation: 13537

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.

Upvotes: 450

Views: 790374

Answers (17)

Asiful Islam Sakib
Asiful Islam Sakib

Reputation: 552

CHAR

  1. Uses specific allocation of memory
  2. Time efficient

VARCHAR

  1. Uses dynamic allocation of memory
  2. Memory efficient

Upvotes: 8

TheLovelySausage
TheLovelySausage

Reputation: 4094

Distinguishing between the two is also good for an integrity aspect.

If you expect to store things that have a rule about their length such as yes or no then you can use char(1) to store Y or N. Also useful for things like currency codes, you can use char(3) to store things like USD, EUR or AUD.

Then varchar is better for things were there is no general rule about their length except for the limit. It's good for things like names or descriptions where there is a lot of variation of how long the values will be.

Then the text data type comes along and puts a spanner in the works (although it's generally just varchar with no defined upper limit).

Upvotes: 1

Dani
Dani

Reputation: 807

CHAR

  • CHAR is a fixed length string data type, so any remaining space in the field is padded with blanks.
  • CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds.

VARCHAR

  • VARCHAR is a variable length string data type, so it holds only the characters you assign to it.
  • VARCHAR takes up 1 byte per character, + 2 bytes to hold length information (For example, if you set a VARCHAR(100) data type = ‘Dhanika’, then it would take up 7 bytes (for D, H, A, N, I, K and A) plus 2 bytes, or 9 bytes in all.)

Upvotes: 6

simplePerson43
simplePerson43

Reputation: 3873

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.

Upvotes: 260

user1445657
user1445657

Reputation: 129

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.

Upvotes: 12

Alireza Rahmani Khalili
Alireza Rahmani Khalili

Reputation: 2954

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.

Upvotes: 0

Anon.
Anon.

Reputation: 60006

VARCHAR is variable-length.

CHAR is fixed length.

If your content is a fixed size, you'll get better performance with CHAR.

See the MySQL page on CHAR and VARCHAR Types for a detailed explanation (be sure to also read the comments).

Upvotes: 438

Bob Minteer
Bob Minteer

Reputation: 149

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.

Upvotes: 1

Grygoriy Gonchar
Grygoriy Gonchar

Reputation: 4018

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.

Upvotes: 13

Amandeep
Amandeep

Reputation: 1

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)

Upvotes: -4

Y.R.S.REDDY
Y.R.S.REDDY

Reputation: 1

CHAR :

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

VARCHAR :

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

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

Upvotes: -11

Amy
Amy

Reputation: 1

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

Upvotes: -6

P Sharma
P Sharma

Reputation: 2728

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

Upvotes: 140

Greg Fenton
Greg Fenton

Reputation: 2808

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.

Upvotes: 11

Andrew
Andrew

Reputation: 139

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.

Upvotes: 10

Donnie DeBoer
Donnie DeBoer

Reputation: 2525

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.

Upvotes: 9

SLaks
SLaks

Reputation: 887215

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.

Upvotes: 88

Related Questions