字符集

客户端修改字符集的方法：

第一种

临时有效

mysql> show variables like 'character_set%';

character_set_client utf8 　　 ##--->客户端字符集，setnames latinl;
character_set_connection utf8 　##--->连接字符集，set names latinl;
character_set_database utf8 　　##--->数据库字符集，配置文件（character-set-server = gbk）建库建表指定。
character_set_filesystem binary
character_set_results utf8 　　 ##--->返回结果字符集，set names latinl;
character_set_server utf8 　　 ##--->服务器字符集，配置文件（character-set-server = gbk）
character_set_system utf8　　 ##--->
character_sets_dir /application/mysql-5.6.34/share/charsets/

第二种

特别注意：多实例不要在/data/3306/my.cnf下修改

多实例的MySQL客户端默认读/etc/my.cnf，所以指定客户端字符集就在/etc/my.cnf

无需重启服务，直接退出MySQL在登陆就可以。

在/etc/my.cnf

[client]

default-character-set=gbk

第三种

在登录时指定

[root@db01-51 ~]# mysql --default-character-set=utf8

服务端修改字符集方法

[mysqld]

default-character-set=latinl　　<---适合5.1以及以前的版本

character-set-server=latinl　　<---适合5.5

编译的时候指定服务端字符集：

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

mysql> show create table test;\G
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                  |
+-------+------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1、mysql客户端
a　　set names gbk;修改3个参数，临时生效
b　　[client]
　　default-character-set=gbk

2、mysql服务端
a　　[mysqld]　　　　　　　　　　　　 #用户访问
　　character-set-server=gbk
b　　库的字符集（默认和数据库一样）
c　　表的字符集（默认和库一样）
d　　程序字符集　　　　　　　　　　　　#运维访问=程序访问
e　　linux系统服务器端

[root@localhost ~]# cat /etc/sysconfig/i18n 
LANG="zh_CN.UTF-8"

f　　客户端xshell

修改库

mysql> alter database bbs CHARACTER SET latin1 COLLATE = latin1_swedish_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database bbs\G
*************************** 1. row ***************************
Database: bbs
Create Database: CREATE DATABASE `bbs` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

修改表　

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table test CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

上面修改方法只能适应新数据，库里的老数据字符集还是没有改。　　

下面模拟将latin1字符集的数据库修改为utf8字符集的实际过程。

模拟环境：

mysql> show create database bbs\G
*************************** 1. row ***************************
       Database: bbs
Create Database: CREATE DATABASE `bbs` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

mysql> use bbs
Database changed
mysql> CREATE TABLE test (
    ->    id int(4) NOT NULL AUTO_INCREMENT,
    ->   name char(20) NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ;
Query OK, 0 rows affected (0.09 sec)

mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table test;
+-------+-----------------------------------------------------+
| Table | Create Table                                                                                                                                         |
+-------+-----------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test(id,name) values(1,'gongli');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2,'bingbing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
|  1 | gongli   |
|  2 | bingbing |
+----+----------+
2 rows in set (0.01 sec)

导出建库建表语句，导出所有mysql数据：

[root@db01-51 ~]# mysqldump -S /data/3308/mysql.sock --default-character-set=latin1 -d bbs >alltable.sql
[root@db01-51 ~]# mysqldump -S /data/3308/mysql.sock --quick --no-create-info --extended-insert --default-character-set=latin1 bbs >alldata.sql
[root@db01-51 ~]# ll
total 2
-rw-r--r--  1 root root  1472 Mar 21 22:23 alldata.sql
-rw-r--r--  1 root root  1651 Mar 21 22:22 alltable.sql

恢复：

[root@db01-51 ~]# mysql -S /data/3307/mysql.sock < alltable.sql
[root@db01-51 ~]# mysql -S /data/3307/mysql.sock bbs <alldata.sql

结果：

[root@db01-51 ~]# mysql -S /data/3307/mysql.sock bbs <alldata.sql 
[root@db01-51 ~]# mysql -S /data/3307/mysql.sock -e "select * from bbs.test;"
+----+----------+
| id | name     |
+----+----------+
|  1 | gongli   |
|  2 | bingbing |
+----+----------+
mysql> show create database bbs\G
*************************** 1. row ***************************
       Database: bbs
Create Database: CREATE DATABASE `bbs` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

总结：字符集更改步骤　　　　##--->字符集大的包含小的，由小改大不能反着改

1、建库建表的语句导出，sed批量修改为utf8

2、导出所有mysql数据

3、修改mysql服务端和客户端编码为utf8

4、删除原有的数据库及数据

5、导入新的建库建表语句

6、导入mysql所有数据　

面试题092：请描述MySQL里中文数据乱码的背后原理，以及工作中如何防止数据库数据乱码？

面试题091：如何调整已有MySQL数据库的字符集，例如：从UTF8改成GBK，请给出完整步骤。

posted @ 2017-03-21 22:53 reborn枪阅读(153) 评论(0) 收藏举报

刷新页面返回顶部

reborn枪

字符集

公告