5 ms·

As a beginner programmer back in the day I'd have agreed that unicode is weird and "length in characters" is the right metric for the database entry. However a

As a beginner programmer back in the day I'd have agreed that unicode is weird and "length in characters" is the right metric for the database entry. However as a senior dev, who have read and messed around enough with binary, UTF-X, compression/GZIP, etc, I'd say that "character length" for database field size is a weird concept and that "size in bytes" would make more sense since that maps better to what you have in the HDD/SSD/network.

hans_castorp 3y ago
> I'd say that "character length" for database field size is a weird concept Until you want a constraint for such a column. From a business perspective, you would limit the column to a specific number of characters, not bytes
- bruce511 3y ago
  Let me be clear. You currently cannot constrain for number of characters in a unicode string. From a business perspective it's unhelpful to think of constraining in that way. By all means constrain on bytes, and if that's close enough for you, use that as a proxy. Or provision lots of space, and count characters in the UI.
- qalmakka 3y ago
  This is ALWAYS broken on unicode. It only makes sense in ASCII and with alphabets similar to the Latin alphabet.
  - Dylan16807 3y ago
    It's not broken if "character" means "extended grapheme cluster". Though in that case it makes it very hard to index the column.
    - qalmakka 3y ago
      Nobody cares about them, I am afraid. Most people _really_ can't accept that you can't work on Unicode like you did with ASCII, and do not want to let go of their old C habits (like iterating char by char and doing something).
- PeterisP 3y ago
  If you want a constraint for such a column, then due to nature of our complex writing, it has to be separate from the actual field type and physical storage allocation - it's the equivalent of an integer field having a constraint that it must be between 1 and 100.
- twic 3y ago
  Is there ever a genuine, non-arbitrary business requirement to limit any string to a certain number of unicode characters? And by characters here we probably mean extended grapheme clusters. If the data is going to get printed in a monospace font, on a passport or credit card or something, then i can understand a limit on the number of characters. But then it's not full unicode either - you want to constrain the length in some specific character set. Otherwise, i think limits are always arbitrary. I suspect there is a strong cultural holdover from the days of punched cards here.
  - bruce511 3y ago
    >> If the data is going to get printed in a monospace font, on a passport or credit card In the case of limited space, you absolutely can count characters and limit them in the UI. For data storage though you would provision "enough bytes". Think approximately 4 bytes per character, plus a bit extra just in case. But I agree in most cases the length limits are completely arbitrary and simply made up by the programmer at the time the database is designed. (Hint: They are _always_ too short, especially if the programmer lacks experience.)
    - Dylan16807 3y ago
      > Think approximately 4 bytes per character, plus a bit extra just in case. I hope you don't have too much korean or compound emojis or some other things in that data, then. One of the unicode annexes needed to set a (partial) limit on code points in a single grapheme and the number it picked was 31.
- scrollaway 3y ago
  Business constraints should be at the server layer (eg api constraints), not the db layer. You want the name to be max 200 characters, the birth year to be minimum 1900, and the email address to not contain the domain name hotmail.com? Don’t do all this at the db layer! What if you want some users to have different constraints, eg selectively disable some of them?
HelloNurse 3y ago
Typically there should be a maximum size in bytes for performance reasons (e.g. how many rows fit in a page) and/or a minimum size in "characters" (very ill defined but usually approximated as code points, with some slack to allow a reasonable amount of combining marks in addition to the desired number of letters), with no guarantee that the two sizes are compatible. I've often found in practice short UTF-8 columns with a bytes length, that "accidentally" truncated text with ctastrophic effects after the application checked value lengths in characters.
layer8 3y ago
It makes it less intuitive for the user if you have input fields limited to n bytes instead of to n characters (complications caused by combining characters notwithstanding).
- colejohnson66 3y ago
  Twitter used(?) to have this problem: emojis, not being in the BMP, would count as two "characters" because they were encoded as UTF-16 surrogate pairs.
