面向英、俄、越多语言WordPress-WAMP项目的MySQL排序规则选型咨询

Great question—setting up the right database collation is critical for avoiding weird sorting glitches down the line, especially with a multilingual store. Let’s break this down specifically for your WordPress/WAMP setup supporting English, Russian, and Vietnamese:

1. Skip utf16_general_ci—go with utf8mb4-based collations instead #

While utf16_general_ci technically covers the character sets you need, it’s not the best choice for your use case:

• WordPress compatibility: WordPress core and nearly all popular plugins are built around utf8mb4 (the full UTF-8 implementation in MySQL). Using utf16 can lead to unexpected compatibility issues with plugin database queries, content storage, and even admin interface display.
• Storage overhead: utf16 uses twice as much storage as utf8mb4 for most characters, which is unnecessary and can slow down database operations over time.
• Sorting accuracy: The general_ci family of collations uses simplified sorting logic that cuts corners for complex languages. For Vietnamese (with its tone markers) and Russian (with specific Cyrillic letter ordering), this can lead to incorrect sorting that confuses users.

2. Best collations for your three languages #

Opt for one of these two utf8mb4 collations—they’re far more reliable for multilingual sorting:

• utf8mb4_unicode_520_ci (preferred if your MySQL version is 5.6+): This is based on Unicode 5.2.0 standards, which includes precise sorting rules for Vietnamese tone characters and Russian Cyrillic letters. It’s the most accurate option for your language set.
• utf8mb4_unicode_ci (fallback for older MySQL versions): While it uses an older Unicode standard (4.0.0), it still handles English, Russian, and Vietnamese sorting far better than general_ci variants.

Why these work for your languages: #

• English: All utf8mb4 collations handle English perfectly—no surprises here.
• Russian: Both collations respect the standard Cyrillic alphabet order, including proper differentiation of letters like ё and е (which general_ci might lump together incorrectly).
• Vietnamese: They account for the priority of tone markers in sorting, ensuring words are ordered the way native speakers expect (unlike general_ci, which treats tone-marked characters as identical to their unmarked counterparts).

3. Quick setup steps for WAMP & WordPress #

• When creating your database in WAMP’s phpMyAdmin, set the Character set to utf8mb4 and Collation to utf8mb4_unicode_520_ci.
• During WordPress installation, it should auto-detect these settings, but double-check your wp-config.php file to confirm:

  define('DB_CHARSET', 'utf8mb4');
  define('DB_COLLATE', 'utf8mb4_unicode_520_ci');
  

If you ever expand to more languages later, these collations will also handle most other Unicode-based languages without needing a full database overhaul.

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