如果我更改 MySQL 中列的排序规则，现有数据会发生什么情况?

2022-01-05 00:00:00 mysql phpmyadmin collation

我正在使用 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.

相关文章