4 ms·
Dear databases, please don't get hung up about string lengths when dealing with UTF8. If I ask for a UTF8 string with a max-length of 100, please don't apply t
by billpg 5y ago
Dear databases, please don't get hung up about string lengths when dealing with UTF8.
If I ask for a UTF8 string with a max-length of 100, please don't apply the worse case scenario and allocate space for 100 emojis. Please give me a box of 100 bytes and allow me to write any UTF-8 string that can fit into 100 bytes in there.
100 ASCII characters. 20 emojis. Any mixture of the two.
If I ask for UTF-8, it'll be because I'd like to make advantage of UTF-8 and I accept the costs. If that means I can't quickly jump to the character at index 84 in a string, no problem, I've accepted the trade-off.
- dchest 5y agoMore like "100 ASCII characters. Unknown number of emojis." You'll get codepoints truncated if you use bytes.
- billpg 5y agoI would hope a database engine would throw an error instead of truncating bytes.
- grogers 5y agoYes! This and other charset oddities almost make me want to store all strings as varbinary and handle any conversions to/from a string type to utf8 bytes client side. It makes ad-hoc query access from a shell much more annoying though.
- billpg 5y agoI wonder if you can do LIKE with a VARBINARY value. "SELECT * FROM Users WHERE Name LIKE @namePattern" @namePattern = UTF8.GetBytes("%billpg%")
- petergeoghegan 5y agoWhy would the database "allocate space" unless it was truly necessary, in any case? While char(n) works that way, most applications don't actually use it, no matter the underlying DB system.
- int_19h 5y agoYou're literally asking them to break the SQL standard. The meaning of N in CHAR(N) is the number of characters.
- mikl 5y agoPostgreSQL recommends you use the text type everywhere, and use CHECK constraints if you really really really need to limit a field’s length on the database level. (VAR)CHAR is considered a code smell. So many fields are artificially limited to 255 bytes because that used to make a performance difference on MySQL 15 years ago.
- billpg 5y agoI feel PostgreSQL should push that as a benefit of their system. I read your comment and said "Where have you been all my life?" "Still using strings with fixed limits like you're a C programmer in the 80s? Use PostgreSQL."