如果更改MySQL中列的排序规则，将对现有数据产生什么影响? [英] What will happen to existing data if I change the collation of a column in MySQL?

问题描述

我正在使用MySQL数据库服务器运行生产应用程序.我忘了将列的排序规则从latin设置为utf8_unicode，这在保存到具有多语言数据的列时会导致奇怪的数据.

I am running a production application with MySQL database server. I forget to set column's collation from latin to utf8_unicode, which results in strange data when saving to the column with multi-language data.

我的问题是，如果现在我将排序规则更改为utf8_unicode，我的现有数据将如何处理?它会破坏或破坏现有数据还是保留数据，但是新数据将按应有的方式另存为utf8?

My question is, what will happen with my existing data if I change my collation to utf8_unicode now? Will it destroy or corrupt the existing data or will the data remain, but the new data will be saved as utf8 as it should?

我将使用phpMyAdmin Web客户端进行更改.

I will change with phpMyAdmin web client.

推荐答案

在VARCHAR列设置为latin1_bin的MySQL 5.1中运行快速测试，我插入了一些非拉丁字符

Running a quick test in MySQL 5.1 with a VARCHAR column set to latin1_bin I inserted some non-latin chars

INSERT INTO Test VALUES ('英國華僑');

我选择了它们并得到了垃圾(如预期的那样).

I select them and get rubbish (as expected).

SELECT text from Test;

给予

text
????

然后我将列的排序规则更改为utf8_unicode并重新运行SELECT，它显示出相同的结果

I then changed the collation of the column to utf8_unicode and re-ran the SELECT and it shows the same result

text
????

这是我所期望的-它会保留数据，并且数据将保持垃圾状态，因为在插入数据时，该列会丢失额外的字符信息，而只是插入一个??每个非拉丁字符，没有办法使用????再次成为英国华侨.

This is what I would expect - It will keep the data and the data will remain rubbish, because when the data was inserted the column lost the extra character information and just inserted a ? for each non-latin character and there is no way for the ???? to again become 英國華僑.

您的数据将保留在原处，但不会被固定.

Your data will stay in place but it won't be fixed.

这篇关于如果更改MySQL中列的排序规则，将对现有数据产生什么影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！