php 如何将 latin1_swedish_ci 数据转换为 utf8_general_ci？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12756877/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert latin1_swedish_ci data into utf8_general_ci?
提问by aslamdoctor
I have a MySQL database with all the table fields collation as
我有一个 MySQL 数据库，其中所有表字段的排序规则为
latin1_swedish_ci
It has almost 1000 of the records already stored and now I want to convert all these data into
它已经存储了将近 1000 条记录，现在我想将所有这些数据转换为
utf8_general_ci
So that I can display any language content. I have already altered the field collations into utf8_general_cibut this does not CONVERTall the old records into utf8_general_ci
这样我就可以显示任何语言的内容。我已经改变了该领域的归类为utf8_general_ci但这并不CONVERT所有的旧记录到utf8_general_ci
回答by E_ONE
one funny thing.
一件有趣的事情。
CONVERT TO CHARSET and CONVERT()/CAST() suggested by Anshu will work fine if charset in the table is in right encoding.
如果表中的字符集编码正确，则 Anshu 建议的 CONVERT TO CHARSET 和 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.
如果由于某种原因 latin1 列包含 utf8 文本，则 CONVERT() 和 CAST() 将无法提供帮助。我用那个设置“弄乱”了我的数据库，所以花更多的时间来解决这个问题。
to fix this in addition to character set conversion, there are several exercises required.
为了解决这个问题，除了字符集转换之外，还需要进行一些练习。
- "Hard one" is to recreate the database from dump that will be converted via console
- "Simple one" is to convert row by row or table by table:
- “难点”是从转储中重新创建将通过控制台转换的数据库
- “简单一个”就是逐行或逐表转换：
INSERT INTO UTF8_TABLE (UTF8_FIELD)
SELECT convert(cast(convert(LATIN1_FIELD using latin1) as binary) using utf8)
FROM LATIN1_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.
基本上，这两种情况都会将字符串处理为原始符号，然后进行正确的编码，这不会发生在simple convert(field using encoding) from table;命令中。
回答by Akhlaque Karim
Export your table. Drop the table. Open the export file in the editor. Edit it manually where the table structure is created.
导出您的表。放下桌子。在编辑器中打开导出文件。在创建表结构的地方手动编辑它。
old query:
旧查询：
CREATE TABLE `message` (
`message_id` int(11) NOT NULL,
`message_thread_id` int(11) NOT NULL,
`message_from` int(11) NOT NULL,
`message_to` int(11) NOT NULL,
`message_text` longtext NOT NULL,
`message_time` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
new query: ( suppose you want to change message_text field. )
新查询：（假设您想更改 message_text 字段。）
CREATE TABLE `message` (
`message_id` int(11) NOT NULL,
`message_thread_id` int(11) NOT NULL,
`message_from` int(11) NOT NULL,
`message_to` int(11) NOT NULL,
`message_text` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`message_time` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
save the file and import back to the database.
保存文件并导入回数据库。
