Convert MySQL database from latin1 to utf8

Question

Convert MySQL database from latin1 to utf8

Navigation

#1 by (2 votes)

1

I have a MySQL database that is currently set to charset latin1. When I make a select in a table, for example:

SELECT nome FROM acolhidos

The accented characters appear like this:

ALESSANDRO ROGÃ‰RIO MOTA DA SILVA
DANIEL MENDONÃ‡A NOVAES
JOSÃ‰ SILVA ARAUJO

So, if I need to run a select in PHP, for example:

$nome = '%JOSÉ%';    

$sql = "SELECT nome FROM acolhidos WHERE nome LIKE '%$nome%'";

$query = @mysqli_query($conn->link, $sql);

José will not appear in the query result, because it was saved in the database as JOSÃ ‰

Is there a function that converts my $ name variable so that the accented characters are in this format: JOSÉ, ROGER etc, so the above select can bring the names with accented characters in the search result? / p>

But if there is no such function, it would solve my problem in a simpler way ... how could I convert the contents of my entire database to utf8?

I tried using the command:

ALTER DATABASE 'bancodedados' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

However, after running the above command, the accented characters continue to appear like this: "JOSÉ ‰" etc - ie - were not converted to "JOSÉ" etc.

Is there any other command in MySQL that I could run beyond this, to do the conversion directly to the database?

This is the current database configuration:

show variables like '%character%'    

character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: latin1
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/

show variables like 'collation%'

collation_connection: utf8mb4_general_ci
collation_database: latin1_swedish_ci
collation_server: latin1_swedish_ci

php mysql

asked by anonymous 29.06.2018 / 14:55

1 answer

How to detect a click or other mouse event in NotifyIcon from win32gui? function that returns mapping letter of the drivers in delphi

score 2 · Answer 1

Thanks for everyone's comments!

I was able to solve the problem by just including 'collate latin1_swedish_ci' at the end of select:

$ sql="SELECT name FROM host WHERE name LIKE '% $ name%' collate latin1_swedish_ci";