- 查看MySQL数据库服务器和数据库MySQL字符集
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 | D:\MySQL Server 5.0\share\charsets\ |......
+--------------------------+-------------------------------------+------
- 查看MySQL数据表(table)的MySQL字符集。
mysql> show table status from sqlstudy_db like '%countries%';
+-----------+--------+---------+------------+------+-----------------+------
| Name | Engine | Version | Row_format | Rows | Collation |......
+-----------+--------+---------+------------+------+-----------------+------
| countries | InnoDB | 10 | Compact | 11 | utf8_general_ci |......
+-----------+--------+---------+------------+------+-----------------+------
- 查看MySQL数据列(column)的MySQL字符集。
mysql> show full columns from countries;
+----------------------+-------------+-----------------+--------
| Field | Type | Collation | .......
+----------------------+-------------+-----------------+--------
| countries_id | int(11) | NULL | .......
| countries_name | varchar(64) | utf8_general_ci | .......
| countries_iso_code_2 | char(2) | utf8_general_ci | .......
| countries_iso_code_3 | char(3) | utf8_general_ci | .......
| address_format_id | int(11) | NULL | .......
+----------------------+-------------+-----------------+--------
- 设置字符集
方案一:
设置 init_connect动态变量,set global init_connect=”SET NAMES utf8”, 同时在my.cnf中的[mysqld]下添加上init_connect=’SET NAMES utf8’
方案二:
由于init_connect的设置对于有SUPPER权限的用户,如root@127.0.0.1等不起作用,所以在my.cnf中的[mysql]或[client]下default-character-set =utf8. mysqld下设置 character-set-server=utf8
windows设置:
修改my.ini配置文件 [client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] default-character-set=utf8 其中[mysqld] default-character-set=utf8 对应的就是修改启动DATABASE时编码
注释:
单独set设置的只要重启MySQL都会失效;
方案一针对所有非SUPPER权限用户,包括你应用程序连接。
方案二中的设置中针对本机连接,处于/etc/mysql/my.cnf文件。
Linux my.cnf目录 /etc/mysql/my.cnf 目录查询. find / -name ‘my.cnf’
windows 是my.ini