1

I understand that if the length is > 255 it will require 2 bytes to store the length prefix. I just need to verify my assumptions. Please tell me the length prefix and the length of string('abcd') for charsets(latin1, ucs2, utf8mb4). For a varchar(20) column. The documentaton says:

[...] For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes). [...]

Reference: 11.7 Data Type Storage Requirements (Oracle | Docs)

I just don't understand why for 'abcd' with charset 'ucs2' it requires 2 byte length prefix even after it being less than 255.

Improve this question
2
  • 1
    So is your question "how much space will be required?" (which you can test for yourself faster than anyone can test it for you and answer) or is your question "why is 2 bytes required for lengths < 255?" (which is a different question we may not be able to answer at all). Remember that [current length] <> [maximum length]. The 2 bytes are likely required for all potential future values, even if the current value doesn't explicitly require them. It's the same reason there's an airbag on the passenger side of my car, even when I'm driving alone, and even if I only ever drive alone.
    – Aaron Bertrand
    Commented Aug 11, 2020 at 19:38
  • Your comment helps understand what they were trying to say in documentation. My question was why in the example they are trying to say that the abcd is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255.
    – Great Greek
    Commented Aug 12, 2020 at 4:33

1 Answer 1

Reset to default
3

abcd is all English, so: 9 bytes for ucs2; 5 bytes for utf8. But...

UCS2 uses 2 byte per character (at least for 'western' letters). I think it takes 4 bytes for most Asian characters.

utf8 (or utf8mb4) is a variable-length encoding. English letters take 1 byte each; most European text takes 1 or 2 bytes per character. Asian takes 3, sometimes 4 bytes per character.

latin1 has only 1-byte characters, so it is limited to English, plus some accented European letters.

But, but...

VARCHAR(40) stores up to 40 characters (not bytes) in whatever encoding is being used. The disk space be 1 byte for length plus up to 4*40 bytes for text. A long example would be 40 Emoji, taking 161 bytes.

<opinion> There is virtually no reason to use the CHARACTER SET ucs2 (or ucs4). In particular, anything involving unicode or utf8 should use utf8mb4. </opinion>

But But But...

Re your comment about 10 bytes -- InnoDB will sometimes use 1 byte for the length, sometimes 2. But the decision is based on all the columns in the table. Your "40" does not force a 2-byte length, but some other column might.

The documentation says "For example, a VARCHAR(255) column ... (up to 510 bytes)". So it needs 2 bytes since the max byte length is >255. Your example is with "VARCHAR(40)", which is "up to 80 bytes" (<=255).

Improve this answer
3
  • Storage space requirements for string types.[dev.mysql.com/doc/refman/8.0/en/…
    – Great Greek
    Commented Aug 12, 2020 at 4:24
  • 1
    @GreatGreek - Their paragraph seems correct. It is however long and confusing, so I see why you read "2". See the addition to my Answer.
    – Rick James
    Commented Aug 12, 2020 at 16:04
  • Great, thanx a lot for followed clarification. Helped a lot understanding the concept.
    – Great Greek
    Commented Aug 15, 2020 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.