Why should I ever choose any other length than 255 for varchar in MySQL?

Question

I know the differnce between CHAR and VARCHAR,

CHAR - Fixed length

VARCHAR - Variable length (size + 1 byte)

But I wanted to know what was the purpse of the having the option for a varchar length e.g. VARCHAR(50), VARCHAR(100), VARCHAR(255)

This seems pointless to me because the actual space used depends on the value stored in the database.

So my questions are:

1) It is fine to set all my varchar's to 255 2) Why would you want to specify any other lenght?

possible duplicate of [Are there disadvantages to using a generic varchar(255) for all text-based fields?](http://stackoverflow.com/questions/262238/are-there-disadvantages-to-using-a-generic-varchar255-for-all-text-based-fields) — Eric Petroelje, Jul 01 '10 at 21:02
Also, http://stackoverflow.com/questions/1262174/mysql-why-use-varchar20-instead-of-varchar255 — Eric Petroelje, Jul 01 '10 at 21:07
Always thought the MySQL documentation was pretty good at explaining the difference: http://dev.mysql.com/doc/refman/5.0/en/char.html — OMG Ponies, Jul 01 '10 at 21:09
Note if you add an index to your varchar column: the maximum length of InnoDB index key prefix is 767 bytes. Due to the way MySQL handles Unicode this can further reduce the number of actual "characters" that can be stored... with utf8mb4 encoding, potentially as low as 191. So for indexed columns `varchar(191)` is probably the "safest" maximum length. See the docs here: https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html — Greg Kennedy, May 31 '20 at 19:04

score 25 · Accepted Answer · answered Jul 01 '10 at 21:05

1) If you dont want to limit the maximum size of a stored varchar, then yes it is fine. That being said...

2) In many cases you want to set an upper limit for the size of a varchar. Lets say you are storing a mailing list, and have a limited amount of space for an address line. By setting an upper limit for your address field, you now allow the database to enforce a maximum address line length for you.

score 19 · Answer 2 · edited Jul 01 '10 at 21:09

Excerpt from the MySQL documentation:

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. As of MySQL 5.0.3, they also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

A small correction: The linked documentation says `VARCHAR` has an upper limit of 65,535 for MySQL >= 5.0.3. Quoting: "`The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.`" — jweyrich, Oct 21 '13 at 22:31

score 4 · Answer 3 · edited May 23 '17 at 11:58

4

CHAR Vs VARCHAR

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

E.g.

create table emp
(f_name CHAR(20),
 l_name VARCHAR(20)
);

insert into emp values('Suraj','Chandak');

select length(f_name), length(l_name) from emp;

Output will be

length(f_name)          Length(l_name)
   20                       7

The best answer for CHAR vs VARCHAR

Edit

You can set maximum upper limit for the column.
Performance and storage can have effect.

Thanks.

edited May 23 '17 at 11:58

Community

1
1

answered Nov 28 '12 at 11:50

Aniket Kulkarni

12,825
9
67
90

The question is "1) It is fine to set all my varchar's to 255 2) Why would you want to specify any other lenght?", not CHAR vs VARCHAR. – Davor Nov 18 '14 at 11:27

score 2 · Answer 4 · answered Nov 22 '13 at 06:45

2

Fixed-length (Static) Tables are Faster. When every single column in a table is “fixed-length”, the table is also considered “static” or “fixed-length”. Examples of column types that are NOT fixed-length are: VARCHAR, TEXT, BLOB.

http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

So if your table does not have any other fields that are varchar, text, or blob; you can use char and make your table a static one. That way they are faster.

answered Nov 22 '13 at 06:45

musafar006

931
11
22

2

That answer has nothing to do with the question asked here. – Davor Nov 18 '14 at 11:27
and you are right. I don't remember why I posted that answer. Maybe at that time, I was searching for something related. Maybe he edited his question? – musafar006 Nov 22 '14 at 13:50
Dunno, could be. I don't know how to see his edit history, or whether I have enough reputation to see it in the first place. – Davor Nov 23 '14 at 16:27

score 1 · Answer 5 · answered May 19 '13 at 07:58

The main difference between these two value types comes into place when doing a comparison between strings.

In a CHAR column which its length is predefined you'll have to "run" all the way throughout the column length, while in VARCHAR column you'll need to "run" all the way throughout the value length and not column length, which is way faster in most cases.

Therefore a value length which is smaller than the field length will be compared faster if stored in a VARCHAR field.

score 1 · Answer 6 · edited Jun 20 '20 at 09:12

But I wanted to know what was the purpse of the having the option for a varchar length e.g. VARCHAR(50), VARCHAR(100), VARCHAR(255)

This seems pointless to me because the actual space used depends on the value stored in the database.

Specifying e.g. VARCHAR(5) instead of VARCHAR(500) can give you better performance in some cases, e.g. for operations which use in-memory temporary tables.

Another case is to restrict column length to compliment domain requirements (when your value should not be greater then some maximum. Example: full domain name in DNS may not exceed the length of 253 characters)

score 1 · Answer 7 · answered Jul 01 '10 at 21:06

1) Technically it is fine, because the fields are created with only 1 or 2 bytes in length in the beginning. Afterwards, they'll grow as necessary.

2) Having said that though, good design principles suggest that you set field lengths appropriately so a) If someone goes through the table scheme and tries to work out how much data is stored in particular fields, they can see that certain fields will hold less data than others and b) you can prevent small amounts of extra work done by the database engine because it has to truncate less space from a VARCHAR(10) field than a VARCHAR(255) during an insert.

You can view extra details about it here:

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

score 1 · Answer 8 · answered Jul 01 '10 at 21:08

1

I have read elsewhere that varchar comes with a performance hit relative to char, when you run selects against columns defined with them. So, maybe you want to choose char, if you know for sure the field will always be a certain length, and you have performance issues...

answered Jul 01 '10 at 21:08

Greg Gauthier

1,336
1
12
25

I would like to see a reference for this claim. – dotancohen Dec 02 '13 at 14:21
Fair enough, and I would give you one, only this comment was over three years ago, and I can't even remember where I saw it. – Greg Gauthier Dec 02 '13 at 20:30

score 0 · Answer 9 · answered Jul 01 '10 at 23:40

0

1) Yes.

2) Historically it was a performance hit.

Look at databases such as sqlite that store everything as text for evidence that it no longer really matters.

answered Jul 01 '10 at 23:40

Toby Allen

10,997
11
73
124

4

sqlite is not exactly high performance database - and varchar is a performance hit if it forces the row size to be variable. – qdot Feb 24 '12 at 17:24
Yes but for most people and most uses its fast enough – Toby Allen Feb 26 '12 at 10:09
4

True, but claiming that it 'no longer' matters and using sqlite as a pinnacle of database engineering is really misleading. And no, for most people and most uses it isn't fast enough - it's just easiest to configure and that outweighs the costs, at least initially.. as long has you never have simultaneous requests from more than one user, that is. – qdot Feb 26 '12 at 15:19

Why should I ever choose any other length than 255 for varchar in MySQL?

9 Answers9