franciscop 3y ago
Everyone replying here seems to be quoting "business reasons" which seem very English-centric. Try to talk to a Japanese or Korean person about their "business reasons". I am suffering the opposite problem. My name has 36+ letters and I live in Japan, where the average full name has "3-4 characters" (Kanji) so they tend to be safe and allow for 10-15 characters, if I'm lucky maybe even 10 for given and 10 for family names (20 in total), where I don't have to totally murder my name and just need to amputate it.

hans_castorp 3y ago

> I'd say that "character length" for database field size is a weird concept Until you want a constraint for such a column. From a business perspective, you would limit the column to a specific number of characters, not bytes

bruce511 3y ago
Let me be clear. You currently cannot constrain for number of characters in a unicode string. From a business perspective it's unhelpful to think of constraining in that way. By all means constrain on bytes, and if that's close enough for you, use that as a proxy. Or provision lots of space, and count characters in the UI.
qalmakka 3y ago
This is ALWAYS broken on unicode. It only makes sense in ASCII and with alphabets similar to the Latin alphabet.
- Dylan16807 3y ago
  It's not broken if "character" means "extended grapheme cluster". Though in that case it makes it very hard to index the column.
  - qalmakka 3y ago
    Nobody cares about them, I am afraid. Most people _really_ can't accept that you can't work on Unicode like you did with ASCII, and do not want to let go of their old C habits (like iterating char by char and doing something).
PeterisP 3y ago
If you want a constraint for such a column, then due to nature of our complex writing, it has to be separate from the actual field type and physical storage allocation - it's the equivalent of an integer field having a constraint that it must be between 1 and 100.
twic 3y ago
Is there ever a genuine, non-arbitrary business requirement to limit any string to a certain number of unicode characters? And by characters here we probably mean extended grapheme clusters. If the data is going to get printed in a monospace font, on a passport or credit card or something, then i can understand a limit on the number of characters. But then it's not full unicode either - you want to constrain the length in some specific character set. Otherwise, i think limits are always arbitrary. I suspect there is a strong cultural holdover from the days of punched cards here.
- bruce511 3y ago
  >> If the data is going to get printed in a monospace font, on a passport or credit card In the case of limited space, you absolutely can count characters and limit them in the UI. For data storage though you would provision "enough bytes". Think approximately 4 bytes per character, plus a bit extra just in case. But I agree in most cases the length limits are completely arbitrary and simply made up by the programmer at the time the database is designed. (Hint: They are _always_ too short, especially if the programmer lacks experience.)
  - Dylan16807 3y ago
    > Think approximately 4 bytes per character, plus a bit extra just in case. I hope you don't have too much korean or compound emojis or some other things in that data, then. One of the unicode annexes needed to set a (partial) limit on code points in a single grapheme and the number it picked was 31.
scrollaway 3y ago
Business constraints should be at the server layer (eg api constraints), not the db layer. You want the name to be max 200 characters, the birth year to be minimum 1900, and the email address to not contain the domain name hotmail.com? Don’t do all this at the db layer! What if you want some users to have different constraints, eg selectively disable some of them?

HelloNurse 3y ago

Typically there should be a maximum size in bytes for performance reasons (e.g. how many rows fit in a page) and/or a minimum size in "characters" (very ill defined but usually approximated as code points, with some slack to allow a reasonable amount of combining marks in addition to the desired number of letters), with no guarantee that the two sizes are compatible. I've often found in practice short UTF-8 columns with a bytes length, that "accidentally" truncated text with ctastrophic effects after the application checked value lengths in characters.

layer8 3y ago

It makes it less intuitive for the user if you have input fields limited to n bytes instead of to n characters (complications caused by combining characters notwithstanding).

colejohnson66 3y ago
Twitter used(?) to have this problem: emojis, not being in the BMP, would count as two "characters" because they were encoded as UTF-16 surrogate pairs.

franciscop 3y ago

Everyone replying here seems to be quoting "business reasons" which seem very English-centric. Try to talk to a Japanese or Korean person about their "business reasons". I am suffering the opposite problem. My name has 36+ letters and I live in Japan, where the average full name has "3-4 characters" (Kanji) so they tend to be safe and allow for 10-15 characters, if I'm lucky maybe even 10 for given and 10 for family names (20 in total), where I don't have to totally murder my name and just need to amputate it.