CODEWITHSUNDEEP
mysqlsqldatabasecharacter-encodingcollation
user3159519
user3159519

Reputation: 63

How to change Column Collation without losing or changing data?

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;

Upvotes: 3

Views: 11024

Answers (1)

Rick James
Rick James

Reputation: 142483

Please do SELECT HEX(col), col FROM ... to see what is stored for "école". Latin1 would look like E9636F6C65. Hebrew, if I am not mistaken, has not include 'é'. See http://collation-charts.org/mysql60/mysql604.hebrew_general_ci.html for what is probably the complete set of characters supported.

Assuming that is correct, do no try to convert to CHARACTER SET hebrew; you will lose information, such as 'é' being turned into '?'.

If you need to store both Hebrew characters and French accented characters (etc), use utf8.

Upvotes: 0

Related Questions