如何将所有行的排序规则从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.

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

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.

我想对所有行的字符编码从latin1_swedish_ci转换在我的数据库中utf8_unicode_ci，但唯一的办法，我知道该怎么做的是改变它在phpMyAdmin，这是非常耗时的一行接一行。

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?

有没有更快的方法，例如可以运行的查询将所有varchar / text行的排序规则从latin1_swedish_ci更改为utf8_unicode_ci？

2 个解决方案

#1

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;

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.

如果在每列上单独设置字符集，AFAIK就无法直接在MySql中对数据库中所有表的所有列执行此操作，但您可以使用您选择的语言编写一个小程序。

Your program would query the INFORMATION_SCHEMA.COLUMNS table and look at the CHARACTER_SET_NAME column:

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

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

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查询是很容易的，它会适当地更改字符集和排序规则：

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

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.

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

#2

You can actually do this inside MySQL, using a procedure.

你可以使用一个程序在MySQL内部实现这一点。

Based on https://*.com/a/12718767/1612273. It uses the current database, so make sure you're doing it on the right one!

基于https://*.com/a/12718767/1612273。它使用当前数据库，因此请确保您在正确的数据库中执行此操作！

delimiter //

DROP PROCEDURE IF EXISTS convert_database_to_utf8 //

CREATE PROCEDURE convert_database_to_utf8()
BEGIN
    DECLARE table_name VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;

    DECLARE cur CURSOR FOR
        SELECT t.table_name FROM information_schema.tables t WHERE t.table_schema = DATABASE() AND t.table_type='BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
        tables_loop: LOOP
            FETCH cur INTO table_name;

            IF done THEN
                LEAVE tables_loop;
            END IF;

            SET @sql = CONCAT("ALTER TABLE ", table_name, " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DROP PREPARE stmt;
        END LOOP;
    CLOSE cur;
END //

delimiter ;
call convert_database_to_utf8();

#1