如果我更改 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 网络客户端进行更改.
I will change with phpMyAdmin web client.
推荐答案
在 MySQL 5.1 中运行快速测试,将 VARCHAR 列设置为 latin1_bin
我插入了一些非拉丁字符
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.
相关文章