如何将 latin1_swedish_ci 数据转换为 utf8_general_ci? [英] How to convert latin1_swedish_ci data into utf8_general_ci?

问题描述

我有一个 MySQL 数据库，其中所有表字段的排序规则都为

I have a MySQL database with all the table fields collation as

latin1_swedish_ci

它已经存储了将近 1000 条记录，现在我想将所有这些数据转换为

It has almost 1000 of the records already stored and now I want to convert all these data into

utf8_general_ci

这样我就可以显示任何语言的内容.我已经将字段排序规则更改为 utf8_general_ci 但这不会将所有旧记录CONVERT 转换为 utf8_general_ci

So that I can display any language content. I have already altered the field collations into utf8_general_ci but this does not CONVERT all the old records into utf8_general_ci

推荐答案

一件有趣的事情.

如果表中的字符集编码正确，则 Anshu 建议的 CONVERT TO CHARSET 和 CONVERT()/CAST() 可以正常工作.

CONVERT TO CHARSET and CONVERT()/CAST() suggested by Anshu will work fine if charset in the table is in right encoding.

如果由于某种原因 latin1 列包含 utf8 文本，CONVERT() 和 CAST() 将无法提供帮助.我用那个设置弄乱"了我的数据库，所以花更多的时间来解决这个问题.

If for some reason latin1 column containts utf8 text, CONVERT() and CAST() will not be able to help. I had "messed" my database with that setup so spend bit more time on solving this.

为了解决这个问题，除了字符集转换之外，还需要进行一些练习.

to fix this in addition to character set conversion, there are several exercises required.

1. 难点"是从转储中重新创建将通过控制台转换的数据库
2. 简单一个"就是逐行或逐表转换:

INSERT INTO UTF8_TABLE (UTF8_FIELD)
SELECT convert(cast(convert(LATIN1_FIELD using latin1) as binary) using utf8)
  FROM LATIN1_TABLE;

基本上，这两种情况都会将字符串处理为原始符号，然后再进行正确的编码，这不会发生在 simple convert(field using encoding) from table; 命令中.

basically, both cases will process string to original symbols and then to right encoding, that won't happen with simple convert(field using encoding) from table; command.

这篇关于如何将 latin1_swedish_ci 数据转换为 utf8_general_ci?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！