这是一种将MySQL表从latin1转换为utf-8的安全方法？ [英] Is this a safe way to convert MySQL tables from latin1 to utf-8?

问题描述

我需要将我的数据库中的所有表从latin1更改为utf-8（使用utf8_bin整理）。

我已经转储了数据库，测试数据库，并对每个表运行以下，而不出现任何错误或警告：

  ALTER TABLE tablename CONVERT TO CHARSET utf8 COLLATION utf8_bin 
  

我真的可以在真实数据库上重复这么做吗？

解决方案

有三种情况需要考虑：

这些值确实是使用Latin1编码的

这是一致的情况：declaration charset和content encoding match。

使用您建议的命令：

  ALTER TABLE tablename CONVERT TO CHARSET utf8 COLLATE utf8_bin 
  

请注意， CONVERT TO CHARACTER SET 命令只出现在MySQL 4.1.2中，因此任何使用2005之前安装的数据库的人都必须使用导出/导入技巧。这是为什么有这么多遗留脚本和文档在互联网上这样做的旧方式。

这些值已经使用utf8编码

在这种情况下，你不想让mysql转换任何数据，只需要更改列的元数据。

先将类型更改为BLOB，然后再将每个列的TEXT utf8更改为类型，以便没有值转换：

  ALTER TABLE t1 CHANGE c1 c1 BLOB; 
 ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8 
  

这是推荐的方式，则会在更改表语法文档中明确记录。 / p>

不同编码中使用的值

在某些Linux发行版中，默认编码为Latin1几年。在这种情况下，您必须使用两种技术的组合：

• 修复表元数据，使用BLOB类型技巧


• 使用 CONVERT TO 转换值。

I need to change all the tables in one of my databases from latin1 to utf-8 (with utf8_bin collation).

I have dumped the database, created a test database from it, and run the following without any errors or warnings for each table:

ALTER TABLE tablename CONVERT TO CHARSET utf8 COLLATION utf8_bin

Is it safe for me to repeat this on the real database? The data seems fine by inspection...

解决方案

There are 3 different cases to consider:

The values are indeed encoded using Latin1

This is the consistent case: declared charset and content encoding match. This was the only case I covered in my initial answer.

Use the command you suggested:

ALTER TABLE tablename CONVERT TO CHARSET utf8 COLLATE utf8_bin

Note that the CONVERT TO CHARACTER SET command only appeared in MySQL 4.1.2, so anyone using a database installed before 2005 had to use an export/import trick. This is why there are so many legacy scripts and document on Internet doing it the old way.

The values are already encoded using utf8

In this case, you don't want mysql to convert any data, you only need to change the column's metadata.

For this, you have to change the type to BLOB first, then to TEXT utf8 for each column, so that there are no value conversions:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8

This is the recommended way, and it is explicitely documented in Alter Table Syntax Documentation.

The values use in a different encoding

The default encoding was Latin1 for several years on a some Linux distributions. In this case, you have to use a combination of the two techniques:

• Fix the table meta-data, using the BLOB type trick
• Convert the values using CONVERT TO.

这篇关于这是一种将MySQL表从latin1转换为utf-8的安全方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！