CODEWITHSUNDEEP
mysql
mannoj
mannoj

Reputation: 465

Size allocated for datatypes

If I set varchar (250) say on charset latin1 when I add a value say 'abc' characters does it means that the data allocated for it is 251bytes or 4bytes? Would it be the same way for other data types? Please advise?

Upvotes: 0

Views: 215

Answers (2)

MvG
MvG

Reputation: 60868

The MySQL manual has a whole chapter on storage requirements.

A VARCHAR(250) NOT NULL takes one byte to store the length n, and n bytes to store the actual string. So a column holding a value 'abc' will account for 4 bytes. In a VARCHAR(250) NULL, the nullity takes one bit, and a NULL value won't take anything else. A non-null value takes that bit, one byte for length and n bytes for data. Needing a bit here means that several nullable columns share one or more bytes to record their nullity.

Other variable length data types are similar. BLOB and TEXT types of the various sizes may have more than a single byte for length, but otherwise work pretty much the same. Numeric types as well as the fixed-length CHAR types have fixed memory requirements. I think I the CHAR will omit the length byte, as its content is padded with spaces to that fixed length.

Indices might increase the memory requirements. Multy-byte character sets (including UTF-8) might increase the memory requirements, so the above statements will only hold if your table was created for latin1 or similar.

Upvotes: 1

Leonel Machava
Leonel Machava

Reputation: 1531

When you have a varchar(250) and you add 'abc' then 3 bytes/characters are allocated. If you had user char(250) then 250 bytes/characters would have been allocated.

Note that for VARCHAR a minimal storage overhead is used internally to represent the datatype.

Upvotes: 1

Related Questions