CODEWITHSUNDEEP
mysqlsize
user2286243
user2286243

Reputation:

Why some MySQL data type require some extra bytes?

enter image description here

I was reading about the MySQL data type size. I saw VARCHAR takes extra 1/2 bytes, MEDIUMTEXT requires extra 3 bytes, LONGTEXT requires extra 4 bytes. What is the reason for such MySQL behaviour?

Upvotes: 4

Views: 1721

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

When MySQL (or any database or computer language) stores a variable length string, there are basically two ways to store the value:

  • The length can be encoded followed by the characters in the string
  • The end of the string can be marked by a special character (typically '0')

Databases (almost?) always use length encoding. So, when you store 'ABC' as a variable length string, in the database storage it looks like:

3  A  B  C

When you store 'A':

1  A

That way, MySQL knows when one string ends and the next begins. The different lengths for the different types are based on the maximum length of the string. So, 1 byte can hold values from 0 to 255. 2 bytes can hold values from 0 to 65,535 and so on.

When you use a regular character expression, say char(3), then 'ABC' looks like:

A   B   C

This occupies three bytes/whatever (depending on the character coding). The length is known from the table metadata.

With char(3), the string 'A' also occupies three slots:

A        
---^space here
--------^space here

The extra two are occupied by spaces. For long strings, this is generally a big waste of space, which is why most strings are stored as varchar rather than char.

Upvotes: 6

Related Questions