MySQL乱码问题及字符集
一、MySQL乱码问题
(一)为什么乱码
我们有时候往数据库中插入中文会出现乱码,那么为什么会出现乱码呢?实际上这是因为字符集没有统一的缘故,它必须同时满足以下的统一才能保证数据库不乱码:
- Linux系统服务器的语言
- MySQL客户端的字符集
- MySQL服务端的字符集
- MySQL的库、表的字符集
- 开发程序的字符集
也就是说上面的字符集都统一才不会出现乱码问题。下面就来看看如何处理每一种情况。
(二)字符集
1、什么是字符集
字符集就是一套文字符号及其编码、比较规则。
MySQL数据库字符集包括字符集(CHARACTER)和校对规则(COLLATION)两个概念。其中,字符集是用来定义MySQL数字字符串的存储方式。而校对规则是定义比较字符串的方式。
2、常见字符集
常用字符集 | 长度 | 说明 |
GBK | 2 | 非国际标准 |
UTF-8 | 3 | 中英文混合环境,建议使用 |
latin1 | 1 | MySQL默认字符集 |
utf8mb4 | 4 | UTF-8 Unicode,用于移动互联网 |
3、如何选择字符集
- 如果处理各种文字,并且发布到不同国家,MySQL选择UTF-8字符集
- 如果只需要支持中文,并且数据量大、性能高,MySQL选择GBK字符集
- 如果处理移动互联网,MySQL选择utf8mb4字符集
(三)解决乱码
1、Linux系统语言选择
# 查看系统当前的语言 [root@hadoop-slave1 /]# echo $LANG en_US.UTF-8 # 查看系统支持的语言,如果没有中文可进行安装yum groupinstall chinese-support [root@hadoop-slave1 /]# locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL=
然后,可进行系统语言设置,假设使用的都是utf8字符集:
# vim /etc/sysconfig/i18n LANG="zh_CN.UTF-8" SYSFONT="latarcyrheb-sun16"
2、MySQL客户端的字符集
- 临时修改
mysql> set names utf8;
这种临时修改,一旦退出MySQL客户端,重新登录就失效了。
- 永久修改
通过更改/etc/my.cnf下客户端的配置参数,可实现临时修改的效果,并且是永久生效的。
# vim /etc/my.cnf [client] default-character-set=utf8
这种永久修改无需重启MySQL服务,只需要退出重新登录就可以生效。
3、MySQL服务端的字符集
[mysqld] default-character-set=utf8 #适合5.1及以前的版本 character-set-server=utf8 #适合5.5版本
修改完毕后重启服务即可。
4、MySQL的库、表的字符集
# 建库指定字符集 create database crm DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci; # 建表指定字符集 CREATE TABLE `userinfo` ( `id` int(4) NOT NULL AUTO_INCREMENT, `username` char(20) NOT NULL, PRIMARY KEY(`id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8
(四)查看设置情况
在上面设置完毕后,可以查看字符集的情况:
mysql> show variables like 'character_set%'; +--------------------------+-------------------------------------------+ | 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 | /application/mysql-5.5.32/share/charsets/ | +--------------------------+-------------------------------------------+ 8 rows in set (0.00 sec)
对于上面的参数,有下面的解释:
Variable_name | Value |
character_set_client | 客户端字符集 |
character_set_connection | 连接字符集 |
character_set_database | 数据库字符集(配置文件指定或者建库、建表指定) |
character_set_results | 返回结果字符集 |
character_set_server | 服务器字符集(配置文件指定或者建库、建表指定) |
我们知道在上面更改了linux的字符集、MySQL客户端字符集、MySQL服务端字符集、MySQL库表字符集,那么对应的上面的参数那个改变了呢?
当改变了Linux系统的字符集后。character_set_client、character_set_connection、character_set_results这三者的字符集都与系统的保持一致了。
当改变了MySQL客户端字符集后。character_set_client、character_set_connection、character_set_results这三者的字符集都被改成了与MySQL客户端的字符集。比如,set names utf8,这样前面的三个参数酒杯改成了utf8的字符集。这三个参数默认是与系统的保持一致。
当改变了MySQL服务端字符集后。比如,在my.cnf配置文件中设置服务端字符集,character_set_database、character_set_server这二者的字符集会与服务端配置文件中的一致。
二、生产环境MySQL字符集更改
对于已经插入到数据库中的数据,通过“alter database character set *” 或者“alter tablename character set *”并不能更改已经插入到数据库中数据的字符集,只是对新创建的表或者记录生效。
对于已经插入到数据库中数据字符集的调整,必须先将数据导出,待字符集修改完毕后再将数据导入即可。
(一)执行步骤
1、导出建库、建表结构
mysqldump -uroot -p --default-character-set=latin1 -d dbname>alltable.sql # 说明:--default-character-set=latin1表示以latin1字符集进行连接;-d 只导出表结构
2、修改建库、建表字符集
修改上述导出的alltable.sql文件,将字符集latin1修改为你要更改的字符集,比如utf8。
3、导出数据库中数据
确保数据库中数据不再更新,然后到处所有的数据。
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 dbname>alltada.sql """ 说明: --quick 用于转储大的表,强制mysqldump从服务器一次一行的检索数据而不是检索所有行,并且输出前CACHE到内存中 --no-create-info 不创建CREATE TABLE语句 --extended-insert 使用包括几个VALUES列表的多行INSERT语法,这样文件更小、IO小,导出数据更快 --default-character-set=latin1 按照原有字符集导出数据,这样导出的中文不会乱码 """
4、修改导出的数据文件
修改导出的alldata.sql文件,将set names latin1修改为你需要更改的字符集(比如:set names utf8,或者修改Linux系统的客户端和服务端)
5、建库、建表、导入数据
# 建库 create database dbname default charset utf8; #建表,导入alldata.sql文件中的建表语句和数据 mysql -uroot -p dbname<alldata.sql
(二)总结
- 建库、建表语句导出,并且通过sed批量将字符集修改为utf8
- 导出MySQL库中数据
- 修改MySQL客户端和服务端字符集为utf8
- 删除原有的库、表及数据
- 导入新的建库、建表语句
- 导入数据