Convert Mysql database from latin1 to utf8

Asked

Viewed 1,797 times

2

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

SELECT nome FROM acolhidos

Accented characters appear this way:

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 bank as JOSÃ

Is there any function that converts my variable $name, so that accented characters stay in this format: JOSÃ , ROGÃ RIO etc, and so the select above can bring the names with accented characters in the search result?

But if there is no such function, it would solve my problem in a simpler way... as I could 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 this way:"JOSÃ " etc - that is - have not been converted to "JOSÉ" etc.

Is there any other command in Mysql that I could run besides this, to make the conversion directly in 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
  • 1

    After connecting to the bank try to use: mysqli_query($connected,"SET NAMES 'utf8'");

  • 2

    First you need to understand if the problem is the conversion or if it is the client environment. Just be careful because there are 2 situations: one is changing the definition to UTF (without messing with the data), the other is actually converting line by line. You would have to look (for example visualizing in hexadecimal) at the real value of the fields to determine the best action. If the data is correct and the definition is wrong, you just have to change the definition. I keep thinking, wouldn’t it be simpler to work in Latin in PHP instead of converting? Not everyone needs UTF. Mostly Western languages.

  • 1

    For base conversion, you can use the software Heidisql, you can change all the settings in the database properties, it has its own script for this. For the data, the last version I used (two years ago) had no support. Maybe now have.

1 answer

2

Thank you for everyone’s comments!

I managed to solve the problem only by including 'collate latin1_swedish_ci' at the end of select:

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

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.