如何更改表的默认排序规则?

create table check2(f1 varchar(20),f2 varchar(20));

creates a table with the default collation latin1_general_ci;

使用默认的collation 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命令的效果是什么呢?

4 个解决方案

#1

503

To change the default character set and collation of a table including those of existing columns (note the convert to clause):

要更改表的默认字符集和排序(包括现有列)(注意转换为子句):

alter table <some_table> convert to character set utf8 collate utf8_unicode_ci;

#2

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.

E。g如果更改数据库的默认排序规则，则在该数据库中创建的每个新表将使用该排序规则，如果更改表的默认排序规则，则在该表中创建的每个列将获得该排序规则。

#3

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在处理这个问题上有一些奇怪的行为。

#4

may need to change the SCHEMA not only table

可能不仅需要更改模式表

ALTER SCHEMA <database name> DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci (as Rich said - utf8mb4);

修改模式 <数据库名称> 默认字符集utf8默认比对utf8_general_ci(如Rich所说- utf8mb4);

(mariaDB 10)

#1

503