一、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-8LC_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
(二)总结
建库、建表语句导出,并且通过sed批量将字符集修改为utf8
导出MySQL库中数据
修改MySQL客户端和服务端字符集为utf8
删除原有的库、表及数据
导入新的建库、建表语句
导入数据