If I have a VARCHAR of 200 characters and that I put a string of 100 characters, will it use 200 bytes or it will just use the actual size of the string?
Asked
Active
Viewed 1.6e+01k times
78
-
Duplicate: http://stackoverflow.com/questions/1962310/importance-of-varchar-length-in-mysql-table – Aug 19 '11 at 15:45
-
A detail blog: http://goo.gl/Hli6G3 – Suresh Kamrushi Jan 20 '16 at 07:31
-
Does this answer your question? [Importance of varchar length in MySQL table](https://stackoverflow.com/questions/1962310/importance-of-varchar-length-in-mysql-table) – Paul Roub Jun 16 '21 at 19:39
3 Answers
104
100 characters.
This is the var (variable) in varchar: you only store what you enter (and an extra 2 bytes to store length upto 65535)
If it was char(200) then you'd always store 200 characters, padded with 100 spaces
See the docs: "The CHAR and VARCHAR Types"
gbn
- 422,506
- 82
- 585
- 676
-
59To be clear: Storing a string 100 characters in a `varchar(200)` field will take 101 bytes. Storing a string of 100 characters in a `varchar(256)` field will take 102 bytes. This is why you see `varchar(255)` so frequently; 255 characters is the longest string you can store in MySQL's `varchar` type with only one byte of overhead. Anything larger requires two bytes of overhead. – rinogo Feb 25 '15 at 00:34
-
5@rinogo Doesn't that depend on the character set? Are you assuming ASCII or what? – mpen Feb 03 '16 at 00:04
-
@mpen I'm not sure, but that's a great question! If you track down the answer, please report back here! :) – rinogo Feb 03 '16 at 00:08
-
10@rinogo The official MySQL docs are fuzzy on this subject but I'm pretty sure in `varchar(N)` `N` is the number of *characters*, so `varchar(255) charset utf8mb4` would actually use *up to* 1021 bytes. I'm not sure if it will always use the full number of bytes or what; I guess it depends how it's packed. – mpen Feb 03 '16 at 00:40
15
VARCHAR means that it's a variable-length character, so it's only going to take as much space as is necessary. But if you knew something about the underlying structure, it may make sense to restrict VARCHAR to some maximum amount.
For instance, if you were storing comments from the user, you may limit the comment field to only 4000 characters; if so, it doesn't really make any sense to make the sql table have a field that's larger than VARCHAR(4000).
dwmcc
- 1,034
- 8
- 19
