MySQL 如何一次性将 Collation 更改为 utf8_bin
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4784168/
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 to utf8_bin in one go
提问by laukok
I have set up the Collation of all my database tables as latin1_swedish_ciand now I realise that I should use utf8_binor utf8_general_ci.
我已经设置了所有数据库表的排序规则，latin1_swedish_ci现在我意识到我应该使用utf8_binor utf8_general_ci。
How can I change the Collation in the tables to utf8_binor utf8_general_ciin one go? Can I use a query or something?
如何将表格中的排序规则更改为utf8_bin或utf8_general_ci一次性更改？我可以使用查询或其他东西吗？
回答by John Parker
You'll simply need to run an ALTER on each of the tables as follows:
您只需要对每个表运行 ALTER，如下所示：
ALTER TABLE <table name> COLLATE utf8_general_ci;
If you also need to update the existing character encoding (unlikely by the sounds of things), you can use:
如果您还需要更新现有的字符编码（不太可能），您可以使用：
ALTER TABLE <table name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
回答by Enbee
You can also update the database collation with:
您还可以使用以下命令更新数据库排序规则：
ALTER DATABASE `DATABASE_NAME` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
回答by Arnaud Le Blanc
You can change the collation of a table with ALTER TABLE:
您可以使用以下命令更改表的排序规则ALTER TABLE：
alter table table_name collate=utf8_general_ci;
回答by sivi
Here are two ways. First one worked for me. From the terminal (Just remember to backup before.)
这里有两种方法。第一个为我工作。从终端（请记住之前备份。）
mysql --database=dbname -B -N -e "SHOW TABLES" | awk '{print "ALTER TABLE", , "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql --database=dbname &
Source: Commandlineinfu.com
资料来源：Commandlineinfu.com
From MySQL you will have to use the Concat command
在 MySQL 中，您必须使用 Concat 命令
SELECT CONCAT('ALTER TABLE `', tbl.`TABLE_SCHEMA`, '`.`', tbl.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM `information_schema`.`TABLES` tbl WHERE tbl.`TABLE_SCHEMA` = 'dbname'
