Correcting Wrong Character Encoding In MySQL

16th March 2009

Sometimes, especially when moving data from one server to another, you might find that you have encoded your MySQL database incorrectly. This problem with first show itself if you have the database encoded in one charset and your website set to display in another. If this is the case then you will find strange characters appearing in your text, especially when using punctuation marks. If you are unable or unwilling to change the character encoding on the site then you need to change how the data is encoded in the database.

The most common sort of thing you might want to do is change from iso-8859-1 (or windows-1252) to UTF-8. This can be done in one of two ways.

The first way is to simply alter the table so that the column contains a different charset.

  1. <p>However, if your database has already been set up and your data has already been inserted in the wrong format then you can also update the data in the column using the CONVERT command. The following snippet turns our latin1 data into uncoded binary data and then into utf8.</p>
  2.  
  3. <pre language=">
  4.  
  5. You should also make sure that the connection to the database is done through a specific character set. This is done by using the SET NAMES command and the SET CHARACTER SET.
  6.  
  7. Connection Character Sets and Collations page on the MySQL website. This ensures that the data we get back from the database is also in the correct charset.
  8.  
  9. For a full list of the different character sets available in MySQL just run the command:
  10.  
  11.  
  12.  
  13. You can even use a LIKE statement to refine the collation data into the information you are looking for.

Comments

Permalink
Awesome! I had issue with spanish words, and thie method helped.

Kru (Mon, 08/21/2017 - 08:47)

Add new comment

The content of this field is kept private and will not be shown publicly.