- 问题引入:
我们经常会遇到一些向MySQL数据库中插入中文,但是select出来的时候,却发现是乱码的情况。如我们向表a出入这样一段记录:i
insert into a values('你好helloworld你好','helloworld');
可能当你访问它的时候,会发现他的结果变成如下图所示:
那怎么样才能解决这种问题呢?通过下文对MySQL中字符集的一些操作,你将会得到答案!
- 查看库、表字符集命令:
要解决字符集的问题,首先要知道现在的系统、数据库、表、客户端等使用什么样的字符集,以及系统支持什么字符集等,下面介绍一些获取相关信息的语句:
1.查看数据库支持的所有字符集
show character set;或者show char set;
2.查看当前状态,里面当然包括字符集的设置:
status或者/s
其中Db characterset对应的是数据库目录下的db.opt文件内容:
3.查看系统字符集设置,包括所有的字符集设置:
show variables like '%char%';
得出如何所示结果:
其中的含义如下:
关于connection相关的字符集的官方文档:
- What character set is the statement in when it leaves the client?
The server takes the character_set_client system variable to be the character set in which statements are sent by the client.
- What character set should the server translate a statement to after receiving it?
For this, the server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.
- What character set should the server translate to before shipping result sets or error messages back to the client?
从上文中可以看出character_set_connection、character_set_client、character_set_results三个字符集什么时候用到。从实际上可以看到,当客户端连接服务器的时候,它会将自己想要的字符集名称发给mysql服务器,然后服务器就会使用这个字符集去设置character_set_connection、character_set_client、character_set_results这三个值。如cmd是用gbk,而mysql workbench是用utf8.
CMD:
MySql WorkBench:
4.查看数据表中字符集设置:
show full columns from tablename;
show create table tablename/G;
5.查看数据库编码:
show create database dbname;
- 创建时指定字符集:
知道了怎么查找字符集的相关信息之后,我们就要懂得怎么在创建指定对象的时候,为该对象匹配相应的字符集。
1.服务器级:
在安装MySQL时可以设置服务器的默认编码格式,也可对my.ini做修改,修改[mysqld]里面的character_set_server=utf8,则可设置character_set_server的值。
2.数据库级:
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8;
注意,如果不指定默认的字符集,则系统会根据character_set_database的值进行设置,如:
3.表级:
CREATE TABLE `db_name`.`tb_name` (id VARCHAR(20) NOT NULL,name VARCHAR(20) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
从下图可看出,定义表的默认字符集为utf8,即使character_set_database为gbk,但是表的列都未utf8
但要注意,如果没有定义表的默认字符集,则他会按照character_set_database的值来设置,如图所示:
4.列级:
CREATE TABLE `db_name`.`tb_name` ( id varchar(20) NOT NULL, name varchar(20) CHARACTER SET utf8 );
从下图可以看到,整个表的默认字符集为gbk,所以没有指定字符集的列都用默认的字符集,而指定了字符集的列name,则使用指定的字符集utf8。
- 修改字符集命令
如果已经是创建好的对象,那又应该如何处理呢。我们就应该对指定对象就行修改字符集的操作。
1.修改character_set_connection、character_set_client、character_set_results三值:
对于某一个连接来说,可以使用:
SET NAMES 'charset_name' [COLLATE 'collation_name']
命令
SET NAMES 'charset_name' [COLLATE 'collation_name']
相当于
SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name;
另外、还可以修改配置文件,对[mysql]下增加default-character-set=utf8,配置成你想要的字符集。(个人尝试在my.ini里面配置过,没有成效,不知道是不是被使用的客户端想要的字符集给覆盖掉了呢?)
2.修改character_set_database字段:
ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]
3.修改character_set_server字段:
最简单的方法是直接改my.ini配置文件里面[mysqld]的字段,增加character-set-server=gbk,然后重启mysqld,则可改为你想要的字符集。
4.修改表的字符集:
ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]
5.修改列的字符集:
col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name] [COLLATE collation_name]
例如:
ALTER TABLE t1 MODIFY col1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci;
个人经验:
我们需要修改MySQL的默认编码,编辑my.ini(MySQL配置文件)文件对编码进行修改
设置MySQL的默认字符集为utf8,找到客户端配置[client]在下面添加。
default-character-set=utf8
设定MySQL数据库以utf8编码运行,连接MySQL数据库时使用utf8编码
停止和重新启动MySQL
net stop mysql
net start mysql
default-character-set=utf8
找到服务器配置[mysqld]在下面添加