MySQL 如何更改表的默认排序规则？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/742205/
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 the default collation of a table?
提问by simplfuzz
create table check2(f1 varchar(20),f2 varchar(20));
creates a table with the default collation latin1_general_ci;
创建一个具有默认排序规则的表latin1_general_ci；
alter table check2 collate latin1_general_cs;
show full columns from check2;
shows the individual collation of the columns as 'latin1_general_ci'.
将列的单独整理显示为“latin1_general_ci”。
Then what is the effect of the alter table command?
那么alter table命令的作用是什么呢？
回答by Nikki Erwin Ramirez
To change the default character set and collation of a table including those of existing columns(note the convert toclause):
要更改表的默认字符集和排序规则，包括现有列的字符集和排序规则（注意convert to子句）：
alter table <some_table> convert to character set utf8mb4 collate utf8mb4_unicode_ci;
Edited the answer, thanks to the prompting of some comments:
由于一些评论的提示，编辑了答案：
Should avoid recommending utf8. It's almost never what you want, and often leads to unexpected messes. The utf8 character set is not fully compatible with UTF-8. The utf8mb4 character set is what you want if you want UTF-8. – Rich Remer Mar 28 '18 at 23:41
应该避免推荐 utf8。这几乎从来都不是你想要的，而且经常会导致意想不到的混乱。utf8 字符集与 UTF-8 不完全兼容。如果你想要 UTF-8，utf8mb4 字符集就是你想要的。– Rich Remer 18 年 3 月 28 日，23:41
and
和
That seems quite important, glad I read the comments and thanks @RichRemer . Nikki , I think you should edit that in your answer considering how many views this gets. See here https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.htmland here What is the difference between utf8mb4 and utf8 charsets in MySQL?– Paulpro Mar 12 at 17:46
这似乎很重要，很高兴我阅读了评论并感谢 @RichRemer 。Nikki ，我认为你应该在你的回答中编辑它，考虑到它有多少浏览量。请参阅此处https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html和此处MySQL 中的 utf8mb4 和 utf8 字符集有什么区别？– Paulpro 3 月 12 日 17:46
回答by fredrik
MySQL has 4 levels of collation: server, database, table, column. If you change the collation of the server, database or table, you don't change the setting for each column, but you change the default collations.
MySQL 有 4 个级别的排序规则：服务器、数据库、表、列。如果更改服务器、数据库或表的排序规则，则不会更改每一列的设置，而是更改默认排序规则。
E.g if you change the default collation of a database, each new table you create in that database will use that collation, and if you change the default collation of a table, each column you create in that table will get that collation.
例如，如果更改数据库的默认排序规则，则在该数据库中创建的每个新表都将使用该排序规则，如果更改表的默认排序规则，则在该表中创建的每一列都将获得该排序规则。
回答by Don Werve
It sets the default collation for the table; if you create a new column, that should be collated with latin_general_ci -- I think. Try specifying the collation for the individual column and see if that works. MySQL has some really bizarre behavior in regards to the way it handles this.
它设置表的默认排序规则；如果您创建一个新列，则应与 latin_general_ci 进行整理 - 我认为。尝试为单个列指定排序规则，看看是否有效。MySQL 在处理这个问题的方式上有一些非常奇怪的行为。
回答by AssyK
may need to change the SCHEMA not only table
可能需要更改 SCHEMA 不仅表
ALTER SCHEMA `<database name>` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci (as Rich said - utf8mb4);
(mariaDB 10)
(玛丽亚DB 10)
