MySQL database character set problems

How to change table’s collation in MySQL.

alter table book_category convert to character set utf8 collate utf8_unicode_ci;

Get the encoding converted properly from latin1 to uft8.
Fixing the problem on import.
first dump the database with

mysqldump -u mysql_username -p --default-character-set=latin1 example_db > example_db.mysql

Open the dump file and change the latin1 in the following line to utf8

/*!40101 SET NAMES latin1 */;

Then import the file into mysql with

mysql -u mysql_username -p new_db < example_db.mysql

Done.

Fixing one column
For a varchar(255) column:

ALTER TABLE example_table MODIFY column_name BINARY(255);
ALTER TABLE example_table MODIFY column_name VARCHAR(255) CHARACTER SET utf8;

For a text column:

ALTER TABLE example_table MODIFY text_column_name BLOB;
ALTER TABLE example_table MODIFY text_column_name TEXT CHARACTER SET utf8;

Leave a Comment

Before leaving a comment, please ensure you have read and understand my comments policy and my privacy policy. Any comment that does not abide by the comment policy will be deleted immediately.

Related Posts