CODEWITHSUNDEEP
mysqlsqlmemorysqldatatypes
Karthikeyan Pandian
Karthikeyan Pandian

Reputation: 357

Memory allocation in MySQL datatypes?

I am new to the MySQL. I have little bit confusion about memory allocation for datatypes. If I initialize type as bigint it will take 8 bytes of memory. If I initialize bigint(10) then would it take 80 bytes or same 8 bytes with limitations. what is use of initializing 10 over the datatype? And also I have bit confused in varchar as well. what would be the memory size of varchar(10) and varchar(100)? will it differ in memory size? Am using InnoDB engine.

Upvotes: 2

Views: 1916

Answers (1)

Rick James
Rick James

Reputation: 142296

Numeric datatypes take a fixed amount of space when not NULL.

VARCHAR(n) takes 1 or 2 bytes for the length, plus room for however many characters you have -- up to n. Note: I say "characters", not "bytes". For ascii and latin1, one character occupies one byte. But for utf8, one character takes up to 3 bytes.

VARCHAR(10) CHARACTER SET utf8 will take between 1 byte (for the empty string) and 32 bytes.
VARCHAR(100) CHARACTER SET utf8 will take between 2 bytes and 302 bytes.
VARCHAR(255) CHARACTER SET utf8 will take between 2 bytes and 767 bytes.

CHAR(10) CHARACTER SET utf8 will always take 30 bytes. Hence, VARCHAR is usually a better choice than CHAR.

NULL is special-cased and takes virtually no space. A related comment: Should I use ... NOT NULL and use ''? or use NULLs? The space difference is not as important as the semantics. Use NULL when suitable for your app.

Upvotes: 3

Related Questions