mysql - 将包含错误编码数据的 MySQL 表转换为 UTF-8

Question

我有一个很大的 MySQL 5.1 数据库，并且出于各种愚蠢的原因，我相信我一直在 UTF8 表中存储编码为 LATIN1 的 UTF8 字符。真奇怪。我想修复它。

MySQL - Convert latin1 characters on a UTF8 table into UTF8问题似乎有效——一次一个专栏。但是我有 24 个表和几十个列要转换。我真的在寻找一种至少可以一次转换一个表的解决方案。

作为引用，适合我的单列解决方案是:

UPDATE foo SET col1 = CONVERT(CAST(CONVERT(col1 USING latin1) AS binary) USING utf8);

对于表格，我可以这样做:

ALTER TABLE foo CONVERT TO CHARACTER SET latin1;
ALTER TABLE foo CONVERT TO CHARACTER SET binary;
ALTER TABLE foo CHARACTER SET utf8  COLLATE utf8_unicode_ci;

这让我非常接近——但是，CONVERT TO CHARACTER SET binary 步骤将我所有的 VARCHAR 列转为 VARBINARY，并将我的 TEXT 列转为 BLOB。我可以通过并改回它们，一切看起来都很好......但后来我又回到了“让我们单独修改所有列”的世界——在这种情况下，我也可以

我已经对这些 SQL 语句尝试了大约 50 种变体，但我找不到一种既能使我的列保留字符数据类型又能正确编码数据的变体。

有什么建议吗？

更新:决定只修复列而不是等待数据库或表解决方案，我想到了:

#!/usr/bin/env ruby
require 'rubygems'
require 'mysql2'

CONNECT_OPTS = {} # whatever you want
Mysql2::Client.default_query_options.merge!(:as => :array)
conn = Mysql2::Client.new(CONNECT_OPTS)

tables = conn.query("SHOW TABLES").map {|row| row[0] }

# See http://dev.mysql.com/doc/refman/5.0/en/charset-column.html
# One might want to include enum and set columns; I don't have them
TYPES_TO_CONVERT = %w(char varchar text)
tables.each do |table|
  puts "converting #{table}"
  # Get all the columns and we'll filter for the ones we want
  columns = conn.query("DESCRIBE #{table}")
  columns_to_convert = columns.find_all {|row|
    TYPES_TO_CONVERT.include? row[1].gsub(/\(\d+\)/, '')
  }.map {|row| row[0]}
  next if columns_to_convert.empty?

  query = "UPDATE `#{table}` SET "
  query += columns_to_convert.map {|col|
    "`#{col}` = convert(cast(convert(`#{col}` using latin1) as binary) using utf8)"
  }.join ", "
  puts query
  conn.query query
end

... 完成工作。有趣的是，这在我的数据库上运行了 36 秒，而不是花费 13 分钟的 ALTER TABLE 路由(并且有 VARBINARY 问题)或 mysqldump 解决方案，如果我能让它们运行的​​话，这将花费超过 20 秒。

如果有人知道一种优雅的方式一步完成整个数据库或表，我仍然会接受答案。

Answer 1

下面的这个方法看起来很有前途，而且更好，它的简单性很漂亮。这个想法是您将整个数据库 mysqldump 为 latin1，然后将其重新编码为 utf-8 导入。

导出:

mysqldump -u [user] -p --opt --quote-names --skip-set-charset --default-character-set=latin1 [database] > dump.sql

导入:

mysql -u [user] -p --default-character-set=utf8 [database] < dump.sql

我不相信这个解决方案，它完全来自 Gareth Price's blog .到目前为止，它对所有给他留下评论的人都有效:“哇，你刚刚救了我的命。我没有花 2 个小时，而是 2 天” 引起了我的注意。

更新 #1: 看起来像 Gareth wasn't the first发现这一点。

更新 #2: 我刚刚尝试过，它在我的 UTF8-stored-as-latin1 数据库中运行良好。只需确保在导入之前将数据库上的默认字符集切换为 utf8，否则会在特殊字符所在的位置出现普通问号。当然，这可能会产生很多其他后果，因此请先进行测试。

ALTER SCHEMA [database] DEFAULT CHARACTER SET utf8;

如果您有任何未设置为架构默认值的表:

ALTER TABLE [table] CHARACTER SET = DEFAULT;

(如果您有任何特定于列的字符集设置，同样的想法，您必须执行 ALTER TABLE [table] CHANGE COLUMN [settings] 而不指定 CHARACTER SET 所以它回到表默认)