MySQL 如何将所有行的排序规则从 latin1_swedish_ci 更改为 utf8_unicode_ci？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18445969/
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 change collation of all rows from latin1_swedish_ci to utf8_unicode_ci?
提问by Nate
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？
回答by Jon
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.COLUMNStable and look at the CHARACTER_SET_NAMEcolumn:
您的程序将查询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 TABLEquery 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, coland TEXTwould be the values of the TABLE_NAME, COLUMN_NAMEand DATA_TYPEcolumns from the INFORMATION_SCHEMA.COLUMNSresult set.
在上面的查询中t，colandTEXT将是结果集中的TABLE_NAME,COLUMN_NAME和DATA_TYPE列的值INFORMATION_SCHEMA.COLUMNS。
回答by arnoud
You can actually do this inside MySQL, using a procedure.
您实际上可以在 MySQL 中使用过程执行此操作。
Based on https://stackoverflow.com/a/12718767/1612273. It uses the current database, so make sure you're doing it on the right one!
基于https://stackoverflow.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();
