TiDB编解码器能否保证UTF-8字符串内存可比较？及UTF-8键编码建议咨询

1. Does TiDB's codec guarantee memory comparability for UTF-8 strings? #

Short answer: It depends on the collation you're using.

TiDB's codec encodes strings into byte sequences aligned with the specified collation rules, which dictates whether in-memory byte comparisons match the logical string order:

• If you're using a binary collation (like utf8mb4_bin), the encoded bytes directly map to the UTF-8 code points of the string. This means in-memory byte comparisons will exactly match the lexicographical order of the original UTF-8 characters—so memory comparability is fully guaranteed here.
• For non-binary collations (e.g., utf8mb4_general_ci, utf8mb4_unicode_ci), the codec applies character normalization (like case folding, accent removal, or custom character mappings) during encoding. The resulting byte sequence's in-memory comparison will reflect the collation's sorted order, not the raw UTF-8 code point order. In this scenario, raw memory comparison won't align with SQL-level sorting results.

2. What encoding recommendations exist for UTF-8 keys in TiDB? #

If you're using UTF-8 strings as keys (primary keys, secondary indexes, etc.), here are practical, battle-tested recommendations to avoid issues and optimize performance:

• Stick to utf8mb4 as the character set: Unlike the older utf8 (which only supports 3-byte Unicode characters), utf8mb4 fully supports all 4-byte Unicode characters (including emojis and rare scripts). This prevents data truncation or encoding errors for complex characters.
• Use binary collation for predictable ordering: If you need strict lexicographical order matching raw UTF-8 code points, opt for utf8mb4_bin. This ensures consistent behavior between in-memory byte comparisons and SQL sorting, and avoids unexpected ordering from collation-specific mappings.
• Keep key lengths reasonable: UTF-8 strings are variable-length, and longer keys increase storage overhead and slow down index lookups. Try to limit key string lengths where possible—for example, use abbreviations or hash values for long descriptive strings if uniqueness allows.
• Avoid inconsistent collations across related columns: Ensure all columns used in composite keys or joined queries share the same collation. Mismatched collations trigger implicit conversions, which hurt performance and may cause unexpected sorting results.
• Test edge cases with special characters: Validate how your keys handle edge cases like control characters, emojis, or characters from non-Latin scripts. This helps catch encoding issues early before they impact production data.

内容的提问来源于stack exchange，提问作者Sharewin