What is difference between char and varchar

Question

CREATE TABLE IF NOT EXISTS `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `country` varchar(5) NOT NULL,
  `state` char(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I tried following query to insert data

INSERT INTO `test`.`test` (`id` ,`country` ,`state`)
VALUES (NULL , 'south-india', 'Gujarat');

When I execute above query It will shows following warning

Warning: #1265 Data truncated for column 'country' at row 1
Warning: #1265 Data truncated for column 'state' at row 1

I found Reference that VARCHAR is variable-length.CHAR is fixed length.

Then what you mean by

VARCHAR is variable-length.

CHAR is fixed length.

are you asking what the difference is between variable length and fixed length? — jdero, Jul 09 '14 at 08:51
Yes, because both field return same warning. Then what is difference. — Sadikhasan, Jul 09 '14 at 08:52
`VARCHAR is variable-length.` does not mean it will increase the data size on the fly, if length is set to 100 and you are adding something of length 60 char then remaining space for 40 will not be allocated and hence it saves space. In char its not the same if you say char(10) and add a char of len 1 then the entire space will be used. — Abhik Chakraborty, Jul 09 '14 at 08:54
@Sadikhasan I really do think the manual covers this pretty well! — Strawberry, Jul 09 '14 at 09:03
@Strawberry When I read given reference link so I have confusion that what you mean by fixed and variable length So I asked here and Used for future reference. — Sadikhasan, Jul 09 '14 at 09:05

score 2 · Answer 1 · answered Jul 09 '14 at 08:54

2

VARCHAR(5) will use at most 5 characters of storage, while CHAR(5) will always use exactly 5.

For a field holding a person's name, for example, you'd want to use a VARCHAR, because while on average someone's name is usually short, you still want to cope with the few people with very long names, without having to have that space wasted for the majority of your database rows.

answered Jul 09 '14 at 08:54

OrangeDog

36,653
12
122
207

2

Actually, `VARCHAR` also needs some storage to hold the length, so `VARCHAR(5)` will use between 1 and 6 bytes. – Barmar Jul 09 '14 at 08:55
That's why I said "characters", not "bytes". – OrangeDog Jul 09 '14 at 08:56
OK, but you still need to mention that it will also add storage space for the length. – Barmar Jul 09 '14 at 08:59

score 1 · Accepted Answer · answered Jul 09 '14 at 08:55

As you said varchar is variable-length and char is fixed. But the main difference is the byte it uses.

Example.

column: username type: char(10)

if you have data on column username which is 'test', it will use 10 bytes. and it will have space.

'test______'

Hence the varchar column will only uses the byte you use. for 'test' it will only use 4 bytes. and your data will be

'test'

THanks.

score 0 · Answer 3 · answered Jul 09 '14 at 09:10

As you mentioned VARCHAR is variable-length. CHAR is fixed length.

when you say

Varchar(5) and if the data you store in it is of length 1, The remaining 4 byte memory space will be used by others. example: "t"

on the other hand

Char(5) and if the data you store in it is of length 1, The remaining 4 byte memory space cant be used. The 4 byte will end up not used by any other data. example: "t____" here ____ is the unused space.

What is difference between char and varchar

3 Answers3