MySQL中latin1与utf8大小写不敏感差异及转表索引冲突咨询

Great question—this is a classic headache when migrating between MySQL character sets, and it all comes down to collations (not just the character sets themselves) and how they define character equivalence. Let’s break this down step by step.

First: Character Sets vs. Collations #

First, a quick clarification: The character set (like latin1 or utf8mb4) defines how characters are stored as binary data. But the collation (like latin1_general_ci or utf8mb4_unicode_ci) defines how those characters are compared, sorted, and whether they’re considered "equal" for operations like unique indexes or WHERE clauses.

When you talk about "case insensitivity," you’re actually referring to the collation’s rules—specifically, the _ci suffix stands for "case-insensitive." But collations also handle accent sensitivity, character equivalence, and even language-specific spelling variants.

Why "Case Insensitivity" (And Character Equivalence) Varies Between latin1 and utf8 #

The key difference lies in the standards each collation is based on:

• latin1 collations (e.g., latin1_general_ci, latin1_swedish_ci): These are rooted in old Western European character set standards, designed to match traditional language usage where certain characters or spelling variants were considered interchangeable. For example:
  • latin1_general_ci treats o = ö and o = oe as equivalent. This is because in languages like German, ö is often written as oe in informal contexts, and early collations wanted to reflect that equivalence for sorting and uniqueness.
  • These collations have a very broad definition of "equality" that goes beyond just case—they merge accented characters with their unaccented counterparts, and even some spelling variants.
• utf8mb4 collations (e.g., utf8mb4_general_ci, utf8mb4_unicode_ci): These follow Unicode standards, which have a much more precise definition of character equivalence. Unicode treats ö as a distinct character separate from o or oe—they’re not considered equal unless explicitly specified by a collation that’s accent-insensitive and handles those specific variants (like utf8mb4_german2_ci, which does recognize ö = oe for German language rules).

In short: latin1 collations prioritize traditional language interchangeability, while Unicode-based collations prioritize precise character identity.

Why This Causes Unique Index Conflicts #

Unique indexes in MySQL enforce uniqueness based on the collation’s equivalence rules, not the raw binary value of the characters. Here’s how that leads to conflicts during migration:

1. In your latin1 table: If you have a unique index on a column, MySQL prevents inserting values that the collation considers equal. So if you already have 'o' in the column, trying to insert 'ö' or 'oe' would throw a duplicate key error—because the collation sees them as the same value.
2. When converting to utf8mb4: If your source data (maybe from external systems, or old exports) contains both 'o', 'ö', and 'oe' values that were never stored together in the latin1 table (thanks to the unique index), when you try to load all these into a utf8mb4 table, they’re now treated as distinct values. But if your business logic expects them to be unique (like treating them as the same entity), you’ll suddenly have duplicate entries.
3. During ALTER TABLE conversion: If your existing latin1 table somehow has multiple values that the latin1 collation considers equal (though the unique index should have prevented this—maybe from a bug, or binary imports bypassing collation checks), converting to utf8mb4 will reveal these as distinct values, violating the unique index in the target character set.

A Quick Fix Note #

If you need to maintain the latin1-style equivalence for specific languages (like German oe = ö) in utf8mb4, use a language-specific collation like utf8mb4_german2_ci instead of the general ones. This will retain those equivalence rules while giving you the benefits of Unicode.

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