How to change MySQL database encoding

When migrating websites or working with older projects, you may occasionally encounter issues with incorrect character display. This usually happens due to an improper database encoding. Below are a few simple ways to change your MySQL database encoding — using phpMyAdmin or SQL queries.

A database is a collection of interrelated tables where all your website’s data is stored.

---

Changing the Encoding via phpMyAdmin and a Text Editor

First, export your database through phpMyAdmin to your local computer.

Open the exported SQL file in a text editor (for example, Notepadqq or Notepad2) and convert its encoding to UTF-8 without BOM.

Then, import the file back into a newly created database via phpMyAdmin.

---

Changing the Encoding via an SQL Query

In phpMyAdmin → select your database → SQL tab, run the following query:

ALTER TABLE `db_name`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

To convert the encoding of all tables at once, use this query:

SELECT CONCAT(
  'ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, 
  '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) AS sqlcode
FROM `information_schema`.`TABLES` t
WHERE t.`TABLE_SCHEMA` = 'DATABASE_NAME'
ORDER BY 1;

Copy the generated lines and run them in phpMyAdmin’s SQL console.

---