How to clean up encoding in MySQL table

Question

The system is a PHP application accessing a MySQL database. The first tables were created with the standard latin1 encoding and filled via mysqli in PHP, without setting any encoding. The PHP scripts and data all work in UTF-8.

The newer tables have set the encoding to utf8_bin and in front of every transaction SET CHARACTER SET utf8 is sent.

If I look at the newer tables in the mysql database (via a sql explorer like HeidiSQL) every special character is displayed correctly. However, in every older table the typical latin1-utf8 errors are visible like Ãœ for ü.

Is there an easy way to fix this like in one of the following ways:

• Fix the encoding for each table so that is displayed correctly in the sql explorer, but keep the PHP code as it is (with SET CHARACTER SET utf8 or latin1 in front of every statement, fitting the tables encoding. (merely a workaround)
• Switch the encoding to utf8 for all tables -> now SET CHARACTER SET utf8 has to be sent at the beginning of every mysqli connection (or maybe there is a way to set this as standard?)
• Switch the encodung to latin1 for all tables -> no need to send SET CHARACTER SET utf8 in front of transactions anymore, but wrong encoding in the database explorer.

It seems as if the Database takes all tables as utf8 and shows latin1 tables therefore with wrong characters. Mysqli takes all tables as latin1 if not told different.

The application is productive, the encoding problem is not visible to the user, as the right encoding is told to mysqli in front of every statement. But I feel like that is not a good practice.

I recognize that there is something wrong with how the database is set up, and I hope to learn what is the best practice to fix this.

Answer 1

The first tables were created with the standard latin1 encoding and filled via mysqli in PHP

So, those tables have garbage. Do you need to recover the data?

in front of every transaction SET CHARACTER SET utf8 is sent.

That's useless. Instead, do SET NAMES utf8 once after connecting.

Ãœ is Mojibake for Ü. Search this forum for Mojibake.

Having some tables latin1 with utf8 bytes, while other tables are utf8 -- that will be a painful mess going forward. Stop now and clean up the mess. Go utf8 all the way.

Summary of Mojibake:

• The bytes you have in the client are correctly encoded in utf8 (good).
• You connected with SET NAMES latin1 (or set_charset('latin1') or ...), probably by default. (It should have been utf8.)
• xx The column in the table was declared CHARACTER SET latin1. (Or possibly it was inherited from the table/database.) (It should have been utf8.)
• The column in the tables may or may not have been CHARACTER SET utf8, but it should have been that.

If you need to fix for the data it takes a "2-step ALTER", something like

ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...;
ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;

Answer 2

Ok, you cannot use mysqldump to create a .sql file with the relevant tables, as would give you the same garbage characters as in HeidiSQL.

But you can do it with a PHP script, creating a .sql file with the same wrong charset as in your PHP application. That PHP script should do the following:

mb_internal_encoding("UTF-8");
$link = mysqli_connect('localhost', 'root', '');

mysqli_set_charset($link, "latin1");

$result = mysqli_query($link, "SELECT * FROM table1", MYSQLI_USE_RESULT);
while($row = mysqli_fetch_assoc($result))
{
  # Generate INSERT statements for table1 using some PHP logic, and write these into a new file, named e.g. fixencoding.sql
}
mysqli_free_result($result);
mysqli_query($link, "DELETE * FROM table1");
mysqli_query($link, "ALTER TABLE table1 COLLATE='utf8_general_ci'");

$result = mysqli_query($link, "SELECT * FROM table2", MYSQLI_USE_RESULT);
while($row = mysqli_fetch_assoc($result))
{
  # Generate INSERT statements for table2 and append these into the .sql file created above
}
mysqli_free_result($result);
mysqli_query($link, "DELETE * FROM table2");
mysqli_query($link, "ALTER TABLE table2 COLLATE='utf8_general_ci'");

# etc. - repeat above steps for every broken table

mysqli_close($link);

The created fixencoding.sql file is now an utf-8 encoded file without BOM, which you can import using mysql.exe:

C:\path\to\mysql\bin\mysql.exe -hlocalhost -uroot name-of-database <fixencoding.sql 

If all went well, your PHP application should contain the right MySQL charset:

mysqli_set_charset($link, 'utf8');

Also, HeidiSQL and other clients should display the real utf-8 characters.

This is all not tested! Don't forget to do a dry run first, by commenting out the DELETE and ALTER queries!