mysql字符乱码

1）MySQL插入中文数据乱码问题：

模拟乱码现象：mysql> create database kitty;Query OK, 1 row affected (0.00 sec)mysql> show create database kitty\G;*************************** 1. row ***************************       Database: kittyCreate Database: CREATE DATABASE `kitty` /*!40100 DEFAULT CHARACTER SET utf8 */1 row in set (0.00 sec)ERROR: No query specifiedmysql> alter database kitty character set latin1;Query OK, 1 row affected (0.01 sec)mysql> flush privileges；mysql> use kitty;Database changed创建teacher表（字符集为latin1）：CREATE TABLE `teacher` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT '0',  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1插入两条数据，其中一行带有中文字符mysql> insert into teacher values(1,'wanlong','31','Server'),(2,'laomao','31','售后部');Query OK, 2 rows affected, 1 warning (0.00 sec)Records: 2  Duplicates: 0  Warnings: 1mysql> flush privileges;Query OK, 0 rows affected (0.00 se查看发现有乱码出现：mysql> select * from teacher;+----+---------+-----+--------+| id | name    | age | dept   |+----+---------+-----+--------+|  1 | wanlong |  31 | Server ||  2 | laomao  |  31 | ???    |+----+---------+-----+--------+2 rows in set (0.00 sec)

2) 如何解决乱码了

不乱码的思想：建议中英文混合的环境，选择utf-8

客户端：

set names utf8（临时生效）

修改my.cnf(永久生效）

[client]

default-character-set=utf8

服务端-库-表-程序

服务端：[mysqld]default-character-set=utf8（适合5.1及以前的版本）character-set-server=utf8（适合5.5）库：查看数据库的编码：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       | /application/mysql-5.5.32/share/charsets/ |+--------------------------+-------------------------------------------+8 rows in set (0.00 sec)表：查看表的编码：mysql> show create table teacher\G;*************************** 1. row ***************************       Table: teacherCreate Table: CREATE TABLE `teacher` (  `id` int(4) NOT NULL,  `name` char(20) CHARACTER SET latin1 NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT '0',  `dept` varchar(16) CHARACTER SET latin1 DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specifiedmysql> select * from teacher;+----+---------+-----+--------+| id | name    | age | dept   |+----+---------+-----+--------+|  1 | wanlong |  31 | Server ||  2 | laomao  |  31 | ???    ||  3 | kobe    |  35 | ???    |+----+---------+-----+--------+3 rows in set (0.00 sec)mysql> show create table teacher\G;*************************** 1. row ***************************       Table: teacherCreate Table: CREATE TABLE `teacher` (  `id` int(4) NOT NULL,  `name` char(20) CHARACTER SET latin1 NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT '0',  `dept` varchar(16) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specified修改字段的字符集：mysql> alter table `teacher` change `dept` `dept` varchar(16) CHARACTER SET utf8 NOT NULL;mysql> alter table `teacher` change `name` `name` char(20) CHARACTER SET utf8 NOT NULL;mysql> show full columns from teacher;+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+--| Field | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | C+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+--| id    | int(4)      | NULL            | NO   |     | NULL    |       | select,insert,update,references |  | name  | char(20)    | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references |  | age   | tinyint(2)  | NULL            | NO   |     | 0       |       | select,insert,update,references |  | dept  | varchar(16) | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references |  +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+--4 rows in set (0.00 sec)mysql> select * from teacher;+----+---------+-----+--------+| id | name    | age | dept   |+----+---------+-----+--------+|  1 | wanlong |  31 | Server ||  2 | laomao  |  31 | ???    ||  3 | kobe    |  35 | ???    |+----+---------+-----+--------+3 rows in set (0.00 sec)测试再次插入带有中文字符的字段：mysql> insert into teacher values(4,'万龙',30,'校园网'),(5,'知行',29,'华东院');Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from teacher;+----+---------+-----+-----------+| id | name    | age | dept      |+----+---------+-----+-----------+|  1 | wanlong |  31 | Server    ||  2 | laomao  |  31 | ???       ||  3 | kobe    |  35 | ???       ||  4 | 万龙    |  30 | 校园网    ||  5 | 知行    |  29 | 华东院    |+----+---------+-----+-----------+5 rows in set (0.00 sec)

3）老数据仍旧是乱码!

说明：

a）对于已有数据库想修改字符集不能直接通过"alter database kitty character set *"或者"alter table tablename character set *"，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或记录生效。

b）已经有记录的字符的调整，必须先将数据导出，经过修改字符集后重新导入后才可完成

修改数据库默认编码：

"alter database kitty character set *"

4）参考解决方法：

如何更改生产MySQL数据库库表的字符集

1、导出表结构mysqldump -uroot -predhat12345 -S /data/3306/mysql.sock --default-character-set=latin1 -d kitty>kittytable.sql说明：-d只导表结构2、编辑kittytable.sql，将lantin1改成utf8可以用sed批量修改3、确保数据库不再更新，导出所有数据mysqldump -uroot -predhat12345 -S /data/3306/mysql.sock --quick --no-create-info --extended-insert --default-character-set=latin1 kitty>kittydata.sql4、打开kittydata.sql，将set names latin1修改成set names utf85、删除原有的库表及数据（需要慎重）6、建库create database kitty default charset utf8;7、创建表，执行kittytable.sqlmysql -uroot -predhat12345 -S /data/3306/mysql.sock kitty

5）执行SQL文件插入中文数据不乱码实战：
a、将需要更新的sql语句放到文本文件中
mysql> system cat test.sqlset names utf8;insert into teacher values (6,'张飞',45,'商务部');
b、通过source来调用sql文件
mysql> source test.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.01 sec)mysql> select * from teacher;+----+---------+-----+-----------+| id | name    | age | dept      |+----+---------+-----+-----------+|  1 | wanlong |  31 | Server    ||  2 | laomao  |  31 | ???       ||  3 | kobe    |  35 | ???       ||  4 | 万龙    |  30 | 校园网    ||  5 | 知行    |  29 | 华东院    ||  6 | 张飞    |  45 | 商务部    |+----+---------+-----+-----------+6 rows in set (0.00 sec)