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;

Collation has nothing to do with how data is stored. http://dev.mysql.com/doc/refman/5.6/en/charset-general.html — zerkms, Mar 16 '15 at 00:41

score 3 · Accepted Answer · edited Jul 13 '15 at 22:02

3

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.

edited Jul 13 '15 at 22:02

MirroredFate

11,736
14
65
95

answered Mar 16 '15 at 00:48

Adrian Cid Almaguer

9,598
13
48
65

I tried this already but it does not work. I believe some character conversions inevitably change data. What i now mean to know is, is there any way for me to know if changing a collation to another will corrupt data? some sql query or programming library? – user3159519 Mar 16 '15 at 01:46
@user3159519 I edit my answer, try with this: ALTER TABLE something MODIFY name VARCHAR(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci – Adrian Cid Almaguer Mar 16 '15 at 20:03
Hey @Adrian Cid Almaguer, can the two queries you have mentioned be merged too? – user3159519 Mar 22 '15 at 02:42
@user3159519 I don't know that do you mean with merge, but make a copy of your database and try. If my answer help you, don't forget check as acepted ;-) – Adrian Cid Almaguer Mar 22 '15 at 04:12
**If the data was correct, the 2-step ALTER will _corrupt_ the data**. Instead, **use ALTER TABLE ... CONVERT TO** to change both the definition and the data. – Rick James Jul 02 '15 at 21:11
@AdrianCidAlmaguer Please add that this will remove any defaults or comments on the modified column, as per [the documentation](https://dev.mysql.com/doc/refman/5.1/en/alter-table.html). – MirroredFate Jul 13 '15 at 21:41
@MirroredFate I don't understand what I need update, can you update the answer please? – Adrian Cid Almaguer Jul 13 '15 at 21:42
@AdrianCidAlmaguer Done. – MirroredFate Jul 13 '15 at 21:46
@MirroredFate thanks, now is better for the users ;-) – Adrian Cid Almaguer Jul 13 '15 at 21:54

score 0 · Answer 2 · answered Mar 16 '15 at 01:13

0

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.

answered Mar 16 '15 at 01:13

Rick James

122,779
10
116
195

Okay, thanks, that was very helpful. This means some conversions inevitably change data no matter what. However, I guess now what I mean to know is, how can I know that a particular charset conversion wont change my data. is there an sql query or some php library that can determine that for me as i give it input? – user3159519 Mar 16 '15 at 01:34
It sounds like some rows are in latin1, some are in hebrew. That makes it difficult to fix. Do you think you can identify which rows are which? – Rick James Mar 16 '15 at 03:23

How to change Column Collation without losing or changing data?

2 Answers2

Linked