Menu
I have made a mistake when designing my application database several years ago and collation settings of my tables and table fields are mixed. Some of them are utf8_general_ci and some of them are utf8_unicode_ci.
This causes problems when joining tables with different collations. Now, I am planning to change collation settings and make them the same: utf8_unicode_ci. I will be running these two SQL queries on all my tables.
ALTER TABLE `table1` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `table1` CHANGE `Action` `Action` VARCHAR( 250 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
My question is, does running these two SQL queries break any field values, especially the ones which contains accented characters? Or is it safe to run these two queries?
Looking forward to hear from you.
Thanks for your answers!
616
To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace dbname with the database name: Copy ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci; To exit the mysql program, type \q at the mysql> prompt.
Similarly, here's the command to change character set of MySQL table from latin1 to UTF8. Replace table_name with your database table name. mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; Hopefully, the above tutorial will help you change database character set to utf8mb4 (UTF-8).
Replace database_name and table_name below with database and field names respectively. alter table database_name. table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; If you want to change collation of all tables in your database, you need to run the above query for each table separately.
please refer mysql doc which talks about changing character set and collation
http://dev.mysql.com/doc/refman/5.1/en/charset-column.html
Its saying that - "If you use ALTER TABLE to convert a column from one character set to another, MySQL attempts to map the data values, but if the character sets are incompatible, there may be data loss."
--Cheers
115
Collation only affects how strings within a field are compared, not how they're stored. As far as you stay with utf8 encoding, you're safe.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
