IT Blog

Here is an example of changing the MySQL encoding of a database and tables.
Before any actions on important data it is necessary to make a backup copy, for example:

mysqldump -u USER -h localhost -p BASE | gzip -c > backup_base_`date +%Y-%m-%d`.sql.gz

For the test, we will connect to MySQL and create a couple of new databases without specifying the encoding and specifying:

mysql -u root -p
CREATE DATABASE test_db1;
CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;

Create a test table in the first database and see its encoding:

USE test_db1;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status like 'users';

Create a test table in the second database and see its encoding:

USE test_db2;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status;

Let’s also look at the encoding of both databases:

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db1";
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db2";

To see the encoding of a column in a specific table, you can do this:

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "test_db1"
AND table_name = "users"
AND column_name = "firstname";

In my case, the table in the first database was encoded with latin1_swedish_ci, since it is standard, and in the second one utf8_general_ci, since I specified it beforehand.

You can see the table of possible encodings by such requests:

show collation;
show collation like 'utf8%';
show collation like 'latin1%';

View existing databases as follows:

show databases;

View existing tables in the database:

USE test_db1;
show tables;

Now we change the encoding of the first database and its tables to utf8 and immediately check:

ALTER DATABASE `test_db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db1;
ALTER TABLE `test_db1`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
show table status;

If you need to change the encoding in a sql file, open it in the Notepad ++ editor, for example, convert it to UTF-8/without BOM, and if the encoding in SET NAMES is specified at the beginning of the file, change it there, then you can import the file into the database.