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

模拟乱码现象：mysql>createdatabasekitty;QueryOK,1rowaffected(0.00sec)mysql>showcreatedatabasekitty\G;***************************1.row***************************Database:kittyCreateDatabase:CREATEDATABASE`kitty`/*!40100DEFAULTCHARACTERSETutf8*/1rowinset(0.00sec)ERROR:Noqueryspecifiedmysql>alterdatabasekittycharactersetlatin1;QueryOK,1rowaffected(0.01sec)mysql>flushprivileges；mysql>usekitty;Databasechanged创建teacher表（字符集为latin1）：CREATETABLE`teacher`(`id`int(4)NOTNULL,`name`char(20)NOTNULL,`age`tinyint(2)NOTNULLDEFAULT'0',`dept`varchar(16)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=latin1插入两条数据，其中一行带有中文字符mysql>insertintoteachervalues(1,'wanlong','31','Server'),(2,'laomao','31','售后部');QueryOK,2rowsaffected,1warning(0.00sec)Records:2Duplicates:0Warnings:1mysql>flushprivileges;QueryOK,0rowsaffected(0.00se查看发现有乱码出现：mysql>select*fromteacher;+----+---------+-----+--------+|id|name|age|dept|+----+---------+-----+--------+|1|wanlong|31|Server||2|laomao|31|???|+----+---------+-----+--------+2rowsinset(0.00sec)

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>showvariableslike'%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/|+--------------------------+-------------------------------------------+8rowsinset(0.00sec)表：查看表的编码：mysql>showcreatetableteacher\G;***************************1.row***************************Table:teacherCreateTable:CREATETABLE`teacher`(`id`int(4)NOTNULL,`name`char(20)CHARACTERSETlatin1NOTNULL,`age`tinyint(2)NOTNULLDEFAULT'0',`dept`varchar(16)CHARACTERSETlatin1DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)ERROR:Noqueryspecifiedmysql>select*fromteacher;+----+---------+-----+--------+|id|name|age|dept|+----+---------+-----+--------+|1|wanlong|31|Server||2|laomao|31|???||3|kobe|35|???|+----+---------+-----+--------+3rowsinset(0.00sec)mysql>showcreatetableteacher\G;***************************1.row***************************Table:teacherCreateTable:CREATETABLE`teacher`(`id`int(4)NOTNULL,`name`char(20)CHARACTERSETlatin1NOTNULL,`age`tinyint(2)NOTNULLDEFAULT'0',`dept`varchar(16)NOTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)ERROR:Noqueryspecified修改字段的字符集：mysql>altertable`teacher`change`dept``dept`varchar(16)CHARACTERSETutf8NOTNULL;mysql>altertable`teacher`change`name``name`char(20)CHARACTERSETutf8NOTNULL;mysql>showfullcolumnsfromteacher;+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+--|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|+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+--4rowsinset(0.00sec)mysql>select*fromteacher;+----+---------+-----+--------+|id|name|age|dept|+----+---------+-----+--------+|1|wanlong|31|Server||2|laomao|31|???||3|kobe|35|???|+----+---------+-----+--------+3rowsinset(0.00sec)测试再次插入带有中文字符的字段：mysql>insertintoteachervalues(4,'万龙',30,'校园网'),(5,'知行',29,'华东院');QueryOK,2rowsaffected(0.01sec)Records:2Duplicates:0Warnings:0mysql>select*fromteacher;+----+---------+-----+-----------+|id|name|age|dept|+----+---------+-----+-----------+|1|wanlong|31|Server||2|laomao|31|???||3|kobe|35|???||4|万龙|30|校园网||5|知行|29|华东院|+----+---------+-----+-----------+5rowsinset(0.00sec)


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-dkitty>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=latin1kitty>kittydata.sql4、打开kittydata.sql，将setnameslatin1修改成setnamesutf85、删除原有的库表及数据（需要慎重）6、建库createdatabasekittydefaultcharsetutf8;7、创建表，执行kittytable.sqlmysql-uroot-predhat12345-S/data/3306/mysql.sockkitty<kittytable.sql8、导入数据mysql-uroot-predhat12345-S/data/3306/mysql.sockkitty<kittydata.sql


5）执行SQL文件插入中文数据不乱码实战：

a、将需要更新的sql语句放到文本文件中

mysql>systemcattest.sqlsetnamesutf8;insertintoteachervalues(6,'张飞',45,'商务部');

b、通过source来调用sql文件

mysql>sourcetest.sqlQueryOK,0rowsaffected(0.00sec)QueryOK,1rowaffected(0.01sec)mysql>select*fromteacher;+----+---------+-----+-----------+|id|name|age|dept|+----+---------+-----+-----------+|1|wanlong|31|Server||2|laomao|31|???||3|kobe|35|???||4|万龙|30|校园网||5|知行|29|华东院||6|张飞|45|商务部|+----+---------+-----+-----------+6rowsinset(0.00sec)