mysql提供了四个等级的默认字符集以及比较规则,分别是服务器、数据库、表、列级别的。文档原话是:There are default settings for character sets and collations at four levels: server, database, table, and column. The description in the following sections may appear complex, but it has been found in practice that multiple-level defaulting leads to natural and obvious results.
2、针对第二个问题,使用character_set_connetion环境变量来回答: character_set_connection ,MySQL server 接收到用户查询后,按照character_set_client将其转化为character_set_connection设定的字符集,一般就是所操作的表对应的编码集。
4.针对第四个问题,使用上面提到的四个等级的默认字符集以及排序规则,即character_set_server、character_set_database以及建立表时的DEFAULT CHARACTER SET=xxx和指定字段时的DEFAULT CHARACTER SET=xxx来回答:
character_set_server决定了服务器的默认编码,character_set_database决定了新建数据库的默认字符集,而数据库的字符集又决定了新建表的默认字符集,而表的字符集又决定了字段的默认字符集,如果没有通过DEFAULT CHARACTER SET=xxx来改变表的字符集,则新表就使用character_set_database指定的字符集。
character_set_system表示元数据的字符集,默认就是utf8,而且不要去更改它,否则,因为类似于用户名密码这种东西,可能用各种奇葩的字符去表示,只有utf8能够容纳它们。如果变成了别的字符集,那么用户名和密码就不能用你想要的字符去表示了。需要注意的是,这个character_set_system也好,character_set_dababase、character_set_server也好,都指标是在数据库内部的保存格式,而不是返回到客户端的编码格式,返回到客户端的结果都会转化为character_set_results指定的字符集之后再返回,官方文档原话是“Storage of metadata using Unicode does not mean that the server returns headers of columns and the results of DESCRIBE functions in the character_set_system character set by default. When you use SELECT column1 FROM t, the name column1 itself is returned from the server to the client in the character set determined by the value of the character_set_results system variable, which has a default value of latin1.”。