MySQL搜索无法匹配全部结果：含Ostéotomie的表查询异常

Hey there! Let's break down why your %Ostéotomie% query only returns 4 results instead of the expected 9, while %Ost% works perfectly.

The Root Cause #

This almost always comes down to character set and collation settings in your MySQL table. Here's what's likely going on:

• Your table or column uses a collation that’s accent-sensitive—it treats é and e as completely distinct characters.
• Your dataset has both versions of the term: some entries use the accented é (Ostéotomie) and others use the plain e (Osteotomie).
• When you use %Ost%, it matches any record starting with "Ost" regardless of the following character, so it catches all 9 entries. But %Ostéotomie% only matches the 4 records with the exact accented é.

Fixes to Try #

1. Force an Accent-Insensitive Collation in Your Query

You can temporarily override the column's collation for the query to treat é and e as identical.

For MySQL 8.0+ (uses modern Unicode collation, recommended):

SELECT * FROM your_table 
WHERE your_column LIKE '%Ostéotomie%' COLLATE utf8mb4_0900_ai_ci;

For older MySQL versions:

SELECT * FROM your_table 
WHERE your_column LIKE '%Ostéotomie%' COLLATE utf8_general_ci;

(Note: Replace your_table and your_column with your actual table/column names.)

2. Use a Regular Expression to Match Both Variants

If you want to explicitly target both accented and non-accented versions, a regex can handle this:

SELECT * FROM your_table 
WHERE your_column REGEXP 'Ost[ée]otomie';

This will match any record containing either Osteotomie or Ostéotomie.

3. Normalize Text with the UNACCENT Function

If your MySQL version supports it (8.0+ or with the unaccent plugin installed), you can strip accents from both the column data and your search term:

SELECT * FROM your_table 
WHERE UNACCENT(your_column) LIKE UNACCENT('%Ostéotomie%');

This converts all accented characters to their non-accented equivalents before matching, so both variants are caught.

First Step: Verify Your Collation #

To confirm the collation of your column, run this query:

SHOW FULL COLUMNS FROM your_table;

Look for the Collation column—if it ends with _as (accent-sensitive) or _bin (binary, which treats all characters as distinct), that’s definitely the source of the issue.

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