MySQL data type char or varchar is best?

Try This Link
MySQL :: MySQL 5.1 Reference Manual :: 11.4.1 The CHAR and VARCHAR Types

No one can say which is best considering you didnt indicate what kind of data you want to store.

Char has a max of 256 characters
Var Char has a max of 65,535 characters

So one consideration is the length of the data you wish to ad to the field.

If you specifiy CHAR as 8 characters long, than no matter if you write "one" or "eightcha", both will take up 8 characters. "one" will be written as "one_____" with the trailing space added.

If you specify VARCHAR 8 characters long and you only want to store the word "one" in it, it will only store those three characters. No trailing space added.

As it is explained on the link already posted:
MySQL :: MySQL 5.1 Reference Manual :: 11.4.1 The CHAR and VARCHAR Types

The data range is given above, the point is what type of data you want to store in it. If it is not more than 256 char, use char.

sql server - Why would I ever pick CHAR over VARCHAR in SQL? - Stack Overflow

CHAR should be used for storing fix length character strings.
VARCHAR provides for variable-length storage of strings.

For fixed and short length i myself use char i think if its not more than 256 than use char.

Generally, varchar for everything but when you're dealing with database optimization char is a great way to go. So it really depends on what you are trying to accomplish.

Use CHAR when you know your data storage size requirement for any given data string will be less to its equivalent in VARCHAR and when you know your data won't exceed the maximum CHAR limit.

Else, use VARCHAR.

Learn to identify your data limits. As same as you choose DATE, choose CHAR and VARCHAR, everything can result in a unknown bug in future development if you do not choose carefully.

The following will best display the differences:

ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Required''' '4 bytes''1 byte'ab''ab '4 bytes'ab'3 bytes'abcd''abcd'4 bytes'abcd'5 bytes'abcdefgh''abcd'4 bytes'abcd'5 bytes

Just stay with varchar and set the length to the max size you will need. In 19 years I have rarely used char. My cousin does massive multi gigabyte government dbs and he uses char.

Again set the length as example:

email varchar(50)
first_name varchar(10)
last_name varchar(15)
state_abbreviated varchar(2)
ssn varchar(10)
postal_code varchar(10)
username varchar(10)

By setting the length you prevent the database from becoming bloated with unused space, plus you will help speed up searches.

Also index each column you will search on.

For example, email, first and last names, state, zip_codes, etc.

Indexing will dramatically help speed up search results.

Hope this helps.

mysql - Any benefit of uses CHAR over VARCHAR? - Stack Overflow

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.