👀 Views: 36 💬 Answers: 1 📅 Created: 2025-06-12

mysql utf-8 like SQL

I'm running into a question with MySQL 5.7 where queries using the `LIKE` operator don't seem to work as expected when filtering UTF-8 characters. I have a table with a `name` column that contains various Unicode characters, and I want to retrieve rows where the name matches a specified pattern. For example, I have the following query: ```sql SELECT * FROM users WHERE name LIKE '%特%'; ``` This query is supposed to return all users whose names include the character "特" (which means 'special' in Chinese). However, running this query returns an empty set, even though I know there are valid entries in the `name` column that contain this character. I've checked that my database and table are using `utf8mb4` character set: ```sql SHOW VARIABLES LIKE 'character_set_database'; SHOW TABLE STATUS LIKE 'users'; ``` Both return `utf8mb4`. I've also confirmed that the collation for my `name` column is set to `utf8mb4_general_ci`. I tried using `BINARY` to force a case-sensitive comparison: ```sql SELECT * FROM users WHERE BINARY name LIKE '%特%'; ``` But that didn't work either. To troubleshoot, I also executed: ```sql SELECT HEX(name) FROM users WHERE name LIKE '%特%'; ``` This returned valid hexadecimal representations of names, so it seems like the data is stored correctly. As a last resort, I attempted to use `CONVERT(name USING utf8mb4)`: ```sql SELECT * FROM users WHERE CONVERT(name USING utf8mb4) LIKE '%特%'; ``` Still no luck. I'm unsure if there's a configuration scenario or if I'm missing something fundamental about how MySQL handles UTF-8 characters in `LIKE` queries. Any insights would be appreciated! This issue appeared after updating to Sql stable. What's the best practice here?