What does the specified number mean in a VARCHAR() clause?

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 number 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 you 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

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.