Fix utf8 broken characters in MySQL

According to this question on stackoverflow: Detecting utf8 broken characters in MySQL. I found that this answer had the correct approach. This approach will transform broken chars like the following into the correct utf8 chars:

á = á
é = é
í- = í
ó = ó
ñ = ñ
á = Á

The idea is to

  1. convert back to latin1
  2. convert to binary
  3. convert to utf8

At least it worked perfect for my setup, so I added a small script to the my class collection simple-php-classes, which transforms a whole MySQL database in the above way. You can find the script on github. Usage:

You will need PHP5 CLI PDO and PDO Mysql

Require the script:

composer require diversen/simple-php-classes

Create config file

mkdir config
touch config/config.ini

Add something like the following to the config/config.ini file:

; database url
url = "mysql:dbname=demo;host=localhost;charset=utf8"
; I am not sure about the next line - some time since i wrote it.
db_init = "SET NAMES utf8"
; username
username = "root"
; password
password = "password"

Make a backup if your database is important!

Run the script:

php vendor/diversen/simple-php-classes/src/scripts/latin1-to-uft8.php

This page has been viewed 7426 times. First hit: 30-Oct-2015 17:02:20
Share email, Google+, Twitter, Facebook.
comments powered by Disqus