How Can I Perform Accent-Sensitive Full-Text Searches in MySQL?

Using Collation for Accent-Sensitive Searches in MySQL

When conducting full-text searches in MySQL, it may be necessary to consider character accent sensitivity. In this situation, you may encounter a challenge when attempting to differentiate between accented and unaccented words, as demonstrated by the example provided in the question.

To address this issue and ensure that searches are accent-sensitive, you have two options:

1. Declare Accent-Sensitive Collation:

   Modify the collation of the field in question to use a binary collation, such as utf8_bin. This will compare the UTF-8-encoded bytes as raw data, resulting in accent sensitivity.

   ALTER TABLE words
   ALTER COLUMN word VARCHAR(10) COLLATE utf8_bin;

   Copy after login

2. Use Collate Clause in Query:

   If your searches are typically accent-insensitive but you need an exception for a specific query, you can append the COLLATE clause to the problematic field in your query:

   SELECT *  FROM `words`
   WHERE `word` = 'abád' COLLATE utf8_bin;

   Copy after login

Additional Considerations for MySQL 8.0 and Future Versions:

• Always match the character set with the beginning of the collation name.
• Collations ending in _bin (e.g., utf8mb4_bin) ignore both case and accents.
• Collations containing _as_ (e.g., utf8mb4_as_ci) ignore accents but maintain case sensitivity.
• Consider using utf8mb4 as the default character set, as it provides better Unicode support.
• Avoid relying on conversion routines like CONVERT() by specifying the proper collation during column creation or table definition.

The above is the detailed content of How Can I Perform Accent-Sensitive Full-Text Searches in MySQL?. For more information, please follow other related articles on the PHP Chinese website!