查看数据库字符集
进入MySQL之后,运行语句:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ |
这样可以看到MySQL所使用的字符集。
开始更改设置
查看当前MySQL所使用的配置文件的顺序
1 2 3 | $ mysql --help | grep Default -A 1 Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf |
修改配置文件
第一次安装MySQL,/etc/my.cnf
文件应该是不存在的。所以打开/etc/mysql/my.cnf
:
1
| vi /etc/mysql/my.cnf
|
在以下3个部分都做相应改动:
(1)在[client]字段里加入default-character-set=utf8,如下:
1 2 3 4 | [client] port = 3306 socket = /var/lib/mysql/mysql.sock default-character-set=utf8 |
(2)在[mysqld]字段里加入character-set-server=utf8,如下:
1 2 3 4 | [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock character-set-server=utf8 |
也可以这样改:
1 2 3 4 | [mysqld] collation-server = utf8_unicode_ci init-connect=’SET NAMES utf8′ character-set-server = utf8 |
(3)在[mysql]字段里加入default-character-set=utf8,如下:
1 2 3 | [mysql] no-auto-rehash default-character-set=utf8 |
重启MySQL
1
| sudo service mysql restart
|
查看是否生效
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> show variables like 'char%'; +--------------------------+----------------------------+ | 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/share/mysql/charsets/ | +--------------------------+----------------------------+ |
如果出错了
如果重启失败了,那么可以到/var/log/mysql/error.log
文件中查看输出的启动错误。
修改后,运行sudo service mysql start
来启动服务。
表的字符集
修改了MySQL的默认字符设置,并不能更改已经生效的字符设置。如果某张表的字符仍是乱码,那么说明它的字符集仍然为Latin1。
可以这样查看某张表的建表情况:
1 2 3 | SHOW CREATE TABLE table_name; .....省略一大堆..() ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
所以最佳实践是:
在建表语句中,显式地声明该表所要使用的字符集