CODEWITHSUNDEEP
mysqlutf-8
brant
brant

Reputation: 537

MySQL, Altering Table from Latin-1 to UTF-8

I would like to rid new entries into my database of Latin-1 characters and just allow UTF-8. I plan to Alter table and make the following changes:

Charset: latin1 -> utf-8

Collation: latin1_swdish_ci -> utf8_general_ci

The table in question has 1 million rows. Is this a good idea? What are the risks of doing this? What happens to data that I try to input that is not in utf-8? What happens to data that has been previously entered that is not utf-8?

Upvotes: 0

Views: 986

Answers (2)

Andy Jarrett
Andy Jarrett

Reputation: 873

Another way is to ALTER the charset. The following 2 statements should convert you table and all columns.

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

If you have foreign key though be warned as you'll need to drop them, run the above SQL, then re-create them afterwards.

Upvotes: 1

santiagobasulto
santiagobasulto

Reputation: 11726

You should create a table duplicate, start a transaction, insert all the rows from the old table, and then commit. The safest way.

To summarize:

CREATE TABLE duplicate ... (with the charset you like, etc)

SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO duplicate (...field-list...)
   SELECT (...field-list...) from original_table;
COMMIT;

ALTER TABLE original RENAME TO original_backup;
ALTER TABLE duplicate RENAME TO original;

You must be careful with unique indexes and autoincrement fields. Be sure to create the duplicate table without indexes, to make the inserts quick, then add them.

Upvotes: 3

Related Questions