MySQL 如何将数据库中的所有表更改为 UTF8 字符集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2150256/
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 all the tables in my database to UTF8 character set?
提问by nubela
My database is not in UTF8, and I'd like to convert all the tables to UTF8, how can I do this?
我的数据库不是 UTF8，我想将所有表转换为 UTF8，我该怎么做？
采纳答案by nubela
mysqldump --user=username --password=password --default-character-set=latin1 --skip-set-charset dbname > dump.sql
sed -r 's/latin1/utf8/g' dump.sql > dump_utf.sql
mysql --user=username --password=password --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;"
mysql --user=username --password=password --default-character-set=utf8 dbname < dump_utf.sql
回答by Tomasz Zieliński
For single table you can do something like this:
对于单表，您可以执行以下操作：
ALTER TABLE tab CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
For the whole database I don't know other method than similar to this:
对于整个数据库，我不知道与此类似的其他方法：
http://www.commandlinefu.com/commands/view/1575/convert-all-mysql-tables-and-fields-to-utf8
http://www.commandlinefu.com/commands/view/1575/convert-all-mysql-tables-and-fields-to-utf8
回答by Thu 01 Jan 1970 000000 GMT
replace my_database_namewith your database name
替换my_database_name为您的数据库名称
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'my_database_name' AND TABLE_TYPE != 'VIEW';
this will build lots of queries which you can run
这将构建大量您可以运行的查询
回答by tippingpints
Better yet, use Percona's tool kit. I'd audit your indices before updating to utf8mb4 as there are issues with key length.
更好的是，使用 Percona 的工具包。我会在更新到 utf8mb4 之前审核您的索引，因为密钥长度存在问题。
SELECT CONCAT('pt-online-schema-change --alter "CONVERT TO CHARACTER SET utf8
COLLATE utf8_unicode_ci" t=', TABLE_NAME, ',D=DB_NAME,u=USER_NAME --statistics --execute')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE != 'VIEW' AND TABLE_COLLATION NOT LIKE '%utf8%';
