Problems retrieving data that contains unicode in mysql


Dear friends, they passed me a site where I have to repair the search function and I run into the following problem:

There is a column in the bd, for this example 'name', which stores the data in the following way: {"is": "Balloon Puc \ u00f3n and Curarrehue 2014"}

As you can see the word "Pucón" is stored with the accent in the unicode format (\ u00f3) if I'm not mistaken. Because of this the query that I put below does not return anything to me

SELECT * FROM projects WHERE name LIKE '%pucon%';

The site uses mysql, the field is of type LONGTEXT, the charset is utf8 with Collation utf8_unicode_ci

How would you retrieve the data that contains characters in unicode?

Thank you in advance!

asked by Camilo Donoso 07.11.2018 в 15:16

1 answer


You can try formatting the data before exporting it example:

global change:


formatiando a utf8 in query:

SELECT CONVERT(name USING utf8) FROM projects name LIKE '%pucon%';

changed completely in the my.ini configuration:

collation_server = utf8mb4_unicode_ci
character-set-server= utf8mb4 

changing from php:

header("Content-Type: text/html;charset=utf-8");

changing from html:

<meta http-equiv="Content-type" content="text/html; charset=utf-8"/>

Changing before from your app:

mysql_query("SET NAMES 'utf8'");

Always remember to clean up:

"SELECT * FROM usuarios WHERE id = ?"
bind_param("s", name );

"SELECT * FROM usuarios WHERE name = :name"
bind_param("s", name );
answered by 07.11.2018 в 15:44