ITPub博客

首页 > Linux操作系统 > Linux操作系统 > mysql字符集设定

mysql字符集设定

原创 Linux操作系统 作者:wang_0720 时间:2013-11-13 11:09:36 0 删除 编辑
MYSQL服务器中有六个关键位置使用了字符集的概念,分别是:client 、connection、database、results、server、system. MYSQL有两个和字符集有关的概念。一个是字符集本身,一个是字符集校验规则。字符集影响数据在传输、存储过程中的处理方式,自己校验则影响order by ,group by这些排序方式。
和存储有关的
服务器字符集 character_set_server
库字符集 character_set_database
表字符集
字段字符集
character_set_server:服务器安装时指定的默认字符集设定
character_set_database:数据库服务器中某个库使用的字符集设定,如果建库时没有明确指定,将使用服务器安装时指定的字符集设置。
character_set_system:数据库系统使用的字符集设定
和传输有个的
character_set_connection:连接数据库的字符集设置类型,如果没有明确指定连接数据库使用的字符集类型就安装服务器端默认的字符设置
character_set_results:数据库给客户端返回时使用的字符集设定,如果没有指明,使用服务器默认的字符集。
character_set_client:客户端使用的字符集
字符集的校对规则
字符集的校对规则设定分别有character_set_connection,character_set_database,character_set_server决定
collation_connection:连接字符集的校对规则
collation_database:默认数据库使用的校对规则
collation_server:服务器的默认校对规则
character_set_connection,character_set_client,character_set_results受客户端默认字符集影响,当默认字符集不是utf8时,设置my.cnf
[mysqld]
default-character-set=utf8
default-collation=utf8_general_ci
default-character-set只能改变存储层(server,database,table,column,system)的设定,对客户端和服务端的通讯层则没有任何影响。
解决字符集通讯层设置不匹配的方法:
在mysql服务端的配置文件my.cnf的mysqld下设置
[mysqld]
...
skip-character-set-client-handshake
...
忽略客户端字符集,所有字符集和服务端保持一致。
没加skip-character-set-client-handshake参数时,登录查看字符集为:
[root@agent1 src]# mysql -h 192.168.151.141 -uxxx -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.69-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show variables like '%character%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | latin1                                 |
| character_set_connection | latin1                                 |
| character_set_database   | utf8                                   |
| character_set_filesystem | binary                                 |
| character_set_results    | latin1                                 |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
加了skip-character-set-client-handshake参数时,登录查看字符集为:
[root@agent1 src]# mysql -h 192.168.151.141 -uxxx -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.69-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show variables like '%character%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | utf8                                   |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8                                   |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.01 sec)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27181165/viewspace-776470/,如需转载,请注明出处,否则将追究法律责任。

下一篇: nginx 注意事项
请登录后发表评论 登录
全部评论

注册时间:2013-11-05

  • 博文量
    111
  • 访问量
    913595