The optimal collation for your MariaDB database depends on your specific use case, but here are the most recommended options:

utf8mb4_unicode_ci is typically the best choice because:

• Supports full Unicode (including emojis and special characters)
• Provides proper linguistic sorting for most languages
• Is case-insensitive (ci) which is usually desired
• Handles most accented characters correctly
• Treats accented characters more intelligently (é, è, ê treated as similar to e)
• Handles special cases in many languages correctly
• More computationally intensive but still very efficient
• Better for multilingual applications (correctly handles German ß, Turkish ı, etc.)

utf8mb4_general_ci may offer slightly better performance because:

• Simpler comparison rules than unicode_ci
• Still supports full Unicode
• Good if you don't need strict linguistic sorting
• Sorts primarily based on code point values
• Less accurate for linguistic sorting
• Slightly faster (5-10% in some benchmarks) because of simpler rules
• May sort these special characters in unexpected ways

utf8mb4_bin: For binary comparisons (case-sensitive, accent-sensitive)

utf8mb4_spanish_ci: Optimized for Spanish

utf8mb4_german2_ci: For German dictionary sorting

Always use utf8mb4 instead of utf8 in MariaDB to ensure full Unicode support (including 4-byte characters like emojis)

The collation should be consistent across your database, tables, and connection settings

For case-sensitive searches, you'll need a _bin collation or use the BINARY operator

-- With utf8mb4_general_ci:
SELECT 'ß' = 'ss'; -- Returns 1 (TRUE)
SELECT 'ı' = 'i';  -- Returns 1 (TRUE)

-- With utf8mb4_unicode_ci:
SELECT 'ß' = 'ss'; -- Returns 0 (FALSE) - more correct for German
SELECT 'ı' = 'i';  -- Returns 0 (FALSE) - important for Turkish