8

CHAR is stored as a fixed length string, VARCHAR is stored as a variable length string. I can use VARCHAR to store a fixed length string, but why people still want to use CHAR to store a fixed length string? Is there any benefit of using CHAR over VARCHAR? If none of benefit, why mySQL database doesn't remove the option of CHAR?

zac1987
  • 2,721
  • 9
  • 45
  • 61
  • http://stackoverflow.com/questions/59667/why-would-i-ever-pick-char-over-varchar-in-sql – Vishwanath Dalvi Sep 29 '11 at 18:01
  • http://stackoverflow.com/questions/350195/char-vs-varchar-for-performance-in-stock-database – Vishwanath Dalvi Sep 29 '11 at 18:02
  • ops, sorry for duplicated question. Got the answer. Thanks for the link. – zac1987 Sep 29 '11 at 18:05
  • another couple of links: http://dba.stackexchange.com/search?q=char+varchar+mysql and http://dev.mysql.com/doc/refman/5.0/en/char.html, the latter, give some attention on the sql query using concat. They are not completely equivalent – bpaulon Sep 29 '11 at 18:12

1 Answers1

12

VARCHAR

VARCHAR stores variable-length character string. It can require less storage than fixed-length types because it uses only as much space as it needs.

VARCHAR also uses 1 or 2 extra bytes to record the value's length. For example varchar(10) will use up to 11 bytes of storage space. VARCHAR helps performance because it saves space. However because the rows are variable length, they can grow when you update them, which can cause extra work. if a row grows and no longer fits in its original location, the behavior is storage engine-dependent.

CHAR

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. Values are padded with spaces as needed for comparisons.

CHAR is useful if you want to store very short strings, or if all the values are nearly the same length.

For example, CHAR is a good choice for MD5 values for user passwords, which are always the same length.

CHAR is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to fragmentation.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Don Gorgon
  • 2,321
  • 3
  • 18
  • 20
  • 1
    Now I see the benefit of CHAR about frequent update value. Thank you very much. – zac1987 Sep 29 '11 at 18:16
  • Sidenote: I wouldn't say that char is a good choice for MD5 hashes, or any hash really. They should be stored in binary format instead of a different encoding like hexadecimal is. – Luc Jan 18 '14 at 12:30
  • i believe it's faster for lookup, as the database knows exactly where to go immediately rather than having to do length calculations, this might not be too noticeable these days though – Toni Leigh Jul 29 '15 at 21:10