如何将所有行的排序规则从latin1_swedish_ci更改为utf8_unicode_ci? [英] How to change collation of all rows from latin1_swedish_ci to utf8_unicode_ci?

问题描述

在开发过程中，我无知地对数据库中的所有varchar行使用了默认的latin1_swedish_ci字符编码，并且我确定这是我一直遇到的字符编码问题的根源.除此之外，似乎当今大多数人都建议使用utf8_unicode_ci.

I ignorantly used the default latin1_swedish_ci character encoding for all of the varchar rows in my database during development and I've determined that this is the root of the character encoding problems I've been having. In addition to that, it seems like most people these days are recommending that utf8_unicode_ci be used.

我想将数据库中所有行的字符编码从latin1_swedish_ci转换为utf8_unicode_ci，但是我唯一知道的方法是在phpMyAdmin中逐行更改它，这确实很耗时.

I'd like to convert the character encoding for all rows in my database from latin1_swedish_ci to utf8_unicode_ci, but the only way I know how to do is is change it row-by-row in phpMyAdmin, which is really time consuming.

是否有更快的方法，例如可以运行的查询将所有varchar/文本行的排序规则从latin1_swedish_ci更改为utf8_unicode_ci?

Is there a faster way, such as a query that can be run that changes the collation of all varchar/text rows from latin1_swedish_ci to utf8_unicode_ci?

推荐答案

如果列使用默认表字符集，则每个表只需要转换一个查询:

If the columns are using the default table character set then it's just one query per table to convert:

ALTER TABLE t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

如果字符集是在每一列上分别设置的，则AFAIK无法直接在MySql中对数据库中所有表的所有列进行此设置，但是您可以使用自己选择的语言编写一个微型程序来做到这一点

If the character set is set individually on each column, AFAIK there is no way to do that on all columns of all tables in the database directly in MySql, but you could write a tiny program in your language of choice that does so.

您的程序将查询 INFORMATION_SCHEMA.COLUMNS 表，并查看CHARACTER_SET_NAME列:

SELECT * FROM `INFORMATION_SCHEMA.COLUMNS`
WHERE TABLE_SCHEMA = 'dbname' AND CHARACTER_SET_NAME = 'latin1'

对于每个结果行，在现场适当地合成并执行ALTER TABLE查询以适当地更改字符集和排序规则很简单:

For each result row it's trivial to synthesize and execute an ALTER TABLE query on the spot that changes the character set and collation appropriately:

ALTER TABLE t MODIFY col TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

在上面的查询t中，col和TEXT将是INFORMATION_SCHEMA.COLUMNS结果集中的TABLE_NAME，COLUMN_NAME和DATA_TYPE列的值.

In the above query t, col and TEXT would be the values of the TABLE_NAME, COLUMN_NAME and DATA_TYPE columns from the INFORMATION_SCHEMA.COLUMNS result set.

这篇关于如何将所有行的排序规则从latin1_swedish_ci更改为utf8_unicode_ci?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！