mysql - How to change Column Collation without losing or changing data?

Question

I have been using mysql version 5.5.41 and have run into an issue. I change the collation of a specific column in my table from latin1_swedish_ci to hebrew_bin, and this changes the data in that column. For instance I inserted école in the field, and on conversion, I got ?cole. So I searched for a solution and found this. You can see it states that to not loose data on changing charsets and collations, you must convert to blob and then to the required charset. I tried that too, only to get ?cole. So how can I change column collations without loosing data.

These were my queries for the blob attempt: -

ALTER TABLE `something` CHANGE `name` `name` BLOB;
ALTER TABLE `something` CHANGE `name` `name` VARCHAR(12) CHARACTER SET hebrew COLLATE hebrew_bin NOT NULL;

Answer 1

You must change CHANGE by MODIFY

The first step is to convert the column to a binary data type, which removes the existing character set information without performing any character conversion:

ALTER TABLE something MODIFY name BLOB;

The next step is to convert the column to a nonbinary data type with the proper character set:

ALTER TABLE something MODIFY name VARCHAR(12) CHARACTER SET hebrew COLLATE hebrew_bin;

Or Try with this:

ALTER TABLE something MODIFY name VARCHAR(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci

Read more at:

http://dev.mysql.com/doc/refman/5.5/en/charset-conversion.html

http://dev.mysql.com/doc/refman/5.5/en/charset-column.html

Please note that running any MODIFY or CHANGE operation on a column will (in a practical sense) remove any default value or comment on the column, as per the documentation.

When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward.