Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

mysql - 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;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...