database datatype size

Question

Just to clarify, by specifying something like VARCHAR(45) means it can take up to max 45 characters? I remember I heard from someone a few years ago that the number in the parenthesis doesn't refer to the no of characters, then the person tried to explain to me something quite complicated which I don't understand and forgot already.

And what is the difference between CHAR and VARCHAR? I did search around a bit and see that CHAR gives u the max of the size of the column and it is better to use it if your data has a fixed size and use VARCHAR if your data size varies.

But if it gives you the max of the size of the column of all the data of this column, isn't it better to use it when your data size varies? Especially if you don't know how big your data size is going to be. VARCHAR needs to specify the size (CHAR don't really need right?), isn't it more troublesome?

Answer 1

You also have to specify the size with CHAR. With CHAR, column values are padded with spaces to fill the size you specified, whereas with VARCHAR, only the actual value you specified is stored.

For example:

CREATE TABLE test (
    char_value CHAR(10),
    varchar_value VARCHAR(10)
);

INSERT INTO test VALUES ('a', 'b');

SELECT * FROM test;

The above will select "a          " for char_value and "b" for varchar_value

If all your values are about the same size, the CHAR is possibly a better choice because it will often require less storage space than VARCHAR. This is because VARCHAR stores both the length of the value and the value itself, whereas CHAR can just store the (fixed-size) value.

Answer 2

The MySQL documentation gives a good explanation of the storage requirements of the various data types.

In particular, for a string of length L, a CHAR(M) datatype will take up (M x c) bytes (where c is the number of bytes required to store a character... this depends on the character set in use). A VARCHAR(M) will take up (L + 1) or (L + 2) depending on whether M is <=255 or >255.

So, it really depends on how long you expect your strings to be, what the variation in length will be.

NB: The documetation doesn't discuss the impact of character sets on the storage requirements of a VARCHAR type. I've tried to quote it accurately, but my guess is that you would need to multiply the string length by the character byte-width as well to get the storage requirement.

Answer 3

If you know a column will contain a small, fixed number of chars use a CHAR, otherwise use a varchar. A CHAR column is padded to the max length.

VARCHAR has a small overhead (4-8 bytes depending on RDBMS), but only uses the overhead + the actual number of chars stored.

Answer 4

For the values you know they are going to be constant, for example for Phone Numbers, Zip Codes etc., It is optimal to use "char" for sure.

Answer 5

The complicated stuff you don't remember is that the 45 refer to bytes, not chars. It's not the same if you are using a multibyte character encoding. In Oracle you can specify bytes or chars explicitly.

varchar2(45 BYTE)

or

varchar2(45 CHAR)

See http://stackoverflow.com/questions/81448/difference-between-varchar211-byte-and-varchar211-char

Answer 6

char and varchar actually becomes irrelevant if you have just 1 variable length field in your table, like a varchar or text. Mysql will automatically change all char to varchar.

The fixed length/size record can give you extra performance, but you can't use any variable length field types. The reason is that it will be quicker and easier for mysql to find the next record.

For example, if you do a SELECT * FROM table LIMIT 10, mysql has to scan the table file for the tenth record. This means finding the end of each record until you find the end of the 10th record. But if your table has fixed length/size records, mysql just needs to know the record size and then skip 10 x #bytes.